Re: JDBC patch, so that bigint indexes get used - Mailing list pgsql-jdbc

Hi!

First, as you said correctly this NOT a JDBC driver problem but a
PostgresSQL PROBLEM.
Second, this is not new. I made a patch my self months ago. It was
exactely the sam thing :) but i used to substitue the
"set(parameterIndex, Long.toString(x));" by
" setString(parameterIndex, Long.toString(x));". It's a matter fact this
work perfectly but as Barry Lind ( Hi, Barry! :) ) said to me when a
told about my patch, this not the correct way to do this is definetely
an "Hack" not  the true solution for the problem because the problem
have to be fixed where it belong:  in the PostgreSql code.
I will forward you the mail Barry Lind sent to me and  I suggest you the
same he suggested try to force Postgresql team to correct this problem
defenitely.

Regards.
JP

PS: Keep the good work. :)




Teofilis Martisius wrote:

>Hello,
>
>Are you aware of this old problem that postgresql doesn't use bigint indexes
>when comparing with integers? Well, adding quotes or explicit casts fixes this.
>
>=> create table aaa (aaa bigint);
>=> create index aaa_aaa on aaa(aaa);
>=> explain select * from aaa where aaa=2;
>Seq Scan on aaa  (cost=0.00..22.50 rows=5 width=8)
>=> explain select * from aaa where aaa='2';
>Index Scan using aaa_aaa on aaa  (cost=0.00..17.07 rows=5 width=8)
>=> explain select * from aaa where aaa=2::bigint;
>Index Scan using aaa_aaa on aaa  (cost=0.00..17.07 rows=5 width=8)
>
>I just finished fixing JDBC driver (one that comes with postgres 7.2.2)
>to add quotes to PreparedStatement.setLong(), and PreparedStatement.setObject()
>methods, when setting numeric values. It gave me a big performance improvement.
>
>I appended the patch. Apply it to $postgresql/src/interfaces/jdbc tree.
>
>Teofilis Martisius
>teo@mediaworks.lt
>
>diff -u -r /usr/src/postgresql-7.2.2/src/interfaces/jdbc/org/postgresql/jdbc1/PreparedStatement.java
./org/postgresql/jdbc1/PreparedStatement.java
>--- /usr/src/postgresql-7.2.2/src/interfaces/jdbc/org/postgresql/jdbc1/PreparedStatement.java    2002-01-15
09:37:33.000000000+0200 
>+++ ./org/postgresql/jdbc1/PreparedStatement.java    2002-08-29 15:47:23.000000000 +0200
>@@ -203,7 +203,7 @@
>      */
>     public void setLong(int parameterIndex, long x) throws SQLException
>     {
>-        set(parameterIndex, Long.toString(x));
>+        set(parameterIndex, "'"+Long.toString(x)+"'");
>     }
>
>     /*
>diff -u -r /usr/src/postgresql-7.2.2/src/interfaces/jdbc/org/postgresql/jdbc2/PreparedStatement.java
./org/postgresql/jdbc2/PreparedStatement.java
>--- /usr/src/postgresql-7.2.2/src/interfaces/jdbc/org/postgresql/jdbc2/PreparedStatement.java    2002-01-15
09:37:33.000000000+0200 
>+++ ./org/postgresql/jdbc2/PreparedStatement.java    2002-08-29 16:17:32.000000000 +0200
>@@ -211,7 +211,7 @@
>      */
>     public void setLong(int parameterIndex, long x) throws SQLException
>     {
>-        set(parameterIndex, Long.toString(x));
>+        set(parameterIndex, "'"+Long.toString(x)+"'");
>     }
>
>     /*
>@@ -674,7 +674,7 @@
>                 if (x instanceof Boolean)
>                     set(parameterIndex, ((Boolean)x).booleanValue() ? "1" : "0");
>                 else
>-                    set(parameterIndex, x.toString());
>+                    set(parameterIndex, "'"+x.toString()+"'");
>                 break;
>             case Types.CHAR:
>             case Types.VARCHAR:
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 6: Have you searched our list archives?
>
>http://archives.postgresql.org
>
>
>
>


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