Hi,
I have been testing this for a while and observed a server crash while referencing table column value in a trigger procedure for AFTER DELETE trigger.
-- Steps to reproduce:
CREATE TABLE t1(c1 int);
CREATE TABLE t2(cc1 int);
INSERT INTO t1 VALUES (10);
INSERT INTO t2 VALUES (10);
CREATE OR REPLACE FUNCTION trig_func() RETURNS trigger AS
$$ BEGIN
DELETE FROM t1 WHERE c1 IN (select OLD.cc1 from my_old);
RETURN OLD;
END; $$ LANGUAGE PLPGSQL;
CREATE TRIGGER trg1
AFTER DELETE ON t2
REFERENCING OLD TABLE AS my_old
FOR EACH ROW
EXECUTE PROCEDURE trig_func();
DELETE FROM t2 WHERE cc1 =10;
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.
On further testing it without delta relations in AFTER trigger,it executed successfully.
CREATE OR REPLACE FUNCTION trig_func() RETURNS trigger AS
$$ BEGIN
DELETE FROM t1 WHERE c1 IN (select OLD.cc1 from t2);
RETURN OLD;
END; $$ LANGUAGE PLPGSQL;
CREATE TRIGGER trg1
AFTER DELETE ON t2
FOR EACH ROW
EXECUTE PROCEDURE trig_func();
DELETE FROM t2 WHERE cc1 =10;
DELETE 1
postgres=# select * from t1;
c1
----
10
(1 row)
postgres=# select * from t2;
cc1
-----
(0 rows)
Logfile and core dump attached for reference.