Barry Lind wrote:
> 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
>>
>
>