PostgreSQL 13触发器异常：目标表无插入且源表数据丢失

问题场景 #

存在两个分属不同schema的表：

• hist.T1：包含stream_id、timestamp、o_value三列，每5秒接收11-20区间的新数据
• public.T2：包含s_id、TrigTime、n_value三列，需同步hist.T1中o_value<15且stream_id=1的记录

已创建的触发器函数和触发器如下：

触发器函数 #

CREATE OR REPLACE FUNCTION TestFunct()
  RETURNS TRIGGER AS $BODY$
BEGIN
  IF NEW.o_value < 15 AND NEW.stream_id = 1 THEN 
    INSERT INTO public.T2(s_id, TrigTime, n_value) 
    VALUES(NEW.stream_id, NEW.timestamp , NEW.o_value);
  END IF;
  RETURN NEW;
END;
$BODY$
  LANGUAGE plpgsql;

触发器 #

CREATE TRIGGER TestTrig
AFTER INSERT ON hist.T1
FOR EACH ROW
EXECUTE PROCEDURE public.TestFunct();

异常现象 #

• hist.T1中完全缺失o_value<15的记录，数据接收间隔从5秒变为10秒，疑似符合条件的数据被回滚
• public.T2未插入任何同步数据

测试验证结果 #

• 当条件改为NEW.o_value <11时，触发器未触发，hist.T1每5秒正常接收全量数据
• 当条件为NEW.o_value <15时，hist.T1仅接收≥15的数据，间隔变为10秒，public.T2仍无数据

已尝试的无效排查操作：

• 修改源数据库读取间隔为15秒，触发时源表数据间隔变为30秒
• 将EXECUTE PROCEDURE改为EXECUTE FUNCTION
• 手动执行插入语句可成功向public.T2写入数据
• 修改触发器函数中的插入语句为静态数据、单字段值
• 将触发器函数迁移至不同schema

问题分析与解决方案 #

核心问题推测 #

你的触发器为AFTER INSERT类型，理论上不会影响hist.T1的插入操作，但源表丢失数据的现象说明：触发器执行过程中出现了未捕获的异常，导致整个事务回滚。PostgreSQL中，触发器与触发它的DML操作属于同一事务，触发器内的异常会触发全局回滚——既回滚public.T2的插入，也回滚hist.T1中刚插入的符合条件的记录。

具体排查与修复步骤 #

1. 验证权限匹配
   触发器函数是在触发它的用户（即OPC读取工具的数据库用户）权限下运行的，需确认该用户拥有public.T2的插入权限：

   -- 替换OPC_USER为实际的OPC读取工具所用账号
   SELECT has_table_privilege('OPC_USER', 'public.T2', 'INSERT');
   

   若返回f，则授予权限：

   GRANT INSERT ON public.T2 TO OPC_USER;
   

2. 检查字段类型兼容性
   确认public.T2的字段类型与hist.T1对应字段完全兼容：

   • s_id与stream_id的类型是否一致（如均为整数）
   • TrigTime与timestamp的类型是否一致（注意区分timestamp和timestamp with time zone）
   • n_value与o_value的类型是否一致（如均为数值型）

3. 添加异常捕获定位问题
   修改触发器函数，添加异常捕获并记录报错信息，避免事务隐式回滚：

   -- 先创建日志表用于记录异常
   CREATE TABLE IF NOT EXISTS public.trigger_errors(
       error_time timestamp DEFAULT NOW(),
       error_msg text,
       record_data json
   );
   
   CREATE OR REPLACE FUNCTION TestFunct()
     RETURNS TRIGGER AS $BODY$
   BEGIN
     IF NEW.o_value < 15 AND NEW.stream_id = 1 THEN 
       BEGIN
         INSERT INTO public.T2(s_id, TrigTime, n_value) 
         VALUES(NEW.stream_id, NEW.timestamp , NEW.o_value);
       EXCEPTION
         WHEN OTHERS THEN
           INSERT INTO public.trigger_errors(error_msg, record_data)
           VALUES(SQLERRM, row_to_json(NEW));
       END;
     END IF;
     RETURN NEW;
   END;
   $BODY$
     LANGUAGE plpgsql;
   

   运行后查看public.trigger_errors表的报错信息，针对性修复问题。

4. 尝试更换触发器时机
   若权限和类型均无问题，可尝试将触发器改为BEFORE INSERT类型验证事务阶段问题：

   DROP TRIGGER IF EXISTS TestTrig ON hist.T1;
   CREATE TRIGGER TestTrig
   BEFORE INSERT ON hist.T1
   FOR EACH ROW
   EXECUTE PROCEDURE public.TestFunct();
   

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