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 <bock@openit.de> wrote:
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.
