PostgreSQL循环调用存储过程致事务缓存内存溢出问题求解

问题场景 #

编写的PostgreSQL存储过程check_all_accounts，通过循环调用main.check_and_cleanup处理约52000条账户数据时，触发共享内存不足错误：

returned_sqlstate=53200，message_text=out of shared memory

但改用DO块循环、每次调用后执行commit就能正常运行，需要修改存储过程以规避内存溢出问题。

原存储过程代码 #

create procedure check_all_accounts()
as $code$
declare 
    id varchar;
    rec record; 
begin
    for rec in select acc.id from main.account acc where acc.type_id = 'ACT'
    loop        
        raise notice '%: Checking Account %', substring(clock_timestamp()::varchar, 1, 19), rec.id;
    call main.check_and_cleanup(rec.id);    
    -- commit; -- Does not work within procedure
    end loop;
exception 
    when others then
    raise notice 'Failed for Account %s. Error: %', rec.id, sqlerrm;
end; $code$ language plpgsql;

正常运行的DO块代码 #

do $code$
declare
    id varchar;
    rec record; 
begin
    for rec in select acc.id from main.account acc where acc.type_id = 'ACT'
    loop        
        raise notice '%: Checking Account %', substring(clock_timestamp()::varchar, 1, 19), rec.id;
        call main.check_and_cleanup(rec.id);    
        commit; -- Commit is allowed/needed here
    end loop;
end; $code$

问题原因 #

PostgreSQL中，普通存储过程默认在单个事务上下文执行，循环过程中所有操作的事务缓存会持续累积，处理数万条数据时会耗尽共享内存。而DO块允许手动执行commit，每次提交后会释放当前事务的缓存，避免内存堆积。

解决方案 #

PostgreSQL 11及以上版本支持在存储过程中使用事务控制语句（commit/rollback），调整存储过程逻辑，在每次循环执行完业务操作后提交事务，并在循环内部处理异常，避免单条数据失败中断整个流程：

修改后的存储过程代码 #

create procedure check_all_accounts()
as $code$
declare 
    rec record; 
begin
    for rec in select acc.id from main.account acc where acc.type_id = 'ACT'
    loop        
        begin -- 子块包裹单条数据处理逻辑，隔离异常
            raise notice '%: Checking Account %', substring(clock_timestamp()::varchar, 1, 19), rec.id;
            call main.check_and_cleanup(rec.id);    
            commit; -- 每次处理完提交事务，释放缓存
        exception
            when others then
                raise notice 'Failed for Account %. Error: %', rec.id, sqlerrm;
                rollback; -- 失败时回滚当前单条操作的事务
        end;
    end loop;
end; $code$ language plpgsql;

关键改动说明 #

1. 添加事务控制：每次调用check_and_cleanup后执行commit，及时释放事务缓存，避免内存累积。
2. 子块异常处理：将单条数据的处理逻辑包裹在子begin...end块中，即使某条数据处理失败，回滚当前子事务后仍能继续处理下一条数据，不会中断整个循环。
3. 修正认知偏差：PostgreSQL 11+的存储过程支持事务控制，原注释中的"Does not work within procedure"已不适用。

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