PostgreSQL级联删除时触发器无法访问已删除外键记录的问题及解决

场景还原 #

表与触发器定义 #

CREATE TABLE "event"
(
    count           bigserial NOT NULL,
    action          text      NOT NULL,
    child_id        int       NOT NULL,
    persisted_field text      NULL,
    CONSTRAINT "event_pkey" PRIMARY KEY (count)
);

CREATE TABLE "parent_table"
(
    id               int PRIMARY KEY,
    field_to_persist text
);

CREATE TABLE "child_table"
(
    id        int PRIMARY KEY,
    parent_id int,
    CONSTRAINT "child_parent_id_fkey" FOREIGN KEY ("parent_id") REFERENCES "parent_table" ("id") ON DELETE CASCADE
);

CREATE OR REPLACE FUNCTION add_child_trigger() RETURNS TRIGGER AS
$$
DECLARE
    v_id        int = coalesce(new.id, old.id);
    v_parent_id int = coalesce(new.parent_id, old.parent_id);
BEGIN
    INSERT INTO "event" ("action", "child_id", "persisted_field")
    VALUES (tg_op, v_id,
            (SELECT "parent_table"."field_to_persist" FROM "parent_table" WHERE "parent_table"."id" = v_parent_id));

    IF (tg_op = 'DELETE') THEN
        RETURN OLD;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER "on_change_add_change_event"
    BEFORE INSERT OR UPDATE OR DELETE
    ON "child_table"
    FOR EACH ROW
EXECUTE PROCEDURE "add_child_trigger"();

执行的SQL语句 #

INSERT INTO "parent_table" VALUES (1, 'something');
INSERT INTO "child_table" VALUES (1, 1);
INSERT INTO "child_table" VALUES (2, 1);
DELETE FROM "child_table" WHERE "id" = 2;
DELETE FROM "parent_table" WHERE "id" = 1;

执行后event表结果 #

问题 #

单独删除child_table条目时persisted_field字段有值，但通过删除parent_table触发级联删除child_table时，count为4的记录中persisted_field为null。请问该字段为null的原因是什么？如何修改触发器使其值为'something'？

原因分析 #

PostgreSQL处理ON DELETE CASCADE级联删除的顺序是：

1. 执行parent_table的BEFORE DELETE触发器（如果存在）
2. 删除parent_table中的目标记录
3. 触发child_table的BEFORE DELETE触发器，执行级联删除操作
4. 删除child_table中的关联记录

当执行步骤3时，parent_table中的对应记录已经被删除，此时触发器函数中查询parent_table.field_to_persist会返回null，最终导致event表中persisted_field字段为null。

而单独删除child_table时，parent_table的记录仍然存在，触发器能正常查询到字段值，所以persisted_field有值。

解决方案 #

要解决这个问题，需要在parent记录被删除前，预先获取关联child对应的parent字段值并插入event表，同时避免重复插入事件记录。具体步骤如下：

1. 修改child_table的触发器函数 #

修改后的函数会在DELETE操作时，仅当parent记录仍存在时才插入event记录（避免级联删除时重复插入）：

CREATE OR REPLACE FUNCTION add_child_trigger() RETURNS TRIGGER AS
$$
DECLARE
    v_id        int = coalesce(new.id, old.id);
    v_parent_id int = coalesce(new.parent_id, old.parent_id);
    v_persisted_field text;
BEGIN
    IF tg_op != 'DELETE' THEN
        SELECT "parent_table"."field_to_persist" INTO v_persisted_field
        FROM "parent_table" WHERE "parent_table"."id" = v_parent_id;
        
        INSERT INTO "event" ("action", "child_id", "persisted_field")
        VALUES (tg_op, v_id, v_persisted_field);
    ELSE
        -- 仅当parent记录存在时插入，级联删除时parent已被删，跳过
        IF EXISTS (SELECT 1 FROM "parent_table" WHERE id = OLD.parent_id) THEN
            SELECT "parent_table"."field_to_persist" INTO v_persisted_field
            FROM "parent_table" WHERE "parent_table"."id" = OLD.parent_id;
            
            INSERT INTO "event" ("action", "child_id", "persisted_field")
            VALUES (tg_op, v_id, v_persisted_field);
        END IF;
    END IF;

    IF (tg_op = 'DELETE') THEN
        RETURN OLD;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

2. 创建parent_table的BEFORE DELETE触发器 #

该触发器会在parent记录被删除前，为所有关联的child插入DELETE类型的event记录，此时parent记录仍存在，能正确获取field_to_persist的值：

CREATE OR REPLACE FUNCTION parent_delete_child_event_trigger() RETURNS TRIGGER AS
$$
BEGIN
    -- 删除parent前，为所有关联child插入DELETE事件
    INSERT INTO "event" ("action", "child_id", "persisted_field")
    SELECT 'DELETE', c.id, OLD.field_to_persist
    FROM "child_table" c
    WHERE c.parent_id = OLD.id;
    
    RETURN OLD;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER "on_parent_delete_add_child_events"
    BEFORE DELETE
    ON "parent_table"
    FOR EACH ROW
EXECUTE PROCEDURE parent_delete_child_event_trigger();

效果验证 #

执行原SQL语句后，event表中count为4的记录persisted_field字段值会变为something，符合预期。

内容的提问来源于stack exchange，提问作者Tobias Marschall