Hi all,
I know this is a known thing encountered by users. After digging
through some threads in the archives I just wanted to make sure I
understand the problem correctly, and I wanted to know what the
thoughts of the developers are on this behaviour and whether there
are any plans to change it.
As a brief backgrounder how I ran into this, I have a library of
perl modules that basically make bioperl objects persistent
(bioperl-db, www.bioperl.org for the curious), which involves a
whole series of dependent parent/child inserts and associations. The
library leaves transaction start and end entirely to the client,
with the most sensible transaction granularity being the whole
object succeeded or not.
The flow of code as I designed it is that inserts may fail due to
unique key violations; if they do, the respective piece of code
either locates the particular object that could not be inserted, or
it ignores the failure for associations (since it indicates that the
association is already there).
This works nicely with both Oracle and MySQL (InnoDB-transactions),
which only abort the failed statement, but not with Postgres, since
it would abort the entire transaction.
Apart from turning off transactions (which would have other
detrimental effects), the only work-around seems to me to rewrite
the code flow such that a look-up is issued before any insert. Since
for the overwhelming majority of those inserts there would be no UK
violation, adding a extra look-up seems also potentially expensive
to me.
I may be missing or overlooking something. Any thoughts appreciated.
Are there any thoughts among the developers to make Postgres behave
more similar to other RDBMSs in this respect (although I can't make
any claims as to how those other than Oracle and MySQL/InnoDB behave
in this regard).
-hilmar
--
-------------------------------------------------------------
Hilmar Lapp email: lapp at gnf.org
GNF, San Diego, Ca. 92121 phone: +1-858-812-1757
-------------------------------------------------------------
