[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
-------------------------------------------------------------