doc/src/sgml/pgwalinspect.sgml - cloudberry - Git at Google

 <!-- 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>
	<!-- doc/src/sgml/pgwalinspect.sgml -->

	<sect1 id="pgwalinspect" xreflabel="pg_walinspect">
	<title>pg_walinspect — 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>