Thread: "Fix" for INT8 literals being parsed as INT4 disqualifying index scan problem in JBoss ...
"Fix" for INT8 literals being parsed as INT4 disqualifying index scan problem in JBoss ...
From
James Robinson
Date:
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
Re: "Fix" for INT8 literals being parsed as INT4 disqualifying index scan problem in JBoss ...
From
Oliver Jowett
Date:
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 >
Re: "Fix" for INT8 literals being parsed as INT4 disqualifying index scan problem in JBoss ...
From
James Robinson
Date:
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