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