v1-0001-Beutify-pg_walinspect-docs-a-bit.patch
application/octet-stream
Filename: v1-0001-Beutify-pg_walinspect-docs-a-bit.patch
Type: application/octet-stream
Part: 0
Message:
Beautify pg_walinspect docs a bit
Patch
Format: format-patch
Series: patch v1-0001
Subject: Beutify pg_walinspect docs a bit
| File | + | − |
|---|---|---|
| doc/src/sgml/pgwalinspect.sgml | 147 | 114 |
From 36a5332394572750b8383ae9bff984a2820f663d Mon Sep 17 00:00:00 2001
From: Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com>
Date: Thu, 12 Jan 2023 11:45:49 +0000
Subject: [PATCH v1] Beutify pg_walinspect docs a bit
---
doc/src/sgml/pgwalinspect.sgml | 261 +++++++++++++++++++--------------
1 file changed, 147 insertions(+), 114 deletions(-)
diff --git a/doc/src/sgml/pgwalinspect.sgml b/doc/src/sgml/pgwalinspect.sgml
index c4c3efe932..c727a70489 100644
--- a/doc/src/sgml/pgwalinspect.sgml
+++ b/doc/src/sgml/pgwalinspect.sgml
@@ -53,20 +53,7 @@
<variablelist>
<varlistentry id="pgwalinspect-funcs-pg-get-wal-record-info">
<term>
- <function>
- pg_get_wal_record_info(in_lsn pg_lsn,
- start_lsn OUT pg_lsn,
- end_lsn OUT pg_lsn,
- prev_lsn OUT pg_lsn,
- xid OUT xid,
- resource_manager OUT text,
- record_type OUT text,
- record_length OUT int4,
- main_data_length OUT int4,
- fpi_length OUT int4,
- description OUT text,
- block_ref OUT text)
- </function>
+ <function>pg_get_wal_record_info(in_lsn pg_lsn) returns record</function>
</term>
<listitem>
@@ -74,6 +61,24 @@
Gets WAL record information of a given LSN. If the given LSN isn't
at the start of a WAL record, it gives the information of the next
available valid WAL record; or an error if no such record is found.
+ For example, usage of the function is as
+ follows:
+<screen>
+postgres=# \x
+postgres=# SELECT * FROM pg_get_wal_record_info('0/1E826E98');
+-[ RECORD 1 ]----+----------------------------------------------------
+start_lsn | 0/1E826F20
+end_lsn | 0/1E826F60
+prev_lsn | 0/1E826C80
+xid | 0
+resource_manager | Heap2
+record_type | PRUNE
+record_length | 58
+main_data_length | 8
+fpi_length | 0
+description | snapshotConflictHorizon 33748 nredirected 0 ndead 2
+block_ref | blkref #0: rel 1663/5/60221 fork main blk 2
+</screen>
</para>
</listitem>
</varlistentry>
@@ -81,19 +86,7 @@
<varlistentry id="pgwalinspect-funcs-pg-get-wal-records-info">
<term>
<function>
- pg_get_wal_records_info(start_lsn pg_lsn,
- end_lsn pg_lsn,
- start_lsn OUT pg_lsn,
- end_lsn OUT pg_lsn,
- prev_lsn OUT pg_lsn,
- xid OUT xid,
- resource_manager OUT text,
- record_type OUT text,
- record_length OUT int4,
- main_data_length OUT int4,
- fpi_length OUT int4,
- description OUT text,
- block_ref OUT text)
+ pg_get_wal_records_info(start_lsn pg_lsn, end_lsn pg_lsn)
returns setof record
</function>
</term>
@@ -107,22 +100,44 @@
function will raise an error. For example, usage of the function is as
follows:
<screen>
-postgres=# select start_lsn, end_lsn, prev_lsn, xid, resource_manager, record_type, record_length, main_data_length, fpi_length, description from pg_get_wal_records_info('0/14F9A30', '0/15011D7');
- start_lsn | end_lsn | prev_lsn | xid | resource_manager | record_type | record_length | main_data_length | fpi_length | description
------------+-----------+-----------+-----+------------------+--------------+---------------+------------------+------------+---------------------
- 0/14FA118 | 0/14FB4B0 | 0/14F9958 | 725 | Btree | INSERT_LEAF | 5013 | 2 | 4960 | off 246
- 0/14FB4B0 | 0/14FD050 | 0/14FA118 | 725 | Btree | INSERT_LEAF | 7045 | 2 | 6992 | off 130
- 0/14FD050 | 0/14FD0A8 | 0/14FB4B0 | 725 | Heap2 | MULTI_INSERT | 85 | 6 | 0 | 1 tuples flags 0x02
- 0/14FD0A8 | 0/14FD0F0 | 0/14FD050 | 725 | Btree | INSERT_LEAF | 72 | 2 | 0 | off 155
- 0/14FD0F0 | 0/14FD138 | 0/14FD0A8 | 725 | Btree | INSERT_LEAF | 72 | 2 | 0 | off 134
- 0/14FD138 | 0/14FD210 | 0/14FD0F0 | 725 | Heap | INSERT | 211 | 3 | 0 | off 11 flags 0x00
- 0/14FD210 | 0/14FD250 | 0/14FD138 | 725 | Btree | INSERT_LEAF | 64 | 2 | 0 | off 246
- 0/14FD250 | 0/14FF260 | 0/14FD210 | 725 | Btree | INSERT_LEAF | 8181 | 2 | 8128 | off 47
- 0/14FF260 | 0/14FF2B8 | 0/14FD250 | 725 | Heap2 | MULTI_INSERT | 85 | 6 | 0 | 1 tuples flags 0x02
- 0/14FF2B8 | 0/14FF300 | 0/14FF260 | 725 | Btree | INSERT_LEAF | 72 | 2 | 0 | off 155
- 0/14FF300 | 0/15008D8 | 0/14FF2B8 | 725 | Btree | INSERT_LEAF | 5565 | 2 | 5512 | off 106
- 0/15008D8 | 0/1500C48 | 0/14FF300 | 725 | Heap | INSERT | 874 | 3 | 820 | off 2 flags 0x01
-(12 rows)
+postgres=# \x
+postgres=# SELECT * FROM pg_get_wal_records_info('0/1E913618', '0/1E913740');
+-[ 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 |
+-[ RECORD 2 ]----+----------------------------------------------------------------------------------
+start_lsn | 0/1E913650
+end_lsn | 0/1E913718
+prev_lsn | 0/1E913618
+xid | 33775
+resource_manager | Heap
+record_type | HOT_UPDATE
+record_length | 197
+main_data_length | 14
+fpi_length | 132
+description | off 2 xmax 33775 flags 0x40 ; new off 3 xmax 0
+block_ref | blkref #0: rel 1663/5/60611 fork main blk 0 (FPW); hole: offset: 36, length: 8060
+-[ RECORD 3 ]----+----------------------------------------------------------------------------------
+start_lsn | 0/1E913718
+end_lsn | 0/1E913740
+prev_lsn | 0/1E913650
+xid | 33775
+resource_manager | Transaction
+record_type | COMMIT
+record_length | 34
+main_data_length | 8
+fpi_length | 0
+description | 2023-01-12 11:10:56.87124+00
+block_ref |
</screen>
</para>
</listitem>
@@ -131,18 +146,7 @@ postgres=# select start_lsn, end_lsn, prev_lsn, xid, resource_manager, record_ty
<varlistentry id="pgwalinspect-funcs-pg-get-wal-records-info-till-end-of-wal">
<term>
<function>
- pg_get_wal_records_info_till_end_of_wal(start_lsn pg_lsn,
- start_lsn OUT pg_lsn,
- end_lsn OUT pg_lsn,
- prev_lsn OUT pg_lsn,
- xid OUT xid,
- resource_manager OUT text,
- record_type OUT text,
- record_length OUT int4,
- main_data_length OUT int4,
- fpi_length OUT int4,
- description OUT text,
- block_ref OUT text)
+ pg_get_wal_records_info_till_end_of_wal(start_lsn pg_lsn)
returns setof record
</function>
</term>
@@ -159,18 +163,7 @@ postgres=# select start_lsn, end_lsn, prev_lsn, xid, resource_manager, record_ty
<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,
- "resource_manager/record_type" OUT text,
- count OUT int8,
- count_percentage OUT float8,
- record_length OUT int8,
- record_length_percentage OUT float8,
- fpi_length OUT int8,
- fpi_length_percentage OUT float8,
- combined_size OUT int8,
- combined_size_percentage OUT float8)
+ pg_get_wal_stats(start_lsn pg_lsn, end_lsn pg_lsn, per_record boolean DEFAULT false)
returns setof record
</function>
</term>
@@ -188,47 +181,97 @@ postgres=# select start_lsn, end_lsn, prev_lsn, xid, resource_manager, record_ty
function will raise an error. For example, usage of the function is as
follows:
<screen>
-postgres=# select * from pg_get_wal_stats('0/12FBA30', '0/15011D7') where count > 0;
- resource_manager/record_type | count | count_percentage | record_size | record_size_percentage | fpi_size | fpi_size_percentage | combined_size | combined_size_percentage
-------------------------------+-------+------------------+-------------+------------------------+----------+---------------------+---------------+--------------------------
- XLOG | 10 | 0.10871929 | 796 | 0.052369177 | 352 | 0.061031006 | 1148 | 0.054751817
- Transaction | 187 | 2.0330508 | 62773 | 4.1298623 | 0 | 0 | 62773 | 2.9938467
- Storage | 13 | 0.14133507 | 546 | 0.035921574 | 0 | 0 | 546 | 0.0260405
- Database | 2 | 0.021743858 | 84 | 0.005526396 | 0 | 0 | 84 | 0.0040062307
- Standby | 218 | 2.3700805 | 15908 | 1.0465941 | 0 | 0 | 15908 | 0.75870377
- Heap2 | 1897 | 20.624048 | 383916 | 25.257998 | 364472 | 63.193447 | 748388 | 35.693035
- Heap | 1318 | 14.329202 | 621390 | 40.88151 | 139660 | 24.214746 | 761050 | 36.29693
- Btree | 5553 | 60.37182 | 434565 | 28.590216 | 72272 | 12.530776 | 506837 | 24.17269
-(8 rows)
+postgres=# \x
+postgres=# SELECT * FROM pg_get_wal_stats('0/1E847D00', '0/1E84F500')
+ WHERE count > 0;
+-[ 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
+-[ RECORD 2 ]----------------+--------------------
+resource_manager/record_type | Standby
+count | 2
+count_percentage | 8
+record_size | 84
+record_size_percentage | 3.9585296889726673
+fpi_size | 0
+fpi_size_percentage | 0
+combined_size | 84
+combined_size_percentage | 0.2748870999410956
+-[ RECORD 3 ]----------------+--------------------
+resource_manager/record_type | Heap2
+count | 1
+count_percentage | 4
+record_size | 58
+record_size_percentage | 2.7332704995287465
+fpi_size | 0
+fpi_size_percentage | 0
+combined_size | 58
+combined_size_percentage | 0.18980299757837554
+-[ RECORD 4 ]----------------+--------------------
+resource_manager/record_type | Heap
+count | 20
+count_percentage | 80
+record_size | 1105
+record_size_percentage | 52.07351555136663
+fpi_size | 28436
+fpi_size_percentage | 100
+combined_size | 29541
+combined_size_percentage | 96.67190261142746
</screen>
With <replaceable>per_record</replaceable> passed as <literal>true</literal>:
<screen>
-postgres=# select * from pg_get_wal_stats('0/14AFC30', '0/15011D7', true) where count > 0;
- resource_manager/record_type | count | count_percentage | record_size | record_size_percentage | fpi_size | fpi_size_percentage | combined_size | combined_size_percentage
-------------------------------+-------+------------------+-------------+------------------------+----------+---------------------+---------------+--------------------------
- XLOG/CHECKPOINT_SHUTDOWN | 1 | 0.32894737 | 114 | 0.22891566 | 0 | 0 | 114 | 0.03534489
- XLOG/CHECKPOINT_ONLINE | 4 | 1.3157895 | 456 | 0.91566265 | 0 | 0 | 456 | 0.14137957
- XLOG/NEXTOID | 1 | 0.32894737 | 30 | 0.060240965 | 0 | 0 | 30 | 0.009301287
- Transaction/COMMIT | 9 | 2.9605262 | 1173 | 2.3554218 | 0 | 0 | 1173 | 0.36368033
- Storage/CREATE | 1 | 0.32894737 | 42 | 0.084337346 | 0 | 0 | 42 | 0.0130218025
- Database/CREATE_FILE_COPY | 2 | 0.65789473 | 84 | 0.16867469 | 0 | 0 | 84 | 0.026043605
- Standby/RUNNING_XACTS | 6 | 1.9736842 | 316 | 0.6345382 | 0 | 0 | 316 | 0.09797356
- Standby/INVALIDATIONS | 45 | 14.802631 | 4018 | 8.068274 | 0 | 0 | 4018 | 1.2457525
- Heap2/PRUNE | 4 | 1.3157895 | 270 | 0.5421687 | 0 | 0 | 270 | 0.08371159
- Heap2/FREEZE_PAGE | 27 | 8.881579 | 20904 | 41.975903 | 0 | 0 | 20904 | 6.481137
- Heap2/VISIBLE | 29 | 9.539474 | 1756 | 3.5261045 | 73728 | 27.032736 | 75484 | 23.403278
- Heap2/MULTI_INSERT | 13 | 4.2763157 | 1049 | 2.1064258 | 12216 | 4.479057 | 13265 | 4.112719
- Heap/INSERT | 19 | 6.25 | 2324 | 4.6666665 | 43884 | 16.090284 | 46208 | 14.326463
- Heap/UPDATE | 7 | 2.3026316 | 511 | 1.0261045 | 54340 | 19.924028 | 54851 | 17.006165
- Heap/HOT_UPDATE | 11 | 3.618421 | 1134 | 2.2771084 | 468 | 0.17159452 | 1602 | 0.49668875
- Heap/LOCK | 8 | 2.631579 | 432 | 0.8674699 | 0 | 0 | 432 | 0.13393854
- Heap/INPLACE | 45 | 14.802631 | 9123 | 18.319277 | 16076 | 5.894345 | 25199 | 7.8127713
- Heap/UPDATE+INIT | 1 | 0.32894737 | 817 | 1.6405623 | 0 | 0 | 817 | 0.25330505
- Btree/INSERT_LEAF | 70 | 23.026316 | 5183 | 10.407631 | 72024 | 26.407955 | 77207 | 23.937483
- Btree/DEDUP | 1 | 0.32894737 | 64 | 0.12851405 | 0 | 0 | 64 | 0.019842746
-(20 rows)
+postgres=# \x
+postgres=# SELECT * FROM pg_get_wal_stats('0/1E847D00', '0/1E84F500', true)
+ WHERE count > 0;
+-[ RECORD 1 ]----------------+--------------------
+resource_manager/record_type | Transaction/COMMIT
+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
+-[ RECORD 2 ]----------------+--------------------
+resource_manager/record_type | Standby/LOCK
+count | 2
+count_percentage | 8
+record_size | 84
+record_size_percentage | 3.9585296889726673
+fpi_size | 0
+fpi_size_percentage | 0
+combined_size | 84
+combined_size_percentage | 0.2748870999410956
+-[ RECORD 3 ]----------------+--------------------
+resource_manager/record_type | Heap2/PRUNE
+count | 1
+count_percentage | 4
+record_size | 58
+record_size_percentage | 2.7332704995287465
+fpi_size | 0
+fpi_size_percentage | 0
+combined_size | 58
+combined_size_percentage | 0.18980299757837554
+-[ RECORD 4 ]----------------+--------------------
+resource_manager/record_type | Heap/DELETE
+count | 20
+count_percentage | 80
+record_size | 1105
+record_size_percentage | 52.07351555136663
+fpi_size | 28436
+fpi_size_percentage | 100
+combined_size | 29541
+combined_size_percentage | 96.67190261142746
</screen>
</para>
</listitem>
@@ -237,17 +280,7 @@ postgres=# select * from pg_get_wal_stats('0/14AFC30', '0/15011D7', true) where
<varlistentry id="pgwalinspect-funcs-pg-get-wal-stats-till-end-of-wal">
<term>
<function>
- pg_get_wal_stats_till_end_of_wal(start_lsn pg_lsn,
- per_record boolean DEFAULT false,
- "resource_manager/record_type" OUT text,
- count OUT int8,
- count_percentage OUT float8,
- record_length OUT int8,
- record_length_percentage OUT float8,
- fpi_length OUT int8,
- fpi_length_percentage OUT float8,
- combined_size OUT int8,
- combined_size_percentage OUT float8)
+ pg_get_wal_stats_till_end_of_wal(start_lsn pg_lsn, end_lsn pg_lsn, per_record boolean DEFAULT false)
returns setof record
</function>
</term>
--
2.34.1