Postgres嵌套存储过程执行进度无法实时查看如何处理？

问题原因 #

默认调用PostgreSQL存储过程时，整个执行链路处于同一个未提交的事务中：

• 未提交的表写入操作受事务隔离机制限制，对其他会话不可见，只有整个事务全部完成提交后，插入的seq_test记录才能被查询到
• 多数数据库客户端默认会缓冲RAISE NOTICE输出内容，直到事务结束后才统一打印，也会导致无法实时看到执行日志

解决方案 #

方案1：中间状态显式提交（适用PostgreSQL 11及以上版本） #

PostgreSQL 11及以上版本支持在存储过程中显式执行COMMIT操作，你可以在每个步骤的状态写入完成后提交事务，让插入的记录实时对外可见。
修改后的代码示例：

create or replace procedure sp1()
as $$
declare
begin
    drop table if exists seq_test;
    create table seq_test (
        step        varchar,
        status      varchar,
        time_log    timestamp not null default now()
    );
    
    insert into seq_test values ('1','running','now');
    raise notice 'step 1 beginning';
    commit; -- 提交当前写入，其他会话可立刻查到这条记录
    
    -- does something

    insert into seq_test values ('1','complete','now');
    raise notice 'step 1 complete';
    commit;
    
    call sp2(); 
end;
$$
language plpgsql;

--------------

create or replace procedure sp2()
as $$
declare
begin
    insert into seq_test values ('2','running','now');
    raise notice 'step 2 beginning';
    commit;
    
    -- does something else

    insert into seq_test values ('2','complete','now');
    raise notice 'step 2 complete';
    commit;
    
    call sp3(); 
end;
$$
language plpgsql;

--------------

create or replace procedure sp3()
as $$
declare
begin
    insert into seq_test values ('3','running','now');
    raise notice 'step 3 beginning';
    commit;
    
    -- does something else

    insert into seq_test values ('3','complete','now');
    raise notice 'step 3 complete';
    commit;
end;
$$
language plpgsql;

注意：该方案会破坏原有事务的原子性，若后续步骤执行失败，已经提交的写入不会回滚，需要业务侧自行适配异常补偿逻辑。

方案2：自治事务写入（不影响主事务原子性） #

如果不希望主业务逻辑的事务原子性被破坏，可以用dblink扩展模拟自治事务，独立提交日志写入操作，不影响主事务的回滚：

1. 首先开启dblink扩展：

CREATE EXTENSION IF NOT EXISTS dblink;

2. 封装独立的日志写入函数：

create or replace function insert_seq_log(p_step varchar, p_status varchar) returns void as $$
declare
    v_conn text := 'dbname=' || current_database(); -- 连接当前数据库
begin
    perform dblink_connect(v_conn);
    perform dblink_exec(
        v_conn,
        format('insert into seq_test(step, status) values (%L, %L)', p_step, p_status)
    );
    perform dblink_exec(v_conn, 'commit');
    perform dblink_disconnect(v_conn);
end;
$$ language plpgsql;

3. 原有存储过程中把直接插入seq_test的逻辑替换为调用insert_seq_log函数即可，无需额外提交主事务，写入的日志会实时对外可见。

方案3：实时查看通知日志 #

如果只需要查看执行进度不需要查询表数据，可以调整客户端配置关闭输出缓冲，实时接收RAISE NOTICE内容：

• psql客户端：执行SET client_min_messages = notice;即可实时打印通知
• pgAdmin/DBeaver等图形化客户端：在连接设置中开启「实时通知/实时消息输出」开关即可
• 也可以修改数据库配置log_min_messages = notice，所有通知会直接写入数据库服务器日志，直接查看日志文件即可获取实时进度。

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