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

[Greg Sabino Mullane]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160


>> 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.
>
> What you are probably looking for is already there, namely:
>
> $dbh->{pg_server_prepare} = 0;

> So disabling server-side prepares will leave values quoted? Having
> server-side prepares would be very useful though, especially for
> Bioperl-db with its many lookup queries that all use similar
> parameter values.

Yes, it forces DBD::Pg to do the quoting itself, which basically means
that everything is shipped to the server as a single SQL string, and
no placeholders are used. In the grand scheme of things, the speed
difference is not large for most queries. Certainly one way would be
to turn this on for 8.3 and above, and slowly migrate the queries/schema
over time.

>> There's good reasons for the casting enforcement in 8.3

> I do understand that, but it's also a sharp contrast to other RDBMSs
> that doesn't it make it easier for people to choose Pg when they
> should, and doesn't help writing cross-platform database applications
> either.

I'm not overly familiar with how other databases treat this, but I've
heard DB2 can be a stickler about this too. I've not dug into the bioperl
code in a while, to be honest, so I'm not sure what sort of queries we're
talking about. Certainly long-term the code and schema should move away
from implicit casting. Maybe a better short-term solution is addind
the more obvious casts (e.g. text<->int) back in.

> Do you have links to some of the key threads showing what rationale
> went into the decision? (Or should I just search for your name?) I'd
> like to read up on that first before pouring more oil into the fire.
> I suspect that many of those who made the decision are never faced
> with needing to write cross-RDBMS code.
>
> Also, I wonder why this wasn't made a configurable option so it can
> be disabled by a simple config file change (such as the move away
> from automatic OID columns). But obviously this is the wrong list for
. discussing this (though Bioperl-db *is* one of those pieces of
> software that must be cross-RDBMS).

I did ask about that, and was told it would not have been easy to do so.
But I agree, a phasing in period (heck, even a warning) would have been
nice. Feel free to pour some oil on the fire, I think this is one of
many apps that has been affected. (I've run across two other major
cross-DB apps (Interchange and MediaWiki) that are struggling with the
same pain. I managed to painfully fix the latter, but the former is way
too complex to tackle at the moment).

I could not find the thread(s?) I weighed in on, but you can find some
relevant discussions by googling "strict-typing benefits grokbase"

- --
Greg Sabino Mullane greg at turnstep.com
PGP Key: 0x14964AC8 200803232039
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iEYEAREDAAYFAkfm+NAACgkQvJuQZxSWSsi4ogCdGNWvCJIzXxb+YKzdm6wwxQMv
p3AAnizkWXoo/rvxv4KVdC8tD0vF87k3
=dNYi
-----END PGP SIGNATURE-----