Adding upstream version 16.2.upstream/16.2

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>

1 files changed, 286 insertions, 0 deletions

diff --git a/doc/src/sgml/pgwalinspect.sgml b/doc/src/sgml/pgwalinspect.sgml
new file mode 100644
index 0000000..3a8121c
--- /dev/null
+++ b/doc/src/sgml/pgwalinspect.sgml
@@ -0,0 +1,286 @@
+<!-- doc/src/sgml/pgwalinspect.sgml -->
+
+<sect1 id="pgwalinspect" xreflabel="pg_walinspect">
+ <title>pg_walinspect &mdash; low-level WAL inspection</title>
+
+ <indexterm zone="pgwalinspect">
+  <primary>pg_walinspect</primary>
+ </indexterm>
+
+ <para>
+  The <filename>pg_walinspect</filename> module provides SQL functions that
+  allow you to inspect the contents of write-ahead log of
+  a running <productname>PostgreSQL</productname> database cluster at a low
+  level, which is useful for debugging, analytical, reporting or
+  educational purposes. It is similar to <xref linkend="pgwaldump"/>, but
+  accessible through SQL rather than a separate utility.
+ </para>
+
+ <para>
+  All the functions of this module will provide the WAL information using the
+  server's current timeline ID.
+ </para>
+
+ <note>
+  <para>
+   The <filename>pg_walinspect</filename> functions are often called
+   using an LSN argument that specifies the location at which a known
+   WAL record of interest <emphasis>begins</emphasis>.  However, some
+   functions, such as
+   <function><link linkend="pg-logical-emit-message">pg_logical_emit_message</link></function>,
+   return the LSN <emphasis>after</emphasis> the record that was just
+   inserted.
+  </para>
+ </note>
+ <tip>
+  <para>
+   All of the <filename>pg_walinspect</filename> functions that show
+   information about records that fall within a certain LSN range are
+   permissive about accepting <replaceable>end_lsn</replaceable>
+   arguments that are after the server's current LSN.  Using an
+   <replaceable>end_lsn</replaceable> <quote>from the future</quote>
+   will not raise an error.
+  </para>
+  <para>
+   It may be convenient to provide the value
+   <literal>FFFFFFFF/FFFFFFFF</literal> (the maximum valid
+   <type>pg_lsn</type> value) as an <replaceable>end_lsn</replaceable>
+   argument.  This is equivalent to providing an
+   <replaceable>end_lsn</replaceable> argument matching the server's
+   current LSN.
+  </para>
+ </tip>
+ <para>
+  By default, use of these functions is restricted to superusers and members of
+  the <literal>pg_read_server_files</literal> role. Access may be granted by
+  superusers to others using <command>GRANT</command>.
+ </para>
+
+ <sect2 id="pgwalinspect-funcs">
+  <title>General Functions</title>
+
+  <variablelist>
+   <varlistentry id="pgwalinspect-funcs-pg-get-wal-record-info">
+    <term>
+     <function>pg_get_wal_record_info(in_lsn pg_lsn) returns record</function>
+    </term>
+
+    <listitem>
+     <para>
+      Gets WAL record information about a record that is located at or
+      after the <replaceable>in_lsn</replaceable> argument.  For
+      example:
+<screen>
+postgres=# SELECT * FROM pg_get_wal_record_info('0/E419E28');
+-[ RECORD 1 ]----+-------------------------------------------------
+start_lsn        | 0/E419E28
+end_lsn          | 0/E419E68
+prev_lsn         | 0/E419D78
+xid              | 0
+resource_manager | Heap2
+record_type      | VACUUM
+record_length    | 58
+main_data_length | 2
+fpi_length       | 0
+description      | nunused: 5, unused: [1, 2, 3, 4, 5]
+block_ref        | blkref #0: rel 1663/16385/1249 fork main blk 364
+</screen>
+     </para>
+     <para>
+      If <replaceable>in_lsn</replaceable> isn't at the start of a WAL
+      record, information about the next valid WAL record is shown
+      instead.  If there is no next valid WAL record, the function
+      raises an error.
+     </para>
+    </listitem>
+   </varlistentry>
+
+    <varlistentry id="pgwalinspect-funcs-pg-get-wal-records-info">
+    <term>
+     <function>
+      pg_get_wal_records_info(start_lsn pg_lsn, end_lsn pg_lsn)
+      returns setof record
+     </function>
+    </term>
+
+    <listitem>
+     <para>
+      Gets information of all the valid WAL records between
+      <replaceable>start_lsn</replaceable> and <replaceable>end_lsn</replaceable>.
+      Returns one row per WAL record.  For example:
+<screen>
+postgres=# SELECT * FROM pg_get_wal_records_info('0/1E913618', '0/1E913740') LIMIT 1;
+-[ RECORD 1 ]----+--------------------------------------------------------------
+start_lsn        | 0/1E913618
+end_lsn          | 0/1E913650
+prev_lsn         | 0/1E9135A0
+xid              | 0
+resource_manager | Standby
+record_type      | RUNNING_XACTS
+record_length    | 50
+main_data_length | 24
+fpi_length       | 0
+description      | nextXid 33775 latestCompletedXid 33774 oldestRunningXid 33775
+block_ref        |
+</screen>
+     </para>
+     <para>
+      The function raises an error if
+      <replaceable>start_lsn</replaceable> is not available.
+     </para>
+    </listitem>
+   </varlistentry>
+
+   <varlistentry id="pgwalinspect-funcs-pg-get-wal-block-info">
+    <term>
+     <function>pg_get_wal_block_info(start_lsn pg_lsn, end_lsn pg_lsn, show_data boolean DEFAULT true) returns setof record</function>
+    </term>
+
+    <listitem>
+     <para>
+      Gets information about each block reference from all the valid
+      WAL records between <replaceable>start_lsn</replaceable> and
+      <replaceable>end_lsn</replaceable> with one or more block
+      references.  Returns one row per block reference per WAL record.
+      For example:
+<screen>
+postgres=# SELECT * FROM pg_get_wal_block_info('0/1230278', '0/12302B8');
+-[ RECORD 1 ]-----+-----------------------------------
+start_lsn         | 0/1230278
+end_lsn           | 0/12302B8
+prev_lsn          | 0/122FD40
+block_id          | 0
+reltablespace     | 1663
+reldatabase       | 1
+relfilenode       | 2658
+relforknumber     | 0
+relblocknumber    | 11
+xid               | 341
+resource_manager  | Btree
+record_type       | INSERT_LEAF
+record_length     | 64
+main_data_length  | 2
+block_data_length | 16
+block_fpi_length  | 0
+block_fpi_info    |
+description       | off: 46
+block_data        | \x00002a00070010402630000070696400
+block_fpi_data    |
+</screen>
+     </para>
+     <para>
+      This example involves a WAL record that only contains one block
+      reference, but many WAL records contain several block
+      references.  Rows output by
+      <function>pg_get_wal_block_info</function> are guaranteed to
+      have a unique combination of
+      <replaceable>start_lsn</replaceable> and
+      <replaceable>block_id</replaceable> values.
+     </para>
+     <para>
+      Much of the information shown here matches the output that
+      <function>pg_get_wal_records_info</function> would show, given
+      the same arguments.  However,
+      <function>pg_get_wal_block_info</function> unnests the
+      information from each WAL record into an expanded form by
+      outputting one row per block reference, so certain details are
+      tracked at the block reference level rather than at the
+      whole-record level.  This structure is useful with queries that
+      track how individual blocks changed over time.  Note that
+      records with no block references (e.g.,
+      <literal>COMMIT</literal> WAL records) will have no rows
+      returned, so <function>pg_get_wal_block_info</function> may
+      actually return <emphasis>fewer</emphasis> rows than
+      <function>pg_get_wal_records_info</function>.
+     </para>
+     <para>
+      The <structfield>reltablespace</structfield>,
+      <structfield>reldatabase</structfield>, and
+      <structfield>relfilenode</structfield> parameters reference
+      <link linkend="catalog-pg-tablespace"><structname>pg_tablespace</structname></link>.<structfield>oid</structfield>,
+      <link linkend="catalog-pg-database"><structname>pg_database</structname></link>.<structfield>oid</structfield>, and
+      <link linkend="catalog-pg-class"><structname>pg_class</structname></link>.<structfield>relfilenode</structfield>
+      respectively.  The <structfield>relforknumber</structfield>
+      field is the fork number within the relation for the block
+      reference; see <filename>common/relpath.h</filename> for
+      details.
+     </para>
+     <tip>
+      <para>
+       The <function>pg_filenode_relation</function> function (see
+       <xref linkend="functions-admin-dblocation"/>) can help you to
+       determine which relation was modified during original execution.
+      </para>
+     </tip>
+     <para>
+      It is possible for clients to avoid the overhead of
+      materializing block data.  This may make function execution
+      significantly faster.  When <replaceable>show_data</replaceable>
+      is set to <literal>false</literal>, <structfield>block_data</structfield>
+      and <structfield>block_fpi_data</structfield> values are omitted
+      (that is, the <structfield>block_data</structfield> and
+      <structfield>block_fpi_data</structfield> <literal>OUT</literal>
+      arguments are <literal>NULL</literal> for all rows returned).
+      Obviously, this optimization is only feasible with queries where
+      block data isn't truly required.
+     </para>
+     <para>
+      The function raises an error if
+      <replaceable>start_lsn</replaceable> is not available.
+     </para>
+    </listitem>
+   </varlistentry>
+
+    <varlistentry id="pgwalinspect-funcs-pg-get-wal-stats">
+    <term>
+     <function>
+      pg_get_wal_stats(start_lsn pg_lsn, end_lsn pg_lsn, per_record boolean DEFAULT false)
+      returns setof record
+     </function>
+    </term>
+
+    <listitem>
+     <para>
+      Gets statistics of all the valid WAL records between
+      <replaceable>start_lsn</replaceable> and
+      <replaceable>end_lsn</replaceable>. By default, it returns one row per
+      <replaceable>resource_manager</replaceable> type. When
+      <replaceable>per_record</replaceable> is set to <literal>true</literal>,
+      it returns one row per <replaceable>record_type</replaceable>.
+      For example:
+<screen>
+postgres=# SELECT * FROM pg_get_wal_stats('0/1E847D00', '0/1E84F500')
+           WHERE count > 0 AND
+                 "resource_manager/record_type" = 'Transaction'
+           LIMIT 1;
+-[ RECORD 1 ]----------------+-------------------
+resource_manager/record_type | Transaction
+count                        | 2
+count_percentage             | 8
+record_size                  | 875
+record_size_percentage       | 41.23468426013195
+fpi_size                     | 0
+fpi_size_percentage          | 0
+combined_size                | 875
+combined_size_percentage     | 2.8634072910530795
+</screen>
+     </para>
+     <para>
+      The function raises an error if
+      <replaceable>start_lsn</replaceable> is not available.
+     </para>
+    </listitem>
+   </varlistentry>
+
+  </variablelist>
+ </sect2>
+
+ <sect2 id="pgwalinspect-author">
+  <title>Author</title>
+
+  <para>
+   Bharath Rupireddy <email>bharath.rupireddyforpostgres@gmail.com</email>
+  </para>
+ </sect2>
+
+</sect1>