|
Home > Archive > PostgreSQL JDBC > November 2005 > Re: Can PostgreSQL do data type automated casting in
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Re: Can PostgreSQL do data type automated casting in
|
|
| Mark Lewis 2005-11-22, 3:24 am |
| 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:
>
>
> 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 2005-11-22, 3:24 am |
| 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:
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
| |
| Oliver Jowett 2005-11-22, 7:24 am |
| 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
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
| |
| Tjioe Ai Xin 2005-11-23, 7:24 am |
| 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:
>
> 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
>
---------------------------(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
| |
| Oliver Jowett 2005-11-23, 7:24 am |
| 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
---------------------------(end of broadcast)---------------------------
TIP 1: 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
| |
| Oliver Jowett 2005-11-24, 3:23 am |
| 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
ServerPreparedStmtTe
st:
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
| |
| Oliver Jowett 2005-11-24, 3:23 am |
| 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
---------------------------(end of broadcast)---------------------------
TIP 1: 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
| |
| Dave Cramer 2005-11-24, 9:23 am |
| 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:
>
> I've implemented this and it seems ok with one exception. This is
> from ServerPreparedStmtTe
st:
>
> 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
>
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
| |
| Oliver Jowett 2005-11-24, 8:24 pm |
| 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
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
| |
| Dave Cramer 2005-11-24, 8:24 pm |
| 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:
>
>
> It's allowed by the spec as far as I can tell, so we need to
> support it.
>
> -O
>
---------------------------(end of broadcast)---------------------------
TIP 1: 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
| |
| Oliver Jowett 2005-11-24, 8:24 pm |
| 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:
Test cases are not meant to be real-world examples, they're test code.
Use your imagination!
[color=darkred]
> 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=unspecifi
ed,
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
| |
| Dave Cramer 2005-11-24, 8:24 pm |
| Oliver,
Sorry, I mis-read your last post before this one. This is great the
way it is.
Dave
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
| |
| Tjioe Ai Xin 2005-11-25, 3:23 am |
| 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:
>
> Why's that? Are we no longer trying to write a spec-compliant driver?
>
>
> 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:
>
>
> Test cases are not meant to be real-world examples, they're test code.
> Use your imagination!
>
>
> It does.
>
> The code I have committed to CVS HEAD deals with the
> changing-parameter-type case correctly even with stringtype=unspecifi
ed,
> 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
| |
| Dave Cramer 2005-11-25, 7:24 am |
| 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:
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
|
|
|
|
|