Thread: PrimaryKeyExists Error

Dear psql friends,
I am trying to replace an Oracle Db by Postgresql.
The problem is that I am using Borland Appserver
application which talks to the oracle db. After i
changed to a postgresql db, i re-imported the data
schemas in JBuilder and changed the driver to jdbc.
all are fine. but when i tried to insert into the db,
tables with a single primary key field was inserted
fine, but tables with composit primary keys reports a
"primaryKeyExists" error like this:

-- Calling
create(Stub[repository_id=RMI:au.com.cybernetics.thinkplando.ejb.ActivityRemote:000000000000
...

java.rmi.ServerException: java.rmi.ServerException:
primaryKeyExists failed; nested exception is:
java.sql.SQLException: ERROR:  current transaction is
aborted, queries ignored until end of transaction
block
; nested exception is:
    java.sql.SQLException: ERROR:  current transaction is
aborted, queries ignored until end of transaction
block


java.sql.SQLException: ERROR:  current transaction is
aborted, queries ignored until end of transaction
block


    at
org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:131)

    at
org.postgresql.jdbc1.AbstractJdbc1Connection.ExecSQL(AbstractJdbc1Connection.java:505)


etc etc

the ejb's create method failed, but i have printed out
the primary keys valules that i was trying o insert,
and it is unique.

Does anyone have any experience like mine? is this
caused by the internal queries generated by jdbc???


Any help or comment will be much appreciated!!!

Yan Yan

http://mobile.yahoo.com.au - Yahoo! Mobile
- Exchange IMs with Messenger friends on your Telstra or Vodafone mobile phone.

This is actually a PostgreSQL issue and not a JDBC issue.  PostgreSQL
transactioning has a flaw in that once an exception is thrown the transaction
is aborted and you must call rollback before any further SQL.  That is why
you are getting the message "current transaction is aborted, queries ignored
until end of transaction".  Borland Appserver is probably calling some SQL
that results in an exception, catching the exception, and expecting to be
able to run a different SQL statement.  This is very common, especially on
applications writting originally for Oracle.  Unfortunately, this will not
work in PostgreSQL.

I have lobbied to have this changed, since this and the write locks taken by
foreign keys are the only two things keeping PostgreSQL from being able to be
used instead of Oracle on most every project I have ever done.
Unfortunately, there is still no concept of save points (internal or
external) in PostgreSQL so continuing after an exception is not possible.

Hope this helps explain things.

On Monday 17 February 2003 03:20 pm, pgsql-jdbc-owner@postgresql.org wrote:
> Date: Mon, 17 Feb 2003 15:21:20 +1100 (EST)
> From: =?iso-8859-1?q?Yan=20Yan=20Che?= <yysydney@yahoo.com.au>
> To: pgsql-jdbc@postgresql.org
> Subject: PrimaryKeyExists Error
> Message-ID: <20030217042120.39469.qmail@web11304.mail.yahoo.com>
>
>
> Dear psql friends,
> I am trying to replace an Oracle Db by Postgresql.
> The problem is that I am using Borland Appserver
> application which talks to the oracle db. After i
> changed to a postgresql db, i re-imported the data
> schemas in JBuilder and changed the driver to jdbc.
> all are fine. but when i tried to insert into the db,
> tables with a single primary key field was inserted
> fine, but tables with composit primary keys reports a
> "primaryKeyExists" error like this:
>
>
> -- Calling
> create(Stub[repository_id=RMI:au.com.cybernetics.thinkplando.ejb.ActivityRe
>mote:000000000000 ...
>
>
> java.rmi.ServerException: java.rmi.ServerException:
> primaryKeyExists failed; nested exception is:
> java.sql.SQLException: ERROR:  current transaction is
> aborted, queries ignored until end of transaction
> block
> ; nested exception is:
>         java.sql.SQLException: ERROR:  current transaction is
> aborted, queries ignored until end of transaction
> block
>
>
>
> java.sql.SQLException: ERROR:  current transaction is
> aborted, queries ignored until end of transaction
> block