Re: Weblogic DuplicateKeyException and JDBC driver

Barry,
         Thanks for the reply. Are there any plans to address this ? Should
I post this to a different list ?

         Tim

At 09:53 PM 1/23/2003 -0800, Barry Lind wrote:
>Tim,
>
>This is a well known limitation of postgres and really has nothing to do
>with the jdbc driver specifically.  There isn't anything that the jdbc
>driver can do fix this limitation, it is something that needs to be done
>in the backend server itself.
>
>thanks,
>--Barry
>
>
>Tim Farrell wrote:
>>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
>>
>>---------------------------(end of broadcast)---------------------------
>>TIP 3: if posting/reading through Usenet, please send an appropriate
>>subscribe-nomail command to majordomo@postgresql.org so that your
>>message can get through to the mailing list cleanly
>