[Bioperl-l] bioperl-db: posqgresql adaptor

Hilmar Lapp hlapp@gnf.org
Sun, 24 Nov 2002 23:37:44 -0800


I applied this. Thanks Yves for your contribution.

It turns out there is a little snag before getting Postgres fully 
supported. If an insert/update/delete fails in Postgres, the entire 
transaction you were in is aborted, not just that statement (which 
is what happens with Oracle and MySQL). In fact, all subsequent 
statements will also fail until you ended the transaction with 
commit or rollback.

For the way the code works right now this is pretty bad, because 
inserts failing due to violated unique key constraints are expected 
and reacted upon as needed (they trigger a find if was an object, 
and are ignored if it was an association). Note that this is not a 
limitation of the DBD::Pg driver.

The only work-around is to rewrite the instruction flow such that 
every insert is preceded by a look-up. This not only takes a while, 
it is also potentially expensive (rows being there already is under 
many circumstances rather unlikely, so you issue many queries for 
which you know that they won't return a row 99% of the time).

Maybe I'm overlooking something, and I'm also going to make sure I 
understand Postgres correctly by querying their mailing list.

	-hilmar

On Tuesday, November 19, 2002, at 05:26 AM, Yves Bastide wrote:

> Hi,
>
> attached is a patch to use bioperl-db with postgresql.  It consists 
> of 2 things:
>
> * the adapter, Bio/DB/BioSQL/Pg/* and Bio/DB/DBI/Pg.pm
>
> * a change to Bio/DB/BioSQL/BasePersistenceAdaptor.pm to not raise 
> an exception on bind_param without bindable param (in WHERE 1 = 1).
>
> Yves
>
--
-------------------------------------------------------------
Hilmar Lapp                            email: lapp at gnf.org
GNF, San Diego, Ca. 92121              phone: +1-858-812-1757
-------------------------------------------------------------