PostgreSQL删除前触发器更新其他行时批量删除报错问题

问题根源 #

行级BEFORE DELETE触发器在批量删除同一分组元素时，会逐行触发并尝试更新同组其他行。当同一行被多个触发器实例修改时，PostgreSQL会抛出ERROR: tuple to be updated was already modified by an operation triggered by the current command——因为同一命令触发的触发器不允许重复修改同一元组。

可靠方案：改用语句级BEFORE DELETE触发器 #

语句级触发器会在整个删除命令执行前一次性处理所有待删除行，从根源避免逐行触发带来的重复修改问题。

步骤1：假设表结构 #

CREATE TABLE group_master (
    id INT PRIMARY KEY,
    group_ref INT REFERENCES group_master(id) -- 自引用外键，关联分组主ID
);

步骤2：创建触发器函数 #

CREATE OR REPLACE FUNCTION reassign_group_members()
RETURNS TRIGGER AS $$
DECLARE
    deleted_ids INT[];
    target_group_ref INT;
    new_master_id INT;
BEGIN
    -- 收集本次删除操作涉及的所有group_master ID
    SELECT array_agg(id) INTO deleted_ids FROM OLD;

    -- 遍历每个待删除的分组主ID，处理对应分组的成员
    FOREACH target_group_ref IN ARRAY deleted_ids LOOP
        -- 获取当前分组的标识（待删除行若为分组主节点，group_ref为NULL，用自身ID代替）
        SELECT COALESCE(group_ref, id) INTO target_group_ref
        FROM group_master WHERE id = target_group_ref;

        -- 从同组中选出未被删除的新主ID（此处选最小ID，可根据业务调整规则）
        SELECT MIN(id) INTO new_master_id
        FROM group_master
        WHERE COALESCE(group_ref, id) = target_group_ref
          AND id NOT IN (SELECT unnest(deleted_ids));

        -- 若存在有效新主ID，批量更新同组成员的外键
        IF new_master_id IS NOT NULL THEN
            UPDATE group_master
            SET group_ref = new_master_id
            WHERE COALESCE(group_ref, id) = target_group_ref
              AND id NOT IN (SELECT unnest(deleted_ids));
        END IF;
    END LOOP;

    RETURN OLD;
END;
$$ LANGUAGE plpgsql;

步骤3：创建语句级触发器 #

CREATE TRIGGER trigger_reassign_group_before_delete
BEFORE DELETE ON group_master
FOR EACH STATEMENT
EXECUTE FUNCTION reassign_group_members();

备选方案：行级触发器加防重复修改判断 #

如果必须保留行级触发器，可以通过判断触发深度跳过嵌套触发，避免同事务内重复修改，但可靠性低于语句级方案：

CREATE OR REPLACE FUNCTION reassign_group_member_row()
RETURNS TRIGGER AS $$
DECLARE
    new_master_id INT;
    target_group_ref INT;
BEGIN
    -- 跳过嵌套触发，避免同事务内重复修改同一行
    IF pg_trigger_depth() > 1 THEN
        RETURN OLD;
    END IF;

    target_group_ref := COALESCE(OLD.group_ref, OLD.id);

    -- 选出同组未被删除的新主ID
    SELECT MIN(id) INTO new_master_id
    FROM group_master
    WHERE COALESCE(group_ref, id) = target_group_ref
      AND id != OLD.id
      AND id NOT IN (SELECT id FROM OLD);

    IF new_master_id IS NOT NULL THEN
        UPDATE group_master
        SET group_ref = new_master_id
        WHERE COALESCE(group_ref, id) = target_group_ref
          AND id != OLD.id
          AND id NOT IN (SELECT id FROM OLD);
    END IF;

    RETURN OLD;
END;
$$ LANGUAGE plpgsql;

-- 创建行级触发器
CREATE TRIGGER trigger_reassign_group_row_before_delete
BEFORE DELETE ON group_master
FOR EACH ROW
EXECUTE FUNCTION reassign_group_member_row();

关键注意事项 #

• 语句级触发器是最优选择，它一次性处理所有待删除行，彻底避免元组重复修改的问题。
• 新主ID的选择逻辑需根据业务需求调整（如最小ID、最大ID、指定优先级等），确保新主ID有效且未被删除。
• 用COALESCE(group_ref, id)处理分组主节点自身（即group_ref为NULL的行，自身就是分组主）的情况。

内容的提问来源于stack exchange，提问作者Ari Baranian