Re: 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 <bock(at)openit(dot)de> wrote:

> Hi
>
> >>>>> "DK" == Dmitry Koterov <dmitry(at)koterov(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>