On Wed, Mar 15, 2023 at 7:20 AM Peter Geoghegan <p...@bowt.ie> wrote:
>
> > But, perhaps you are suggesting a parameter to pg_get_wal_records_info()
> > like "with_block_info" or something, which produces the full
> > denormalized block + record output?
>
> I was thinking of something like that, yes -- though it wouldn't
> necessarily have to be the *full* denormalized block_ref info, the FPI
> itself, etc. Just the more useful stuff.
>
> It occurs to me that my concern about the information that
> pg_get_wal_records_info() lacks could be restated as a concern about
> what pg_get_wal_block_info() lacks: pg_get_wal_block_info() fails to
> show basic information about the WAL record whose blocks it reports
> on, even though it could easily show all of the
> pg_get_wal_records_info() info once per block (barring block_ref). So
> addressing my concern by adjusting pg_get_wal_block_info() might be
> the best approach. I'd probably be happy with that -- I'd likely just
> stop using pg_get_wal_records_info() completely under this scheme.
How about something like the attached? It adds the per-record columns
to pg_get_wal_block_info() avoiding "possibly expensive" joins with
pg_get_wal_records_info().
With this, pg_get_wal_records_info() too will be useful for users
scanning WAL at record level. That is to say that we can retain both
pg_get_wal_records_info() and pg_get_wal_block_info().
--
Bharath Rupireddy
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com
From c98e250011d9944b44fe166bb8bdf8ac81ac9955 Mon Sep 17 00:00:00 2001
From: Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com>
Date: Wed, 15 Mar 2023 06:33:31 +0000
Subject: [PATCH v1] Emit WAL record info via pg_get_wal_block_info()
---
.../pg_walinspect/pg_walinspect--1.0--1.1.sql | 10 +++-
contrib/pg_walinspect/pg_walinspect.c | 48 ++++++++++++++-----
doc/src/sgml/pgwalinspect.sgml | 38 +++++++++------
3 files changed, 68 insertions(+), 28 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..46fcaff3a1 100644
--- a/contrib/pg_walinspect/pg_walinspect--1.0--1.1.sql
+++ b/contrib/pg_walinspect/pg_walinspect--1.0--1.1.sql
@@ -12,7 +12,15 @@ 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 start_lsn pg_lsn,
+ OUT end_lsn pg_lsn,
+ OUT prev_lsn pg_lsn,
+ 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 reltablespace oid,
OUT reldatabase oid,
diff --git a/contrib/pg_walinspect/pg_walinspect.c b/contrib/pg_walinspect/pg_walinspect.c
index 3b3215daf5..d766a050a4 100644
--- a/contrib/pg_walinspect/pg_walinspect.c
+++ b/contrib/pg_walinspect/pg_walinspect.c
@@ -43,7 +43,8 @@ static XLogRecPtr GetCurrentLSN(void);
static XLogReaderState *InitXLogReaderState(XLogRecPtr lsn);
static XLogRecord *ReadNextXLogRecord(XLogReaderState *xlogreader);
static void GetWALRecordInfo(XLogReaderState *record, Datum *values,
- bool *nulls, uint32 ncols);
+ bool *nulls, uint32 ncols,
+ bool fetch_blk_ref);
static void GetWALRecordsInfo(FunctionCallInfo fcinfo,
XLogRecPtr start_lsn,
XLogRecPtr end_lsn);
@@ -180,7 +181,8 @@ ReadNextXLogRecord(XLogReaderState *xlogreader)
*/
static void
GetWALRecordInfo(XLogReaderState *record, Datum *values,
- bool *nulls, uint32 ncols)
+ bool *nulls, uint32 ncols,
+ bool fetch_blk_ref)
{
const char *id;
RmgrData desc;
@@ -200,8 +202,11 @@ GetWALRecordInfo(XLogReaderState *record, Datum *values,
desc.rm_desc(&rec_desc, record);
/* Block references. */
- initStringInfo(&rec_blk_ref);
- XLogRecGetBlockRefInfo(record, false, true, &rec_blk_ref, &fpi_len);
+ if (fetch_blk_ref)
+ {
+ initStringInfo(&rec_blk_ref);
+ XLogRecGetBlockRefInfo(record, false, true, &rec_blk_ref, &fpi_len);
+ }
main_data_len = XLogRecGetDataLen(record);
@@ -213,9 +218,14 @@ GetWALRecordInfo(XLogReaderState *record, Datum *values,
values[i++] = CStringGetTextDatum(id);
values[i++] = UInt32GetDatum(XLogRecGetTotalLen(record));
values[i++] = UInt32GetDatum(main_data_len);
- values[i++] = UInt32GetDatum(fpi_len);
+
+ if (fetch_blk_ref)
+ values[i++] = UInt32GetDatum(fpi_len);
+
values[i++] = CStringGetTextDatum(rec_desc.data);
- values[i++] = CStringGetTextDatum(rec_blk_ref.data);
+
+ if (fetch_blk_ref)
+ values[i++] = CStringGetTextDatum(rec_blk_ref.data);
Assert(i == ncols);
}
@@ -228,19 +238,30 @@ GetWALRecordInfo(XLogReaderState *record, Datum *values,
static void
GetWALBlockInfo(FunctionCallInfo fcinfo, XLogReaderState *record)
{
-#define PG_GET_WAL_BLOCK_INFO_COLS 11
+#define PG_GET_WAL_BLOCK_INFO_COLS 19
+#define PG_GET_WAL_BLOCK_INFO_PER_RECORD_COLS 9
+ Datum values[PG_GET_WAL_BLOCK_INFO_COLS] = {0};
+ bool nulls[PG_GET_WAL_BLOCK_INFO_COLS] = {0};
int block_id;
ReturnSetInfo *rsinfo = (ReturnSetInfo *) fcinfo->resultinfo;
+ /* Get per-record info. */
+ GetWALRecordInfo(record, values, nulls,
+ PG_GET_WAL_BLOCK_INFO_PER_RECORD_COLS, false);
+
for (block_id = 0; block_id <= XLogRecMaxBlockId(record); block_id++)
{
DecodedBkpBlock *blk;
BlockNumber blkno;
RelFileLocator rnode;
ForkNumber fork;
- Datum values[PG_GET_WAL_BLOCK_INFO_COLS] = {0};
- bool nulls[PG_GET_WAL_BLOCK_INFO_COLS] = {0};
- int i = 0;
+ int i = PG_GET_WAL_BLOCK_INFO_PER_RECORD_COLS;
+
+ /* Reset only the per-block output columns. */
+ memset(&nulls[PG_GET_WAL_BLOCK_INFO_PER_RECORD_COLS], 0,
+ PG_GET_WAL_BLOCK_INFO_PER_RECORD_COLS * sizeof(bool));
+ memset(&values[PG_GET_WAL_BLOCK_INFO_PER_RECORD_COLS], 0,
+ PG_GET_WAL_BLOCK_INFO_PER_RECORD_COLS * sizeof(bool));
if (!XLogRecHasBlockRef(record, block_id))
continue;
@@ -250,7 +271,6 @@ GetWALBlockInfo(FunctionCallInfo fcinfo, XLogReaderState *record)
(void) XLogRecGetBlockTagExtended(record, block_id,
&rnode, &fork, &blkno, NULL);
- values[i++] = LSNGetDatum(record->ReadRecPtr);
values[i++] = Int16GetDatum(block_id);
values[i++] = ObjectIdGetDatum(blk->rlocator.spcOid);
values[i++] = ObjectIdGetDatum(blk->rlocator.dbOid);
@@ -345,6 +365,7 @@ GetWALBlockInfo(FunctionCallInfo fcinfo, XLogReaderState *record)
values, nulls);
}
+#undef PG_GET_WAL_BLOCK_INFO_PER_RECORD_COLS
#undef PG_GET_WAL_FPI_BLOCK_COLS
}
@@ -434,7 +455,8 @@ pg_get_wal_record_info(PG_FUNCTION_ARGS)
errmsg("could not read WAL at %X/%X",
LSN_FORMAT_ARGS(xlogreader->EndRecPtr))));
- GetWALRecordInfo(xlogreader, values, nulls, PG_GET_WAL_RECORD_INFO_COLS);
+ GetWALRecordInfo(xlogreader, values, nulls, PG_GET_WAL_RECORD_INFO_COLS,
+ true);
pfree(xlogreader->private_data);
XLogReaderFree(xlogreader);
@@ -505,7 +527,7 @@ GetWALRecordsInfo(FunctionCallInfo fcinfo, XLogRecPtr start_lsn,
old_cxt = MemoryContextSwitchTo(tmp_cxt);
GetWALRecordInfo(xlogreader, values, nulls,
- PG_GET_WAL_RECORDS_INFO_COLS);
+ PG_GET_WAL_RECORDS_INFO_COLS, true);
tuplestore_putvalues(rsinfo->setResult, rsinfo->setDesc,
values, nulls);
diff --git a/doc/src/sgml/pgwalinspect.sgml b/doc/src/sgml/pgwalinspect.sgml
index 9a0241a8d6..cf94f36200 100644
--- a/doc/src/sgml/pgwalinspect.sgml
+++ b/doc/src/sgml/pgwalinspect.sgml
@@ -177,23 +177,33 @@ combined_size_percentage | 2.8634072910530795
<replaceable>start_lsn</replaceable> is not available. For example,
usage of the function is as follows:
<screen>
-postgres=# SELECT lsn, blockid, reltablespace, reldatabase, relfilenode,
+postgres=# SELECT start_lsn, end_lsn, prev_lsn, xid, resource_manager,
+ record_type, record_length, main_data_length, description,
+ blockid, reltablespace, reldatabase, relfilenode,
relblocknumber, forkname,
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/15457B0', '0/1545A40');
+-[ RECORD 1 ]----+---------------------------------------------------
+start_lsn | 0/1545860
+end_lsn | 0/1545998
+prev_lsn | 0/15457E8
+xid | 750
+resource_manager | Heap
+record_type | HOT_UPDATE
+record_length | 305
+main_data_length | 14
+description | off 2 xmax 750 flags 0x00 ; new off 6 xmax 0
+blockid | 0
+reltablespace | 1663
+reldatabase | 5
+relfilenode | 24594
+relblocknumber | 0
+forkname | main
+block_trimmed |
+fpi_trimmed | \x00000000203a5401000000003000401f00200420ed020000
+fpilen | 240
+fpiinfo | {HAS_HOLE,APPLY}
</screen>
</para>
</listitem>
--
2.34.1
