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"
