Re: 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 <dmitry(at)koterov(dot)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 <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
>>
>
>