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

Sat, 23 Jun 2001 16:32:53 -0700
btoback@mac.com
JDBC adaptor issue

On Sunday, June 24, 2001, at 10:32 PM, 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.
>

That would be my preference as well.

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

It's certainly not a generic solution. I was looking for a 
solution that would break fewer of the applications that rely on 
the current nonstandard behavior. I'd much prefer to simply have 
get/set...stream just implement the standard behavior. But not 
knowing the Postgres developers' preferences when it comes to 
these questions, I chose the break-fewer-existing-apps approach.

If the answer is that the Postgres developers are willing to 
tell current JDBC users to switch to the Blob/Clob methods when 
that's what they really mean, I'll remove the switch before 
submitting the patch.

-- Bruce

Sun, 24 Jun 2001 22:55:40 -0700
Bruce Toback <btoback@mac.com>
Re: JDBC adaptor issue


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



Mon, 25 Jun 2001 08:43:10 -0700
Barry Lind <barry@xythos.com>
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@mac.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@optc.com                   |     -- Edna St. Vincent Millay
>>>
>>> ---------------------------(end of broadcast)---------------------------
>>> TIP 3: if posting/reading through Usenet, please send an appropriate
>>> subscribe-nomail command to majordomo@postgresql.org so that your
>>> message can get through to the mailing list cleanly
>>>
>>
>>
> 
> 



Mon, 25 Jun 2001 15:49:01 -0700
Barry Lind <barry@xythos.com>
Re: [HACKERS] JDBC adaptor issue