Add system view pg_wait_events

This new view, wrapped around a SRF, shows some information known about
wait events, as of:
- Name.
- Type (Activity, I/O, Extension, etc.).
- Description.

All the information retrieved comes from wait_event_names.txt, and the
description is the same as the documentation with filters applied to
remove any XML markups.  This view is useful when joined with
pg_stat_activity to get the description of a wait event reported.

Custom wait events for extensions are included in the view.

Original idea by Yves Colin.

Author: Bertrand Drouvot
Reviewed-by: Kyotaro Horiguchi, Masahiro Ikeda, Tom Lane, Michael
Paquier
Discussion: https://postgr.es/m/0e2ae164-dc89-03c3-cf7f-de86378053ac@gmail.com

2 files changed, 77 insertions, 1 deletions

diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index 70511a2388e..4ff415d6a0c 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -1103,7 +1103,7 @@ postgres   27093  0.0  0.0  30096  2752 ?        Ss   11:34   0:00 postgres: ser
   &wait_event_types;
 
    <para>
-     Here is an example of how wait events can be viewed:
+     Here are examples of how wait events can be viewed:
 
 <programlisting>
 SELECT pid, wait_event_type, wait_event FROM pg_stat_activity WHERE wait_event is NOT NULL;
@@ -1113,6 +1113,18 @@ SELECT pid, wait_event_type, wait_event FROM pg_stat_activity WHERE wait_event i
  6644 | LWLock          | ProcArray
 (2 rows)
 </programlisting>
+
+<programlisting>
+SELECT a.pid, a.wait_event, w.description
+  FROM pg_stat_activity a JOIN
+       pg_wait_events w ON (a.wait_event_type = w.type AND
+                            a.wait_event = w.name)
+  WHERE wait_event is NOT NULL and a.state = 'active';
+-[ RECORD 1 ]------------------------------------------------------------------
+pid         | 686674
+wait_event  | WALInitSync
+description | Waiting for a newly initialized WAL file to reach durable storage
+</programlisting>
    </para>
 
    <note>
diff --git a/doc/src/sgml/system-views.sgml b/doc/src/sgml/system-views.sgml
index 57b228076e8..2b35c2f91b3 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-events"><structname>pg_wait_events</structname></link></entry>
+      <entry>wait events</entry>
+     </row>
+
     </tbody>
    </tgroup>
   </table>
@@ -4825,4 +4830,63 @@ SELECT * FROM pg_locks pl LEFT JOIN pg_prepared_xacts ppx
   </table>
  </sect1>
 
+
+ <sect1 id="view-pg-wait-events">
+  <title><structname>pg_wait_events</structname></title>
+
+  <indexterm zone="view-pg-wait-events">
+   <primary>pg_wait_events</primary>
+  </indexterm>
+
+  <para>
+   The view <structname>pg_wait_events</structname> provides description about the
+   wait events.
+  </para>
+
+  <table>
+   <title><structname>pg_wait_events</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>type</structfield> <type>text</type>
+      </para>
+      <para>
+       Wait event type
+      </para></entry>
+     </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>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>