surprising trigger/foreign key interaction
От | Stefan Kaltenbrunner |
---|---|
Тема | surprising trigger/foreign key interaction |
Дата | |
Msg-id | 4A82E59F.1060406@kaltenbrunner.cc обсуждение исходный текст |
Ответы |
Re: surprising trigger/foreign key interaction
|
Список | 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
В списке pgsql-hackers по дате отправления: