PostgreSQL多函数事务控制：回滚业务操作但保留错误日志

核心思路 #

要实现主事务回滚时仅保留日志记录，关键是让日志插入操作脱离主事务上下文，在独立的自治事务中执行。结合禁止合并函数、不使用dblink的要求，提供以下两种可行方案：

方案一：使用PROCEDURE实现自治日志插入（PostgreSQL 14+） #

1. 创建独立的日志插入过程，内部单独提交事务：

CREATE OR REPLACE PROCEDURE insert_log(p_msg text)
LANGUAGE plpgsql
AS $$
BEGIN
  INSERT INTO log(content) VALUES(p_msg);
  COMMIT; -- 独立提交日志事务
  BEGIN; -- 重新开启事务，保证后续逻辑兼容主事务上下文
END $$;

2. 修改test3函数，调用上述过程写入错误日志：

CREATE OR REPLACE FUNCTION test3()
RETURNS void
LANGUAGE plpgsql
AS $$
BEGIN
  -- 模拟插入过长数据触发失败
  INSERT INTO test(col) VALUES(repeat('a', 10000)); -- 假设col字段长度限制小于10000
EXCEPTION
  WHEN OTHERS THEN
    CALL insert_log('test3 error: ' || SQLERRM); -- 日志写入独立事务
    RAISE; -- 抛出异常触发主事务回滚
END $$;

3. 执行逻辑：

• 在同一事务内依次调用test1()、test2()、test3()
• test3失败触发异常后，主事务回滚test1、test2对test表的修改
• insert_log过程的COMMIT已独立提交日志，log表的错误记录会被保留

方案二：利用pg_notify+后台监听进程实现异步日志写入 #

1. 修改test3函数，错误发生时发送通知：

CREATE OR REPLACE FUNCTION test3()
RETURNS void
LANGUAGE plpgsql
AS $$
BEGIN
  INSERT INTO test(col) VALUES(repeat('a', 10000));
EXCEPTION
  WHEN OTHERS THEN
    PERFORM pg_notify('log_channel', 'test3 error: ' || SQLERRM); -- 发送错误通知
    RAISE;
END $$;

2. 编写后台监听脚本（以Python为例），独立插入日志：

import psycopg2
from psycopg2.extensions import ISOLATION_LEVEL_AUTOCOMMIT

# 连接数据库，设置自动提交模式
conn = psycopg2.connect("dbname=your_db user=your_user password=your_pwd")
conn.set_isolation_level(ISOLATION_LEVEL_AUTOCOMMIT)
cur = conn.cursor()

cur.execute("LISTEN log_channel;")
print("监听日志通知中...")

while True:
    conn.poll()
    while conn.notifies:
        notify = conn.notifies.pop(0)
        # 独立插入日志到log表
        cur.execute("INSERT INTO log(content) VALUES(%s);", (notify.payload,))

3. 执行逻辑：

• 启动后台监听脚本
• 在主事务中调用test1、test2、test3
• test3失败时发送通知，后台脚本独立完成日志写入（不受主事务回滚影响）
• 主事务回滚test1、test2的修改，log表保留错误记录

事后补救方案（若事务已提交） #

如果当前test表已存在1、2两条数据且事务已提交，直接执行删除操作即可回滚test1、test2的修改，同时保留log表记录：

DELETE FROM test WHERE id IN (1, 2);

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