Stange "duplicate key value violates unique constraint" after "delete" at ON UPDATE trigger

From: Dmitry Koterov <dmitry(at)koterov(dot)ru>
To: Postgres General <pgsql-general(at)postgresql(dot)org>
Subject: Stange "duplicate key value violates unique constraint" after "delete" at ON UPDATE trigger
Date: 2012-01-26 20:04:55
Message-ID: CA+CZih6PweAP5DNOv+HU0N=b_tOJA=si3yRWCO4oG-QT_ZHLhw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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"

Responses

Browse pgsql-general by date

  From Date Subject
Next Message salah jubeh 2012-01-26 20:41:59 Re: Help needed creating a view
Previous Message David Johnston 2012-01-26 19:50:45 Re: Help needed creating a view