[GENERAL] Why does primary key violation cause an abort?

[Jack Orenstein]

I am using Postgres 7.3.4 through JDBC, and turning auto-commit off. My 
application needs to insert a row or, if a row with the same primary key 
already exists, update the existing row. I was hoping to implement this 
 by just trying the insert, and doing the update only in case of a PK 
violation (which results in a SQLException). I've run into two problems.

1) Detecting a PK violation cannot be done cleanly. The violation 
results in a SQLException, and the only way I can see to distinguish a 
PK violation from some other problem is to check the text of the error 
message returned by SQLException.getMessage(). 
(SQLException.getErrorCode() returns 0, and getSQLState() returns null). 
It would be nice if the error code clearly identified a PK violation, 
(or even just a uniqueness violation).

2) The more serious problem is that the PK violation causes an abort of 
the transaction, so I can't proceed to do the update in the same 
transaction. Yes, there are easy ways to code around this problem, but 
they are going to be slower. Duplicates are very unlikely in my 
application, so if I update, and then do the insert on an update count 
of zero, I will end up executing twice as many commands as I would 
otherwise.

Why does PostgreSQL abort a transaction when a PK violation occurs? The 
closest I was able to find was this:

    http://archives.postgresql.org/pgsql-hackers/2002-06/msg00325.php

but it doesn't really answer my question.

I can understand this behavior for pgplsql programs, where exceptions 
cannot be caught, but it seems to be an unnecessary restriction for 
Java, and in general, for applications written using APIs that permit 
continuation following an error.

Jack Orenstein
Reference Information Systems, Inc.
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]