[BioSQL-l] postgres 8.3 will not cast text to integer any longer

[Hilmar Lapp]

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 :
===========================================================