通过Python/psycopg2捕获PostgreSQL触发器抛出的SQL异常

核心原因 #

1. PostgreSQL的BEFORE INSERT/UPDATE触发器如果返回NULL，会直接静默终止当前行的插入/更新操作，不会抛出任何数据库错误，这是PostgreSQL原生行为，并非psycopg2的bug。
2. 你现有的代码仅对cursor.execute做了异常捕获，部分事务级错误会延迟到conn.commit阶段才抛出，导致部分错误漏捕获。
3. 你示例中psql执行报错是因为psql会话的search_path未包含audit_test schema，找不到patients表；而Python连接时指定了options='-c search_path=audit_test'，触发器内的update语句可以正常执行，只会因为触发器返回NULL静默跳过插入，自然不会抛出错误。

具体解决方案 #

1. 修正触发器逻辑 #

BEFORE INSERT/UPDATE触发器如果需要当前行的插入/更新操作正常执行，必须返回NEW而不是NULL：

create function update_health() returns trigger as $body$
begin
  if new.treated is false then
    update patients set ishealthy = false where patients.id = new.patient;
  elsif new.treated is true then
    update patients set ishealthy = true where patients.id = new.patient;
  end if;
  -- 返回NEW才会继续执行当前行的写入操作
  return NEW;
  -- 如果需要主动终止操作并抛出错误，使用RAISE语句
  -- RAISE EXCEPTION '触发器校验失败，操作已终止';
end;
$body$ language plpgsql;

如果确实需要在触发器逻辑异常时终止操作，直接通过RAISE EXCEPTION抛出错误即可，psycopg2可以正常捕获该类错误。

2. 修正Python代码的异常处理逻辑 #

• 将conn.commit也纳入异常捕获范围，捕获事务阶段的报错
• 新增执行行数校验，识别触发器静默拦截的场景
• 使用参数化查询代替字符串拼接，避免SQL注入风险

#!/usr/bin/python3
import psycopg2

conn = psycopg2.connect(
    dbname='mydb',
    user='fakeadminlol',
    host='myserver',
    port=5432,
    options='-c search_path=audit_test',
    # 调试阶段可以开启自动提交，错误会在execute阶段直接抛出
    # autocommit=True
)
cursor = conn.cursor()

# 使用参数化查询，不要用format拼接SQL
sql = '''insert into visits (tstamp, patient, treated, facility) values (CURRENT_TIMESTAMP, %s, FALSE, %s);'''
try:
    cursor.execute(sql, (1, 1))
    # 校验影响行数，单条插入预期影响1行，返回0说明被触发器拦截
    if cursor.rowcount != 1:
        raise Exception(f"写入操作未生效，预期影响1行，实际影响{cursor.rowcount}行，可能被触发器拦截")
    conn.commit()
except (Exception, psycopg2.DatabaseError) as ex:
    # 报错时回滚事务
    conn.rollback()
    print(f"操作失败：{str(ex)}")
finally:
    conn.close()

3. 调试阶段额外排查手段 #

如果仍然捕获不到错误，可以在执行语句后主动查询cursor.statusmessage属性，确认数据库返回的执行状态，也可以通过开启PostgreSQL的日志记录，对比psql和Python会话的执行日志差异。

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