how many seq_scans one procedure executed on a table

Question

CREATE OR REPLACE PROCEDURE testscan ()
LANGUAGE plpgsql
AS $func$
DECLARE
    s text;
BEGIN
    EXECUTE 'explain analyze SELECT from tenk1' INTO s;
    EXECUTE 'explain analyze SELECT from tenk1' INTO s;
END;
$func$;

the following way works fine. But I want to wrapped it in a transaction, so that I can see the number of seq_scans this procedure have executed.

SELECT  seq_scan,last_seq_scan,seq_tup_read
FROM    pg_stat_user_tables
WHERE   schemaname = 'public' AND relname = 'tenk1';

CALL testscan ();

SELECT  seq_scan,last_seq_scan,seq_tup_read
FROM    pg_stat_user_tables
WHERE   schemaname = 'public'   AND relname = 'tenk1';

but the following way not working as I intended.

BEGIN;
    SELECT  seq_scan,last_seq_scan,seq_tup_read
    FROM    pg_stat_user_tables
    WHERE   schemaname = 'public' AND relname = 'tenk1';

    CALL testscan ();

    SELECT  seq_scan,last_seq_scan,seq_tup_read
    FROM    pg_stat_user_tables
    WHERE   schemaname = 'public'   AND relname = 'tenk1';
COMMIT;

wrapped in transaction, before and after call testscan(), returns same number of seq_scan.
question: how to measure, how many seq_scans one procedure executed on a specific table.

Answer 1

That is more or less what I would expect, or at least not very surprising. From the docs:

Each individual server process flushes out accumulated statistics to shared memory just before going idle, but not more frequently than once per PGSTAT_MIN_INTERVAL milliseconds (1 second unless altered while building the server); so a query or transaction still in progress does not affect the displayed totals and the displayed information lags behind actual activity.

Now it isn't particularly clear what 'going idle' means. If it is about to read the next command, but at least on byte of that next command is already present in the buffer, is that about to go idle or not?

the following way works fine. But I want to wrapped it in a transaction, so that I can see the number of seq_scans this procedure have executed.

Why do you think you need to wrap it in a transactions in order to see the number of seq_scans?