如何在单个PostgreSQL触发器函数中删除事务外已存在的行
问题根因
你原实现中的全表DELETE语句会删除entity_authorization表内的所有记录,包含当前事务中刚刚插入的未提交新记录,导致后续的延迟约束触发器查询不到有效数据,抛出not allowed异常。
解决方案
要实现仅删除当前事务启动前就已经持久化的旧记录,你可以使用PostgreSQL自带的dblink扩展实现:dblink发起的查询运行在独立的新事务中,天然看不到当前事务的未提交修改,正好匹配你要查询"事务外已持久化记录"的需求。
步骤1:安装dblink扩展
CREATE EXTENSION IF NOT EXISTS dblink;
如果是本地peer认证场景,安装后不需要额外配置,直接即可使用。如果你的数据库需要密码登录,可以在后续的连接串中补全账号密码参数。
步骤2:修改触发器函数
替换原有revoke_obsolete_autorization_entity函数实现:
CREATE or REPLACE FUNCTION revoke_obsolete_autorization_entity() RETURNS TRIGGER LANGUAGE plpgsql AS $$ BEGIN -- 仅删除事务启动前已持久化的旧记录,不影响当前事务新增的记录 DELETE FROM entity_authorization WHERE id IN ( SELECT id FROM dblink( 'dbname=' || current_database(), 'SELECT id FROM entity_authorization' ) AS pre_tx_auth(id varchar) ); RETURN new; END $$;
效果验证
你原有的测试事务不需要做任何修改,运行逻辑会变成:
- 事务中先插入
'AAA'、'BBB'两条记录,仅当前事务可见 - 执行UPDATE触发前触发器,通过dblink查询到的只有事务前就存在的旧
'AAA'记录,仅删除这条旧记录,当前事务新插入的两条记录保留 - 延迟约束触发器执行时能正常查到两条未删除的授权记录,不会抛出异常
- 事务提交后,表中最终保留的就是当前事务新增的两条授权记录
内容的提问来源于stack exchange,提问作者AnselmoMS
相关产品推荐
相关产品推荐

