Обсуждение: Re: [HACKERS] 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 >> > >
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 >>> >> >> > >