PostgreSQL中使用RULE替换SpecificResource表INSERT操作如何避免无限递归

问题原因 #

你当前的写法出现无限递归，是因为RULE拦截了所有对specific_resource的INSERT请求，而规则内部又发起了对specific_resource的INSERT操作，会再次触发同一个RULE，因此陷入死循环。同时你当前的规则仅插入了uid字段，会丢失原INSERT语句携带的content字段数据，即使解决递归也无法完成写入需求。 #

方案1：使用触发器实现（更推荐，行级操作场景下逻辑更可控，无递归问题） #

对于这种需要关联操作其他表的行级写入场景，触发器比RULE更不容易出现逻辑异常，实现如下：

1.1 插入自动关联写入Resource表 #

-- 创建插入触发器函数
CREATE OR REPLACE FUNCTION specific_resource_insert_handler()
RETURNS TRIGGER AS $$
BEGIN
    -- 先写入Resource主表
    INSERT INTO resource (uid, type) VALUES (NEW.uid, 'SPECIFIC_RESOURCE');
    -- 返回NEW让原INSERT操作正常执行，不会触发递归
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 绑定触发器到specific_resource表，插入前触发
CREATE TRIGGER trg_specific_resource_before_insert
BEFORE INSERT ON specific_resource
FOR EACH ROW EXECUTE FUNCTION specific_resource_insert_handler();

1.2 删除自动关联删除Resource表 #

-- 创建删除触发器函数
CREATE OR REPLACE FUNCTION specific_resource_delete_handler()
RETURNS TRIGGER AS $$
BEGIN
    -- 同步删除Resource主表对应记录
    DELETE FROM resource WHERE uid = OLD.uid;
    RETURN OLD;
END;
$$ LANGUAGE plpgsql;

-- 绑定触发器到specific_resource表，删除后触发
CREATE TRIGGER trg_specific_resource_after_delete
AFTER DELETE ON specific_resource
FOR EACH ROW EXECUTE FUNCTION specific_resource_delete_handler();

1.3 更新逻辑说明 #

如果业务允许修改uid字段，可新增BEFORE UPDATE触发器同步更新Resource表的uid；如果仅修改content字段，无需额外处理主表。 #

方案2：坚持使用RULE的修正方案 #

如果必须使用RULE实现，可通过「视图+底层物理表」的结构绕开递归问题，操作后用户访问视图和原表的体验完全一致：

1. 重命名原物理表

ALTER TABLE specific_resource RENAME TO specific_resource_base;

2. 创建同名视图替代原表入口

CREATE VIEW specific_resource AS SELECT * FROM specific_resource_base;

3. 给视图绑定INSERT RULE

CREATE OR REPLACE RULE insert_specific_resource
AS ON INSERT TO specific_resource
DO INSTEAD (
    -- 写入主表
    INSERT INTO resource (uid, type) VALUES (NEW.uid, 'SPECIFIC_RESOURCE');
    -- 写入底层物理表，不会触发视图的RULE，避免递归
    INSERT INTO specific_resource_base (uid, content) VALUES (NEW.uid, NEW.content);
);

同理UPDATE、DELETE操作也可通过给视图新增对应RULE操作底层表实现。 #

注意：请确认'SPECIFIC_RESOURCE'是Resource表type字段对应的枚举类型的合法取值，否则会触发类型校验错误。

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