On Tue, Mar 28, 2023 at 5:29 AM Peter Geoghegan <p...@bowt.ie> wrote:
>
> On Mon, Mar 27, 2023 at 12:42 AM Bharath Rupireddy
> <bharath.rupireddyforpostg...@gmail.com> wrote:
> > Thanks. Here's the v6 patch (last patch that I have with me for
> > pg_walinspect) for adding per-record info to pg_get_wal_block_info.
> > Note that I addressed all review comments received so far. Any
> > thoughts?
>
> Looking at this now, with the intention of committing it for 16.
>
> In addition to what I said a little while ago about the forknum
> parameter and parameter ordering, I have a concern about the data
> type: perhaps the forknum paramater should be declared as
> "relforknumber smallint", instead of using text? That would match the
> approach taken by pg_buffercache, and would be more efficient.
>
> I don't think that using a text column with the fork name adds too
> much, since this is after all supposed to be a tool used by experts.
> Plus it's usually pretty clear what it is from context. Not that many
> WAL records touch the visibility map, and those that do make it
> relatively obvious which block is from the VM based on other details.
> Details such as blockid and relblocknumber (the VM is approximately
> 32k times smaller than the heap). Once I see that the record is (say)
> a VISIBLE record, I'm already looking at the order of each block
> reference, and maybe at relblocknumber -- I'm not likely to visually
> scan the forknum column at all.
Hm, agreed. Changed in the attached v7-0002 patch. We can as well
write a case statement in the create function SQL to output forkname
instead forknumber, but I'd stop doing that to keep in sync with
pg_buffercache.
On Tue, Mar 28, 2023 at 6:37 AM Peter Geoghegan <p...@bowt.ie> wrote:
>
> On Mon, Mar 27, 2023 at 4:59 PM Peter Geoghegan <p...@bowt.ie> wrote:
> > Looking at this now, with the intention of committing it for 16.
>
> I see a bug on HEAD, following yesterday's commit 0276ae42dd.
>
> GetWALRecordInfo() will now output the value of the fpi_len variable
> before it has actually been set by our call to XXXX. So it'll always
> be 0.
>
> Can you post a bugfix patch for this, Bharath?
Oh, thanks for finding it out. Fixed in the attached v7-0001 patch. I
also removed the "invalid fork number" error as users can figure that
out if at all the fork number is wrong.
On the ordering of the columns, I kept start_lsn, end_lsn and prev_lsn
first and then the rel** columns (this rel** columns order follows
pg_buffercache) and then block data related columns. Michael and
Kyotaro are of the opinion that it's better to keep LSNs first to be
consistent and also given that this function is WAL related, it makes
sense to have LSNs first.
--
Bharath Rupireddy
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com
From f2fba90775ef2fee6ea6f04dff3a08c2ed7724f1 Mon Sep 17 00:00:00 2001
From: Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com>
Date: Tue, 28 Mar 2023 01:46:08 +0000
Subject: [PATCH v7] Fix fpi_len issue introduced by 0276ae42dd in
pg_walinspect
0276ae42dd changed the code to emit block references only if
exists. That change moved the block references calucation code
around which left fpi_len to be emitted as always 0. This commit
fixes that issue.
---
contrib/pg_walinspect/pg_walinspect.c | 15 +++++++--------
1 file changed, 7 insertions(+), 8 deletions(-)
diff --git a/contrib/pg_walinspect/pg_walinspect.c b/contrib/pg_walinspect/pg_walinspect.c
index 2933734122..062e90dbce 100644
--- a/contrib/pg_walinspect/pg_walinspect.c
+++ b/contrib/pg_walinspect/pg_walinspect.c
@@ -186,6 +186,7 @@ GetWALRecordInfo(XLogReaderState *record, Datum *values,
RmgrData desc;
uint32 fpi_len = 0;
StringInfoData rec_desc;
+ StringInfoData rec_blk_ref;
int i = 0;
desc = GetRmgr(XLogRecGetRmid(record));
@@ -197,6 +198,12 @@ GetWALRecordInfo(XLogReaderState *record, Datum *values,
initStringInfo(&rec_desc);
desc.rm_desc(&rec_desc, record);
+ if (XLogRecHasAnyBlockRefs(record))
+ {
+ initStringInfo(&rec_blk_ref);
+ XLogRecGetBlockRefInfo(record, false, true, &rec_blk_ref, &fpi_len);
+ }
+
values[i++] = LSNGetDatum(record->ReadRecPtr);
values[i++] = LSNGetDatum(record->EndRecPtr);
values[i++] = LSNGetDatum(XLogRecGetPrev(record));
@@ -205,7 +212,6 @@ GetWALRecordInfo(XLogReaderState *record, Datum *values,
values[i++] = CStringGetTextDatum(id);
values[i++] = UInt32GetDatum(XLogRecGetTotalLen(record));
values[i++] = UInt32GetDatum(XLogRecGetDataLen(record));
-
values[i++] = UInt32GetDatum(fpi_len);
if (rec_desc.len > 0)
@@ -213,15 +219,8 @@ GetWALRecordInfo(XLogReaderState *record, Datum *values,
else
nulls[i++] = true;
- /* Block references. */
if (XLogRecHasAnyBlockRefs(record))
- {
- StringInfoData rec_blk_ref;
-
- initStringInfo(&rec_blk_ref);
- XLogRecGetBlockRefInfo(record, false, true, &rec_blk_ref, &fpi_len);
values[i++] = CStringGetTextDatum(rec_blk_ref.data);
- }
else
nulls[i++] = true;
--
2.34.1
From 55126ffd91bae629d1116949467a53ba8ba58f8b Mon Sep 17 00:00:00 2001
From: Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com>
Date: Tue, 28 Mar 2023 02:41:34 +0000
Subject: [PATCH v7] Emit WAL record info via pg_get_wal_block_info
---
.../pg_walinspect/pg_walinspect--1.0--1.1.sql | 16 ++-
contrib/pg_walinspect/pg_walinspect.c | 66 +++++++++----
doc/src/sgml/pgwalinspect.sgml | 98 ++++++++++++-------
3 files changed, 123 insertions(+), 57 deletions(-)
diff --git a/contrib/pg_walinspect/pg_walinspect--1.0--1.1.sql b/contrib/pg_walinspect/pg_walinspect--1.0--1.1.sql
index 586c3b4467..5d7dabee9a 100644
--- a/contrib/pg_walinspect/pg_walinspect--1.0--1.1.sql
+++ b/contrib/pg_walinspect/pg_walinspect--1.0--1.1.sql
@@ -12,13 +12,21 @@ DROP FUNCTION pg_get_wal_stats_till_end_of_wal(pg_lsn, boolean);
--
CREATE FUNCTION pg_get_wal_block_info(IN start_lsn pg_lsn,
IN end_lsn pg_lsn,
- OUT lsn pg_lsn,
- OUT blockid int2,
+ OUT start_lsn pg_lsn,
+ OUT end_lsn pg_lsn,
+ OUT prev_lsn pg_lsn,
+ OUT relfilenode oid,
OUT reltablespace oid,
OUT reldatabase oid,
- OUT relfilenode oid,
+ OUT relforknumber int2,
OUT relblocknumber int8,
- OUT forkname text,
+ OUT xid xid,
+ OUT resource_manager text,
+ OUT record_type text,
+ OUT record_length int4,
+ OUT main_data_length int4,
+ OUT description text,
+ OUT blockid int2,
OUT blockdata bytea,
OUT fpi bytea,
OUT fpilen int4,
diff --git a/contrib/pg_walinspect/pg_walinspect.c b/contrib/pg_walinspect/pg_walinspect.c
index 062e90dbce..9ef7eaace9 100644
--- a/contrib/pg_walinspect/pg_walinspect.c
+++ b/contrib/pg_walinspect/pg_walinspect.c
@@ -177,6 +177,10 @@ ReadNextXLogRecord(XLogReaderState *xlogreader)
/*
* Get a single WAL record info.
+ *
+ * Note that the per-record information fetching code is same in both
+ * GetWALBlockInfo() and GetWALRecordInfo(), it's only the output columns order
+ * that differs. Try to keep this code in sync.
*/
static void
GetWALRecordInfo(XLogReaderState *record, Datum *values,
@@ -229,15 +233,33 @@ GetWALRecordInfo(XLogReaderState *record, Datum *values,
/*
- * Store a set of block information from a single record (FPI and block
- * information).
+ * Get a single WAL record info along with its block information (block data
+ * and FPI).
+ *
+ * Note that the per-record information fetching code is same in both
+ * GetWALBlockInfo() and GetWALRecordInfo(), it's only the output columns order
+ * that differs. Try to keep this code in sync.
*/
static void
GetWALBlockInfo(FunctionCallInfo fcinfo, XLogReaderState *record)
{
-#define PG_GET_WAL_BLOCK_INFO_COLS 11
+#define PG_GET_WAL_BLOCK_INFO_COLS 19
int block_id;
ReturnSetInfo *rsinfo = (ReturnSetInfo *) fcinfo->resultinfo;
+ RmgrData desc;
+ const char *id;
+ StringInfoData rec_desc;
+
+ Assert(XLogRecHasAnyBlockRefs(record));
+
+ desc = GetRmgr(XLogRecGetRmid(record));
+ id = desc.rm_identify(XLogRecGetInfo(record));
+
+ if (id == NULL)
+ id = psprintf("UNKNOWN (%x)", XLogRecGetInfo(record) & ~XLR_INFO_MASK);
+
+ initStringInfo(&rec_desc);
+ desc.rm_desc(&rec_desc, record);
for (block_id = 0; block_id <= XLogRecMaxBlockId(record); block_id++)
{
@@ -258,18 +280,25 @@ GetWALBlockInfo(FunctionCallInfo fcinfo, XLogReaderState *record)
&rnode, &fork, &blkno, NULL);
values[i++] = LSNGetDatum(record->ReadRecPtr);
- values[i++] = Int16GetDatum(block_id);
+ values[i++] = LSNGetDatum(record->EndRecPtr);
+ values[i++] = LSNGetDatum(XLogRecGetPrev(record));
+ values[i++] = ObjectIdGetDatum(blk->rlocator.relNumber);
values[i++] = ObjectIdGetDatum(blk->rlocator.spcOid);
values[i++] = ObjectIdGetDatum(blk->rlocator.dbOid);
- values[i++] = ObjectIdGetDatum(blk->rlocator.relNumber);
+ values[i++] = Int16GetDatum(fork);
values[i++] = Int64GetDatum((int64) blkno);
-
- if (fork >= 0 && fork <= MAX_FORKNUM)
- values[i++] = CStringGetTextDatum(forkNames[fork]);
+ values[i++] = TransactionIdGetDatum(XLogRecGetXid(record));
+ values[i++] = CStringGetTextDatum(desc.rm_name);
+ values[i++] = CStringGetTextDatum(id);
+ values[i++] = UInt32GetDatum(XLogRecGetTotalLen(record));
+ values[i++] = UInt32GetDatum(XLogRecGetDataLen(record));
+
+ if (rec_desc.len > 0)
+ values[i++] = CStringGetTextDatum(rec_desc.data);
else
- ereport(ERROR,
- (errcode(ERRCODE_INTERNAL_ERROR),
- errmsg_internal("invalid fork number: %u", fork)));
+ nulls[i++] = true;
+
+ values[i++] = Int16GetDatum(block_id);
/* Block data */
if (blk->has_data)
@@ -356,11 +385,12 @@ GetWALBlockInfo(FunctionCallInfo fcinfo, XLogReaderState *record)
}
/*
- * Get information about all the blocks saved in WAL records between start
- * and end LSNs. This produces information about the full page images with
- * their relation information, and the data saved in each block associated
- * to a record. Decompression is applied to the full page images, if
- * necessary.
+ * Get info of all WAL records having block references between start LSN and
+ * end LSN. It outputs one row for each WAL record's block reference (note that
+ * a single WAL record can have one or more blocks associated with it). It
+ * outputs WAL record information as well as block information (block data
+ * and/or FPI). Decompression is applied to the full page images, if necessary.
+ * It omits WAL records that contain no block references.
*/
Datum
pg_get_wal_block_info(PG_FUNCTION_ARGS)
@@ -484,7 +514,7 @@ ValidateInputLSNs(XLogRecPtr start_lsn, XLogRecPtr *end_lsn)
}
/*
- * Get info and data of all WAL records between start LSN and end LSN.
+ * Get info of all WAL records between start LSN and end LSN.
*/
static void
GetWALRecordsInfo(FunctionCallInfo fcinfo, XLogRecPtr start_lsn,
@@ -536,7 +566,7 @@ GetWALRecordsInfo(FunctionCallInfo fcinfo, XLogRecPtr start_lsn,
}
/*
- * Get info and data of all WAL records between start LSN and end LSN.
+ * Get info of all WAL records between start LSN and end LSN.
*/
Datum
pg_get_wal_records_info(PG_FUNCTION_ARGS)
diff --git a/doc/src/sgml/pgwalinspect.sgml b/doc/src/sgml/pgwalinspect.sgml
index 9a0241a8d6..f39f9df2ba 100644
--- a/doc/src/sgml/pgwalinspect.sgml
+++ b/doc/src/sgml/pgwalinspect.sgml
@@ -94,9 +94,10 @@ block_ref | blkref #0: rel 1663/5/60221 fork main blk 2
<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. If a future
- <replaceable>end_lsn</replaceable> (i.e. ahead of the current LSN of
- the server) is specified, it returns information until the end of WAL.
+ Returns one row per WAL record. If <replaceable>end_lsn</replaceable>
+ specified is in future (i.e. ahead of the current LSN of the server) or
+ <literal>FFFFFFFF/FFFFFFFF</literal> (i.e. highest possible value for
+ <type>pg_lsn</type> type), it returns information until the end of WAL.
The function raises an error if <replaceable>start_lsn</replaceable>
is not available. For example, usage of the function is as follows:
<screen>
@@ -133,11 +134,13 @@ block_ref |
<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>. If a
- future <replaceable>end_lsn</replaceable> (i.e. ahead of the current
- LSN of the server) is specified, it returns statistics until the end
- of WAL. An error is raised if <replaceable>start_lsn</replaceable> is
- not available. For example, usage of the function is as follows:
+ it returns one row per <replaceable>record_type</replaceable>.
+ If <replaceable>end_lsn</replaceable> specified is in future (i.e. ahead
+ of the current LSN of the server) or <literal>FFFFFFFF/FFFFFFFF</literal>
+ (i.e. highest possible value for <type>pg_lsn</type> type), it returns
+ statistics until the end of WAL. An error is raised if
+ <replaceable>start_lsn</replaceable> is not available. For example, usage
+ of the function is as follows:
<screen>
postgres=# SELECT * FROM pg_get_wal_stats('0/1E847D00', '0/1E84F500')
WHERE count > 0 LIMIT 1 AND
@@ -164,38 +167,63 @@ combined_size_percentage | 2.8634072910530795
<listitem>
<para>
- Gets a copy of the block information stored in WAL records. This includes
- copies of the block data (<literal>NULL</literal> if none) and full page
- images as <type>bytea</type> values (after
- applying decompression when necessary, or <literal>NULL</literal> if none)
- and their information associated with all the valid WAL records between
- <replaceable>start_lsn</replaceable> and
- <replaceable>end_lsn</replaceable>. Returns one row per block registered
- in a WAL record. If a future <replaceable>end_lsn</replaceable> (i.e.
- ahead of the current LSN of the server) is specified, it returns
- statistics until the end of WAL. An error is raised if
- <replaceable>start_lsn</replaceable> is not available. For example,
- usage of the function is as follows:
+ Gets both WAL record and block information of all the valid WAL records
+ between <replaceable>start_lsn</replaceable> and
+ <replaceable>end_lsn</replaceable>. It omits WAL records that contain no
+ block references. The block information includes copies of the block data
+ (<literal>NULL</literal> if none) and full page images as
+ <type>bytea</type> values (after applying decompression when necessary,
+ or <literal>NULL</literal> if none). Returns one row per block registered
+ in a WAL record. If <replaceable>end_lsn</replaceable> specified is in
+ future (i.e. ahead of the current LSN of the server) or
+ <literal>FFFFFFFF/FFFFFFFF</literal> (i.e. highest possible value for
+ <type>pg_lsn</type> type), it returns information until the end of WAL.
+ An error is raised if <replaceable>start_lsn</replaceable> is not
+ available. For example, usage of the function is as follows:
<screen>
-postgres=# SELECT lsn, blockid, reltablespace, reldatabase, relfilenode,
- relblocknumber, forkname,
+postgres=# SELECT start_lsn, end_lsn, prev_lsn, relfilenode,
+ reltablespace, reldatabase, relforknumber,
+ relblocknumber, xid, resource_manager,
+ record_type, record_length, main_data_length,
+ description, blockid,
substring(blockdata for 24) as block_trimmed,
substring(fpi for 24) as fpi_trimmed, fpilen, fpiinfo
- FROM pg_get_wal_block_info('0/1871080', '0/1871440');
--[ RECORD 1 ]--+---------------------------------------------------
-lsn | 0/18712F8
-blockid | 0
-reltablespace | 1663
-reldatabase | 16384
-relfilenode | 16392
-relblocknumber | 0
-forkname | main
-block_trimmed | \x02800128180164000000
-fpi_trimmed | \x0000000050108701000000002c00601f00200420e0020000
-fpilen | 204
-fpiinfo | {HAS_HOLE,APPLY}
+ FROM pg_get_wal_block_info('0/14BF938', '0/14BFBA0');
+-[ RECORD 1 ]----+---------------------------------------------------
+start_lsn | 0/14BFA20
+end_lsn | 0/14BFB38
+prev_lsn | 0/14BF9A8
+relfilenode | 16391
+reltablespace | 1663
+reldatabase | 5
+relforknumber | 0
+relblocknumber | 9
+xid | 735
+resource_manager | Heap
+record_type | HOT_UPDATE
+record_length | 279
+main_data_length | 14
+description | off 1 xmax 735 flags 0x10 ; new off 5 xmax 0
+blockid | 7
+block_trimmed | \x02800128180102000000
+fpi_trimmed | \x00000000d0f84b01000000002c00601f00200420df020000
+fpilen | 204
+fpiinfo | {HAS_HOLE,APPLY}
</screen>
</para>
+ <note>
+ <para>
+ Note that although <function>pg_get_wal_records_info</function>
+ and <function>pg_get_wal_block_info</function> functions return
+ information of WAL records in common, the
+ <function>pg_get_wal_records_info</function> outputs block information
+ (without block data and FPI data) as a text column
+ (<literal>NULL</literal> if none), and
+ <function>pg_get_wal_block_info</function> outputs one row for each block
+ (including block data and FPI data) associated with each WAL record, omitting
+ WAL records that contain no block references.
+ </para>
+ </note>
</listitem>
</varlistentry>
--
2.34.1
