JDBC adaptor issue - Mailing list pgsql-hackers

Hi all,

I've been trying to get PostgreSQL to work with Apple's 
WebObjects application server. WebObjects uses JDBC as an 
interface to back-end databases, translating between SQL and a 
pure object model.

I had a problem with incorrect SQL being generated and sent to 
the PostgreSQL back end. After some work, I tracked it down. I 
have a fix, but the fix has ramifications for the way that 
others use PostgreSQL, so I decided to post here and see what 
people think.

It turns out that WebObjects uses the 
PreparedStatement.setCharacterStream method in order to set the 
values of some character parameters in prepared statements, and 
thus the generated SQL. It's not at all clear why it does this 
for some parameters but not others; the reason doesn't seem to 
have anything to do with the declared length of the parameters. 
This seems odd, because setCharacterStream is a very 
high-overhead operation, but in any case, that's what it does.

The PostgreSQL JDBC driver, however, makes the assumption that 
any JDBC client class that's using the set/get...stream methods 
wants to exchange information with a field that's been 
explicitly typed as a BLOB. It therefore does what PostgreSQL 
requires: it creates a new object containing the data, then uses 
the object ID of the new object as the value to stuff into the 
query. This has the effect of generating queries like
   SELECT ...    WHERE some_text_field = 57909 ...

57909 is an object ID. The comparison doesn't work because 
some_text_field is an ordinary char or varchar, not a BLOB.

It's kind of hard to figure out the "right" solution to this 
problem. I've patched the PostgreSQL JDBC implementation of 
PreparedStatement.setCharacterStream to treat any stream smaller 
than 8190 bytes as a string. I chose 8190 because of the old 
limit of 8192 bytes per tuple in versions prior to 7.1, so this 
change is least likely to cause compatibility problems with 
systems using setCharacterStream the way that the PostgreSQL 
developers anticipated. I can provide the patch to anyone who 
needs it.

The WebObjects use of JDBC is in line with the JDBC 2.0 
specification; that spec does not place any restrictions on the 
types of fields that can be accessed via get/set...stream. 
Whether it's a good use is a different question, of course, but 
it's still legal. My little kludge with an 8190-byte "switch" to 
the old behavior really can't be the last word.

I was hoping that someone could look at the PostgreSQL back end 
to see if there's any reason to keep the 8190-byte limiting 
behavior in the JDBC driver. The limit needs to be removed so 
that character streams and strings are symmetric in order to 
comply with JDBC 2.0. The effect of switching will simply be the 
possibility that the back end will have to deal with very long 
(>8k) quoted strings. I got the impression from reading TOAST 
project documents that all such limitations had been removed, 
but I wanted to check before submitting my patch for inclusion 
in the distribution.

Thanks,
-- Bruce

--------------------------------------------------------------------------
Bruce Toback    Tel: (602) 996-8601| My candle burns at both ends;
OPT, Inc.            (800) 858-4507| It will not last the night;
11801 N. Tatum Blvd. Ste. 142      | But ah, my foes, and oh, my 
friends -
Phoenix AZ 85028                   | It gives a lovely light.
btoback@optc.com                   |     -- Edna St. Vincent Millay