I'm not sure the cause is that DELETE does not see the row. Seems the following method solves the problem when 2 same-time transactions are active:
CREATE FUNCTION a_tr() RETURNS trigger AS $body$ DECLARE tmp INTEGER; BEGIN -- Lock until the mathed entry (possibly phantom - i.e. not yet committed -- by another transaction) is released. SELECT i INTO tmp FROM a WHERE i = NEW.i FOR UPDATE; -- The lock is released here in one of two cases: -- 1. Matched row was phantom, so tmp IS NULL now. -- 2. Matched row was real and committed, so tmp holds its ID. -- So we cannot use ID in tmp - it is not always returned. That's why we have to -- duplicate the selection predicate above... DELETE FROM a WHERE i = NEW.i; RETURN NEW; END; $body$ LANGUAGE 'plpgsql'; But this method still does not work if 3 or more transactions are active (if I commit first and commit second, the third fails with "duplicate key" error). Are there any universal method which could be implemented purely in a trigger?.. On Fri, Jan 27, 2012 at 3:45 PM, Julian v. Bock <b...@openit.de> wrote: > Hi > > >>>>> "DK" == Dmitry Koterov <dmi...@koterov.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 > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >