I have had 2 hours of experiments and finally I suppose that there is no way to satisfy this unique constraint index from within a trigger with non-whole-table locking. So Julian seems to be right (unfortunately). Only
LOOP BEGIN INSERT ...; EXIT; EXCEPTION WHEN unique_violation THEN DELETE FROM ... WHERE <unique constraint predicate>; END; END LOOP; construction helps. There seems to be no way to implement the same using triggers only. On Thu, Apr 26, 2012 at 3:39 PM, Dmitry Koterov <dmi...@koterov.ru> wrote: > 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 >> > >