Re: exposing wait events for non-backends (was: Tracking wait event for latches)

Hello everyone,

As discussed in this thread, I've attached a set of patches to show
auxiliary processes, autovacuum launcher and bgworker along with other
backends in pg_stat_activity. For now, I've extended
BackendStatusArray to store auxiliary processes. Backends use slots
indexed in the range from 1 to MaxBackends (inclusive), so we use
MaxBackends + AuxProcType + 1 as the index of the slot for an
auxiliary process. However, BackendStatusArray should be renamed to
something like 'ProcStatusArray' along with many others in pgstat.c
and pgstatfuncs.c(Ex: LocalPgBackendStatus etc). But, that needs a
major code refactoring. I can do the changes if we agree with that.

I've also kept a local array, named localBackendStatusIndex, which
stores the index of currently active backends from BackendStatusArray.
It assigns ids to currently active backend from 1 to the number of
active backends.(It is required in some pgstat_* functions, for
example: pg_stat_get_backend_idset). Hence, we are not affecting the
outputs of other sql functions apart from pg_stat_activity and
pg_stat_get_activity.

I've also added an extra column, named proc_type (suggested by Craig
and Robert), to indicate the type of process in pg_stat_activity view.
proc_type includes:

* client backend
* autovacuum launcher
* wal sender
* bgworker
* writer
* checkpointer
* wal writer
* wal receiver

Here is the present output with the relevant columns. (Didn't show
backend_start since it takes too long space)

postgres=# select pid, usesysid, application_name, wait_event_type,
wait_event, state, proc_type from pg_stat_activity;
pid | usesysid | application_name | wait_event_type |
wait_event | state | proc_type
--------+----------+------------------------------+-----------------+---------------------+--------+---------------------
109945 | | | Activity |
AutoVacuumMain | idle | autovacuum launcher
109947 | | logical replication launcher | Activity |
LogicalLauncherMain | idle | bgworker
109962 | 10 | walreceiver | Activity |
WalSenderMain | idle | wal sender
109976 | 10 | psql | |
| active | client backend
109943 | | | Activity |
BgWriterMain | idle | writer
109942 | | | Activity |
CheckpointerMain | idle | checkpointer
109944 | | | Activity |
WalWriterMain | idle | wal writer
(7 rows)

Whereas, the output of other pgstat_* functions remains unchanged. For example,

postgres=# SELECT pg_stat_get_backend_pid(s.backendid) AS procpid,
pg_stat_get_backend_activity(s.backendid) AS current_query
FROM (SELECT pg_stat_get_backend_idset() AS backendid) AS s;
procpid | current_query
---------+-------------------------------------------------------------------
120713 | SELECT pg_stat_get_backend_pid(s.backendid) AS procpid, +
| pg_stat_get_backend_activity(s.backendid) AS current_query+
| FROM (SELECT pg_stat_get_backend_idset() AS backendid) AS s;

Thoughts?

--
Thanks & Regards,
Kuntal Ghosh
EnterpriseDB: http://www.enterprisedb.com