V3 extended query protocol and type problems - Mailing list pgsql-jdbc

I'm currently working on making the driver use the V3 extended query
protocol. It's "mostly working" but there are some issues around
typecasting of booleans (and potentially other types, but boolean is the
one the test cases catch).

Given this schema:

   CREATE TABLE testbool (a boolean);
   CREATE TABLE testbit (a bit);

The current driver will run both these queries ok:

   INSERT INTO testbool VALUES (?)
   INSERT INTO testbit VALUES (?)

using setBoolean() to set the parameter value. The queries, run under
the simple query protocol, expand to:

   INSERT INTO testbit VALUES ('0')
   INSERT INTO testbool VALUES ('0')

and work fine because of the implicit casting of untyped string constants.

....

The only way I've managed to get the same queries working under the
extended query protocol is if I *don't* specify parameter types in the
Parse, to get the same implicit casting behaviour.

Here's the behaviour I see when specifying a parameter type of 'bool'
(OID=16) in Parse. I'm using text-format Binds in all of the following
cases.

  => Parse(query="insert into testbool values ($1)", oids={16})
  => Bind($1="0")
  => Describe
  => Execute(limit=0)
  => Close
  => Sync
  <= ParseComplete
  <= BindComplete
  <= NoData
  <= CommandStatus(INSERT 887874 1)
  <= CloseComplete
  <= ReadyForQuery(I)

  => Parse(query="insert into testbit values ($1)", oids={16})
  => Bind($1="0")
  => Describe
  => Execute(limit=0)
  => Close
  => Sync
  <= ErrorMessage(ERROR: column "a" is of type bit but expression is of
type boolean)
  <= ReadyForQuery(I)

Trying to give an "untyped" value to Bind after a typed Parse
unsurprisingly doesn't work:

  => Parse(query="insert into testbool values ($1)", oids={16})
  => Bind($1="'0'")
  => Describe
  => Execute(limit=0)
  => Close
  => Sync
  <= ParseComplete
  <= ErrorMessage(ERROR: invalid input syntax for type boolean: "'0'")
  <= ReadyForQuery(I)

It all works fine if I don't specify an OID in the Parse:

  => Parse(query="insert into testbool values ($1)", oids={0})
  => Bind($1="0")
  => Describe
  => Execute(limit=0)
  => Close
  => Sync
  <= ParseComplete
  <= BindComplete
  <= NoData
  <= CommandStatus(INSERT 889828 1)
  <= CloseComplete
  <= ReadyForQuery(I)

  => Parse(query="insert into testbit values ($1)", oids={0})
  => Bind($1="0")
  => Describe
  => Execute(limit=0)
  => Close
  => Sync
  <= ParseComplete
  <= BindComplete
  <= NoData
  <= CommandStatus(INSERT 889832 1)
  <= CloseComplete
  <= ReadyForQuery(I)

....

The driver can't determine whether the caller wants a boolean or a bit
type just from the setBoolean() call; there's just not enough
information available.

Not specifying OIDs for all parameters makes it work, but at the expense
of streaming parameters using a binary Bind at some later stage (we'd
have to do an extra round-trip to get the inferred types via a statement
Describe before doing a Bind, and then deal with any typecasting issues
on the driver side -- ugh).

We could treat parameters set via setBoolean() as an unknown type i.e.
don't specify an OID just for that one parameter. But that's then
inconsistent with how all the other builtin types are handled (they'd
provide an explicit OID that matches the accessor used to set the
parameter -- setInt() results in a 'int4' OID, and so on), and it
becomes harder to predict how the driver will behave (it becomes
dependent on the exact queries and schema in use).

I'm tempted to drop 'bit' support via setBoolean() entirely; it only
supports the degenerate single-bit-bitfield case anyway, which can be
entirely handled by 'boolean'.

I really don't like having to fall back to implicit casting behaviour ..
the driver has a lot of type information about the parameters already,
and it'd be a shame if we can't use that.

Anyone have thoughts on this?

-O