Incremental progress on nested stored procedure (Postgres)-CodePudding

I have three Postgres stored procedures that are being ran sequentially (i.e. SP1 will call SP2 which then calls SP3). while proc'ing these nested stored procedures I noticed that I do not see the results from SP1 only until the last stored procedure has completed (SP3).

Is there a way to have the results of these procedures be produced at the time of execution rather than waiting for all of them to be complete?

Thanks!

Some code for reference:

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';
    
    -- does something

    insert into seq_test values ('1','complete','now');
    raise notice 'step 1 complete';
    
    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';
    
    -- does something else

    insert into seq_test values ('2','complete','now');
    raise notice 'step 2 complete';
    
    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';
    
    -- does something else

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

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

call sp1();

In the above script, if hypothetically each SP takes 1 minute to complete, despite records being inserted into seq_test throughout the entire 3 minute runtime I wont actually be able to observe these records until every SP has completed.

CodePudding user response：

You don't see the results, because data modifications only become visible when the transaction that made them has ended. This is guaranteed by the default READ COMMITTED transaction isolation level, and there is no way in PostgreSQL to get “dirty reads”.

By default, CALL sp1() will run in a single transaction, which explains what you observe.

You can change that behavior by explicitly calling COMMIT AND CHAIN inside your stored procedures. That will commit the work up to this point and start a new transaction. But consider the consequences: if execution encounters an error, the transaction will be rolled back (undone). A COMMIT in the middle of your procedure can then result in only part of the work being done. This may be exactly what you need, but often it is undesirable.