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

[Hilmar Lapp]

On Mar 20, 2008, at 8:51 PM, Erik wrote:
> On Fri, March 21, 2008 01:34, Hilmar Lapp wrote:
>>
>> 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.
>
> Pg 8.3 is indeed very noticably faster, and it has other
> excellent new features like full text indexing. (This also
> makes that downgrading is not really an option)

Right, I saw that too. It is, however, just migrated from what was a  
contrib module before, so downgrading and using the contrib module is  
an option.

Furthermore, folding these new features together with a behavior  
change that is backwards incompatible was a choice the PostgreSQL  
people made, not we.

We also aren't doing poor typing that deserves fixing; we're just not  
doing any typing by treating everything as a string. This is the Perl  
paradigm.

At this point it's actually unclear to me how this new behavior is  
compatible with untyped scripting languages unless you know the type  
of each column that you're binding a value for, because if you  
actually force typecasts to string for everything you get an error if  
an integer is indeed what's needed.

I'm wondering what I'm missing.

	-hilmar

BTW what does the following query yield on your 8.3.1 database:

select s.typname as source, t.typname as target, f.proname as  
function, c.castcontextfrom pg_cast c, pg_type s, pg_type t, pg_proc  
f where c.castsource = s.oid and c.casttarget = t.oid and c.castfunc  
= f.oidand t.typname = 'text';

On my 8.1.4 database I get:

   source    | target | function | castcontext
-------------+--------+----------+-------------
  bpchar      | text   | text     | i
  char        | text   | text     | i
  name        | text   | text     | i
  int8        | text   | text     | i
  int2        | text   | text     | i
  int4        | text   | text     | i
  oid         | text   | text     | i
  float4      | text   | text     | i
  float8      | text   | text     | i
  macaddr     | text   | text     | e
  cidr        | text   | text     | e
  inet        | text   | text     | e
  date        | text   | text     | i
  time        | text   | text     | i
  timestamp   | text   | text     | i
  timestamptz | text   | text     | i
  interval    | text   | text     | i
  timetz      | text   | text     | i
  numeric     | text   | text     | i
(19 rows)

-- 
===========================================================
: Hilmar Lapp  -:-  Durham, NC  -:-  hlapp at gmx dot net :
===========================================================