Re: raw data broken in 7.2 driver? - Mailing list pgsql-jdbc

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
>
>
>
>