Postgres嵌套存储过程调用时内部存储过程回滚失败问题排查

核心错误原因 #

1. 事务上下文冲突：PostgreSQL中存储过程默认继承调用者的事务上下文，你的inner过程直接执行ROLLBACK和COMMIT会破坏外层事务：
   • ROLLBACK会回滚整个外层事务，而非仅inner的操作
   • 后续的COMMIT因无活跃事务触发2D000（无效事务终止）错误
2. 异常处理逻辑错误：outer的EXCEPTION块用RETURN终止循环，不符合“继续处理其他任务”的需求
3. 变量未初始化：inner中v_pending_updates_rowcount和v_existing_updates_rowcount未赋值，默认NULL的比较结果直接触发错误分支

修正后的代码 #

1. 重构inner过程（移除事务控制，专注业务逻辑） #

CREATE OR REPLACE PROCEDURE inner(last_job_id integer)
AS $$
DECLARE
  v_pending_updates_rowcount integer;
  v_existing_updates_rowcount integer;
BEGIN
    -- 替换为你的实际业务逻辑，示例为计数查询
    SELECT COUNT(*) INTO v_pending_updates_rowcount 
    FROM some_pending_table WHERE job_id = last_job_id;
    
    SELECT COUNT(*) INTO v_existing_updates_rowcount 
    FROM some_existing_table WHERE job_id = last_job_id;

    IF v_pending_updates_rowcount <> v_existing_updates_rowcount THEN
      -- 仅抛出异常，事务控制交由外层处理
      RAISE EXCEPTION 'Update failed! Job ID: %', last_job_id;
    END IF;

    -- 这里添加正常业务处理逻辑（如数据更新、插入等）
    -- UPDATE target_table SET ... WHERE job_id = last_job_id;
END;
$$ LANGUAGE plpgsql;

2. 重构outer过程（独立事务处理每个任务，异常后继续循环） #

CREATE OR REPLACE PROCEDURE outer()
  LANGUAGE PLPGSQL
  AS
$$
DECLARE
  v_job_id integer;
BEGIN
  FOR v_job_id IN SELECT job_id FROM pending_jobs ORDER BY job_id
  LOOP
    BEGIN
      -- 可选：用排他锁防止并发处理同一任务
      PERFORM pg_advisory_lock(v_job_id);

      -- 为每个任务创建独立事务
      BEGIN;
        CALL inner(v_job_id);
        -- 任务正常完成，移除待处理队列
        DELETE FROM pending_jobs WHERE job_id = v_job_id;
      COMMIT;

      PERFORM pg_advisory_unlock(v_job_id);
    EXCEPTION WHEN OTHERS THEN
      -- 回滚当前任务的活跃事务
      IF current_transaction_status() = 'idle in transaction' THEN
        ROLLBACK;
      END IF;
      
      PERFORM pg_advisory_unlock(v_job_id);

      -- 在新事务中记录错误并移除待处理队列（避免被回滚影响）
      BEGIN;
        INSERT INTO blocked_jobs (job_id) VALUES (v_job_id);
        DELETE FROM pending_jobs WHERE job_id = v_job_id;
      COMMIT;

      -- 打印错误日志
      RAISE NOTICE 'Job Id: %, Error: %, SQL State: %', v_job_id, SQLERRM, SQLSTATE;
      -- 继续循环处理下一个任务
    END;
  END LOOP;
END;
$$;

关键说明 #

• 独立事务隔离：每个任务用独立的BEGIN...COMMIT，单个任务失败不影响其他任务的事务上下文
• 锁机制：可选的pg_advisory_lock避免多进程同时处理同一任务，防止数据冲突
• 异常处理：捕获异常后回滚当前事务，在新事务中记录错误，确保错误信息不丢失，同时继续处理后续任务

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