more on BLOB/OID with Torque and postgresql

[Bill Schneider <bs...@vecna.com>]

I looked into this in more detail and it turns out that if you patch the
PostgreSQL JDBC driver to return VARBINARY for "oid" columns as documented,
things still break even though the village Record class is calling
PreparedStatement.setBytes().

It looks like the most recent versions of the PostgreSQL driver (7.2+) don't
support setBytes(int, byte[]) as a mechanism for setting the content of an
OID column anymore.  Instead, the driver code appears to make the assumption
that, if you're calling setBytes, you must be dealing with a "bytea" column
instead, which does work with Torque.

The code in question is in org.postgresql.jdbc1.AbstractJdbc1Statement:,
whose logic boils down to something like this:

public void setBytes(int i, byte[] x) {
  if (7.2 or higher) {
    setString(i, convert-to-string(x));
    // correct behavior if column is really a "bytea" but breaks if column
is an "oid";
    // if column is an oid, tries to parse x as the oid instead of the
large-object content
  } else {
    create new LOB and write x to it;
    setInt(i, OID of new LOB)
    // correct behavior if column is an "oid" but would break if is a bytea
  }
}

Further discussions on the subject probably belong on the pgsql-jdbc mailing
list instead.

-- Bill