Skip site navigation (1) Skip section navigation (2)

Re: Can PostgreSQL do data type automated casting in

From: Oliver Jowett <oliver(at)opencloud(dot)com>
To: Dave Cramer <pg(at)fastcrypt(dot)com>
Cc: Mark Lewis <mark(dot)lewis(at)mir3(dot)com>, Kris Jurka <books(at)ejurka(dot)com>, Tjioe Ai Xin <xinxincute(at)gmail(dot)com>, pgsql-jdbc(at)postgresql(dot)org
Subject: Re: Can PostgreSQL do data type automated casting in
Date: 2005-11-24 05:00:11
Message-ID: 438548DB.8070407@opencloud.com (view raw or flat)
Thread:
Lists: pgsql-jdbc
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

In response to

Responses

pgsql-jdbc by date

Next:From: Oliver JowettDate: 2005-11-24 05:36:24
Subject: Re: Can PostgreSQL do data type automated casting in
Previous:From: Oliver JowettDate: 2005-11-24 02:34:36
Subject: Re: Logging from multiple connections

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group