Re: Weblogic DuplicateKeyException and JDBC driver

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(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>