Re: JBoss w/int8 primary keys in postgres ...

On Wed, Sep 10, 2003 at 08:54:33PM -0400, James Robinson wrote:

> From the perspective of someone running the EJB server who has to
> deploy the beans (possibly written by 3rd parties who have no clue what
> particular DB (or, for that matter, which EJB server it is being
> deployed on -- in theory, anyway)) on a given DB backend, say, PG, the
> CMP component of JBoss lets you tell it two major things off the top:
> which JDBC driver URL to bind into the datasource, plus the name of a
> set of typemappings that map Java datatype (i.e. java.lang.Long) to
> JDBC datatype to SQL datatype that JBoss would use when issuing the
> create table command for persisting a component that uses that
> datatype. It is this suite of XML mapping tags that lets you describe
> to JBoss that, for example, byte arrays should correspond to 'bytea'
> columns. This type info, AFAIK, is only used at table creation time.

This sounds like a reasonable way to operate.

In the absence of a backend fix, it sounds like appending explicit types to
parameter values is the way to go. That is, have setLong(12345) produce a
parameter of "12345::int8", setShort(12345) produce "12345::int2", etc.

This moves the breakage from "setLong() vs. int8 column" to "setLong() vs.
int4 column". Is this a reasonable thing to do? ISTM that the case where the
Java and DB types match is the case that should work generally, and the case
where type conversion is needed is dependant on Postgresql's exact behaviour
anyway.

Something related to think about: with PREPARE, we already explicitly
specify the types of parameters based on the method (or sql typecode) used
to set them. Given:

  CREATE TABLE foo(bar int4 not null primary key);

and this code fragment:

  PreparedStatement ps = c.prepareStatement("SELECT * FROM foo WHERE bar = ?");
  ps.setLong(1, 12345)

The current driver generates something like this normally:

  SELECT * FROM foo WHERE bar = 12345

which should use the index. However with server-side prepared queries
enabled the query is transformed to something like this:

  PREPARE jdbc_statement_N(int8) AS SELECT * FROM foo WHERE bar = $1;
  EXECUTE jdbc_statement_N(12345)

Which I suspect won't use the index as $1 is typed to int8. (anyone know for
sure? I can't find an easy way to get at the query plan produced by
PREPARE).

If it doesn't use the index this breaks the transparency of converting to
PREPARE i.e. the "setInt() vs. int8 column" is already broken in some cases
with the current driver. So making setLong() vs. int8 work at the expense of
this case doesn't really break things further. Honest. :)

-O