why not type casting by default in prepared statements? - Mailing list pgsql-jdbc

as you all know (well, all except me until yesterday night 3am ;-) postgres
ignores an index if the proper type of a constant isn't used in
the query, e.g. with "select * from address where id=123" and 'id' being
an int8, postgres insists on a sequential scan even if 'id' is a primary
key and even if you turned off ENABLE_SEQSCAN and did a VACCUUM ANALYZE
and all that stuff I went through the last three days figuring out why
updating a row in a table with 200k records was sooooo sloooooow
while other databases like Inf... and Or... ran like hell ;-/
Well, an explicit type cast like "id=123::int8" does the trick.

However, I'm not satisfied with that solution, cause when it comes to
portability (isn't that one of the reasons why we're using Java/JDBC?)
the Java code looks suspiciously ugly, like this: FIELD_ID + "=?" + "::INT8"

So, the short story is: I patched the JDBC driver in
org/postgresql/jdbc1/AbstractJdbc1Statement.java
to do the proper type casting by default, i.e. adding the
"::<type>" automatically where appropriate.

My application is flying as it did before it was
filled with real production data and I'm reconciled
with postgres again.

But now I'm wondering whether there are any good reasons
not to type cast by default?
Any suggestions?

Harald.


Anyway, here's the patch:


801c801
<               bind(parameterIndex, Integer.toString(x), PG_TEXT);
---
>               bind(parameterIndex, "'" + Integer.toString(x) + "'",
PG_TEXT);
814c814
<               bind(parameterIndex, Integer.toString(x), PG_INT2);
---
>               bind(parameterIndex, Integer.toString(x) + "::INT2", PG_INT2);
840c840
<               bind(parameterIndex, Long.toString(x), PG_INT8);
---
>               bind(parameterIndex, Long.toString(x) + "::INT8", PG_INT8);
853c853
<               bind(parameterIndex, Float.toString(x), PG_FLOAT);
---
>               bind(parameterIndex, Float.toString(x) + "::FLOAT4",
PG_FLOAT);
884c884
<                       bind(parameterIndex, x.toString(), PG_NUMERIC);
---
>                       bind(parameterIndex, "'" + x.toString() + "'",
PG_NUMERIC);