[Drouvot, Bertrand <bertranddrouvot.pg@gmail.com>]

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

Attachments:

v1-0001-pg_wait_event.patch (text/plain, 10.6K) download
From c56ce13af7193bf0d679ec0a7533ab686464f34e Mon Sep 17 00:00:00 2001
From: Bertrand Drouvot <bertranddrouvot.pg@gmail.com>
Date: Tue, 1 Aug 2023 03:55:51 +0000
Subject: [PATCH v1] pg_wait_event

Adding a new shared catalog relation, namely pg_wait_event, that describes the
wait events.

The content is auto-generated with generate-wait_event_types.pl.
---
 doc/src/sgml/catalogs.sgml                    | 62 ++++++++++++++++++
 src/backend/catalog/.gitignore                |  1 +
 src/backend/catalog/Makefile                  | 13 ++--
 src/backend/catalog/catalog.c                 |  4 +-
 .../activity/generate-wait_event_types.pl     | 65 ++++++++++++++++++-
 src/include/catalog/catversion.h              |  2 +-
 src/include/catalog/pg_wait_event.h           | 45 +++++++++++++
 7 files changed, 184 insertions(+), 8 deletions(-)
  26.7% doc/src/sgml/
  16.5% src/backend/catalog/
  33.6% src/backend/utils/activity/
  23.0% src/include/catalog/

diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index 307ad88b50..f181a5cedb 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -369,6 +369,11 @@
       <entry><link linkend="catalog-pg-user-mapping"><structname>pg_user_mapping</structname></link></entry>
       <entry>mappings of users to foreign servers</entry>
      </row>
+
+     <row>
+      <entry><link linkend="catalog-pg-wait-event"><structname>pg_wait_event</structname></link></entry>
+      <entry>wait events description</entry>
+     </row>
     </tbody>
    </tgroup>
   </table>
@@ -9668,4 +9673,61 @@ SCRAM-SHA-256$<replaceable>&lt;iteration count&gt;</replaceable>:<replaceable>&l
   </table>
  </sect1>
 
+
+ <sect1 id="catalog-pg-wait-event">
+  <title><structname>pg_wait_event</structname></title>
+
+  <indexterm zone="catalog-pg-wait-event">
+   <primary>pg_wait_event</primary>
+  </indexterm>
+
+  <para>
+   The catalog <structname>pg_wait_event</structname> stores description about
+   the wait events.
+  </para>
+
+  <para>
+   Unlike most system catalogs, <structname>pg_wait_event</structname>
+   is shared across all databases of a cluster: there is only one
+   copy of <structname>pg_wait_event</structname> per cluster, not
+   one per database.
+  </para>
+
+  <table>
+   <title><structname>pg_wait_event</structname> Columns</title>
+   <tgroup cols="1">
+    <thead>
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       Column Type
+      </para>
+      <para>
+       Description
+      </para></entry>
+     </row>
+    </thead>
+
+    <tbody>
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>wait_event_name</structfield> <type>text</type>
+      </para>
+      <para>
+       Wait event name
+      </para></entry>
+     </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>description</structfield> <type>text</type>
+      </para>
+      <para>
+       Wait event description
+      </para></entry>
+     </row>
+    </tbody>
+   </tgroup>
+  </table>
+ </sect1>
+
 </chapter>
diff --git a/src/backend/catalog/.gitignore b/src/backend/catalog/.gitignore
index 237ff54165..7f2f3045dc 100644
--- a/src/backend/catalog/.gitignore
+++ b/src/backend/catalog/.gitignore
@@ -4,3 +4,4 @@
 /system_constraints.sql
 /pg_*_d.h
 /bki-stamp
+/pg_wait_event.dat
diff --git a/src/backend/catalog/Makefile b/src/backend/catalog/Makefile
index a60107bf94..68a97fbdcd 100644
--- a/src/backend/catalog/Makefile
+++ b/src/backend/catalog/Makefile
@@ -72,7 +72,8 @@ CATALOG_HEADERS := \
 	pg_collation.h pg_parameter_acl.h pg_partitioned_table.h \
 	pg_range.h pg_transform.h \
 	pg_sequence.h pg_publication.h pg_publication_namespace.h \
-	pg_publication_rel.h pg_subscription.h pg_subscription_rel.h
+	pg_publication_rel.h pg_subscription.h pg_subscription_rel.h \
+	pg_wait_event.h
 
 GENERATED_HEADERS := $(CATALOG_HEADERS:%.h=%_d.h) schemapg.h system_fk_info.h
 
@@ -89,9 +90,13 @@ POSTGRES_BKI_DATA = $(addprefix $(top_srcdir)/src/include/catalog/,\
 	pg_ts_template.dat pg_type.dat \
 	)
 
-all: distprep generated-header-symlinks
+all: distprep generated-header-symlinks pg_wait_event.dat
 
-distprep: bki-stamp
+pg_wait_event.dat:
+	$(PERL) $(top_srcdir)/src/backend/utils/activity/generate-wait_event_types.pl --data $(top_srcdir)/src/backend/utils/activity/wait_event_names.txt
+	$(LN_S) "$(top_srcdir)/src/backend/catalog/pg_wait_event.dat" '$(top_srcdir)/src/include/catalog/pg_wait_event.dat'
+
+distprep: pg_wait_event.dat bki-stamp
 
 .PHONY: generated-header-symlinks
 
@@ -143,4 +148,4 @@ uninstall-data:
 clean:
 
 maintainer-clean: clean
-	rm -f bki-stamp postgres.bki system_constraints.sql $(GENERATED_HEADERS)
+	rm -f pg_wait_event.dat bki-stamp postgres.bki system_constraints.sql $(GENERATED_HEADERS) $(top_srcdir)/src/include/catalog/pg_wait_event.dat
diff --git a/src/backend/catalog/catalog.c b/src/backend/catalog/catalog.c
index 1bf6c5633c..da12247551 100644
--- a/src/backend/catalog/catalog.c
+++ b/src/backend/catalog/catalog.c
@@ -41,6 +41,7 @@
 #include "catalog/pg_subscription.h"
 #include "catalog/pg_tablespace.h"
 #include "catalog/pg_type.h"
+#include "catalog/pg_wait_event.h"
 #include "miscadmin.h"
 #include "storage/fd.h"
 #include "utils/fmgroids.h"
@@ -255,7 +256,8 @@ IsSharedRelation(Oid relationId)
 		relationId == SharedDescriptionRelationId ||
 		relationId == SharedSecLabelRelationId ||
 		relationId == SubscriptionRelationId ||
-		relationId == TableSpaceRelationId)
+		relationId == TableSpaceRelationId ||
+		relationId == WaitEventRelationId)
 		return true;
 	/* These are their indexes */
 	if (relationId == AuthIdOidIndexId ||
diff --git a/src/backend/utils/activity/generate-wait_event_types.pl b/src/backend/utils/activity/generate-wait_event_types.pl
index 56335e8730..81f21eaf44 100644
--- a/src/backend/utils/activity/generate-wait_event_types.pl
+++ b/src/backend/utils/activity/generate-wait_event_types.pl
@@ -5,6 +5,7 @@
 # - wait_event_types.h (if --code is passed)
 # - pgstat_wait_event.c (if --code is passed)
 # - wait_event_types.sgml (if --docs is passed)
+# - pg_wait_event.dat (if --data is passed)
 #
 # Portions Copyright (c) 1996-2023, PostgreSQL Global Development Group
 # Portions Copyright (c) 1994, Regents of the University of California
@@ -20,6 +21,7 @@ use Getopt::Long;
 my $output_path = '.';
 my $gen_docs = 0;
 my $gen_code = 0;
+my $gen_data = 0;
 
 my $continue = "\n";
 my %hashwe;
@@ -27,14 +29,21 @@ my %hashwe;
 GetOptions(
 	'outdir:s' => \$output_path,
 	'docs' => \$gen_docs,
+	'data' => \$gen_data,
 	'code' => \$gen_code) || usage();
 
-die "Needs to specify --docs or --code"
-  if (!$gen_docs && !$gen_code);
+die "Needs to specify --docs, --code or --data"
+  if (!$gen_docs && !$gen_code && !$gen_data);
 
 die "Not possible to specify --docs and --code simultaneously"
   if ($gen_docs && $gen_code);
 
+die "Not possible to specify --docs and --data simultaneously"
+  if ($gen_docs && $gen_data);
+
+die "Not possible to specify --code and --data simultaneously"
+  if ($gen_code && $gen_data);
+
 open my $wait_event_names, '<', $ARGV[0] or die;
 
 my @lines;
@@ -239,7 +248,59 @@ elsif ($gen_docs)
 	rename($stmp, "$output_path/wait_event_types.sgml")
 	  || die "rename: $stmp to $output_path/wait_event_types.sgml: $!";
 }
+# Generate the .dat file.
+elsif ($gen_data)
+{
 
+	# Include PID in suffix in case parallel make runs this script
+	# multiple times.
+	my $dtmp = "$output_path/pg_wait_event.dat.tmp$$";
+	open my $d, '>', $dtmp or die "Could not open $dtmp: $!";
+
+	my $data_header_comment =
+'#----------------------------------------------------------------------
+#
+# %s
+#    Initial contents of the pg_wait_event system catalog.
+#
+# Portions Copyright (c) 1996-2023, PostgreSQL Global Development Group
+# Portions Copyright (c) 1994, Regents of the University of California
+#
+# NOTES
+#  ******************************
+#  *** DO NOT EDIT THIS FILE! ***
+#  ******************************
+#
+#  It has been GENERATED by src/backend/utils/activity/generate-wait_event_types.pl
+#
+#----------------------------------------------------------------------
+';
+
+	printf $d $data_header_comment, 'pg_wait_event.dat';
+	printf $d "[\n\n";
+
+	# uc() is being used to force the comparison to be case-insensitive.
+	foreach my $waitclass (sort { uc($a) cmp uc($b) } keys %hashwe)
+	{
+		foreach my $wev (@{ $hashwe{$waitclass} })
+		{
+			my $new_desc = substr $wev->[2], 1, -2;
+			$new_desc =~ s/'/\\'/g;
+			$new_desc =~ s/<.*>(.*?)<.*>/$1/g;
+			$new_desc =~ s/<xref linkend="guc-(.*?)"\/>/$1/g;
+			$new_desc =~ s/; see.*$//;
+
+			printf $d "{ wait_event_name => '$wev->[1]', description => '%s' },\n",
+						$new_desc;
+		}
+	}
+
+	printf $d "\n]";
+	close $d;
+
+	rename($dtmp, "$output_path/pg_wait_event.dat")
+	  || die "rename: $dtmp to $output_path/pg_wait_event.dat: $!";
+}
 close $wait_event_names;
 
 sub usage
diff --git a/src/include/catalog/catversion.h b/src/include/catalog/catversion.h
index f507b49bb2..5a534771ed 100644
--- a/src/include/catalog/catversion.h
+++ b/src/include/catalog/catversion.h
@@ -57,6 +57,6 @@
  */
 
 /*							yyyymmddN */
-#define CATALOG_VERSION_NO	202307261
+#define CATALOG_VERSION_NO	202308011
 
 #endif
diff --git a/src/include/catalog/pg_wait_event.h b/src/include/catalog/pg_wait_event.h
new file mode 100644
index 0000000000..9647d45bd1
--- /dev/null
+++ b/src/include/catalog/pg_wait_event.h
@@ -0,0 +1,45 @@
+/*-------------------------------------------------------------------------
+ *
+ * pg_wait_event.h
+ *	  definition of the "wait event" system catalog (pg_wait_event)
+ *
+ *
+ * Portions Copyright (c) 1996-2023, PostgreSQL Global Development Group
+ * Portions Copyright (c) 1994, Regents of the University of California
+ *
+ * src/include/catalog/pg_wait_event.h
+ *
+ * NOTES
+ *	  The Catalog.pm module reads this file and derives schema
+ *	  information.
+ *
+ *-------------------------------------------------------------------------
+ */
+#ifndef PG_SE_H
+#define PG_WAIT_EVENT_H
+
+#include "catalog/genbki.h"
+#include "catalog/pg_wait_event_d.h"
+
+/* ----------------
+ *		pg_wait_event definition.  cpp turns this into
+ *		typedef struct FormData_pg_wait_event
+ * ----------------
+ */
+CATALOG(pg_wait_event,8403,WaitEventRelationId) BKI_SHARED_RELATION
+{
+#ifdef CATALOG_VARLEN			/* variable-length fields start here */
+	text		wait_event_name BKI_FORCE_NOT_NULL;
+
+	text		description BKI_FORCE_NOT_NULL;
+#endif
+} FormData_pg_wait_event;
+
+/* ----------------
+ *      Form_pg_wait_event corresponds to a pointer to a tuple with
+ *      the format of pg_wait_event relation.
+ * ----------------
+ */
+typedef FormData_pg_wait_event * Form_pg_wait_event;
+
+#endif							/* PG_WAIT_EVENT_H */
-- 
2.34.1