Re: Why does primary key violation cause an abort?
От | Ron Johnson |
---|---|
Тема | Re: Why does primary key violation cause an abort? |
Дата | |
Msg-id | 1068502932.3277.169.camel@haggis.homelan обсуждение исходный текст |
Ответ на | Why does primary key violation cause an abort? (Jack Orenstein <jorenstein@reference-info.com>) |
Список | pgsql-general |
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.l.johnson@cox.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
В списке pgsql-general по дате отправления: