PostgreSQL存储过程：循环成功提交、异常续行的写法正确性问询

我编写了如下PostgreSQL存储过程，包含循环逻辑。需求是：循环中某次执行抛出异常时，不终止整个流程，继续执行，且提交每个执行成功的循环查询。因此我在循环内部添加了异常捕获块。如你所见，存储过程末尾有一个commit，还有若干begin/end块。我的问题是：当前写法是否正确？是否需要在循环内的begin/end块中（execute mysql语句之后）添加额外的commit？

初始代码 #

CREATE OR REPLACE PROCEDURE myProc()
 LANGUAGE plpgsql
AS $procedure$
declare

mysql text;
tb_name text;

myTables CURSOR for
    SELECT table_name
    FROM information_schema.tables
    WHERE table_type='BASE TABLE'
    AND table_schema='dist';
    
begin
    begin 
        call DoSomeJob();
                  
                  
        for tb in myTables loop         
            tb_name := tb;
           
           begin                                         
                    mysql := format('delete from %I where somecol=2', tb_name);
                    execute mysql; 
                            
            exception
                  when others then      
                        raise notice '% %', SQLERRM, SQLSTATE;
            end ;
        end loop;
       
        call doOtherJob();     
       
    exception 
       when others then 
            raise notice 'The transaction is in an uncommittable state. '
           'Transaction was rolled back';
            raise notice '%: %', SQLSTATE, sqlerrm;

   end ;
    
commit; 
end;
$procedure$
;

更新后的代码 #

CREATE OR REPLACE PROCEDURE myProc()
 LANGUAGE plpgsql
AS $procedure$
declare
mysql text;
tb_name text;
myTables CURSOR for
    SELECT table_name
    FROM information_schema.tables
    WHERE table_type='BASE TABLE'
    AND table_schema='dist';
    
begin

        begin
           call DoSomeJob();
        exception 
            when others then 
                raise notice 'The transaction is in an uncommittable state. '
               'Transaction was rolled back';
                raise notice '%: %', SQLSTATE, sqlerrm;
        end;

        RAISE EXCEPTION 'ERROR test';
                  
        for tb in myTables loop         
            tb_name := tb;
           
           begin                                         
                    mysql := format('delete from %I where somecol=2', tb_name);
                    execute mysql; 
                            
            exception
                  when others then      
                        raise notice '% %', SQLERRM, SQLSTATE;
            end ;
        end loop;
       
        begin
            call doOtherJob();     
        exception 
            when others then 
                raise notice 'The transaction is in an uncommittable state. '
               'Transaction was rolled back';
                raise notice '%: %', SQLSTATE, sqlerrm;
        end;
    
commit; 
end;
$procedure$;

1. 当前写法的核心问题 #

初始代码问题 #

外层大begin/end块包裹了所有逻辑，一旦DoSomeJob()、循环或doOtherJob()中出现未被内层捕获的异常，会触发外层异常块。此时PostgreSQL事务会进入不可恢复的中止状态，后续的全局commit无法执行，所有已成功的delete操作都会被整体回滚，完全不符合“提交每个成功循环查询”的需求。

更新后代码问题 #

• 测试用的RAISE EXCEPTION 'ERROR test';会直接中断流程，循环和doOtherJob()都不会执行，实际使用必须删除。
• 虽然拆分了DoSomeJob()和doOtherJob()的异常捕获块，但所有操作仍处于同一个全局事务中，只有最后一次commit。如果存储过程在最后提交前失败，所有已成功的操作都会丢失。

2. 是否需要在循环内添加commit？ #

必须加。你的需求是每个成功的循环操作独立提交，而当前写法中所有操作都在同一个事务里，只有最后统一提交。要实现独立提交，需在循环内的execute之后添加commit：

for tb in myTables loop         
    tb_name := tb;
   
    begin                                         
        mysql := format('delete from %I where somecol=2', tb_name);
        execute mysql; 
        commit; -- 提交当前循环的成功操作
    exception
        when others then      
            raise notice '% %', SQLERRM, SQLSTATE;
            rollback; -- 异常时回滚当前子事务，避免影响下一次循环
    end ;
end loop;

PostgreSQL中，执行commit后会自动开启新事务，因此下一次循环的操作会在新事务中执行，确保单次循环的成功操作不会因后续流程失败而回滚。

3. 其他优化建议 #

• 如果DoSomeJob()和doOtherJob()也需要独立提交（失败不影响其他环节），也要在各自的异常块内添加commit和rollback：

  begin
      call DoSomeJob();
      commit; -- 独立提交DoSomeJob的操作
  exception 
      when others then 
          raise notice '%: %', SQLSTATE, sqlerrm;
          rollback;
  end;
  

• 删除存储过程末尾的全局commit，因为各个环节已经独立提交，全局commit可能引发不必要的事务问题。

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