[BioSQL-l] postgres 8.3 will not cast text to integer any longer
Hilmar Lapp
hlapp at gmx.net
Fri Mar 21 00:34:42 UTC 2008
On Mar 20, 2008, at 7:30 PM, Erik wrote:
> Here is the postgres 8.3.1 result of your sql statements:
>
> CREATE TABLE t1 (a varchar(10), b text, c integer);
>
> SELECT * from t1 WHERE a = 1; -- fails in 8.3.1
> SELECT * from t1 WHERE b = 1; -- fails in 8.3.1
> SELECT * from t1 WHERE c = '1'; -- ok
>
> [...]
> The failure is always (virtually) the same:
> ERROR: operator does not exist: character varying = integer
> LINE 1: SELECT * from t1 WHERE a = 1;
> ^
> HINT: No operator matches the given name and argument
> type(s). You might need to add explicit type casts.
So it's indeed the backend that changed behavior. It's actually
documented as I see now:
http://www.postgresql.org/docs/8.3/static/release-8-3.html
scroll to section E.2.2. Migration to Version 8.3, E.2.2.1. General,
and the first item there:
<quote>
Non-character data types are no longer automatically cast to TEXT
(Peter, Tom)
Previously, if a non-character value was supplied to an operator or
function that requires text input, it was automatically cast to text,
for most (though not all) built-in data types. This no longer
happens: an explicit cast to text is now required for all non-
character-string types.
</quote>
I can see the arguments there but this will prevent upgrading to 8.3
for many many applications, and the comments from the Pg developers
('fix your SQL to use casts') that I've seen there on the mailing
lists are just not helpful. Fixing SQL is for many legacy
applications is just not an option.
In the case of Bioperl-db it's very non-trivial, because all of a
sudden we would be changing from a hands-off and let-the-driver-
figure-it-out approach to forcing types everywhere.
So I think at this point with this change I have to declare Bioperl-
db officially incompatible with PostgreSQL 8.3+ until we've found a
solution to this, which is too bad because it seems 8.3 has some
really nice performance features added.
One possible solution might be to create a CAST in the database
(namely the one that was taken away, restoring behavior to pre-8.3).
Another possibility is to move the parameter binding method into the
driver adaptor which would then delegate to the DBI method but would
be overridden for the PostgreSQL adapter to force all bindings to
type string.
Which leads me back to the surprise observation that the parameter
was bound as an integer in the first place, when DBD::Pg used to bind
everything as string unless you told it otherwise. Which DBD::Pg
version is it that you are using? I would suspect (or hope) that
maybe there is soon an update release of DBD::Pg that fixes this
problem by going back to binding everything as string by default (and
as the tests show PostgreSQL will still convert strings to integer if
necessary).
Depending on what I (or can someone else update us on this?) find out
for the DBD::Pg plans, I'll probably start looking into moving the
parameter binding into the driver adapters. Though it does feel
pathetic that this is now also not transparent between drivers.
-hilmar
--
===========================================================
: Hilmar Lapp -:- Durham, NC -:- hlapp at gmx dot net :
===========================================================
More information about the BioSQL-l
mailing list