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"
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 |