Hi hackers,
Now that fa88928470 generates automatically code and documentation
related to wait events, why not exposing the wait events description
through a system catalog relation? (the idea has been proposed on twitter
by Yves Colin [1]).
I think that could be useful to:
- join this new relation with pg_stat_activity and have a quick
understanding of what the sessions are waiting for (if any).
- quickly compare the wait events across versions (what are the new
ones if any,..)
Please find attached a POC patch creating this new system catalog
pg_wait_event.
The patch:
- updates the documentation
- adds a new option to generate-wait_event_types.pl to generate the pg_wait_event.dat
- creates the pg_wait_event.h
- works with autoconf
It currently does not:
- works with meson (has to be done)
- add tests (not sure it's needed)
- create an index on the new system catalog (not sure it's needed as the data fits
in 4 pages (8kB default size)).
Outcome example:
postgres=# select a.pid, a.application_name, a.wait_event,d.description from pg_stat_activity a, pg_wait_event d where
a.wait_event= d.wait_event_name and state='active';
pid | application_name | wait_event | description
---------+------------------+-------------+-------------------------------------------------------------------
2937546 | pgbench | WALInitSync | Waiting for a newly initialized WAL file to reach durable storage
(1 row)
There is still some work to be done to generate the pg_wait_event.dat file, specially when the
same wait event name can be found in multiple places (like for example "WALWrite" in IO and LWLock),
leading to:
postgres=# select * from pg_wait_event where wait_event_name = 'WALWrite';
wait_event_name | description
-----------------+----------------------------------------------------------------------------------
WALWrite | Waiting for a write to a WAL file. Waiting for WAL buffers to be written to disk
WALWrite | Waiting for WAL buffers to be written to disk
(2 rows)
which is obviously not right (we'll probably have to add the wait class name to the game).
I'm sharing it now (even if it's still WIP) so that you can start sharing your thoughts
about it.
[1]: https://twitter.com/Ycolin/status/1676598065048743948
Regards,
--
Bertrand Drouvot
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com