Date: Fri, 29 Oct 2021 15:50:32 -0300
From: Alvaro Herrera <alvherre@alvh.no-ip.org>
To: Kamigishi Rei <iijima.yun@koumakan.jp>
Cc: Andres Freund <andres@anarazel.de>,
	Andrew Gierth <andrew@tao11.riddles.org.uk>,
	Peter Geoghegan <pg@bowt.ie>,
	PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>,
	Thomas Munro <thomas.munro@gmail.com>
Subject: Re: BUG #17245: Index corruption involving deduplicated entries
Message-ID: <202110291850.cxcsg7j7ye6r@alvherre.pgsql>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="qdef2x44tviuv7bm"
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <9c7433f6-65b3-9f57-e8a3-4414d0cf0c53@koumakan.jp>
Archived-At: 
 <https://www.postgresql.org/message-id/202110291850.cxcsg7j7ye6r%40alvherre.pgsql>
Precedence: bulk


--qdef2x44tviuv7bm
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: 8bit

On 2021-Oct-29, Kamigishi Rei wrote:

> The weird part about this is that the WAL archive does not seem to contain
> any data for 157 and 158 above (in 1663/19243/274869 blk 17). The last two
> entries are
> 
> rmgr: Btree       len (rec/tot):     53/  4885, tx:    2085600, lsn:
> 2/A0195AE0, prev 2/A01943F0, desc: INSERT_LEAF off 155, blkref #0: rel
> 1663/19243/274869 blk 17 FPW
> rmgr: Btree       len (rec/tot):     72/    72, tx:    2085602, lsn:
> 2/A019DD30, prev 2/A019DCF0, desc: INSERT_LEAF off 156, blkref #0: rel
> 1663/19243/274869 blk 17
> 
> The WAL file in data14/pg_wal does not have anything related to 157 and 158
> for this filenode/blk as well.

Hmm, I don't remember precisely how index tuple removal works, but maybe
these 157 and 158 entries existed previously and were truncated away.
It might be useful to have a look at the page header in the page image
contained in the FPW for 2/A0195AE0.

pg_waldump doesn't do it, but I have this patch sitting around ...
probably outdated, but it may be a useful starting point for somebody.

-- 
Álvaro Herrera              Valdivia, Chile  —  https://www.EnterpriseDB.com/

--qdef2x44tviuv7bm
Content-Type: text/x-diff; charset=utf-8
Content-Disposition: attachment; filename="waldump-save-fpi.patch"

diff --git a/src/bin/pg_waldump/pg_waldump.c b/src/bin/pg_waldump/pg_waldump.c
index d1c1cf0fa4..0153af5517 100644
--- a/src/bin/pg_waldump/pg_waldump.c
+++ b/src/bin/pg_waldump/pg_waldump.c
@@ -69,6 +69,8 @@ typedef struct XLogDumpStats
 
 static void fatal_error(const char *fmt,...) pg_attribute_printf(1, 2);
 
+static XLogRecPtr	lsn_to_dump;
+
 /*
  * Big red button to push when things go horribly wrong.
  */
@@ -445,6 +447,10 @@ XLogDumpDisplayRecord(XLogDumpConfig *config, XLogReaderState *record)
 	int			block_id;
 	uint8		info = XLogRecGetInfo(record);
 	XLogRecPtr	xl_prev = XLogRecGetPrev(record);
+	bool		dump_data = false;
+
+	if (record->ReadRecPtr == lsn_to_dump)
+		dump_data = true;
 
 	XLogDumpRecordLen(record, &rec_len, &fpi_len);
 
@@ -491,6 +497,27 @@ XLogDumpDisplayRecord(XLogDumpConfig *config, XLogReaderState *record)
 				else
 					printf(" FPW for WAL verification");
 			}
+
+			if (dump_data)
+			{
+				char   *path;
+				char	page[BLCKSZ];
+				FILE   *file;
+
+				/* LSN.relfilenode.blkno */
+				path = psprintf("%08x_%08x.%u_%u_%u.%u",
+								(uint32) (record->ReadRecPtr >> 32),
+								(uint32) record->ReadRecPtr,
+								rnode.spcNode, rnode.dbNode, rnode.relNode,
+								blk);
+				file = fopen(path, PG_BINARY_W);
+				if (file == NULL)
+					fatal_error("could not open output file \"%s\": %s\n",
+								path, strerror(errno));
+				RestoreBlockImage(record, block_id, page);
+				fwrite((void *) page, 1, BLCKSZ, file);
+				fclose(file);
+			}
 		}
 		putchar('\n');
 	}
@@ -745,6 +772,7 @@ main(int argc, char **argv)
 		{"limit", required_argument, NULL, 'n'},
 		{"path", required_argument, NULL, 'p'},
 		{"rmgr", required_argument, NULL, 'r'},
+		{"save-fpi", required_argument, NULL, 2},
 		{"start", required_argument, NULL, 's'},
 		{"timeline", required_argument, NULL, 't'},
 		{"xid", required_argument, NULL, 'x'},
@@ -775,6 +803,7 @@ main(int argc, char **argv)
 	config.filter_by_rmgr = -1;
 	config.filter_by_xid = InvalidTransactionId;
 	config.filter_by_xid_enabled = false;
+	lsn_to_dump = InvalidXLogRecPtr;
 	config.stats = false;
 	config.stats_per_record = false;
 
@@ -892,6 +921,17 @@ main(int argc, char **argv)
 					}
 				}
 				break;
+			case 2:
+				/* save-fpi */
+				if (sscanf(optarg, "%X/%X", &xlogid, &xrecoff) != 2)
+				{
+					fprintf(stderr, _("%s: could not parse start WAL location \"%s\"\n"),
+							progname, optarg);
+					goto bad_argument;
+				}
+				else
+					lsn_to_dump = (uint64) xlogid << 32 | xrecoff;
+				break;
 			default:
 				goto bad_argument;
 		}

--qdef2x44tviuv7bm--