Subject: WIP: new system catalog pg_wait_event

From: "Drouvot, Bertrand" <[email-masked]>
Date: Fri, 4 Aug 2023 16:22:54 +0200

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

From c56ce13af7193bf0d679ec0a7533ab686464f34e Mon Sep 17 00:00:00 2001
From: Bertrand Drouvot <[email-masked]>
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

---

From: Tom Lane <[email-masked]>
Date: Fri, 04 Aug 2023 11:08:21 -0400

"Drouvot, Bertrand" <[email-masked]> writes:
> through a system catalog relation?

I think you'd be better off making this a view over a set-returning
function.  The nearby work to allow run-time extensibility of the
set of wait events is not going to be happy with a static catalog.

			regards, tom lane

---

From: "Drouvot, Bertrand" <[email-masked]>
Date: Mon, 7 Aug 2023 10:23:27 +0200

Hi,

> set of wait events is not going to be happy with a static catalog.

Oh right, good point, thanks!: I'll come back with a new patch version to make
use of SRF instead.

Regards,

---

From: "Drouvot, Bertrand" <[email-masked]>
Date: Mon, 7 Aug 2023 17:11:50 +0200

Hi,

> Oh right, good point, thanks!: I'll come back with a new patch version to make

Please find attached v2 making use of SRF.

v2:

- adds a new pg_wait_event.c that acts as the "template" for the SRF
- generates pg_wait_event_insert.c (through generate-wait_event_types.pl) that
is included into pg_wait_event.c

That way I think it's flexible enough to add more code if needed in the SRF.

The patch also:

- updates the doc
- works with autoconf and meson
- adds a simple test

I'm adding a new CF entry for it.

Regards,

From 63f1316b2c160b72bdc1bcff6272e2888fce0db4 Mon Sep 17 00:00:00 2001
From: bdrouvotAWS <[email-masked]>
Date: Sat, 5 Aug 2023 12:39:42 +0000
Subject: [PATCH v2] pg_wait_event

Adding a new system view, namely pg_wait_event, that describes the wait events.
---
 doc/src/sgml/system-views.sgml                | 55 ++++++++++++++
 src/backend/catalog/system_views.sql          |  3 +
 src/backend/utils/activity/.gitignore         |  1 +
 src/backend/utils/activity/Makefile           |  6 +-
 .../activity/generate-wait_event_types.pl     | 76 +++++++++++++------
 src/backend/utils/activity/meson.build        |  1 +
 src/backend/utils/activity/pg_wait_event.c    | 41 ++++++++++
 src/include/catalog/pg_proc.dat               |  6 ++
 src/include/utils/meson.build                 |  4 +-
 src/test/regress/expected/rules.out           |  3 +
 src/test/regress/expected/sysviews.out        |  8 ++
 src/test/regress/sql/sysviews.sql             |  4 +
 src/tools/msvc/clean.bat                      |  1 +
 13 files changed, 183 insertions(+), 26 deletions(-)
  19.8% doc/src/sgml/
  60.6% src/backend/utils/activity/
   5.3% src/include/catalog/
   5.7% src/test/regress/expected/
   3.0% src/test/regress/sql/
   5.3% src/

diff --git a/doc/src/sgml/system-views.sgml b/doc/src/sgml/system-views.sgml
index 57b228076e..e9cbeff682 100644
--- a/doc/src/sgml/system-views.sgml
+++ b/doc/src/sgml/system-views.sgml
@@ -221,6 +221,11 @@
       <entry>views</entry>
      </row>
 
+     <row>
+      <entry><link linkend="view-pg-wait-event"><structname>pg_wait_event</structname></link></entry>
+      <entry>wait events</entry>
+     </row>
+
     </tbody>
    </tgroup>
   </table>
@@ -4825,4 +4830,54 @@ SELECT * FROM pg_locks pl LEFT JOIN pg_prepared_xacts ppx
   </table>
  </sect1>
 
+
+ <sect1 id="view-pg-wait-event">
+  <title><structname>pg_wait_event</structname></title>
+
+  <indexterm zone="view-pg-wait-event">
+   <primary>pg_wait_event</primary>
+  </indexterm>
+
+  <para>
+   The view <structname>pg_wait_event</structname> provides description about the
+   wait events.
+  </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>texte</type>
+      </para>
+      <para>
+       Wait event description
+      </para></entry>
+     </row>
+    </tbody>
+   </tgroup>
+  </table>
+ </sect1>
+
 </chapter>
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index af65af6bdd..f86a4dd770 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -1342,3 +1342,6 @@ CREATE VIEW pg_stat_subscription_stats AS
         ss.stats_reset
     FROM pg_subscription as s,
          pg_stat_get_subscription_stats(s.oid) as ss;
+
+CREATE VIEW pg_wait_event AS
+    SELECT * FROM pg_get_wait_events() AS we;
diff --git a/src/backend/utils/activity/.gitignore b/src/backend/utils/activity/.gitignore
index d77079285b..ad089a0b63 100644
--- a/src/backend/utils/activity/.gitignore
+++ b/src/backend/utils/activity/.gitignore
@@ -1,2 +1,3 @@
 /pgstat_wait_event.c
 /wait_event_types.h
+/pg_wait_event_insert.c
diff --git a/src/backend/utils/activity/Makefile b/src/backend/utils/activity/Makefile
index f1117745d4..8595e6ea77 100644
--- a/src/backend/utils/activity/Makefile
+++ b/src/backend/utils/activity/Makefile
@@ -32,10 +32,14 @@ OBJS = \
 	pgstat_subscription.o \
 	pgstat_wal.o \
 	pgstat_xact.o \
+	pg_wait_event.o \
 	wait_event.o
 
 include $(top_srcdir)/src/backend/common.mk
 
+pg_wait_event.o: pg_wait_event_insert.c
+pg_wait_event_insert.c: wait_event_types.h
+
 wait_event.o: pgstat_wait_event.c
 pgstat_wait_event.c: wait_event_types.h
 	touch $@
@@ -44,4 +48,4 @@ wait_event_types.h: $(top_srcdir)/src/backend/utils/activity/wait_event_names.tx
 	$(PERL) $(srcdir)/generate-wait_event_types.pl --code $<
 
 maintainer-clean: clean
-	rm -f wait_event_types.h pgstat_wait_event.c
+	rm -f wait_event_types.h pgstat_wait_event.c pg_wait_event_insert.c
diff --git a/src/backend/utils/activity/generate-wait_event_types.pl b/src/backend/utils/activity/generate-wait_event_types.pl
index 56335e8730..6f2b823b6b 100644
--- a/src/backend/utils/activity/generate-wait_event_types.pl
+++ b/src/backend/utils/activity/generate-wait_event_types.pl
@@ -4,6 +4,7 @@
 # Generate wait events support files from wait_event_names.txt:
 # - wait_event_types.h (if --code is passed)
 # - pgstat_wait_event.c (if --code is passed)
+# - pg_wait_event_insert.c (if --code is passed)
 # - wait_event_types.sgml (if --docs is passed)
 #
 # Portions Copyright (c) 1996-2023, PostgreSQL Global Development Group
@@ -98,8 +99,10 @@ if ($gen_code)
 	# multiple times.
 	my $htmp = "$output_path/wait_event_types.h.tmp$$";
 	my $ctmp = "$output_path/pgstat_wait_event.c.tmp$$";
+	my $ictmp = "$output_path/pg_wait_event_insert.c.tmp$$";
 	open my $h, '>', $htmp or die "Could not open $htmp: $!";
 	open my $c, '>', $ctmp or die "Could not open $ctmp: $!";
+	open my $ic, '>', $ictmp or die "Could not open $ictmp: $!";
 
 	my $header_comment =
 	  '/*-------------------------------------------------------------------------
@@ -129,16 +132,17 @@ if ($gen_code)
 
 	printf $c $header_comment, 'pgstat_wait_event.c';
 
+	printf $ic $header_comment, 'pg_wait_event_insert.c';
+
 	# uc() is being used to force the comparison to be case-insensitive.
 	foreach my $waitclass (sort { uc($a) cmp uc($b) } keys %hashwe)
 	{
 
-		# Don't generate .c and .h files for Extension, LWLock and
-		# Lock, these are handled independently.
-		next
-		  if ( $waitclass eq 'WaitEventExtension'
-			|| $waitclass eq 'WaitEventLWLock'
-			|| $waitclass eq 'WaitEventLock');
+		# Don't generate .c (except pg_wait_event_insert.c) and .h files for
+		# Extension, LWLock and Lock, these are handled independently.
+		my $is_exception = $waitclass eq 'WaitEventExtension' ||
+						   $waitclass eq 'WaitEventLWLock' ||
+						   $waitclass eq 'WaitEventLock';
 
 		my $last = $waitclass;
 		$last =~ s/^WaitEvent//;
@@ -147,50 +151,76 @@ if ($gen_code)
 		my $firstpass = 1;
 		my $pg_wait_class;
 
-		printf $c
-		  "static const char *\npgstat_get_wait_$lastlc($waitclass w)\n{\n";
-		printf $c "\tconst char *event_name = \"unknown wait event\";\n\n";
-		printf $c "\tswitch (w)\n\t{\n";
+		if (!$is_exception)
+		{
+			printf $c
+			  "static const char *\npgstat_get_wait_$lastlc($waitclass w)\n{\n";
+			printf $c "\tconst char *event_name = \"unknown wait event\";\n\n";
+			printf $c "\tswitch (w)\n\t{\n";
+		}
 
 		foreach my $wev (@{ $hashwe{$waitclass} })
 		{
-			if ($firstpass)
+			if ($firstpass && !$is_exception)
 			{
 				printf $h "typedef enum\n{\n";
 				$pg_wait_class = "PG_WAIT_" . $lastuc;
 				printf $h "\t%s = %s", $wev->[0], $pg_wait_class;
 				$continue = ",\n";
 			}
-			else
+			elsif (!$is_exception)
 			{
 				printf $h "%s\t%s", $continue, $wev->[0];
 				$continue = ",\n";
 			}
-			$firstpass = 0;
 
-			printf $c "\t\t case %s:\n", $wev->[0];
-			# Apply quotes to the wait event name string.
-			printf $c "\t\t\t event_name = \"%s\";\n\t\t\t break;\n",
-			  $wev->[1];
+			if (!$is_exception)
+			{
+				$firstpass = 0;
+
+				printf $c "\t\t case %s:\n", $wev->[0];
+				# Apply quotes to the wait event name string.
+				printf $c "\t\t\t event_name = \"%s\";\n\t\t\t break;\n",
+				$wev->[1];
+			}
+
+			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 $ic "\tmemset(values, 0, sizeof(values));\n";
+			printf $ic "\tmemset(nulls, 0, sizeof(nulls));\n\n";
+			printf $ic "\tvalues[0] = CStringGetTextDatum(\"%s\");\n", $wev->[1];
+			printf $ic "\tvalues[1] = CStringGetTextDatum(\"%s\");\n\n", $new_desc;
+
+			printf $ic "\ttuplestore_putvalues(rsinfo->setResult, rsinfo->setDesc, values, nulls);\n\n";
 		}
 
-		printf $h "\n} $waitclass;\n\n";
+		if (!$is_exception)
+		{
+			printf $h "\n} $waitclass;\n\n";
 
-		printf $c
-		  "\t\t\t /* no default case, so that compiler will warn */\n";
-		printf $c "\t}\n\n";
-		printf $c "\treturn event_name;\n";
-		printf $c "}\n\n";
+			printf $c
+			  "\t\t\t /* no default case, so that compiler will warn */\n";
+			printf $c "\t}\n\n";
+			printf $c "\treturn event_name;\n";
+			printf $c "}\n\n";
+		}
 	}
 
 	printf $h "#endif                          /* WAIT_EVENT_TYPES_H */\n";
 	close $h;
 	close $c;
+	close $ic;
 
 	rename($htmp, "$output_path/wait_event_types.h")
 	  || die "rename: $htmp to $output_path/wait_event_types.h: $!";
 	rename($ctmp, "$output_path/pgstat_wait_event.c")
 	  || die "rename: $ctmp to $output_path/pgstat_wait_event.c: $!";
+	rename($ictmp, "$output_path/pg_wait_event_insert.c")
+	  || die "rename: $ictmp to $output_path/pg_wait_event_insert.c: $!";
 }
 # Generate the .sgml file.
 elsif ($gen_docs)
diff --git a/src/backend/utils/activity/meson.build b/src/backend/utils/activity/meson.build
index 9633f3623c..774e0bd348 100644
--- a/src/backend/utils/activity/meson.build
+++ b/src/backend/utils/activity/meson.build
@@ -23,6 +23,7 @@ backend_sources += files(
 # seems nicer to not add that as an include path for the whole backend.
 waitevent_sources = files(
   'wait_event.c',
+  'pg_wait_event.c',
 )
 
 wait_event = static_library('wait_event_names',
diff --git a/src/backend/utils/activity/pg_wait_event.c b/src/backend/utils/activity/pg_wait_event.c
new file mode 100644
index 0000000000..9407d18254
--- /dev/null
+++ b/src/backend/utils/activity/pg_wait_event.c
@@ -0,0 +1,41 @@
+/* ----------
+ * pg_wait_event.c
+ *    Wait event reporting infrastructure.
+ *
+ * Copyright (c) 2001-2023, PostgreSQL Global Development Group
+ *
+ *
+ * IDENTIFICATION
+ *    src/backend/utils/activity/pg_wait_event.c
+ *
+ * NOTES
+ *
+ * The "pg_wait_event_insert.c" included in this file is generated by
+ *                    src/backend/utils/activity/generate-wait_event_types.pl
+ *
+ */
+#include "postgres.h"
+
+#include "funcapi.h"
+#include "utils/builtins.h"
+
+/*
+ * This function lists the wait events and their descriptions.
+ *
+ * The system view pg_wait_event provides a user interface to this
+ * SRF.
+ */
+Datum
+pg_get_wait_events(PG_FUNCTION_ARGS)
+{
+#define NUM_WAIT_EVENT_TABLES_ELEM 2
+	ReturnSetInfo *rsinfo = (ReturnSetInfo *) fcinfo->resultinfo;
+	Datum       values[NUM_WAIT_EVENT_TABLES_ELEM];
+	bool        nulls[NUM_WAIT_EVENT_TABLES_ELEM];
+
+	/* Build tuplestore to hold the result rows */
+	InitMaterializedSRF(fcinfo, 0);
+
+	#include "pg_wait_event_insert.c"
+	return (Datum) 0;
+}
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 6996073989..57d8875dee 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -5417,6 +5417,12 @@
   proargmodes => '{i,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o}',
   proargnames => '{pid,datid,pid,usesysid,application_name,state,query,wait_event_type,wait_event,xact_start,query_start,backend_start,state_change,client_addr,client_hostname,client_port,backend_xid,backend_xmin,backend_type,ssl,sslversion,sslcipher,sslbits,ssl_client_dn,ssl_client_serial,ssl_issuer_dn,gss_auth,gss_princ,gss_enc,gss_delegation,leader_pid,query_id}',
   prosrc => 'pg_stat_get_activity' },
+{ oid => '8403', descr => 'describe wait events',
+  proname => 'pg_get_wait_events', procost => '10', prorows => '100',
+  proretset => 't', provolatile => 's', prorettype => 'record',
+  proargtypes => '', proallargtypes => '{text,text}',
+  proargmodes => '{o,o}', proargnames => '{wait_event_name,description}',
+  prosrc => 'pg_get_wait_events' },
 { oid => '3318',
   descr => 'statistics: information about progress of backends running maintenance command',
   proname => 'pg_stat_get_progress_info', prorows => '100', proretset => 't',
diff --git a/src/include/utils/meson.build b/src/include/utils/meson.build
index 6de5d93799..4f14f770ef 100644
--- a/src/include/utils/meson.build
+++ b/src/include/utils/meson.build
@@ -1,6 +1,6 @@
 # Copyright (c) 2022-2023, PostgreSQL Global Development Group
 
-wait_event_output = ['wait_event_types.h', 'pgstat_wait_event.c']
+wait_event_output = ['wait_event_types.h', 'pgstat_wait_event.c', 'pg_wait_event_insert.c']
 wait_event_target = custom_target('wait_event_names',
   input: files('../../backend/utils/activity/wait_event_names.txt'),
   output: wait_event_output,
@@ -11,7 +11,7 @@ wait_event_target = custom_target('wait_event_names',
   ],
   build_by_default: true,
   install: true,
-  install_dir: [dir_include_server / 'utils', false],
+  install_dir: [dir_include_server / 'utils', false, false],
 )
 
 wait_event_types_h = wait_event_target[0]
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index e07afcd4aa..23b5f7dbca 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -2631,6 +2631,9 @@ pg_views| SELECT n.nspname AS schemaname,
    FROM (pg_class c
      LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace)))
   WHERE (c.relkind = 'v'::"char");
+pg_wait_event| SELECT wait_event_name,
+    description
+   FROM pg_get_wait_events() we(wait_event_name, description);
 SELECT tablename, rulename, definition FROM pg_rules
 WHERE schemaname = 'pg_catalog'
 ORDER BY tablename, rulename;
diff --git a/src/test/regress/expected/sysviews.out b/src/test/regress/expected/sysviews.out
index 001c6e7eb9..12309be267 100644
--- a/src/test/regress/expected/sysviews.out
+++ b/src/test/regress/expected/sysviews.out
@@ -134,6 +134,14 @@ select name, setting from pg_settings where name like 'enable%';
  enable_tidscan                 | on
 (21 rows)
 
+-- There will surely be at least 240 wait events and at least 27 related to WAL
+select count(*) > 239 as ok, count(*) FILTER (WHERE description like '%WAL%') > 26 AS ok_wal_desc
+  from pg_wait_event;
+ ok | ok_wal_desc 
+----+-------------
+ t  | t
+(1 row)
+
 -- Test that the pg_timezone_names and pg_timezone_abbrevs views are
 -- more-or-less working.  We can't test their contents in any great detail
 -- without the outputs changing anytime IANA updates the underlying data,
diff --git a/src/test/regress/sql/sysviews.sql b/src/test/regress/sql/sysviews.sql
index 351e469c77..6bc6bd1322 100644
--- a/src/test/regress/sql/sysviews.sql
+++ b/src/test/regress/sql/sysviews.sql
@@ -55,6 +55,10 @@ select count(*) = 0 as ok from pg_stat_wal_receiver;
 -- a regression test run.
 select name, setting from pg_settings where name like 'enable%';
 
+-- There will surely be at least 240 wait events and at least 27 related to WAL
+select count(*) > 239 as ok, count(*) FILTER (WHERE description like '%WAL%') > 26 AS ok_wal_desc
+  from pg_wait_event;
+
 -- Test that the pg_timezone_names and pg_timezone_abbrevs views are
 -- more-or-less working.  We can't test their contents in any great detail
 -- without the outputs changing anytime IANA updates the underlying data,
diff --git a/src/tools/msvc/clean.bat b/src/tools/msvc/clean.bat
index 7cb23ea894..028d174d87 100755
--- a/src/tools/msvc/clean.bat
+++ b/src/tools/msvc/clean.bat
@@ -55,6 +55,7 @@ if exist src\include\catalog\header-stamp del /q src\include\catalog\header-stam
 if exist doc\src\sgml\version.sgml del /q doc\src\sgml\version.sgml
 
 if %DIST%==1 if exist src\backend\utils\activity\pgstat_wait_event.c del /q src\backend\utils\activity\pgstat_wait_event.c
+if %DIST%==1 if exist src\backend\utils\activity\pg_wait_event_insert.c del /q src\backend\utils\activity\pg_wait_event_insert.c
 if %DIST%==1 if exist src\backend\utils\activity\wait_event_types.h del /q src\backend\utils\activity\wait_event_types.h
 if %DIST%==1 if exist src\backend\utils\fmgroids.h del /q src\backend\utils\fmgroids.h
 if %DIST%==1 if exist src\backend\utils\fmgrprotos.h del /q src\backend\utils\fmgrprotos.h
-- 
2.34.1

---

From: Kyotaro Horiguchi <[email-masked]>
Date: Tue, 08 Aug 2023 11:53:32 +0900 (JST)

At Mon, 7 Aug 2023 17:11:50 +0200, "Drouvot, Bertrand" <[email-masked]> wrote in 
> I'm adding a new CF entry for it.

As I mentioned in another thread, I'm uncertain about our stance on
the class id of the wait event. If a class acts as a namespace, we
should include it in the view. Otherwise, if the class id is just an
attribute of the wait event, we should make the event name unique.

regards.

-- 
Kyotaro Horiguchi
NTT Open Source Software Center

---

From: Michael Paquier <[email-masked]>
Date: Tue, 8 Aug 2023 12:05:25 +0900

> attribute of the wait event, we should make the event name unique.

Including the class name in the view makes the most sense to me, FWIW,
as it could be also possible that one reuses an event name in the
existing in-core list, but for extensions.  That's of course not
something I would recommend.
--
Michael