Re: Abort state on duplicated PKey in transactions

On Fri, Sep 07, 2001 at 04:19:01PM -0500, Haroldo Stenger wrote:
> 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?

Most of the time people just use a sequence for the PK and thus you never
insert a duplicate. Can you do this in your situation?

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

Don't insert duplicates or use smaller transactions?

HTH,
--
Martijn van Oosterhout <kleptog@svana.org>
http://svana.org/kleptog/
> Magnetism, electricity and motion are like a three-for-two special offer:
> if you have two of them, the third one comes free.