"Fix" for INT8 literals being parsed as INT4 disqualifying index scan problem in JBoss ...

Hello folks,

Folks who use postgresql with JBoss and use non-INT4 number primary
keys have been plagued with all of their indexes built on those columns
being ignored in queries as simple as:

SELECT COUNT(*) FROM foo WHERE (id=1234)

on table

CREATE TABLE foo (
id INT8 primary key not null
);

since early on in the parsing / planning of the query, the "1234" gets
typed as INT4 literal, disqualifying it from consideration for use with
any indexes built against any INT8 column. Likewise problem exists for
INT2 column indexes. This query unfortunately becomes a full table
scan. JBoss is capable of issuing, um, *many* such small queries.

Anyway, suggestions for fixes have varied to and from fixing in the
client application (JBoss, in this case), JDBC driver (tried once,
broke other things), and the backend parser (generally agreed to be the
best place to fix, but a good solution fixing more than it breaks has
not jumped up yet).

Ends up that it can be fixed at the JBoss layer via editing the
standardjbosscmp-jdbc.xml config file's Postgres 7.2 typemapping tags,
changing the clause that reads:

<mapping>
<java-type>java.lang.Long</java-type>
<jdbc-type>BIGINT</jdbc-type>
<sql-type>INT8</sql-type>
</mapping>

to

<mapping>
<java-type>java.lang.Long</java-type>
<jdbc-type>VARCHAR</jdbc-type>
<sql-type>INT8</sql-type>
</mapping>

This ultimately causes JBoss to make the call to
PreparedStatement.setObject(int index, Object value, int targetSqlType)
method with targetSqlType set to java.sql.Types.VARCHAR, which,
ultimately, causes the JDBC driver to wrap single quotes around the
literal long, as in

SELECT COUNT(*) FROM foo WHERE (id='1234')

which causes a different parsing / type cooersion path in the backend
which ultimately lets the indexes get used.

It seems to work for our JBoss application running on JBoss 3.2.1 /
postgresql versions 7.2 -> 7.4RC1, however it did cause the JBoss 3.2.1
JMS provider to get unhappy, but we didn't depend upon JMS, so we
simply removed it from being deployed, so YMMV.

----
James Robinson
Socialserve.com