0
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.

1 Answer 1

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?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.