Re: [GENERAL] Stange duplicate key value violates unique constraint after delete at ON UPDATE trigger
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
Re: [GENERAL] Stange duplicate key value violates unique constraint after delete at ON UPDATE trigger
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
Re: [GENERAL] Stange duplicate key value violates unique constraint after delete at ON UPDATE trigger
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
[GENERAL] Stange duplicate key value violates unique constraint after delete at ON UPDATE trigger
Hello. PostgreSQL 8.4. I am trying to create a trigger which removes old rows from the table just before inserting a new one. The table has an UNIQUE INDEX on a column, that's why I need to remove an old item with the same value of the column before inserting a new one. If I work without transactions (in auto-commit mode), all seems to be fine. But something strange is happened when I use transactions. The following SQL represents the problem. How to avoid strange duplicate key value violates unique constraint error (with minimum locking level)?.. And why this error happens at all? -- Prepare the fixture. create table a(i integer); CREATE UNIQUE INDEX a_idx ON a USING btree (i); CREATE FUNCTION a_tr() RETURNS trigger AS $body$ BEGIN DELETE FROM a WHERE i = NEW.i; RETURN NEW; END; $body$ LANGUAGE 'plpgsql'; CREATE TRIGGER a_tr BEFORE INSERT ON a FOR EACH ROW EXECUTE PROCEDURE a_tr(); -- Check if the trigger really works. No unique constraint errors are thrown. insert into a values(1); insert into a values(1); -- ok -- NOW IN CONNECTION (A): begin; insert into a values(1); -- do not commit! -- THEN IN CONNECTION (B): insert into a values(1); -- it hangs, because the connection (A) is not committed - ok -- NOW IN CONNECTION (A) AGAIN: commit; -- ok -- WE SEE AT CONNECTION (B) THE THE FOLLOWING IMMEDIATELY: ERROR: duplicate key value violates unique constraint a_idx
