Beautify pg_walinspect docs a bit
Started by Bharath Rupireddyabout 3 years ago4 messages
Hi,
As discussed [1]/messages/by-id/Y7+gQy/lOuWk4tFj@paquier.xyz, here's a patch to beautify pg_walinspect docs
similar to pageinspect docs. The existing pg_walinspect docs calls out
the column names explicitly and then also shows them in the function
execution examples which is duplicate and too informative. Also \x
isn't used so some of the execution outputs are out of indentation.
Thoughts?
[1]: /messages/by-id/Y7+gQy/lOuWk4tFj@paquier.xyz
--
Bharath Rupireddy
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com
Attachments:
v1-0001-Beutify-pg_walinspect-docs-a-bit.patchapplication/octet-stream; name=v1-0001-Beutify-pg_walinspect-docs-a-bit.patchDownload
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
Re: Beautify pg_walinspect docs a bit
On Thu, Jan 12, 2023 at 05:29:39PM +0530, Bharath Rupireddy wrote:
As discussed [1], here's a patch to beautify pg_walinspect docs
similar to pageinspect docs. The existing pg_walinspect docs calls out
the column names explicitly and then also shows them in the function
execution examples which is duplicate and too informative. Also \x
isn't used so some of the execution outputs are out of indentation.Thoughts?
Thanks, this looked basically fine, so applied. I have tweaked a few
sentences while reviewing the docs, while on it. I have decided to
remove the example where we specify per_record=true for
pg_get_wal_stats(), as it does not bring much value while bloating the
whole, and the parameter is clearly documented.
--
Michael
Re: Beautify pg_walinspect docs a bit
It looks like 58597ed accidentally added an "end_lsn" to the docs for
pg_get_wal_stats_till_end_of_wal().
diff --git a/doc/src/sgml/pgwalinspect.sgml b/doc/src/sgml/pgwalinspect.sgml
index 22677e54f2..3d7cdb95cc 100644
--- a/doc/src/sgml/pgwalinspect.sgml
+++ b/doc/src/sgml/pgwalinspect.sgml
@@ -174,7 +174,7 @@ combined_size_percentage | 2.8634072910530795
<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, end_lsn pg_lsn, per_record boolean DEFAULT false)
+ pg_get_wal_stats_till_end_of_wal(start_lsn pg_lsn, per_record boolean DEFAULT false)
returns setof record
</function>
</term>--
Nathan Bossart
Amazon Web Services: https://aws.amazon.com