Loop inside the function runs as a single transaction and reads data of a single snapshot

Question

I am writing the function to collect rows from pg_stat_activity system view (PostgreSQL 13).

Inside this function I have a simple loop to insert the pg_stat_activity data into log table.

The problem is when I run the function it inserts the rows actual for the first second and duplicates them later. Seems like functions works as a single transaction and gets data from snapshot of rows from the time of the first run. How can I run function "body" in multiple individual transactions to have result for each second or what workaround can I have?

CREATE OR REPLACE FUNCTION public.fn_activity(
    integer)
    RETURNS void 
    LANGUAGE 'plpgsql'
    --COST 100
    --VOLATILE PARALLEL UNSAFE
AS $BODY$
declare counter integer := 0;
begin
    while counter < $1 loop
        raise notice 'Counter %', counter;
        counter := counter + 1;
        
        insert into public.pg_stat_activity_log("time",datid,datname,pid,leader_pid,usesysid,usename,application_name,client_addr,client_hostname,
        client_port,backend_start,xact_start,query_start,state_change,wait_event_type,wait_event,state,backend_xid,backend_xmin,query,backend_type)
        SELECT now()::time(0),datid,datname,pid,leader_pid,usesysid,usename,application_name,client_addr,client_hostname,client_port,backend_start,xact_start,query_start,
        state_change,wait_event_type,wait_event,state,backend_xid,backend_xmin,query,backend_type
        from pg_stat_activity;
        
        raise notice '-------';
        
        PERFORM pg_sleep(1);
   
        end loop;
        return;
END;
$BODY$;

truncate table public.pg_stat_activity_log;
select public.fn_activity(60);

Currently I have the result for the 14:53:15 only, but need to have: 14:53:15 14:53:16 14:53:17 14:53:18

Thanks!

`now()::time(0)` can be simplified to `localtime` or `localtime(0)` — , Oct 28 '21 at 12:52

score 0 · Answer 1 · answered Oct 28 '21 at 12:21

The values in pg_stat_activity don't change as long as you are in a transaction, because the data are read only once, then cached in the private memory of the database session.

Since a function always runs in a single transactions, it will always see the same values in pg_stat_activity, regardless if that still corresponds with reality or not.

See the function comment from pgstat_read_current_status in src/backend/utils/activity/backend_status.c, which copies the data:

/* ----------
 * pgstat_read_current_status() -
 *
 *  Copy the current contents of the PgBackendStatus array to local memory,
 *  if not already done in this transaction.
 * ----------
 */

@OlegAlekseiev: you could use a procedure and then do a commit after each insert — , Oct 28 '21 at 12:51

Loop inside the function runs as a single transaction and reads data of a single snapshot

1 Answers1