why not type casting by default in prepared statements?

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);

Harald Krake <harald(at)krake(dot)de> writes:
> But now I'm wondering whether there are any good reasons
> not to type cast by default?

This has been suggested, and rejected, repeatedly. It's not a good
idea to apply a driver-side band-aid to mask a server-side problem;
especially not when the driver does not have adequate information to
fix the problem properly. (In other words, any driver-side solution
will fix some queries and break others.)

There will be a real solution in the backend someday soon, perhaps 7.4.

regards, tom lane

On Saturday 16 November 2002 05:20 pm, Tom Lane wrote:
> This has been suggested, and rejected, repeatedly.

ah!
Sorry for the post. Obviously I used the wrong patterns
searching through the archives.

> It's not a good idea to apply a driver-side band-aid to mask a
> server-side problem;

agreed.

> In other words, any driver-side solution
> will fix some queries and break others.

imho it will only break queries written by "postgres-aware" programmers.
I suppose, most of the java/jdbc code written for other
databases does not contain any casts (in prepared statements) because
the programmers leave that up to the server backend and/or the driver.
And if you look at setString(), even the postgres driver casts, respectively
quotes.
What happens if someone tries to move from XY-DB to postgres --
like me -- is that postgres performs very bad compared to XY-DB und thus
might be considered as slow, which it isn't. In my case postgres now
outperforms the former commercial database significantly (approx. 3-5
times faster!).

> There will be a real solution in the backend someday soon, perhaps 7.4.

until then, what about a switch, for instance as an option to the url,
for those who don't want to change their code? (like me ;-)

regards,
harald.