PostgreSQL：单条行更新后批量更新同组行的规则/触发器实现

问题分析 #

你原来的RULE触发无限递归，是因为规则中的DO INSTEAD会替换原UPDATE操作，执行一个更新同组所有行的语句——而这个语句又会对每一行触发同一个规则，形成循环调用，最终被PostgreSQL检测为无限递归。

下面提供两种可行的实现方案，优先推荐触发器方案，更稳定易维护。

方案一：使用行级触发器（推荐） #

通过触发器函数控制递归逻辑，仅在初始更新时同步同组其他行，避免循环触发。

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

CREATE OR REPLACE FUNCTION sync_x_loc()
RETURNS TRIGGER AS $$
BEGIN
    -- 当触发器嵌套深度大于1时，直接返回（避免递归触发）
    IF pg_trigger_depth() > 1 THEN
        RETURN NEW;
    END IF;

    -- 更新同组（id前三位相同）的所有其他行
    UPDATE tbl_test
    SET x_loc = NEW.x_loc
    WHERE LEFT(id::TEXT, 3) = LEFT(NEW.id::TEXT, 3)
      AND id <> NEW.id; -- 排除当前正在更新的行，避免重复操作

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

步骤2：创建触发器 #

CREATE TRIGGER trigger_sync_x_loc
BEFORE UPDATE OF x_loc ON tbl_test
FOR EACH ROW
WHEN (NEW.x_loc <> OLD.x_loc)
EXECUTE FUNCTION sync_x_loc();

说明 #

• BEFORE UPDATE OF x_loc：仅当x_loc字段被修改时触发触发器，减少不必要的执行。
• WHEN (NEW.x_loc <> OLD.x_loc)：确保只有x_loc实际发生变化时才执行同步逻辑。
• pg_trigger_depth()：检测当前触发器嵌套深度，当深度大于1时（即递归触发时）直接返回，避免无限循环。

方案二：改进RULE（不推荐） #

如果坚持使用RULE，可以通过限制触发条件避免递归，但RULE基于查询重写，行为相对复杂，容易出现意外情况。

CREATE OR REPLACE RULE x_loc_update AS
ON UPDATE TO tbl_test
WHERE NEW.x_loc <> OLD.x_loc AND pg_trigger_depth() = 0
DO ALSO
UPDATE tbl_test
SET x_loc = NEW.x_loc
WHERE LEFT(id::TEXT, 3) = LEFT(NEW.id::TEXT, 3)
  AND id <> NEW.id;

说明 #

• pg_trigger_depth() = 0：仅在初始更新（非递归触发）时执行规则逻辑。
• DO ALSO：保留原UPDATE操作（更新当前行），同时执行同步同组其他行的操作，避免替换原操作导致的递归循环。

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