nulls on pg_stat_get_progress_info('VACUUM') columns on pg_exporter role

Question

I'm trying to export the results of a query select * from pg_stat_progress_vacuum;. The problem is that the only column values I can see from the created user are:

• pid
• datid
• datname

Other columns are constantly null. When query is run from superuser account there are values of course.

Based on pg_stat_progress_vacuum view definition I noticed that the problematic part is usage of pg_stat_get_progress_info function with VACUUM parameter (view names params returned by this function).

User has pg_monitor role assigned.

What am I missing?

Result of select * from pg_stat_progress_vacuum; from monitoring user

[
  {
    "pid": 33079,
    "datid": 16410,
    "datname": "<database name>",
    "relid": null,
    "phase": null,
    "heap_blks_total": null,
    "heap_blks_scanned": null,
    "heap_blks_vacuumed": null,
    "index_vacuum_count": null,
    "max_dead_tuples": null,
    "num_dead_tuples": null
  },
  {
    "pid": 38625,
    "datid": 121786502,
    "datname": "<database name>",
    "relid": null,
    "phase": null,
    "heap_blks_total": null,
    "heap_blks_scanned": null,
    "heap_blks_vacuumed": null,
    "index_vacuum_count": null,
    "max_dead_tuples": null,
    "num_dead_tuples": null
  }
]

Result from superuser for the same query

postgres=# select * from pg_stat_progress_vacuum;
  pid  |   datid   |     datname      |   relid   |       phase       | heap_blks_total | heap_blks_scanned | heap_blks_vacuumed | index_vacuum_count | max_dead_tuples | num_dead_tuples
-------+-----------+------------------+-----------+-------------------+-----------------+-------------------+--------------------+--------------------+-----------------+-----------------
 38625 | 121786502 | <database name> | 121786624 | vacuuming indexes |         7091104 |           7091104 |                  0 |                  0 |       178956970 |        73659774
(1 row)

A couple of seconds passed from one query execution to the other, so I assume that just autovacuum has finished thus the difference in row count.

Postgres version is 12.2

Answer 1

You are missing the required privileges.

To see all values in a line of pg_stat_progress_vacuum, one of the following must be satisfied:

• you are a superuser

• the VACUUM is run by the same user as your current connection

• you are a member of the system role pg_read_all_stats

Answer 2

I encountered exactly the same issue on Postgres 11.3 and finish up with the following solution:

1. Define wrapper function from superuser:

CREATE OR REPLACE FUNCTION public.my_stat_progress_vacuum()
 RETURNS SETOF pg_stat_progress_vacuum
 LANGUAGE sql
 SECURITY DEFINER
AS $function$ 
select * from pg_stat_progress_vacuum;
$function$;

2. Use it from monitoring user as:

select * from my_stat_progress_vacuum();

3. Same trick would be useful to access other views from pg_stat_* family