Re: Can PostgreSQL do data type automated casting in

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