Hi,
When using pg_walinspect, and calling functions like
pg_get_wal_records_info(), I often wish that the various information in
the block_ref column was separated out into columns so that I could
easily access them and pass them to various other functions to add
information -- like getting the relname from pg_class like this:
SELECT n.nspname, c.relname, wal_info.*
FROM pg_get_wal_records_extended_info(:start_lsn, :end_lsn) wal_info
JOIN pg_class c
ON wal_info.relfilenode = pg_relation_filenode(c.oid) AND
wal_info.reldatabase IN (0, (SELECT oid FROM pg_database
WHERE datname = current_database()))
JOIN pg_namespace n ON n.oid = c.relnamespace;
This has been mentioned in [1] amongst other places.
So, attached is a patch with pg_get_wal_records_extended_info(). I
suspect the name is not very good. Also, it is nearly a direct copy of
pg_get_wal_fpi_infos() except for the helper called to fill in the
tuplestore, so it might be worth doing something about that.
However, I am mainly looking for feedback about whether or not others
would find this useful, and, if so, what columns they would like to see
in the returned tuplestore.
Note that I didn't include the cumulative fpi_len for all the pages
since pg_get_wal_fpi_info() now exists. I noticed that
pg_get_wal_fpi_info() doesn't list compression information (which is in
the block_ref column of pg_get_wal_records_info()). I don't know if this
is worth including in my proposed function
pg_get_wal_records_extended_info().
- Melanie
[1]
https://www.postgresql.org/message-id/CAH2-Wz%3DacGKoP8cZ%2B6Af2inoai0N5cZKCY13DaqXCwQNupK8qg%40mail.gmail.com
From 4066b480c35b5de75589812ae2d3c2fc9626bf0a Mon Sep 17 00:00:00 2001
From: Melanie Plageman <melanieplage...@gmail.com>
Date: Wed, 1 Mar 2023 12:16:07 -0500
Subject: [PATCH v1] Add extended block info function to pg_walinspect
Add a function which returns denormalized block data for every block in
every WAL record in the specified range.
---
.../pg_walinspect/pg_walinspect--1.0--1.1.sql | 27 +++++
contrib/pg_walinspect/pg_walinspect.c | 111 ++++++++++++++++++
2 files changed, 138 insertions(+)
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 1e9e1e6115..debbe43791 100644
--- a/contrib/pg_walinspect/pg_walinspect--1.0--1.1.sql
+++ b/contrib/pg_walinspect/pg_walinspect--1.0--1.1.sql
@@ -22,3 +22,30 @@ LANGUAGE C STRICT PARALLEL SAFE;
REVOKE EXECUTE ON FUNCTION pg_get_wal_fpi_info(pg_lsn, pg_lsn) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION pg_get_wal_fpi_info(pg_lsn, pg_lsn) TO pg_read_server_files;
+--
+-- pg_get_wal_records_extended_info()
+--
+CREATE FUNCTION pg_get_wal_records_extended_info(IN start_lsn pg_lsn,
+ IN end_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 block_id int8,
+ OUT relblocknumber int8,
+ OUT reltablespace oid,
+ OUT reldatabase oid,
+ OUT relfilenode oid,
+ OUT forkname text
+)
+RETURNS SETOF record
+AS 'MODULE_PATHNAME', 'pg_get_wal_records_extended_info'
+LANGUAGE C STRICT PARALLEL SAFE;
+
+REVOKE EXECUTE ON FUNCTION pg_get_wal_records_extended_info(pg_lsn, pg_lsn) FROM PUBLIC;
+GRANT EXECUTE ON FUNCTION pg_get_wal_records_extended_info(pg_lsn, pg_lsn) TO pg_read_server_files;
diff --git a/contrib/pg_walinspect/pg_walinspect.c b/contrib/pg_walinspect/pg_walinspect.c
index b7b0a805ee..aaf7928591 100644
--- a/contrib/pg_walinspect/pg_walinspect.c
+++ b/contrib/pg_walinspect/pg_walinspect.c
@@ -33,6 +33,7 @@ PG_MODULE_MAGIC;
PG_FUNCTION_INFO_V1(pg_get_wal_fpi_info);
PG_FUNCTION_INFO_V1(pg_get_wal_record_info);
PG_FUNCTION_INFO_V1(pg_get_wal_records_info);
+PG_FUNCTION_INFO_V1(pg_get_wal_records_extended_info);
PG_FUNCTION_INFO_V1(pg_get_wal_records_info_till_end_of_wal);
PG_FUNCTION_INFO_V1(pg_get_wal_stats);
PG_FUNCTION_INFO_V1(pg_get_wal_stats_till_end_of_wal);
@@ -220,6 +221,7 @@ GetWALRecordInfo(XLogReaderState *record, Datum *values,
}
+
/*
* Store a set of full page images from a single record.
*/
@@ -342,6 +344,115 @@ pg_get_wal_fpi_info(PG_FUNCTION_ARGS)
PG_RETURN_VOID();
}
+/*
+ * Store a set of block ref infos from a single record.
+ */
+static void
+GetWALBlockRefInfo(FunctionCallInfo fcinfo, XLogReaderState *record)
+{
+#define PG_GET_WAL_BLOCK_REF_INFO_COLS 15
+ StringInfoData rec_desc;
+ ReturnSetInfo *rsinfo = (ReturnSetInfo *) fcinfo->resultinfo;
+ uint32 main_data_len = XLogRecGetDataLen(record);
+ RmgrData desc = GetRmgr(XLogRecGetRmid(record));
+ const char *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 (int block_id = 0; block_id <= XLogRecMaxBlockId(record); block_id++)
+ {
+ DecodedBkpBlock *block;
+ Datum values[PG_GET_WAL_BLOCK_REF_INFO_COLS] = {0};
+ bool nulls[PG_GET_WAL_BLOCK_REF_INFO_COLS] = {0};
+ int i = 0;
+
+ if (!XLogRecHasBlockRef(record, block_id))
+ continue;
+
+ block = XLogRecGetBlock(record, block_id);
+
+ values[i++] = LSNGetDatum(record->ReadRecPtr);
+ values[i++] = LSNGetDatum(record->EndRecPtr);
+ values[i++] = LSNGetDatum(XLogRecGetPrev(record));
+ values[i++] = TransactionIdGetDatum(XLogRecGetXid(record));
+ values[i++] = CStringGetTextDatum(desc.rm_name);
+ values[i++] = CStringGetTextDatum(id);
+ values[i++] = UInt32GetDatum(XLogRecGetTotalLen(record));
+ values[i++] = UInt32GetDatum(main_data_len);
+ values[i++] = CStringGetTextDatum(rec_desc.data);
+ values[i++] = Int64GetDatum(block_id);
+ values[i++] = Int64GetDatum((int64) block->blkno);
+ values[i++] = ObjectIdGetDatum(block->rlocator.spcOid);
+ values[i++] = ObjectIdGetDatum(block->rlocator.dbOid);
+ values[i++] = ObjectIdGetDatum(block->rlocator.relNumber);
+
+ if (block->forknum >= 0 && block->forknum <= MAX_FORKNUM)
+ values[i++] = CStringGetTextDatum(forkNames[block->forknum]);
+ else
+ ereport(ERROR,
+ (errcode(ERRCODE_INTERNAL_ERROR),
+ errmsg_internal("invalid fork number: %u", block->forknum)));
+
+ Assert(i == PG_GET_WAL_BLOCK_REF_INFO_COLS);
+
+ tuplestore_putvalues(rsinfo->setResult, rsinfo->setDesc,
+ values, nulls);
+ }
+
+#undef PG_GET_WAL_BLOCK_REF_INFO_COLS
+}
+
+Datum
+pg_get_wal_records_extended_info(PG_FUNCTION_ARGS)
+{
+ XLogRecPtr start_lsn;
+ XLogRecPtr end_lsn;
+ XLogReaderState *xlogreader;
+ MemoryContext old_cxt;
+ MemoryContext tmp_cxt;
+
+ start_lsn = PG_GETARG_LSN(0);
+ end_lsn = PG_GETARG_LSN(1);
+
+ end_lsn = ValidateInputLSNs(false, start_lsn, end_lsn);
+
+ InitMaterializedSRF(fcinfo, 0);
+
+ xlogreader = InitXLogReaderState(start_lsn);
+
+ tmp_cxt = AllocSetContextCreate(CurrentMemoryContext,
+ "pg_get_wal_records_extended_info temporary cxt",
+ ALLOCSET_DEFAULT_SIZES);
+
+ while (ReadNextXLogRecord(xlogreader) &&
+ xlogreader->EndRecPtr <= end_lsn)
+ {
+ /* Use the tmp context so we can clean up after each tuple is done */
+ old_cxt = MemoryContextSwitchTo(tmp_cxt);
+
+
+ GetWALBlockRefInfo(fcinfo, xlogreader);
+
+ /* clean up and switch back */
+ MemoryContextSwitchTo(old_cxt);
+ MemoryContextReset(tmp_cxt);
+
+ CHECK_FOR_INTERRUPTS();
+ }
+
+ MemoryContextDelete(tmp_cxt);
+ pfree(xlogreader->private_data);
+ XLogReaderFree(xlogreader);
+
+ PG_RETURN_VOID();
+}
+
+
/*
* Get WAL record info.
*
--
2.37.2
