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

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

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