One little tip for int8 and decimal :)

Hi!

We are working with java and postgresql for a while.

In our experiences we have seen the problem with int8 and decimal:
postgres dont convert this types easyli and because of this the result
sometimes wil not be the expected.

A simple example:
We have this table:

create table test(

data int8 NOT NULL PRIMARY KEY

);

we put n lines (n> 70000) :)

if we try to make query withou explicit cast the postgres will not use
the index.
Example:

pvs=# explain select * from test where data=12345;

NOTICE: QUERY PLAN:

Seq Scan on test (cost=0.00..22.50 rows=1 width=8)

EXPLAIN

pvs=#

but with a explicit cast:

pvs=# explain select * from test where data=12345::int8;

NOTICE: QUERY PLAN:

Index Scan using test_pkey on test (cost=0.00..4.82 rows=1 width=8)

EXPLAIN

pvs=#

another aproach is to force the postgresql to evaluate and transform the
value to the desired datatype using quotes '

pvs=# explain select * from test where data='12345';

NOTICE: QUERY PLAN:

Index Scan using test_pkey on test (cost=0.00..4.82 rows=1 width=8)

EXPLAIN

pvs=#

This problem is well known for the postgres user.
But the problem go further when you use JDBC to access the postgresql.
Using the same table.
We have a little program that make a simple query:
...

DBConnection con = someKindOfDbPool.allocateConnection();

PreparedStatement ps = con.prepareStatement("Select * from user2 where obid=?");

ps.setlong(1,123456);

ps.executeQuery();

...

This query will never use the index because of the problem explained above.
We can use setBigDecimal and problem will persist.

I use DODs with Enhydra and the data layer generated by the DODs have
this problem.

What we propose is to change the prepared statment to force postgres to
correctly use the index and the result will be the expected. :)
For example, at the office we made a little change to the setLong and
setBigDecimal from PreparedStatement class.

The orginal look like:

public void setBigDecimal(int parameterIndex, BigDecimal x) throws SQLException
{
if (x == null)
setNull(parameterIndex, Types.OTHER);
else
set(parameterIndex, x.toString());
}

public void setLong(int parameterIndex, long x) throws SQLException {
set(parameterIndex, (new Long(x)).toString());
}

and we changed de set(...) to setString(..) and its look like:

public void setBigDecimal(int parameterIndex, BigDecimal x) throws SQLException {
if (x == null)
setNull(parameterIndex, Types.OTHER);
else
setString(parameterIndex, x.toString());
}

public void setLong(int parameterIndex, long x) throws SQLException {
setString(parameterIndex, (new Long(x)).toString());

}

With this change when we use the setBigdecimal or the setLong in a query and we expect that a index will be used, it will really be used. :)

This has been working in a production database for couple of month and is really working fine.

Regards.

João Paulo Ribeiro & Marco Leal

--
----------------------------------------------------------------------------
MobiComp - Mobile Computing & Wireless Solutions
phone: +351 253 305 250 fax: +351 253 305 251
web: http://www.mobicomp.com
----------------------------------------------------------------------------