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.
