Re: JDBC patch, so that bigint indexes get used

This is now 'fixed' in current sources.

--Barry

João Paulo Caldas Ribeiro wrote:

> 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(at)mediaworks(dot)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
>>
>>
>>
>>
>
>