Pg_stat_activity is not updated in a procedure or transaction

Question

I request pg_stat_activity, but the content seems unchanged after the first time I request it in the body of the plpgsql function, transaction SERIALIZABLEor REPEATABLE READ.

Why pg_stat_activitydoes not comply with the rules for READ COMMITTEDin the plpgsql procedure?

How do I get the current status? I want to program the request on pg_stat_activityin plpgsql until another request running on another server ends.

Answer 1

PostgreSQL makes the cache for each backend (for each connection efficiently) a function pg_stat_get_activity()used, used both pg_stat_activity, and pg_stat_replication.

This cache is cleared upon commit / rollback, but not at the end of each statement inside a transaction in READ COMMITTED, as usual.

You can explicitly clean it with SELECT pg_stat_clear_snapshot(). Call it in the body of PL / PgSQL LOOPto update.

There is AFAIK without the ability to ask PostgreSQL to automatically update after each statement when using repeatable reador higher isolation.

In the source code see pgstat_read_current_status(void)and pgstat_clear_snapshot(void).