Thread: Can PostgreSQL do data type automated casting in prepared statement?
Dear all, I already used PostgreSQL from version 7.3.3. So far, I have no problem about using prepared statement especially in data type. From Mr. Kris Jurka I got answer that it because of different data type when I try to cast them. >I'm guessing you are using PreparedStatement.setString for a numeric >value. Do you have a query like "WHERE colname / ? " ? You need to use a >different setXXX method that takes a numeric value. My code is like this: I have query: "Select count(*) / paramPage from tblFirst where condition"; The paramPage were filled from parameter which I specified as string data type. So I using setString method for my prepared statement for passing paramPage value. When I'm using the old jdbc driver pg74jdbc3.jar, I got no problem at all. But when I try to upgrade my PostgreSQL to 8.0.3. I got problem data type error. Can PostgreSQL do data type automated casting in prepared statement again? So I do not need change my code :) Thanks in advance. Best Regards, Xin Xin
On Mon, 21 Nov 2005, Tjioe Ai Xin wrote: > > My code is like this: I have query: "Select count(*) / paramPage from > tblFirst where condition"; The paramPage were filled from parameter > which I specified as string data type. So I using setString method for > my prepared statement for passing paramPage value. When I'm using the > old jdbc driver pg74jdbc3.jar, I got no problem at all. But when I try > to upgrade my PostgreSQL to 8.0.3. I got problem data type error. > > Can PostgreSQL do data type automated casting in prepared statement again? > So I do not need change my code :) > Yes, and no. You can restore the old behavior by adding ?protocolVersion=2 to your connection URL, but using the older protocol also means some new features and performance gains will be unavailable to you. Kris Jurka
On Mon, 21 Nov 2005, Mark Lewis wrote: > Here's a thought; do you think it's feasible to detect cases where the > protocol=3 driver throws an error due to invalid or ambiguous typing > issues when the protocol=2 driver would just do the expected thing? > > Instead of throwing the error back to the user, could the driver then > issue a 'describe statement' call, use the result to disambiguate the > parameter settings, and re-issue the call? It increases the overhead > but only for the error cases, and the result could be cached to avoid > repeating that overhead. > There a number of problems with the fallback approach. First, since we've got a server error that will necessitate the transaction be rolled back so we'd have to establish a savepoint before every statement. Then you'd have to detect an error condition as being related to type resolution which isn't really clear. Even if this did work for people it certainly wouldn't be optimal because you could end up doing a lot more work, parsing twice and establishing and rolling back to savepoint, without knowing it. Your application would look like it was working, but it's certainly not how you want it to. Kris Jurka
Drat. I didn't realize that it's not possible to detect a bad statement without invalidating the current transaction, that's definitely a no- starter. -- Mark On Mon, 2005-11-21 at 19:28 -0500, Kris Jurka wrote: > > On Mon, 21 Nov 2005, Mark Lewis wrote: > > > Here's a thought; do you think it's feasible to detect cases where the > > protocol=3 driver throws an error due to invalid or ambiguous typing > > issues when the protocol=2 driver would just do the expected thing? > > > > Instead of throwing the error back to the user, could the driver then > > issue a 'describe statement' call, use the result to disambiguate the > > parameter settings, and re-issue the call? It increases the overhead > > but only for the error cases, and the result could be cached to avoid > > repeating that overhead. > > > > There a number of problems with the fallback approach. First, since we've > got a server error that will necessitate the transaction be rolled back so > we'd have to establish a savepoint before every statement. Then you'd > have to detect an error condition as being related to type resolution > which isn't really clear. Even if this did work for people it certainly > wouldn't be optimal because you could end up doing a lot more work, > parsing twice and establishing and rolling back to savepoint, without > knowing it. Your application would look like it was working, but it's > certainly not how you want it to. > > Kris Jurka >
Here's a thought; do you think it's feasible to detect cases where the protocol=3 driver throws an error due to invalid or ambiguous typing issues when the protocol=2 driver would just do the expected thing? Instead of throwing the error back to the user, could the driver then issue a 'describe statement' call, use the result to disambiguate the parameter settings, and re-issue the call? It increases the overhead but only for the error cases, and the result could be cached to avoid repeating that overhead. I haven't done any hacking in the driver yet, so maybe it would be too much of a pain to implement to justify any possible gains. -- Mark Lewis On Mon, 2005-11-21 at 18:08 -0500, Kris Jurka wrote: > > On Mon, 21 Nov 2005, Tjioe Ai Xin wrote: > > > > > My code is like this: I have query: "Select count(*) / paramPage from > > tblFirst where condition"; The paramPage were filled from parameter > > which I specified as string data type. So I using setString method for > > my prepared statement for passing paramPage value. When I'm using the > > old jdbc driver pg74jdbc3.jar, I got no problem at all. But when I try > > to upgrade my PostgreSQL to 8.0.3. I got problem data type error. > > > > Can PostgreSQL do data type automated casting in prepared statement again? > > So I do not need change my code :) > > > > Yes, and no. You can restore the old behavior by adding > ?protocolVersion=2 to your connection URL, but using the older protocol > also means some new features and performance gains will be unavailable to > you. > > Kris Jurka > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: explain analyze is your friend
The easier way to deal with this has already been discussed. Simply bind String to the Oid.Unknown type, and let the server deal with it. How about we make this a configuration parameter. Dave On 21-Nov-05, at 6:37 PM, Mark Lewis wrote: > Here's a thought; do you think it's feasible to detect cases where the > protocol=3 driver throws an error due to invalid or ambiguous typing > issues when the protocol=2 driver would just do the expected thing? > > Instead of throwing the error back to the user, could the driver then > issue a 'describe statement' call, use the result to disambiguate the > parameter settings, and re-issue the call? It increases the overhead > but only for the error cases, and the result could be cached to avoid > repeating that overhead. > > I haven't done any hacking in the driver yet, so maybe it would be too > much of a pain to implement to justify any possible gains. > > -- Mark Lewis > > On Mon, 2005-11-21 at 18:08 -0500, Kris Jurka wrote: >> >> On Mon, 21 Nov 2005, Tjioe Ai Xin wrote: >> >>> >>> My code is like this: I have query: "Select count(*) / paramPage >>> from >>> tblFirst where condition"; The paramPage were filled from parameter >>> which I specified as string data type. So I using setString >>> method for >>> my prepared statement for passing paramPage value. When I'm using >>> the >>> old jdbc driver pg74jdbc3.jar, I got no problem at all. But when >>> I try >>> to upgrade my PostgreSQL to 8.0.3. I got problem data type error. >>> >>> Can PostgreSQL do data type automated casting in prepared >>> statement again? >>> So I do not need change my code :) >>> >> >> Yes, and no. You can restore the old behavior by adding >> ?protocolVersion=2 to your connection URL, but using the older >> protocol >> also means some new features and performance gains will be >> unavailable to >> you. >> >> Kris Jurka >> >> >> ---------------------------(end of >> broadcast)--------------------------- >> TIP 6: explain analyze is your friend > > ---------------------------(end of > broadcast)--------------------------- > TIP 3: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq >
Dave Cramer wrote: > The easier way to deal with this has already been discussed. Simply > bind String to the Oid.Unknown type, and let the server deal with it. > > How about we make this a configuration parameter. I was thinking about this some more and that seems like the best thing to do for now. Make it default to off (i.e. current 8.0/8.1 behaviour by default) and turn it on if you specify a separate parameter or if you specify compatible=7.4 -O
Hi Oliver.. How can I make it default to off? Does the performance of PostgreSQL drop a lot if I change it? Especially the speed? Thanks in advance. Xin Xin On Tuesday 22 November 2005 16:33, you wrote: > Dave Cramer wrote: > > The easier way to deal with this has already been discussed. Simply > > bind String to the Oid.Unknown type, and let the server deal with it. > > > > How about we make this a configuration parameter. > > I was thinking about this some more and that seems like the best thing > to do for now. > > Make it default to off (i.e. current 8.0/8.1 behaviour by default) and > turn it on if you specify a separate parameter or if you specify > compatible=7.4 > > -O >
Tjioe Ai Xin wrote: > How can I make it default to off? With current drivers you can't. You can try protocolVersion=2 as a workaround as suggested elsewhere but that's a bit of a blunt instrument really. > Does the performance of PostgreSQL drop a lot if I change it? > Especially the speed? You will have to try it with your particular application to find out. Yes, query plans can change, and that may affect performance one way or the other. -O
Dear Kris, Where I can set protocolVersion=2? And how about the performance? Thanks in advance. Xin Xin On Tuesday 22 November 2005 06:08, Kris Jurka wrote: > > On Mon, 21 Nov 2005, Tjioe Ai Xin wrote: > > > > > My code is like this: I have query: "Select count(*) / paramPage from > > tblFirst where condition"; The paramPage were filled from parameter > > which I specified as string data type. So I using setString method for > > my prepared statement for passing paramPage value. When I'm using the > > old jdbc driver pg74jdbc3.jar, I got no problem at all. But when I try > > to upgrade my PostgreSQL to 8.0.3. I got problem data type error. > > > > Can PostgreSQL do data type automated casting in prepared statement again? > > So I do not need change my code :) > > > > Yes, and no. You can restore the old behavior by adding > ?protocolVersion=2 to your connection URL, but using the older protocol > also means some new features and performance gains will be unavailable to > you. > > Kris Jurka > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: explain analyze is your friend >
Please see the following for all the connection parameters. http://jdbc.postgresql.org/documentation/head/connect.html#connection- parameters Dave On 23-Nov-05, at 7:05 AM, Tjioe Ai Xin wrote: > Dear Kris, > > Where I can set protocolVersion=2? > And how about the performance? > > Thanks in advance. > Xin Xin > > On Tuesday 22 November 2005 06:08, Kris Jurka wrote: >> >> On Mon, 21 Nov 2005, Tjioe Ai Xin wrote: >> >>> >>> My code is like this: I have query: "Select count(*) / paramPage >>> from >>> tblFirst where condition"; The paramPage were filled from parameter >>> which I specified as string data type. So I using setString >>> method for >>> my prepared statement for passing paramPage value. When I'm using >>> the >>> old jdbc driver pg74jdbc3.jar, I got no problem at all. But when >>> I try >>> to upgrade my PostgreSQL to 8.0.3. I got problem data type error. >>> >>> Can PostgreSQL do data type automated casting in prepared >>> statement again? >>> So I do not need change my code :) >>> >> >> Yes, and no. You can restore the old behavior by adding >> ?protocolVersion=2 to your connection URL, but using the older >> protocol >> also means some new features and performance gains will be >> unavailable to >> you. >> >> Kris Jurka >> >> >> ---------------------------(end of >> broadcast)--------------------------- >> TIP 6: explain analyze is your friend >> > > ---------------------------(end of > broadcast)--------------------------- > TIP 9: In versions below 8.0, the planner will ignore your desire to > choose an index scan if your joining column's datatypes do not > match >
Dave Cramer wrote:
> The easier way to deal with this has already been discussed. Simply
> bind String to the Oid.Unknown type, and let the server deal with it.
>
> How about we make this a configuration parameter.
I've implemented this and it seems ok with one exception. This is from
ServerPreparedStmtTest:
public void testTypeChange() throws Exception {
PreparedStatement pstmt = con.prepareStatement("SELECT ?");
((PGStatement)pstmt).setUseServerPrepare(true);
// Prepare with int parameter.
pstmt.setInt(1, 1);
ResultSet rs = pstmt.executeQuery();
assertTrue(rs.next());
assertEquals(1, rs.getInt(1));
assertTrue(!rs.next());
// Change to text parameter, check it still works.
pstmt.setString(1, "test string");
rs = pstmt.executeQuery();
assertTrue(rs.next());
assertEquals("test string", rs.getString(1));
assertTrue(!rs.next());
}
With String bound to OID 0 this fails with:
ERROR: invalid input syntax for integer: "test string"
What is happening is that the use of setInt() prepares a server-side
statement with the parameter typed as integer. When setString() is later
used, it feeds oid 0 down into the query executor which decides to reuse
the existing statement since it is "compatible enough" as the parameter
which was previously integer now has an unspecified type. Then the
server tries to parse the string as an integer and breaks.
IIRC this was originally done to avoid re-preparing the statement when
setNull(x,Types.OTHER) was done (or equivalently, setObject(x,null),
which just calls setNull) -- which can pass oid 0 to an existing query
with resolved types.
I guess that we should tighten the checks in the query executor so that
it will only consider types "compatible enough" if the new value is both
of unspecified type *and* null?
(I am also renaming Oid.INVALID to Oid.UNSPECIFIED in the driver to
reduce confusion -- InvalidOid in the backend is indeed 0 but "invalid"
is not the protocol-level meaning for oid 0 here, and "unknown" is an
actual pseudotype that has a non-zero oid).
-O
Oliver Jowett wrote:
> I guess that we should tighten the checks in the query executor so that
> it will only consider types "compatible enough" if the new value is both
> of unspecified type *and* null?
With that change the test just fails differently:
ERROR: could not determine data type of parameter $1
since the test query ("SELECT ?") is actually one of the cases where
string-as-unspecified-type does not work.
I changed it to use a different query that still appears to exercise the
type-changing behaviour correctly ("SELECT CAST (? AS TEXT)") but
doesn't fail with a parameter of unspecified type.
-O
Interesting.
Looking at the test case is this a realistic situation ? Would anyone
really want to change the types of a parameter of a statement ?
Dave
On 24-Nov-05, at 12:00 AM, Oliver Jowett wrote:
> Dave Cramer wrote:
>> The easier way to deal with this has already been discussed.
>> Simply bind String to the Oid.Unknown type, and let the server
>> deal with it.
>> How about we make this a configuration parameter.
>
> I've implemented this and it seems ok with one exception. This is
> from ServerPreparedStmtTest:
>
> public void testTypeChange() throws Exception {
> PreparedStatement pstmt = con.prepareStatement("SELECT ?");
> ((PGStatement)pstmt).setUseServerPrepare(true);
>
> // Prepare with int parameter.
> pstmt.setInt(1, 1);
> ResultSet rs = pstmt.executeQuery();
> assertTrue(rs.next());
> assertEquals(1, rs.getInt(1));
> assertTrue(!rs.next());
>
> // Change to text parameter, check it still works.
> pstmt.setString(1, "test string");
> rs = pstmt.executeQuery();
> assertTrue(rs.next());
> assertEquals("test string", rs.getString(1));
> assertTrue(!rs.next());
> }
>
> With String bound to OID 0 this fails with:
>
> ERROR: invalid input syntax for integer: "test string"
>
> What is happening is that the use of setInt() prepares a server-
> side statement with the parameter typed as integer. When setString
> () is later used, it feeds oid 0 down into the query executor which
> decides to reuse the existing statement since it is "compatible
> enough" as the parameter which was previously integer now has an
> unspecified type. Then the server tries to parse the string as an
> integer and breaks.
>
> IIRC this was originally done to avoid re-preparing the statement
> when setNull(x,Types.OTHER) was done (or equivalently, setObject
> (x,null), which just calls setNull) -- which can pass oid 0 to an
> existing query with resolved types.
>
> I guess that we should tighten the checks in the query executor so
> that it will only consider types "compatible enough" if the new
> value is both of unspecified type *and* null?
>
> (I am also renaming Oid.INVALID to Oid.UNSPECIFIED in the driver to
> reduce confusion -- InvalidOid in the backend is indeed 0 but
> "invalid" is not the protocol-level meaning for oid 0 here, and
> "unknown" is an actual pseudotype that has a non-zero oid).
>
> -O
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org
>
Dave Cramer wrote: > Looking at the test case is this a realistic situation ? Would anyone > really want to change the types of a parameter of a statement ? It's allowed by the spec as far as I can tell, so we need to support it. -O
You're on fairly shaky ground using "allowed by the spec" as justification. I'm thinking there are far more instances where people expect Oid unspecified to work than instances where they are going to change the type of the IN parameter in the same statement. Given that the default behaviour adheres to the spec, I'm not too worried about the case below failing under these specific circumstances. I presume it passes with the 8.0,8.1 behaviour. Dave On 24-Nov-05, at 3:47 PM, Oliver Jowett wrote: > Dave Cramer wrote: > >> Looking at the test case is this a realistic situation ? Would >> anyone really want to change the types of a parameter of a >> statement ? > > It's allowed by the spec as far as I can tell, so we need to > support it. > > -O >
Dave Cramer wrote:
> You're on fairly shaky ground using "allowed by the spec" as
> justification.
Why's that? Are we no longer trying to write a spec-compliant driver?
> I'm thinking there are far more instances where people
> expect Oid unspecified to work than
> instances where they are going to change the type of the IN parameter
> in the same statement.
Sure, but I'd rather not have an option that makes the driver break
unexpectedly. Given that we can have both unspecified string types AND a
fix for the changing-type problem, why do you *not* want to do that?
If you want a more "real world" example, how about something like this:
>> ArrayList toInsert = new ArrayList();
>> toInsert.add(new Integer(42));
>> toInsert.add(new Date());
>> toInsert.add("test string");
>> // ...
>> PreparedStatement ps = conn.prepareStatement("INSERT INTO sometexttable(sometextcolumn) VALUES (CAST (? AS TEXT))");
>> for (Iterator i = toInsert.iterator(); i.hasNext(); ) {
>> ps.setObject(1, i.next());
>> ps.executeUpdate();
>> }
Test cases are not meant to be real-world examples, they're test code.
Use your imagination!
> Given that the default behaviour adheres to the spec, I'm not too
> worried about the case below failing under these specific
> circumstances. I presume it passes with the 8.0,8.1 behaviour.
It does.
The code I have committed to CVS HEAD deals with the
changing-parameter-type case correctly even with stringtype=unspecified,
anyway. Can you please try it out and see if you have any problems with it?
Otherwise, as far as I'm concerned I'm done with this -- if people don't
want to change their (arguably broken) apps, they have an escape hatch
they can enable explicitly or via compatible=7.4.. IMO we don't need to
do anything more.
-O
Oliver, Sorry, I mis-read your last post before this one. This is great the way it is. Dave
Dear all,
Does it mean in the future PostgreSQL JDBC Driver will support automated casting?
So I don't have to write my code again in order to customize for new driver?
Thanks in advance.
Xin Xin
--------------------------------------------------------------------
On Friday 25 November 2005 04:20, Oliver Jowett wrote:
> Dave Cramer wrote:
> > You're on fairly shaky ground using "allowed by the spec" as
> > justification.
>
> Why's that? Are we no longer trying to write a spec-compliant driver?
>
> > I'm thinking there are far more instances where people
> > expect Oid unspecified to work than
> > instances where they are going to change the type of the IN parameter
> > in the same statement.
>
> Sure, but I'd rather not have an option that makes the driver break
> unexpectedly. Given that we can have both unspecified string types AND a
> fix for the changing-type problem, why do you *not* want to do that?
>
> If you want a more "real world" example, how about something like this:
>
> >> ArrayList toInsert = new ArrayList();
> >> toInsert.add(new Integer(42));
> >> toInsert.add(new Date());
> >> toInsert.add("test string");
> >> // ...
> >> PreparedStatement ps = conn.prepareStatement("INSERT INTO sometexttable(sometextcolumn) VALUES (CAST (? AS
TEXT))");
> >> for (Iterator i = toInsert.iterator(); i.hasNext(); ) {
> >> ps.setObject(1, i.next());
> >> ps.executeUpdate();
> >> }
>
> Test cases are not meant to be real-world examples, they're test code.
> Use your imagination!
>
> > Given that the default behaviour adheres to the spec, I'm not too
> > worried about the case below failing under these specific
> > circumstances. I presume it passes with the 8.0,8.1 behaviour.
>
> It does.
>
> The code I have committed to CVS HEAD deals with the
> changing-parameter-type case correctly even with stringtype=unspecified,
> anyway. Can you please try it out and see if you have any problems with it?
>
> Otherwise, as far as I'm concerned I'm done with this -- if people don't
> want to change their (arguably broken) apps, they have an escape hatch
> they can enable explicitly or via compatible=7.4.. IMO we don't need to
> do anything more.
>
> -O
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
>
What we are supporting is setString will allow the backend to
determine the type of the input parameter if and only if you use
setString
Dave
On 25-Nov-05, at 2:03 AM, Tjioe Ai Xin wrote:
> Dear all,
>
> Does it mean in the future PostgreSQL JDBC Driver will support
> automated casting?
> So I don't have to write my code again in order to customize for
> new driver?
>
> Thanks in advance.
> Xin Xin
>
> --------------------------------------------------------------------
> On Friday 25 November 2005 04:20, Oliver Jowett wrote:
>> Dave Cramer wrote:
>>> You're on fairly shaky ground using "allowed by the spec" as
>>> justification.
>>
>> Why's that? Are we no longer trying to write a spec-compliant driver?
>>
>>> I'm thinking there are far more instances where people
>>> expect Oid unspecified to work than
>>> instances where they are going to change the type of the IN
>>> parameter
>>> in the same statement.
>>
>> Sure, but I'd rather not have an option that makes the driver break
>> unexpectedly. Given that we can have both unspecified string types
>> AND a
>> fix for the changing-type problem, why do you *not* want to do that?
>>
>> If you want a more "real world" example, how about something like
>> this:
>>
>>>> ArrayList toInsert = new ArrayList();
>>>> toInsert.add(new Integer(42));
>>>> toInsert.add(new Date());
>>>> toInsert.add("test string");
>>>> // ...
>>>> PreparedStatement ps = conn.prepareStatement("INSERT INTO
>>>> sometexttable(sometextcolumn) VALUES (CAST (? AS TEXT))");
>>>> for (Iterator i = toInsert.iterator(); i.hasNext(); ) {
>>>> ps.setObject(1, i.next());
>>>> ps.executeUpdate();
>>>> }
>>
>> Test cases are not meant to be real-world examples, they're test
>> code.
>> Use your imagination!
>>
>>> Given that the default behaviour adheres to the spec, I'm not too
>>> worried about the case below failing under these specific
>>> circumstances. I presume it passes with the 8.0,8.1 behaviour.
>>
>> It does.
>>
>> The code I have committed to CVS HEAD deals with the
>> changing-parameter-type case correctly even with
>> stringtype=unspecified,
>> anyway. Can you please try it out and see if you have any problems
>> with it?
>>
>> Otherwise, as far as I'm concerned I'm done with this -- if people
>> don't
>> want to change their (arguably broken) apps, they have an escape
>> hatch
>> they can enable explicitly or via compatible=7.4.. IMO we don't
>> need to
>> do anything more.
>>
>> -O
>>
>> ---------------------------(end of
>> broadcast)---------------------------
>> TIP 9: In versions below 8.0, the planner will ignore your desire to
>> choose an index scan if your joining column's datatypes do not
>> match
>>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>