I'm using PostgreSQL under Weblogic 6.1 and due to the way that postgres
deals with errors during a transaction the code generated by Weblogic
doesn't handle the case when an insert is done for an existing primary key.
The reason the weblogic code fails is due to the fact that postgres ignores
all further queries until the transaction is rolled back. The weblogic code
does something like this:
insert into T values(A,B,C)
if ( insert fails )
select key from T where key = A
if ( A exists )
throw DuplicateKeyexception
As a total hack in the JDBC driver I am catching the exception for the
duplicate key case during the insert, comitting the current transaction and
then allowing the JDBC queries to continue. This kind of works but has
obvious problems.
Without this hack the exception comes through and the weblogic server
proceeds to the insert fails case and then fails on the select of the
primary key(s) used to determine when to throw the DuplicateKeyException,
instead the original SQLException is thrown and our app doesn't know its a
duplicate without parsing the exception message.
What would fix this is if postgresql would allow selects ( maybe restricted
to a very simple form ) when the transaction is in the aborted state not
just rollbacks or commits.
For whats its worth Oracle and DB2 do not have this issue.
BTW, PostgreSQL beats the pants off of Oracle 8i for performance with our
application.
Tim Farrell