Re: One little tip for int8 and decimal :)

João,

This has been discussed before on the mailing list and rejected as the
wrong way to fix the problem.  The correct way to fix this problem is to
fix the problem in the backend, not to work around the problem in each
of the front ends.

--Barry


João Paulo Ribeiro wrote:
> 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
>
>