Subject: [PATCH] add relation and block-level filtering to pg_waldump

From: David Christensen <[email-masked]>
Date: Thu, 24 Feb 2022 13:02:33 -0600

Greetings,

This patch adds the ability to specify a RelFileNode and optional BlockNum to limit output of
pg_waldump records to only those which match the given criteria.  This should be more performant
than `pg_waldump | grep` as well as more reliable given specific variations in output style
depending on how the blocks are specified.

This currently affects only the main fork, but we could presumably add the option to filter by fork
as well, if that is considered useful.

Best,

David

>From 9194b2cb07172e636030b9b4e979b7f2caf7cbc0 Mon Sep 17 00:00:00 2001
From: David Christensen <[email-masked]>
Date: Thu, 24 Feb 2022 11:00:46 -0600
Subject: [PATCH] Add relation/block filtering to pg_waldump

This feature allows you to only output records that are targeting a specific RelFileNode and optional
BlockNumber within this relation.  Currently only applies this filter to the relation's main fork.
---
 doc/src/sgml/ref/pg_waldump.sgml | 23 ++++++++++
 src/bin/pg_waldump/pg_waldump.c  | 74 +++++++++++++++++++++++++++++++-
 2 files changed, 96 insertions(+), 1 deletion(-)

diff --git a/doc/src/sgml/ref/pg_waldump.sgml b/doc/src/sgml/ref/pg_waldump.sgml
index 5735a161ce..c953703bc8 100644
--- a/doc/src/sgml/ref/pg_waldump.sgml
+++ b/doc/src/sgml/ref/pg_waldump.sgml
@@ -100,6 +100,29 @@ PostgreSQL documentation
       </listitem>
      </varlistentry>
 
+     <varlistentry>
+      <term><option>-k <replaceable>block</replaceable></option></term>
+      <term><option>--block=<replaceable>block</replaceable></option></term>
+      <listitem>
+       <para>
+        Display only records touching the given block. (Requires also
+        providing the relation via <option>--relation</option>.)
+       </para>
+      </listitem>
+     </varlistentry>
+
+     <varlistentry>
+      <term><option>-l <replaceable>tbl</replaceable>/<replaceable>db</replaceable>/<replaceable>rel</replaceable></option></term>
+      <term><option>--relation=<replaceable>tbl</replaceable>/<replaceable>db</replaceable>/<replaceable>rel</replaceable></option></term>
+      <listitem>
+       <para>
+        Display only records touching the given relation.  The relation is
+        specified via tablespace oid, database oid, and relfilenode separated
+        by slashes.
+       </para>
+      </listitem>
+     </varlistentry>
+
      <varlistentry>
       <term><option>-n <replaceable>limit</replaceable></option></term>
       <term><option>--limit=<replaceable>limit</replaceable></option></term>
diff --git a/src/bin/pg_waldump/pg_waldump.c b/src/bin/pg_waldump/pg_waldump.c
index a6251e1a96..faae547a5c 100644
--- a/src/bin/pg_waldump/pg_waldump.c
+++ b/src/bin/pg_waldump/pg_waldump.c
@@ -55,6 +55,10 @@ typedef struct XLogDumpConfig
 	bool		filter_by_rmgr_enabled;
 	TransactionId filter_by_xid;
 	bool		filter_by_xid_enabled;
+	RelFileNode filter_by_relation;
+	bool		filter_by_relation_enabled;
+	BlockNumber	filter_by_relation_block;
+	bool		filter_by_relation_block_enabled;
 } XLogDumpConfig;
 
 typedef struct Stats
@@ -394,6 +398,34 @@ WALDumpReadPage(XLogReaderState *state, XLogRecPtr targetPagePtr, int reqLen,
 	return count;
 }
 
+/*
+ * Boolean to return whether the given WAL record matches a specific relation and optional block
+ */
+static bool
+XLogRecordMatchesRelationBlock(XLogReaderState *record, RelFileNode matchRnode, BlockNumber matchBlock)
+{
+	RelFileNode rnode;
+	ForkNumber	forknum;
+	BlockNumber blk;
+	int			block_id;
+
+	for (block_id = 0; block_id <= record->max_block_id; block_id++)
+	{
+		if (!XLogRecHasBlockRef(record, block_id))
+			continue;
+
+		XLogRecGetBlockTag(record, block_id, &rnode, &forknum, &blk);
+
+		if (forknum == MAIN_FORKNUM &&
+			matchRnode.spcNode == rnode.spcNode &&
+			matchRnode.dbNode == rnode.dbNode &&
+			matchRnode.relNode == rnode.relNode &&
+			(matchBlock == InvalidBlockNumber || matchBlock == blk))
+			return true;
+	}
+	return false;
+}
+
 /*
  * Calculate the size of a record, split into !FPI and FPI parts.
  */
@@ -767,6 +799,8 @@ usage(void)
 	printf(_("  -b, --bkp-details      output detailed information about backup blocks\n"));
 	printf(_("  -e, --end=RECPTR       stop reading at WAL location RECPTR\n"));
 	printf(_("  -f, --follow           keep retrying after reaching end of WAL\n"));
+	printf(_("  -k, --block=N          only show records matching a given relation block (requires -l)\n"));
+	printf(_("  -l, --relation=N/N/N   only show records that touch a specific relation\n"));
 	printf(_("  -n, --limit=N          number of records to display\n"));
 	printf(_("  -p, --path=PATH        directory in which to find log segment files or a\n"
 			 "                         directory with a ./pg_wal that contains such files\n"
@@ -802,12 +836,14 @@ main(int argc, char **argv)
 
 	static struct option long_options[] = {
 		{"bkp-details", no_argument, NULL, 'b'},
+		{"block", required_argument, NULL, 'k'},
 		{"end", required_argument, NULL, 'e'},
 		{"follow", no_argument, NULL, 'f'},
 		{"help", no_argument, NULL, '?'},
 		{"limit", required_argument, NULL, 'n'},
 		{"path", required_argument, NULL, 'p'},
 		{"quiet", no_argument, NULL, 'q'},
+		{"relation", required_argument, NULL, 'l'},
 		{"rmgr", required_argument, NULL, 'r'},
 		{"start", required_argument, NULL, 's'},
 		{"timeline", required_argument, NULL, 't'},
@@ -860,6 +896,8 @@ main(int argc, char **argv)
 	config.filter_by_rmgr_enabled = false;
 	config.filter_by_xid = InvalidTransactionId;
 	config.filter_by_xid_enabled = false;
+	config.filter_by_relation_enabled = false;
+	config.filter_by_relation_block_enabled = false;
 	config.stats = false;
 	config.stats_per_record = false;
 
@@ -872,7 +910,7 @@ main(int argc, char **argv)
 		goto bad_argument;
 	}
 
-	while ((option = getopt_long(argc, argv, "be:fn:p:qr:s:t:x:z",
+	while ((option = getopt_long(argc, argv, "be:fk:l:n:p:qr:s:t:x:z",
 								 long_options, &optindex)) != -1)
 	{
 		switch (option)
@@ -892,6 +930,25 @@ main(int argc, char **argv)
 			case 'f':
 				config.follow = true;
 				break;
+			case 'k':
+				if (sscanf(optarg, "%ul", &config.filter_by_relation_block) != 1)
+				{
+					pg_log_error("could not parse block number \"%s\"", optarg);
+					goto bad_argument;
+				}
+				config.filter_by_relation_block_enabled = true;
+				break;
+			case 'l':
+				if (sscanf(optarg, "%d/%d/%d",
+						   &config.filter_by_relation.spcNode,
+						   &config.filter_by_relation.dbNode,
+						   &config.filter_by_relation.relNode) != 3)
+				{
+					pg_log_error("could not parse relation from \"%s\" (expecting \"spc/dat/rel\")", optarg);
+					goto bad_argument;
+				}
+				config.filter_by_relation_enabled = true;
+				break;
 			case 'n':
 				if (sscanf(optarg, "%d", &config.stop_after_records) != 1)
 				{
@@ -978,6 +1035,12 @@ main(int argc, char **argv)
 		}
 	}
 
+	if (config.filter_by_relation_block_enabled && !config.filter_by_relation_enabled)
+	{
+		pg_log_error("cannot filter by --block without also filtering --relation");
+		goto bad_argument;
+	}
+
 	if ((optind + 2) < argc)
 	{
 		pg_log_error("too many command-line arguments (first is \"%s\")",
@@ -1150,6 +1213,15 @@ main(int argc, char **argv)
 			config.filter_by_xid != record->xl_xid)
 			continue;
 
+		/* check for extended filtering */
+		if (config.filter_by_relation_enabled &&
+			!XLogRecordMatchesRelationBlock(
+				xlogreader_state,
+				config.filter_by_relation,
+				config.filter_by_relation_block_enabled ? config.filter_by_relation_block : InvalidBlockNumber
+				))
+			continue;
+
 		/* perform any per-record work */
 		if (!config.quiet)
 		{
-- 
2.32.0 (Apple Git-132)

--

---

From: Peter Geoghegan <[email-masked]>
Date: Thu, 24 Feb 2022 11:26:09 -0800

> depending on how the blocks are specified.

Sounds useful to me.

-- 
Peter Geoghegan

---

From: David Christensen <[email-masked]>
Date: Thu, 24 Feb 2022 13:28:30 -0600

Added to commitfest as:

https://commitfest.postgresql.org/37/3565/

---

From: Japin Li <[email-masked]>
Date: Fri, 25 Feb 2022 14:32:22 +0800

> as well, if that is considered useful.

Cool.  I think we can report an error instead of reading wal files,
if the tablespace, database, or relation is invalid.  Does there any
WAL record that has invalid tablespace, database, or relation OID?

-- 
Regrads,
Japin Li.
ChengDu WenWu Information Technology Co.,Ltd.