Обсуждение: raw data broken in 7.2 driver?

Good day.

I'm having problems inserting raw data with the 7.2 jdbc driver. I've
tried out most of the ones available for download (Java 2). Some of them
seem to work with raw data in 7.2 but has problems with timestamp
instead. Is there any patch available for this?`For more detailed
description see below (found in the interactive docs).

Comment from Adam Jenkins "<adam@thejenkins.org>":
"The JDBC example given above as example 8-2 does not work as
advertised. I'm using postgresql 7.2, and the jdbc7.2dev-1.2.jar JDBC
driver. It seems that the driver interprets the data it reads from the
InputStream passed to setBinaryStream as a binary representation of the
actual field value -- in this case as a binary representation of an oid.
This is of course incorrect; really the binary data is image data, and
one would hope that what the driver would do is create a new large
object, store the binary data in it, and store the oid of the large
object in the oid field. What really happens is that the driver
constructs a SQL statement which has the binary data which was read from
the InputStream embedded directly in it as the value of the oid field.
So executeUpdate throws a SQLException like this:

java.sql.SQLException: ERROR: oidin: error in "213"

regards David

David,

Can you send a test example which demonstrates the problems?

Dave
On Mon, 2002-04-08 at 07:03, David Bernhill wrote:
> Good day.
>
> I'm having problems inserting raw data with the 7.2 jdbc driver. I've
> tried out most of the ones available for download (Java 2). Some of them
> seem to work with raw data in 7.2 but has problems with timestamp
> instead. Is there any patch available for this?`For more detailed
> description see below (found in the interactive docs).
>
> Comment from Adam Jenkins "<adam@thejenkins.org>":
> "The JDBC example given above as example 8-2 does not work as
> advertised. I'm using postgresql 7.2, and the jdbc7.2dev-1.2.jar JDBC
> driver. It seems that the driver interprets the data it reads from the
> InputStream passed to setBinaryStream as a binary representation of the
> actual field value -- in this case as a binary representation of an oid.
> This is of course incorrect; really the binary data is image data, and
> one would hope that what the driver would do is create a new large
> object, store the binary data in it, and store the oid of the large
> object in the oid field. What really happens is that the driver
> constructs a SQL statement which has the binary data which was read from
> the InputStream embedded directly in it as the value of the oid field.
> So executeUpdate throws a SQLException like this:
>
> java.sql.SQLException: ERROR: oidin: error in "213"
>
> regards David
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>
>

Sure, however the problem seem to be that I didn't read the
documentation regarding the 7.2 driver properly. Doing
setBinaryStream/setBytes on a column declared as OID in the 7.2 driver
throws an exception where the converted byte[] is attached as a message.

regards David

> David,
>
> Can you send a test example which demonstrates the problems?
>
> Dave
> On Mon, 2002-04-08 at 07:03, David Bernhill wrote:
> > Good day.
> >
> > I'm having problems inserting raw data with the 7.2 jdbc driver. I've
> > tried out most of the ones available for download (Java 2). Some of them
> > seem to work with raw data in 7.2 but has problems with timestamp
> > instead. Is there any patch available for this?`For more detailed
> > description see below (found in the interactive docs).
> >
> > Comment from Adam Jenkins "<adam@thejenkins.org>":
> > "The JDBC example given above as example 8-2 does not work as
> > advertised. I'm using postgresql 7.2, and the jdbc7.2dev-1.2.jar JDBC
> > driver. It seems that the driver interprets the data it reads from the
> > InputStream passed to setBinaryStream as a binary representation of the
> > actual field value -- in this case as a binary representation of an oid.
> > This is of course incorrect; really the binary data is image data, and
> > one would hope that what the driver would do is create a new large
> > object, store the binary data in it, and store the oid of the large
> > object in the oid field. What really happens is that the driver
> > constructs a SQL statement which has the binary data which was read from
> > the InputStream embedded directly in it as the value of the oid field.
> > So executeUpdate throws a SQLException like this:
> >
> > java.sql.SQLException: ERROR: oidin: error in "213"
> >
> > regards David
> >
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 5: Have you checked our extensive FAQ?
> >
> > http://www.postgresql.org/users-lounge/docs/faq.html
> >
> >
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

Hello Barry.

Great! Thanks for your answer. Are there any documentation/discussion
concerning the performance of the jdbc driver for postgresql (or some
tuning parameters that can be of interest)? Are there any figures
related to "blob" performance? I takes me about 11 seconds to insert 1mb
raw data in postgresql, compared to 1.5 second in oracle. I tried the
bytea type as well, but the result was more or less the same.

thanks, David

> David,
>
> The behavior you are describing is correct for the 7.2 driver.  In 7.2 the driver now uses the bytea datatype to
storebinary data (not largeobjects as it did in 7.1 and earlier).  This is documented in the 7.2 documentation.  Also
inthe doc is a description on how to revert back to the old behavior.   
>
> Thanks,
> --Barry
> -----Original Message-----
> From: David Bernhill <david.bernhill@digitalroute.com>
> Date: 08 Apr 2002 13:03:15
> To: pgsql-jdbc@postgresql.org
> Subject: [JDBC] raw data broken in 7.2 driver?
>
> Good day.
>
> I'm having problems inserting raw data with the 7.2 jdbc driver. I've
> tried out most of the ones available for download (Java 2). Some of them
> seem to work with raw data in 7.2 but has problems with timestamp
> instead. Is there any patch available for this?`For more detailed
> description see below (found in the interactive docs).
>
> Comment from Adam Jenkins "<adam@thejenkins.org>":
> "The JDBC example given above as example 8-2 does not work as
> advertised. I'm using postgresql 7.2, and the jdbc7.2dev-1.2.jar JDBC
> driver. It seems that the driver interprets the data it reads from the
> InputStream passed to setBinaryStream as a binary representation of the
> actual field value -- in this case as a binary representation of an oid.
> This is of course incorrect; really the binary data is image data, and
> one would hope that what the driver would do is create a new large
> object, store the binary data in it, and store the oid of the large
> object in the oid field. What really happens is that the driver
> constructs a SQL statement which has the binary data which was read from
> the InputStream embedded directly in it as the value of the oid field.
> So executeUpdate throws a SQLException like this:
>
> java.sql.SQLException: ERROR: oidin: error in "213"
>
> regards David
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html

David,

This is quite a difference? Are there any network differences that would
account for the difference?

Dave
On Tue, 2002-04-09 at 08:50, David Bernhill wrote:
> Hello Barry.
>
> Great! Thanks for your answer. Are there any documentation/discussion
> concerning the performance of the jdbc driver for postgresql (or some
> tuning parameters that can be of interest)? Are there any figures
> related to "blob" performance? I takes me about 11 seconds to insert 1mb
> raw data in postgresql, compared to 1.5 second in oracle. I tried the
> bytea type as well, but the result was more or less the same.
>
> thanks, David
>
> > David,
> >
> > The behavior you are describing is correct for the 7.2 driver.  In 7.2 the driver now uses the bytea datatype to
storebinary data (not largeobjects as it did in 7.1 and earlier).  This is documented in the 7.2 documentation.  Also
inthe doc is a description on how to revert back to the old behavior.   
> >
> > Thanks,
> > --Barry
> > -----Original Message-----
> > From: David Bernhill <david.bernhill@digitalroute.com>
> > Date: 08 Apr 2002 13:03:15
> > To: pgsql-jdbc@postgresql.org
> > Subject: [JDBC] raw data broken in 7.2 driver?
> >
> > Good day.
> >
> > I'm having problems inserting raw data with the 7.2 jdbc driver. I've
> > tried out most of the ones available for download (Java 2). Some of them
> > seem to work with raw data in 7.2 but has problems with timestamp
> > instead. Is there any patch available for this?`For more detailed
> > description see below (found in the interactive docs).
> >
> > Comment from Adam Jenkins "<adam@thejenkins.org>":
> > "The JDBC example given above as example 8-2 does not work as
> > advertised. I'm using postgresql 7.2, and the jdbc7.2dev-1.2.jar JDBC
> > driver. It seems that the driver interprets the data it reads from the
> > InputStream passed to setBinaryStream as a binary representation of the
> > actual field value -- in this case as a binary representation of an oid.
> > This is of course incorrect; really the binary data is image data, and
> > one would hope that what the driver would do is create a new large
> > object, store the binary data in it, and store the oid of the large
> > object in the oid field. What really happens is that the driver
> > constructs a SQL statement which has the binary data which was read from
> > the InputStream embedded directly in it as the value of the oid field.
> > So executeUpdate throws a SQLException like this:
> >
> > java.sql.SQLException: ERROR: oidin: error in "213"
> >
> > regards David
> >
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 5: Have you checked our extensive FAQ?
> >
> > http://www.postgresql.org/users-lounge/docs/faq.html
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
>

Hi Dave.

No network issues. Everything is running on my machine (dual intel with
scsi and 512mb ram). Anyone else that has some figures of the raw data
performance?

regards David

> David,
>
> This is quite a difference? Are there any network differences that would
> account for the difference?
>
> Dave
> On Tue, 2002-04-09 at 08:50, David Bernhill wrote:
> > Hello Barry.
> >
> > Great! Thanks for your answer. Are there any documentation/discussion
> > concerning the performance of the jdbc driver for postgresql (or some
> > tuning parameters that can be of interest)? Are there any figures
> > related to "blob" performance? I takes me about 11 seconds to insert 1mb
> > raw data in postgresql, compared to 1.5 second in oracle. I tried the
> > bytea type as well, but the result was more or less the same.
> >
> > thanks, David
> >
> > > David,
> > >
> > > The behavior you are describing is correct for the 7.2 driver.  In 7.2 the driver now uses the bytea datatype to
storebinary data (not largeobjects as it did in 7.1 and earlier).  This is documented in the 7.2 documentation.  Also
inthe doc is a description on how to revert back to the old behavior.   
> > >
> > > Thanks,
> > > --Barry
> > > -----Original Message-----
> > > From: David Bernhill <david.bernhill@digitalroute.com>
> > > Date: 08 Apr 2002 13:03:15
> > > To: pgsql-jdbc@postgresql.org
> > > Subject: [JDBC] raw data broken in 7.2 driver?
> > >
> > > Good day.
> > >
> > > I'm having problems inserting raw data with the 7.2 jdbc driver. I've
> > > tried out most of the ones available for download (Java 2). Some of them
> > > seem to work with raw data in 7.2 but has problems with timestamp
> > > instead. Is there any patch available for this?`For more detailed
> > > description see below (found in the interactive docs).
> > >
> > > Comment from Adam Jenkins "<adam@thejenkins.org>":
> > > "The JDBC example given above as example 8-2 does not work as
> > > advertised. I'm using postgresql 7.2, and the jdbc7.2dev-1.2.jar JDBC
> > > driver. It seems that the driver interprets the data it reads from the
> > > InputStream passed to setBinaryStream as a binary representation of the
> > > actual field value -- in this case as a binary representation of an oid.
> > > This is of course incorrect; really the binary data is image data, and
> > > one would hope that what the driver would do is create a new large
> > > object, store the binary data in it, and store the oid of the large
> > > object in the oid field. What really happens is that the driver
> > > constructs a SQL statement which has the binary data which was read from
> > > the InputStream embedded directly in it as the value of the oid field.
> > > So executeUpdate throws a SQLException like this:
> > >
> > > java.sql.SQLException: ERROR: oidin: error in "213"
> > >
> > > regards David
> > >
> > >
> > >
> > > ---------------------------(end of broadcast)---------------------------
> > > TIP 5: Have you checked our extensive FAQ?
> > >
> > > http://www.postgresql.org/users-lounge/docs/faq.html
> >
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 2: you can get off all lists at once with the unregister command
> >     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
> >
> >
>
>

David,

It would be interesting to know if this is a jdbc issue or a core
postgres issue.  What I might suggest is to turn query tracing on in the
backend (postgresql.conf) so that all sql sent by the jdbc driver is
logged to the log file.  Then run the same sql statements through psql
to get jdbc out of the loop and see what the performance is.

My guess is that the problem is mostly in the backend code that parses
the sql statement.  The problem being that the protocol between the
backend and clients does not support bind variables.  Thus for 1mb of
raw data it will produce a sql statement that is over 1mb in length.
The parser then needs to parse this entire 1+mb looking for syntax
errors etc.

I will forward on a mail thread that I had a couple of months ago with
Tom Lane on what I believe is the problem you are facing.

thanks,
--Barry

David Bernhill wrote:
> Hello Barry.
>
> Great! Thanks for your answer. Are there any documentation/discussion
> concerning the performance of the jdbc driver for postgresql (or some
> tuning parameters that can be of interest)? Are there any figures
> related to "blob" performance? I takes me about 11 seconds to insert 1mb
> raw data in postgresql, compared to 1.5 second in oracle. I tried the
> bytea type as well, but the result was more or less the same.
>
> thanks, David
>
>
>>David,
>>
>>The behavior you are describing is correct for the 7.2 driver.  In 7.2 the driver now uses the bytea datatype to
storebinary data (not largeobjects as it did in 7.1 and earlier).  This is documented in the 7.2 documentation.  Also
inthe doc is a description on how to revert back to the old behavior.   
>>
>>Thanks,
>>--Barry
>>-----Original Message-----
>>From: David Bernhill <david.bernhill@digitalroute.com>
>>Date: 08 Apr 2002 13:03:15
>>To: pgsql-jdbc@postgresql.org
>>Subject: [JDBC] raw data broken in 7.2 driver?
>>
>>Good day.
>>
>>I'm having problems inserting raw data with the 7.2 jdbc driver. I've
>>tried out most of the ones available for download (Java 2). Some of them
>>seem to work with raw data in 7.2 but has problems with timestamp
>>instead. Is there any patch available for this?`For more detailed
>>description see below (found in the interactive docs).
>>
>>Comment from Adam Jenkins "<adam@thejenkins.org>":
>>"The JDBC example given above as example 8-2 does not work as
>>advertised. I'm using postgresql 7.2, and the jdbc7.2dev-1.2.jar JDBC
>>driver. It seems that the driver interprets the data it reads from the
>>InputStream passed to setBinaryStream as a binary representation of the
>>actual field value -- in this case as a binary representation of an oid.
>>This is of course incorrect; really the binary data is image data, and
>>one would hope that what the driver would do is create a new large
>>object, store the binary data in it, and store the oid of the large
>>object in the oid field. What really happens is that the driver
>>constructs a SQL statement which has the binary data which was read from
>>the InputStream embedded directly in it as the value of the oid field.
>>So executeUpdate throws a SQLException like this:
>>
>>java.sql.SQLException: ERROR: oidin: error in "213"
>>
>>regards David
>>
>>
>>
>>---------------------------(end of broadcast)---------------------------
>>TIP 5: Have you checked our extensive FAQ?
>>
>>http://www.postgresql.org/users-lounge/docs/faq.html
>
>
>
>

----- Original Message -----
From: "David Bernhill"

>
> Great! Thanks for your answer. Are there any documentation/discussion
> concerning the performance of the jdbc driver for postgresql (or some
> tuning parameters that can be of interest)? Are there any figures
> related to "blob" performance? I takes me about 11 seconds to insert 1mb
> raw data in postgresql, compared to 1.5 second in oracle. I tried the
> bytea type as well, but the result was more or less the same.
>

I've created a sample java test that inserts 16K/128K/512K/1024K binary data
in Oracle9i and Postgres7.2 databases residing on the same server.

Data is stored in Oracle as BLOB, in Postgres as BYTEA, only the thin
drivers were used (one usually gets at least 20% better performance with the
Oracle OCI driver). The test reports the avg time of 3 consecutive inserts
of data chunks of the same size.

the differences in performance are comparable with the ones you report but
they vary substantially depending on the size of the binary data. The
difference in *times* (Oracle vs Postgres, with Oracle considered as base )
is as follows

16K          - x 1.5
128K        - x 7.4
512K        - x 16.9
1024K      - x  22.4

Note that the Oracle instance is fairly well tuned, while the Postgres one
is a default installation with the buffer cache set to 3000 (x 8K) so these
numbers are unlikely to be very representative.

The facts of concern are that:

    -  the difference (in times) in performance grows with the size of
binary data

    - the CPU load on the client machine during the test was ~80% when
connected to Oracle, while when connected to Postgres it was constantly 100%

    - the memory consumption for Oracle is almost constant (<15MB) while in
the case of the postgres driver the memory used grows as the binary data
size grows (up to 120MB!) which is strange IMHO - although bytea data cannot
be read in chunks but only as a whole, this should not affect data writes,
since the source buffer is already in memory


the code looks almost like in the Programmer's manual, so it's unlikely that
there is something wrong with it (I don't quite care about the proper
cleanup for this test):

  public void testPostgresLOB(int size, int count) throws Exception {

    byte[] buffer = new byte[size];
    String url =
"jdbc:postgresql://192.168.128.208:5432/gate09?user=gateuser&password=gate";

    try {
      Connection conn = DBHelper.connect(url);
      conn.setAutoCommit(false);
      PreparedStatement pstmt = conn.prepareStatement("insert into lob_test
values(?)");

      for (int i =0; i< count; i++) {
        pstmt.setBytes(1,buffer);
        pstmt.executeUpdate();
        conn.commit();
      }
    }
    catch(Exception e) {
      e.printStackTrace();
    }
  }


is there anything specific in the Postgres configuration that could improve
binary data handling?

thanx,

    Marin

----
"...what you brought from your past, is of no use in your present. When
you must choose a new path, do not bring old experiences with you.
Those who strike out afresh, but who attempt to retain a little of the
old life, end up torn apart by their own memories. "

Thanks for sharing the inforamtion.

I am also interested in the performance issue, especial binary data for image files.

Marin's comparsion test concludes that binary data insertion (write ?) operation in pg is not as good as in Oracle. How
is 
the read operation?


4/10/2002 11:58:42 PM, "Marin Dimitrov" <marin.dimitrov@sirma.bg> wrote:

>
>----- Original Message -----
>From: "David Bernhill"
>
>>
>> Great! Thanks for your answer. Are there any documentation/discussion
>> concerning the performance of the jdbc driver for postgresql (or some
>> tuning parameters that can be of interest)? Are there any figures
>> related to "blob" performance? I takes me about 11 seconds to insert 1mb
>> raw data in postgresql, compared to 1.5 second in oracle. I tried the
>> bytea type as well, but the result was more or less the same.
>>
>
>I've created a sample java test that inserts 16K/128K/512K/1024K binary data
>in Oracle9i and Postgres7.2 databases residing on the same server.
>
>Data is stored in Oracle as BLOB, in Postgres as BYTEA, only the thin
>drivers were used (one usually gets at least 20% better performance with the
>Oracle OCI driver). The test reports the avg time of 3 consecutive inserts
>of data chunks of the same size.
>
>the differences in performance are comparable with the ones you report but
>they vary substantially depending on the size of the binary data. The
>difference in *times* (Oracle vs Postgres, with Oracle considered as base )
>is as follows
>
>16K          - x 1.5
>128K        - x 7.4
>512K        - x 16.9
>1024K      - x  22.4
>
>Note that the Oracle instance is fairly well tuned, while the Postgres one
>is a default installation with the buffer cache set to 3000 (x 8K) so these
>numbers are unlikely to be very representative.
>
>The facts of concern are that:
>
>    -  the difference (in times) in performance grows with the size of
>binary data
>
>    - the CPU load on the client machine during the test was ~80% when
>connected to Oracle, while when connected to Postgres it was constantly 100%
>
>    - the memory consumption for Oracle is almost constant (<15MB) while in
>the case of the postgres driver the memory used grows as the binary data
>size grows (up to 120MB!) which is strange IMHO - although bytea data cannot
>be read in chunks but only as a whole, this should not affect data writes,
>since the source buffer is already in memory
>
>
>the code looks almost like in the Programmer's manual, so it's unlikely that
>there is something wrong with it (I don't quite care about the proper
>cleanup for this test):
>
>  public void testPostgresLOB(int size, int count) throws Exception {
>
>    byte[] buffer = new byte[size];
>    String url =
>"jdbc:postgresql://192.168.128.208:5432/gate09?user=gateuser&password=gate";
>
>    try {
>      Connection conn = DBHelper.connect(url);
>      conn.setAutoCommit(false);
>      PreparedStatement pstmt = conn.prepareStatement("insert into lob_test
>values(?)");
>
>      for (int i =0; i< count; i++) {
>        pstmt.setBytes(1,buffer);
>        pstmt.executeUpdate();
>        conn.commit();
>      }
>    }
>    catch(Exception e) {
>      e.printStackTrace();
>    }
>  }
>
>
>is there anything specific in the Postgres configuration that could improve
>binary data handling?
>
>thanx,
>
>    Marin
>
>----
>"...what you brought from your past, is of no use in your present. When
>you must choose a new path, do not bring old experiences with you.
>Those who strike out afresh, but who attempt to retain a little of the
>old life, end up torn apart by their own memories. "
>
>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 4: Don't 'kill -9' the postmaster
>

David,

The behavior you are describing is correct for the 7.2 driver.  In 7.2 the driver now uses the bytea datatype to store
binarydata (not largeobjects as it did in 7.1 and earlier).  This is documented in the 7.2 documentation.  Also in the
docis a description on how to revert back to the old behavior.  
 

Thanks,
--Barry
-----Original Message-----
From: David Bernhill <david.bernhill@digitalroute.com>
Date: 08 Apr 2002 13:03:15 
To: pgsql-jdbc@postgresql.org
Subject: [JDBC] raw data broken in 7.2 driver?

Good day.

I'm having problems inserting raw data with the 7.2 jdbc driver. I've
tried out most of the ones available for download (Java 2). Some of them
seem to work with raw data in 7.2 but has problems with timestamp
instead. Is there any patch available for this?`For more detailed
description see below (found in the interactive docs).

Comment from Adam Jenkins "<adam@thejenkins.org>":
"The JDBC example given above as example 8-2 does not work as
advertised. I'm using postgresql 7.2, and the jdbc7.2dev-1.2.jar JDBC
driver. It seems that the driver interprets the data it reads from the
InputStream passed to setBinaryStream as a binary representation of the
actual field value -- in this case as a binary representation of an oid.
This is of course incorrect; really the binary data is image data, and
one would hope that what the driver would do is create a new large
object, store the binary data in it, and store the oid of the large
object in the oid field. What really happens is that the driver
constructs a SQL statement which has the binary data which was read from
the InputStream embedded directly in it as the value of the oid field.
So executeUpdate throws a SQLException like this:

java.sql.SQLException: ERROR: oidin: error in "213"

regards David



---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html