Re: Why does primary key violation cause an abort?

On Mon, 2003-11-10 at 13:12, Jack Orenstein wrote:
> 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.

Because that's how the designers wanted it.
http://archives.postgresql.org/pgsql-sql/2001-11/msg00172.php

--
-----------------------------------------------------------------
Ron Johnson, Jr. ron(dot)l(dot)johnson(at)cox(dot)net
Jefferson, LA USA

"As I like to joke, I may have invented it, but Microsoft made it
popular"
David Bradley, regarding Ctrl-Alt-Del