PostgreSQL触发器向副表插入数据偶发失效问题排查

首先明确核心结论：PostgreSQL中，触发器与触发它的表操作处于同一事务，触发器内的所有DML操作会和主操作原子性地提交或回滚。

针对你遇到的10条A表记录无对应B表数据、且事后无法插入的异常，可能的原因如下：

1. B表已存在对应记录，但查询未匹配 #

你提到事后执行带WHERE NOT EXISTS的插入语句无法生效，说明B表中已经存在reference值等于这10条A记录id的行，但手动查询时未匹配到，可能的原因：

• 字符匹配问题：如果id和reference是varchar类型，可能存在大小写不一致（如A表id为'USER001'，B表中为'user001'）、或包含不可见控制字符（如空格、换行符），导致肉眼查询时无法匹配，但数据库的严格相等判断会认为记录已存在。
• 查询逻辑错误：手动查询B表时误用了错误的关联条件，或添加了额外过滤规则导致遗漏数据。

2. 触发器从未触发 #

你的触发器定义为AFTER INSERT OR UPDATE OR DELETE OF column1, column2 ON "A"，存在触发范围限制：

• UPDATE操作未修改指定列：如果这10条A记录是通过未修改column1和column2的UPDATE语句完成的更新，触发器不会触发。比如这些记录是触发器上线前的历史数据，后续又没有触发column1/column2变更的操作，自然不会执行插入B表的逻辑。
• 历史数据未被覆盖：若这10条A记录在触发器创建前就已存在于表中，触发器不会对存量数据自动执行逻辑。

3. 并发场景下的异常 #

高并发操作可能导致逻辑异常：

• 唯一约束冲突静默处理：如果B表的reference字段有唯一约束，当两个事务同时对同一条A记录执行操作（如一个INSERT、一个UPDATE），第一个事务成功插入B表，第二个事务的INSERT会因约束冲突报错。若应用层捕获了该异常但仍提交了A表的操作（未回滚），就会出现A表记录存在但触发器逻辑未生效的情况——不过你提到日志无异常，这种可能性较低，需检查应用层错误处理逻辑。

4. 触发函数的潜在问题 #

你的触发函数在DELETE分支返回NEW，虽然PostgreSQL允许AFTER DELETE触发器返回NEW（返回值会被忽略），但这不会影响逻辑执行。需确认A表的id字段在DELETE操作时是否被其他触发器或规则修改，导致OLD.id值异常，但这种场景极少发生。

1. 直接验证B表是否存在对应记录：

   SELECT reference, encode(reference, 'hex') FROM B WHERE reference IN ('[id1]', '[id2]', ...);
   -- 替换为那10条A记录的id，对比hex值排查不可见字符
   

2. 检查这10条A记录的创建/更新时间，确认是否早于触发器创建时间；同时查看操作日志，确认是否有未修改column1/column2的UPDATE操作。
3. 检查B表的reference字段约束，查看数据库历史日志（可能需调整日志级别）是否存在约束冲突报错。

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