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 <[email protected]> wrote:
> Hi
>
> >>>>> "DK" == Dmitry Koterov <[email protected]> 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 ([email protected])
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
