Обсуждение: Weblogic DuplicateKeyException and JDBC driver

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

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
>

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
>