pg_stat_activity doesn't update within a procedure or transaction

Question

I query pg_stat_activity but the contents seem to stay the same after the first time I query it within a plpgsql function body, SERIALIZABLE or REPEATABLE READ transaction.

Why does pg_stat_activity not follow the rules for READ COMMITTED in a plpgsql procedure?

How do I get the current status? I want to loop over a query against pg_stat_activity in plpgsql until another query running on another backend finishes.

Answer 1

PostgreSQL makes a per-backend (per-connection, effectively) cache of the data used by the pg_stat_get_activity() function used by both pg_stat_activity and pg_stat_replication.

This cache is cleared on commit/rollback, but not at the end of each statement within a transaction in READ COMMITTED like usual.

You can explicitly clear it with SELECT pg_stat_clear_snapshot(). Call it within the body of a PL/PgSQL LOOP to refresh.

There is AFAIK no way to ask PostgreSQL to auto-refresh after each statement when using repeatable read or higher isolation.

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