Abort state on duplicated PKey in transactions

From: Haroldo Stenger <hstenger(at)adinet(dot)com(dot)uy>
To: pgsql-general(at)postgresql(dot)org
Cc: dmendez(at)artech(dot)com(dot)uy, fwagner(at)artech(dot)com(dot)uy
Subject: Abort state on duplicated PKey in transactions
Date: 2001-09-07 21:19:01
Message-ID: 3B9939C5.7BA819BC@adinet.com.uy
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi dear people,

I'm not sure if this is for general or for hackers, but let's start here
;-)

We are building a RAD tool (GeneXus) support, for PostgreSQL.

A problem which arose, is that within a transaction, if one inserts on a
table and the PK restriction is violated, the transaction aborts and
leaves itselft in abort state. One has to END the transaction and start
a new one. This is a problem, in large transactions, where lots of
things have been done to the database, and an insertion is to be done,
which may yield an error just because the PK already existed. The whole
transaction should have to be redone if the insertion failed. A
solution, could be to query for the existance of the PK, just before the
insertion. But there is a little span between the test and the
insertion, where another insertion from another transaction could void
the existance test. Any clever ideas on how to solve this? Using
triggers maybe? Other solutions?

I'm aware that savepoints and nested transactions will be implemented in
future versions, but how to solve the problem before that starts
working?

Thanks

Regards,
Haroldo.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Haroldo Stenger 2001-09-07 21:29:45 Abort state on duplicated PKey in transactions
Previous Message Alvaro Herrera 2001-09-07 21:07:11 Re: moving char() to varchar()