Re: [HACKERS] JDBC adaptor issue

This is an interesting problem. And I can't think a any easy
solution. But given TOAST in 7.1 the existing implementation doesn't
make sense IMHO My suggestion would be that the get/setXXXStream
methods work on TOASTed data types and get/setBlob be used for Blobs.

As far as your patch, I don't see that as a generic solution. It is
equally likely that a Blob could contain less than 8190 characters, or
a varchar could contain more that 8190 characters in 7.1. Using this
number as a magic switch to decide whether the driver uses the BLOB
API or not just won't work in the general case.

thanks,
--Barry

>>
>> btoback(at)mac(dot)com wrote:
>>
>>> 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(at)optc(dot)com | -- Edna St. Vincent Millay
>>>
>>> ---------------------------(end of broadcast)---------------------------
>>> TIP 3: if posting/reading through Usenet, please send an appropriate
>>> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
>>> message can get through to the mailing list cleanly
>>>
>>
>>
>
>