surprising trigger/foreign key interaction

From: Stefan Kaltenbrunner <stefan(at)kaltenbrunner(dot)cc>
To: pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: surprising trigger/foreign key interaction
Date: 2009-08-12 15:54:07
Message-ID: 4A82E59F.1060406@kaltenbrunner.cc
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

While working on some code I ran into a problem where some DELETE
requests would get seamingly ignored after a while I managed to boil it
down to:

CREATE TABLE foo (a INT PRIMARY KEY, b int);
CREATE TABLE bar (x int PRIMARY KEY, a int references foo(a) ON DELETE
SET NULL);

INSERT INTO foo VALUES (1,10);
INSERT INTO bar VALUES (99,1);

CREATE OR REPLACE FUNCTION bar_proc() RETURNS TRIGGER AS $$
BEGIN
IF (TG_OP = 'INSERT') THEN
RETURN NEW;
ELSIF (TG_OP = 'UPDATE') THEN
RETURN NEW;
ELSIF (TG_OP = 'DELETE') THEN
DELETE FROM foo WHERE a=1;
RETURN OLD;
END IF;
RETURN OLD;
END;
$$
LANGUAGE plpgsql SECURITY DEFINER;

CREATE TRIGGER bar_tr BEFORE INSERT OR DELETE ON bar FOR EACH ROW
EXECUTE PROCEDURE bar_proc();

DELETE FROM bar where x=99;

which results in:

CREATE TABLE
INSERT 0 1
INSERT 0 1
CREATE FUNCTION
CREATE TRIGGER
DELETE 0

the "surprise" here was that the delete is getting silently surpressed
even though the original Qual still holds and afaik should result in the
row deleted.
Is that somehow expected behaviour or a bug(or at least something that
should get documented somehow)?

Stefan

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Alvaro Herrera 2009-08-12 16:00:31 Re: COPY speedup
Previous Message Alvaro Herrera 2009-08-12 15:53:26 Re: WIP: getting rid of the pg_database flat file