Skip site navigation (1) Skip section navigation (2)

Re: Stange "duplicate key value violates unique constraint" after "delete" at ON UPDATE trigger

From: bock(at)openit(dot)de (Julian v(dot) Bock)
To: dmitry(at)koterov(dot)ru
Cc: Postgres General <pgsql-general(at)postgresql(dot)org>
Subject: Re: Stange "duplicate key value violates unique constraint" after "delete" at ON UPDATE trigger
Date: 2012-01-27 11:45:47
Message-ID: lp62fxl66s.fsf@warpcore.i.openit.de (view raw or flat)
Thread:
Lists: pgsql-general
Hi

>>>>> "DK" == Dmitry Koterov <dmitry(at)koterov(dot)ru> writes:

DK> create table a(i integer);
DK> CREATE UNIQUE INDEX a_idx ON a USING btree (i);
DK> CREATE FUNCTION a_tr() RETURNS trigger AS
DK> $body$
DK> BEGIN
DK>     DELETE FROM a WHERE i = NEW.i;
DK>     RETURN NEW;
DK> END;
DK> $body$
DK> LANGUAGE 'plpgsql';
DK> CREATE TRIGGER a_tr BEFORE INSERT ON a FOR EACH ROW EXECUTE PROCEDURE
DK> a_tr();

The DELETE doesn't see the row the other transaction inserted and
doesn't delete anything (and doesn't block). This happens later when the
row is inserted and the index is updated.

You can try the insert and catch the unique violation in a loop (see
http://www.postgresql.org/docs/9.1/static/plpgsql-control-structures.html)
although that won't work with a BEFORE trigger.

Regards,
Julian

-- 
Julian v. Bock               Projektleitung Software-Entwicklung
OpenIT GmbH                  Tel +49 211 239 577-0
In der Steele 33a-41         Fax +49 211 239 577-10
D-40599 Düsseldorf           http://www.openit.de
________________________________________________________________
HRB 38815 Amtsgericht Düsseldorf             USt-Id DE 812951861
Geschäftsführer: Oliver Haakert, Maurice Kemmann

In response to

Responses

pgsql-general by date

Next:From: David JohnstonDate: 2012-01-27 14:02:51
Subject: Fwd: [SQL] Query question
Previous:From: Chris TraversDate: 2012-01-27 11:03:48
Subject: Re: Don't Thread On Me (PostgreSQL related)

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group