Re: "Fix" for INT8 literals being parsed as INT4 disqualifying

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

On Thu, Nov 06, 2003 at 02:13:15PM -0500, James Robinson wrote:

> 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
> );

[...]

> 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).

There's a patch pending that should fix this problem .. it adds an explicit
typecast to all literals that reflects the type provided to JDBC (either
implied by the method used, or from the java.sql.Types value for
setObject()). So the above query actually turns into:

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

if you use setLong() or setObject(..., Types.BIGINT) to set the parameter.

This does break other cases (e.g. using setLong() when you actually want an
int4 value) but it's necessary to get consistency between the PREPARE and
non-PREPARE paths.. and it's almost an application error in this case
anyway.

I'm not sure what the plan is for applying the patch though -- Barry?

[...]

> 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.

This is essentially the "fix" that was tried earlier at the driver level,
but it does break other cases so it's not a general solution.

-O

Oliver,

That patch will be applied soon. But it will *not* be in the 7.4 stable
branch. It will be fixed in head of tree. Since it will break existing
code I didn't want to add it to 7.4 in the middle of beta, or now that
7.4 is RC1.

This argues though for a short release cycle for the jdbc driver that is
off cycle from the rest of the the database to get this out in a
production version sooner rather than later.

thanks,
--Barry

Oliver Jowett wrote:
> On Thu, Nov 06, 2003 at 02:13:15PM -0500, James Robinson wrote:
>
>
>> 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
>> );
>
>
> [...]
>
>
>>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).
>
>
> There's a patch pending that should fix this problem .. it adds an explicit
> typecast to all literals that reflects the type provided to JDBC (either
> implied by the method used, or from the java.sql.Types value for
> setObject()). So the above query actually turns into:
>
> SELECT COUNT(*) FROM foo WHERE (id=1234::int8)
>
> if you use setLong() or setObject(..., Types.BIGINT) to set the parameter.
>
> This does break other cases (e.g. using setLong() when you actually want an
> int4 value) but it's necessary to get consistency between the PREPARE and
> non-PREPARE paths.. and it's almost an application error in this case
> anyway.
>
> I'm not sure what the plan is for applying the patch though -- Barry?
>
> [...]
>
>
>>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.
>
>
> This is essentially the "fix" that was tried earlier at the driver level,
> but it does break other cases so it's not a general solution.
>
> -O
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>

On Nov 6, 2003, at 6:09 PM, Oliver Jowett wrote:

> There's a patch pending that should fix this problem .. it adds an
> explicit
> typecast to all literals that reflects the type provided to JDBC
> (either
> implied by the method used, or from the java.sql.Types value for
> setObject()). So the above query actually turns into:
>
> SELECT COUNT(*) FROM foo WHERE (id=1234::int8)
>
> if you use setLong() or setObject(..., Types.BIGINT) to set the
> parameter.
>
> This does break other cases (e.g. using setLong() when you actually
> want an
> int4 value) but it's necessary to get consistency between the PREPARE
> and
> non-PREPARE paths.. and it's almost an application error in this case
> anyway.
>

This sounds like a good fix. It was unfortunate that the additional
type info that the PreparedStatement knew was evaporating before it
could be used by the backend to stop some maddness. JBoss does indeed
use the setObject(..., Types.BIGINT) (see line 1278 of
org.jboss.ejb.plugins.cmp.jdbc.JDBCUtil -- that was fun to ultimately
track down), so that should keep it happy.

Thanks!

----
James Robinson
Socialserve.com