Add LSN along with offset to error messages reported for WAL file read/write/validate header failures

From f8ed45d9fa59690d8b3375d658c1baedb8510195 Mon Sep 17 00:00:00 2001
From: Nathan Bossart <nathandbossart@gmail.com>
Date: Mon, 19 Sep 2022 20:24:10 -0700
Subject: [PATCH v1 1/1] introduce pg_walfile_offset_lsn()

---
 doc/src/sgml/func.sgml                       | 14 +++++++
 src/backend/access/transam/xlogfuncs.c       | 39 ++++++++++++++++++++
 src/include/catalog/pg_proc.dat              |  5 +++
 src/test/regress/expected/misc_functions.out | 19 ++++++++++
 src/test/regress/sql/misc_functions.sql      |  9 +++++
 5 files changed, 86 insertions(+)

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 67eb380632..318ac22769 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -25891,6 +25891,20 @@ LOG:  Grand total: 1651920 bytes in 201 blocks; 622360 free (88 chunks); 1029560
        </para></entry>
       </row>
 
+      <row>
+       <entry role="func_table_entry"><para role="func_signature">
+        <indexterm>
+         <primary>pg_walfile_offset_lsn</primary>
+        </indexterm>
+        <function>pg_walfile_offset_lsn</function> ( <parameter>file_name</parameter> <type>text</type>, <parameter>file_offset</parameter> <type>integer</type> )
+        <returnvalue>pg_lsn</returnvalue>
+       </para>
+       <para>
+        Converts a WAL file name and byte offset within that file to a
+        write-ahead log location.
+       </para></entry>
+      </row>
+
       <row>
        <entry role="func_table_entry"><para role="func_signature">
         <indexterm>
diff --git a/src/backend/access/transam/xlogfuncs.c b/src/backend/access/transam/xlogfuncs.c
index 27aeb6e281..75f58cb118 100644
--- a/src/backend/access/transam/xlogfuncs.c
+++ b/src/backend/access/transam/xlogfuncs.c
@@ -313,6 +313,45 @@ pg_last_wal_replay_lsn(PG_FUNCTION_ARGS)
 	PG_RETURN_LSN(recptr);
 }
 
+/*
+ * Compute an LSN given a WAL file name and decimal byte offset.
+ */
+Datum
+pg_walfile_offset_lsn(PG_FUNCTION_ARGS)
+{
+	char	   *filename = text_to_cstring(PG_GETARG_TEXT_PP(0));
+	int			offset = PG_GETARG_INT32(1);
+	TimeLineID	tli;
+	XLogSegNo	segno;
+	XLogRecPtr	result;
+	uint32		log;
+	uint32		seg;
+
+	if (!IsXLogFileName(filename))
+		ereport(ERROR,
+				(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+				 errmsg("invalid WAL file name \"%s\"", filename)));
+
+	sscanf(filename, "%08X%08X%08X", &tli, &log, &seg);
+	if (seg >= XLogSegmentsPerXLogId(wal_segment_size) ||
+		(log == 0 && seg == 0) ||
+		tli == 0)
+		ereport(ERROR,
+				(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+				 errmsg("invalid WAL file name \"%s\"", filename)));
+
+	if (offset < 0 || offset >= wal_segment_size)
+		ereport(ERROR,
+				(errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE),
+				 errmsg("\"offset\" must not be negative or greater than or "
+						"equal to WAL segment size")));
+
+	XLogFromFileName(filename, &tli, &segno, wal_segment_size);
+	XLogSegNoOffsetToRecPtr(segno, offset, wal_segment_size, result);
+
+	PG_RETURN_LSN(result);
+}
+
 /*
  * Compute an xlog file name and decimal byte offset given a WAL location,
  * such as is returned by pg_backup_stop() or pg_switch_wal().
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index a07e737a33..224fe590ac 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -6328,6 +6328,11 @@
   proargtypes => 'pg_lsn', proallargtypes => '{pg_lsn,text,int4}',
   proargmodes => '{i,o,o}', proargnames => '{lsn,file_name,file_offset}',
   prosrc => 'pg_walfile_name_offset' },
+{ oid => '8205',
+  descr => 'wal location, given a wal filename and byte offset',
+  proname => 'pg_walfile_offset_lsn', prorettype => 'pg_lsn',
+  proargtypes => 'text int4', proargnames => '{file_name,file_offset}',
+  prosrc => 'pg_walfile_offset_lsn' },
 { oid => '2851', descr => 'wal filename, given a wal location',
   proname => 'pg_walfile_name', prorettype => 'text', proargtypes => 'pg_lsn',
   prosrc => 'pg_walfile_name' },
diff --git a/src/test/regress/expected/misc_functions.out b/src/test/regress/expected/misc_functions.out
index 9f106c2a10..b2d6f23ac1 100644
--- a/src/test/regress/expected/misc_functions.out
+++ b/src/test/regress/expected/misc_functions.out
@@ -594,3 +594,22 @@ SELECT * FROM tenk1 a JOIN my_gen_series(1,10) g ON a.unique1 = g;
          Index Cond: (unique1 = g.g)
 (4 rows)
 
+-- pg_walfile_offset_lsn
+SELECT pg_walfile_offset_lsn('invalid', 15);
+ERROR:  invalid WAL file name "invalid"
+SELECT pg_walfile_offset_lsn('0000000100000000FFFFFFFF', 15);
+ERROR:  invalid WAL file name "0000000100000000FFFFFFFF"
+SELECT pg_walfile_offset_lsn('000000010000000000000000', 15);
+ERROR:  invalid WAL file name "000000010000000000000000"
+SELECT pg_walfile_offset_lsn('000000000000000100000000', 15);
+ERROR:  invalid WAL file name "000000000000000100000000"
+SELECT pg_walfile_offset_lsn('000000010000000100000000', -1);
+ERROR:  "offset" must not be negative or greater than or equal to WAL segment size
+SELECT pg_walfile_offset_lsn('000000010000000100000000', 2000000000);
+ERROR:  "offset" must not be negative or greater than or equal to WAL segment size
+SELECT pg_walfile_offset_lsn('000000010000000100000000', 15);
+ pg_walfile_offset_lsn 
+-----------------------
+ 1/F
+(1 row)
+
diff --git a/src/test/regress/sql/misc_functions.sql b/src/test/regress/sql/misc_functions.sql
index 639e9b352c..cb54901029 100644
--- a/src/test/regress/sql/misc_functions.sql
+++ b/src/test/regress/sql/misc_functions.sql
@@ -223,3 +223,12 @@ SELECT * FROM tenk1 a JOIN my_gen_series(1,1000) g ON a.unique1 = g;
 
 EXPLAIN (COSTS OFF)
 SELECT * FROM tenk1 a JOIN my_gen_series(1,10) g ON a.unique1 = g;
+
+-- pg_walfile_offset_lsn
+SELECT pg_walfile_offset_lsn('invalid', 15);
+SELECT pg_walfile_offset_lsn('0000000100000000FFFFFFFF', 15);
+SELECT pg_walfile_offset_lsn('000000010000000000000000', 15);
+SELECT pg_walfile_offset_lsn('000000000000000100000000', 15);
+SELECT pg_walfile_offset_lsn('000000010000000100000000', -1);
+SELECT pg_walfile_offset_lsn('000000010000000100000000', 2000000000);
+SELECT pg_walfile_offset_lsn('000000010000000100000000', 15);
-- 
2.25.1

From b19aa25a0d1f2ce85abe0c2081c0e7ede256e329 Mon Sep 17 00:00:00 2001
From: Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com>
Date: Thu, 29 Sep 2022 13:29:44 +0000
Subject: [PATCH v1] Add LSN along with offset to error messages reported for
 WAL file read/write/validate header failures

---
 src/backend/access/transam/xlog.c         |  8 ++++++--
 src/backend/access/transam/xlogreader.c   | 16 +++++++++++-----
 src/backend/access/transam/xlogrecovery.c | 13 ++++++++-----
 src/backend/access/transam/xlogutils.c    | 10 ++++++----
 src/include/access/xlogreader.h           |  1 +
 5 files changed, 32 insertions(+), 16 deletions(-)

diff --git a/src/backend/access/transam/xlog.c b/src/backend/access/transam/xlog.c
index 8e15256db8..a495bbac85 100644
--- a/src/backend/access/transam/xlog.c
+++ b/src/backend/access/transam/xlog.c
@@ -2218,6 +2218,7 @@ XLogWrite(XLogwrtRqst WriteRqst, TimeLineID tli, bool flexible)
 				{
 					char		xlogfname[MAXFNAMELEN];
 					int			save_errno;
+					XLogRecPtr	lsn;
 
 					if (errno == EINTR)
 						continue;
@@ -2226,11 +2227,14 @@ XLogWrite(XLogwrtRqst WriteRqst, TimeLineID tli, bool flexible)
 					XLogFileName(xlogfname, tli, openLogSegNo,
 								 wal_segment_size);
 					errno = save_errno;
+					XLogSegNoOffsetToRecPtr(openLogSegNo, startoffset,
+											wal_segment_size, lsn);
 					ereport(PANIC,
 							(errcode_for_file_access(),
 							 errmsg("could not write to log file %s "
-									"at offset %u, length %zu: %m",
-									xlogfname, startoffset, nleft)));
+									"at offset %u, LSN %X/%X, length %zu: %m",
+									xlogfname, startoffset,
+									LSN_FORMAT_ARGS(lsn), nleft)));
 				}
 				nleft -= written;
 				from += written;
diff --git a/src/backend/access/transam/xlogreader.c b/src/backend/access/transam/xlogreader.c
index 5a8fe81f82..c3befc44ba 100644
--- a/src/backend/access/transam/xlogreader.c
+++ b/src/backend/access/transam/xlogreader.c
@@ -1229,9 +1229,10 @@ XLogReaderValidatePageHeader(XLogReaderState *state, XLogRecPtr recptr,
 		XLogFileName(fname, state->seg.ws_tli, segno, state->segcxt.ws_segsize);
 
 		report_invalid_record(state,
-							  "invalid magic number %04X in WAL segment %s, offset %u",
+							  "invalid magic number %04X in WAL segment %s, LSN %X/%X, offset %u",
 							  hdr->xlp_magic,
 							  fname,
+							  LSN_FORMAT_ARGS(recptr),
 							  offset);
 		return false;
 	}
@@ -1243,9 +1244,10 @@ XLogReaderValidatePageHeader(XLogReaderState *state, XLogRecPtr recptr,
 		XLogFileName(fname, state->seg.ws_tli, segno, state->segcxt.ws_segsize);
 
 		report_invalid_record(state,
-							  "invalid info bits %04X in WAL segment %s, offset %u",
+							  "invalid info bits %04X in WAL segment %s, LSN %X/%X, offset %u",
 							  hdr->xlp_info,
 							  fname,
+							  LSN_FORMAT_ARGS(recptr),
 							  offset);
 		return false;
 	}
@@ -1284,9 +1286,10 @@ XLogReaderValidatePageHeader(XLogReaderState *state, XLogRecPtr recptr,
 
 		/* hmm, first page of file doesn't have a long header? */
 		report_invalid_record(state,
-							  "invalid info bits %04X in WAL segment %s, offset %u",
+							  "invalid info bits %04X in WAL segment %s, LSN %X/%X, offset %u",
 							  hdr->xlp_info,
 							  fname,
+							  LSN_FORMAT_ARGS(recptr),
 							  offset);
 		return false;
 	}
@@ -1303,9 +1306,10 @@ XLogReaderValidatePageHeader(XLogReaderState *state, XLogRecPtr recptr,
 		XLogFileName(fname, state->seg.ws_tli, segno, state->segcxt.ws_segsize);
 
 		report_invalid_record(state,
-							  "unexpected pageaddr %X/%X in WAL segment %s, offset %u",
+							  "unexpected pageaddr %X/%X in WAL segment %s, LSN %X/%X, offset %u",
 							  LSN_FORMAT_ARGS(hdr->xlp_pageaddr),
 							  fname,
+							  LSN_FORMAT_ARGS(recptr),
 							  offset);
 		return false;
 	}
@@ -1328,10 +1332,11 @@ XLogReaderValidatePageHeader(XLogReaderState *state, XLogRecPtr recptr,
 			XLogFileName(fname, state->seg.ws_tli, segno, state->segcxt.ws_segsize);
 
 			report_invalid_record(state,
-								  "out-of-sequence timeline ID %u (after %u) in WAL segment %s, offset %u",
+								  "out-of-sequence timeline ID %u (after %u) in WAL segment %s, LSN %X/%X, offset %u",
 								  hdr->xlp_tli,
 								  state->latestPageTLI,
 								  fname,
+								  LSN_FORMAT_ARGS(recptr),
 								  offset);
 			return false;
 		}
@@ -1556,6 +1561,7 @@ WALRead(XLogReaderState *state,
 			errinfo->wre_req = segbytes;
 			errinfo->wre_read = readbytes;
 			errinfo->wre_off = startoff;
+			errinfo->wre_lsn = recptr;
 			errinfo->wre_seg = state->seg;
 			return false;
 		}
diff --git a/src/backend/access/transam/xlogrecovery.c b/src/backend/access/transam/xlogrecovery.c
index cb07694aea..c6ffb29c05 100644
--- a/src/backend/access/transam/xlogrecovery.c
+++ b/src/backend/access/transam/xlogrecovery.c
@@ -3079,9 +3079,10 @@ ReadRecord(XLogPrefetcher *xlogprefetcher, int emode,
 			XLogFileName(fname, xlogreader->seg.ws_tli, segno,
 						 wal_segment_size);
 			ereport(emode_for_corrupt_record(emode, xlogreader->EndRecPtr),
-					(errmsg("unexpected timeline ID %u in WAL segment %s, offset %u",
+					(errmsg("unexpected timeline ID %u in WAL segment %s, LSN %X/%X, offset %u",
 							xlogreader->latestPageTLI,
 							fname,
+							LSN_FORMAT_ARGS(xlogreader->latestPagePtr),
 							offset)));
 			record = NULL;
 		}
@@ -3284,14 +3285,16 @@ retry:
 			errno = save_errno;
 			ereport(emode_for_corrupt_record(emode, targetPagePtr + reqLen),
 					(errcode_for_file_access(),
-					 errmsg("could not read from WAL segment %s, offset %u: %m",
-							fname, readOff)));
+					 errmsg("could not read from WAL segment %s, LSN %X/%X, offset %u: %m",
+							fname, LSN_FORMAT_ARGS(targetPagePtr),
+							readOff)));
 		}
 		else
 			ereport(emode_for_corrupt_record(emode, targetPagePtr + reqLen),
 					(errcode(ERRCODE_DATA_CORRUPTED),
-					 errmsg("could not read from WAL segment %s, offset %u: read %d of %zu",
-							fname, readOff, r, (Size) XLOG_BLCKSZ)));
+					 errmsg("could not read from WAL segment %s, LSN %X/%X, offset %u: read %d of %zu",
+							fname, LSN_FORMAT_ARGS(targetPagePtr),
+							readOff, r, (Size) XLOG_BLCKSZ)));
 		goto next_record_is_invalid;
 	}
 	pgstat_report_wait_end();
diff --git a/src/backend/access/transam/xlogutils.c b/src/backend/access/transam/xlogutils.c
index 563cba258d..9d171f2ad9 100644
--- a/src/backend/access/transam/xlogutils.c
+++ b/src/backend/access/transam/xlogutils.c
@@ -1051,15 +1051,17 @@ WALReadRaiseError(WALReadError *errinfo)
 		errno = errinfo->wre_errno;
 		ereport(ERROR,
 				(errcode_for_file_access(),
-				 errmsg("could not read from WAL segment %s, offset %d: %m",
-						fname, errinfo->wre_off)));
+				 errmsg("could not read from WAL segment %s, LSN %X/%X, offset %d: %m",
+						fname, LSN_FORMAT_ARGS(errinfo->wre_lsn),
+						errinfo->wre_off)));
 	}
 	else if (errinfo->wre_read == 0)
 	{
 		ereport(ERROR,
 				(errcode(ERRCODE_DATA_CORRUPTED),
-				 errmsg("could not read from WAL segment %s, offset %d: read %d of %d",
-						fname, errinfo->wre_off, errinfo->wre_read,
+				 errmsg("could not read from WAL segment %s, LSN %X/%X, offset %d: read %d of %d",
+						fname, LSN_FORMAT_ARGS(errinfo->wre_lsn),
+						errinfo->wre_off, errinfo->wre_read,
 						errinfo->wre_req)));
 	}
 }
diff --git a/src/include/access/xlogreader.h b/src/include/access/xlogreader.h
index e87f91316a..1e47169b5a 100644
--- a/src/include/access/xlogreader.h
+++ b/src/include/access/xlogreader.h
@@ -386,6 +386,7 @@ typedef struct WALReadError
 	int			wre_off;		/* Offset we tried to read from. */
 	int			wre_req;		/* Bytes requested to be read. */
 	int			wre_read;		/* Bytes read by the last read(). */
+	XLogRecPtr	wre_lsn;		/* WAL LSN being read. */
 	WALOpenSegment wre_seg;		/* Segment we tried to read from. */
 } WALReadError;
 
-- 
2.34.1

From b19aa25a0d1f2ce85abe0c2081c0e7ede256e329 Mon Sep 17 00:00:00 2001
From: Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com>
Date: Thu, 29 Sep 2022 13:29:44 +0000
Subject: [PATCH v1] Add LSN along with offset to error messages reported for
 WAL file read/write/validate header failures

---
 src/backend/access/transam/xlog.c         |  8 ++++++--
 src/backend/access/transam/xlogreader.c   | 16 +++++++++++-----
 src/backend/access/transam/xlogrecovery.c | 13 ++++++++-----
 src/backend/access/transam/xlogutils.c    | 10 ++++++----
 src/include/access/xlogreader.h           |  1 +
 5 files changed, 32 insertions(+), 16 deletions(-)

diff --git a/src/backend/access/transam/xlog.c b/src/backend/access/transam/xlog.c
index 8e15256db8..a495bbac85 100644
--- a/src/backend/access/transam/xlog.c
+++ b/src/backend/access/transam/xlog.c
@@ -2218,6 +2218,7 @@ XLogWrite(XLogwrtRqst WriteRqst, TimeLineID tli, bool flexible)
 				{
 					char		xlogfname[MAXFNAMELEN];
 					int			save_errno;
+					XLogRecPtr	lsn;
 
 					if (errno == EINTR)
 						continue;
@@ -2226,11 +2227,14 @@ XLogWrite(XLogwrtRqst WriteRqst, TimeLineID tli, bool flexible)
 					XLogFileName(xlogfname, tli, openLogSegNo,
 								 wal_segment_size);
 					errno = save_errno;
+					XLogSegNoOffsetToRecPtr(openLogSegNo, startoffset,
+											wal_segment_size, lsn);
 					ereport(PANIC,
 							(errcode_for_file_access(),
 							 errmsg("could not write to log file %s "
-									"at offset %u, length %zu: %m",
-									xlogfname, startoffset, nleft)));
+									"at offset %u, LSN %X/%X, length %zu: %m",
+									xlogfname, startoffset,
+									LSN_FORMAT_ARGS(lsn), nleft)));
 				}
 				nleft -= written;
 				from += written;
diff --git a/src/backend/access/transam/xlogreader.c b/src/backend/access/transam/xlogreader.c
index 5a8fe81f82..c3befc44ba 100644
--- a/src/backend/access/transam/xlogreader.c
+++ b/src/backend/access/transam/xlogreader.c
@@ -1229,9 +1229,10 @@ XLogReaderValidatePageHeader(XLogReaderState *state, XLogRecPtr recptr,
 		XLogFileName(fname, state->seg.ws_tli, segno, state->segcxt.ws_segsize);
 
 		report_invalid_record(state,
-							  "invalid magic number %04X in WAL segment %s, offset %u",
+							  "invalid magic number %04X in WAL segment %s, LSN %X/%X, offset %u",
 							  hdr->xlp_magic,
 							  fname,
+							  LSN_FORMAT_ARGS(recptr),
 							  offset);
 		return false;
 	}
@@ -1243,9 +1244,10 @@ XLogReaderValidatePageHeader(XLogReaderState *state, XLogRecPtr recptr,
 		XLogFileName(fname, state->seg.ws_tli, segno, state->segcxt.ws_segsize);
 
 		report_invalid_record(state,
-							  "invalid info bits %04X in WAL segment %s, offset %u",
+							  "invalid info bits %04X in WAL segment %s, LSN %X/%X, offset %u",
 							  hdr->xlp_info,
 							  fname,
+							  LSN_FORMAT_ARGS(recptr),
 							  offset);
 		return false;
 	}
@@ -1284,9 +1286,10 @@ XLogReaderValidatePageHeader(XLogReaderState *state, XLogRecPtr recptr,
 
 		/* hmm, first page of file doesn't have a long header? */
 		report_invalid_record(state,
-							  "invalid info bits %04X in WAL segment %s, offset %u",
+							  "invalid info bits %04X in WAL segment %s, LSN %X/%X, offset %u",
 							  hdr->xlp_info,
 							  fname,
+							  LSN_FORMAT_ARGS(recptr),
 							  offset);
 		return false;
 	}
@@ -1303,9 +1306,10 @@ XLogReaderValidatePageHeader(XLogReaderState *state, XLogRecPtr recptr,
 		XLogFileName(fname, state->seg.ws_tli, segno, state->segcxt.ws_segsize);
 
 		report_invalid_record(state,
-							  "unexpected pageaddr %X/%X in WAL segment %s, offset %u",
+							  "unexpected pageaddr %X/%X in WAL segment %s, LSN %X/%X, offset %u",
 							  LSN_FORMAT_ARGS(hdr->xlp_pageaddr),
 							  fname,
+							  LSN_FORMAT_ARGS(recptr),
 							  offset);
 		return false;
 	}
@@ -1328,10 +1332,11 @@ XLogReaderValidatePageHeader(XLogReaderState *state, XLogRecPtr recptr,
 			XLogFileName(fname, state->seg.ws_tli, segno, state->segcxt.ws_segsize);
 
 			report_invalid_record(state,
-								  "out-of-sequence timeline ID %u (after %u) in WAL segment %s, offset %u",
+								  "out-of-sequence timeline ID %u (after %u) in WAL segment %s, LSN %X/%X, offset %u",
 								  hdr->xlp_tli,
 								  state->latestPageTLI,
 								  fname,
+								  LSN_FORMAT_ARGS(recptr),
 								  offset);
 			return false;
 		}
@@ -1556,6 +1561,7 @@ WALRead(XLogReaderState *state,
 			errinfo->wre_req = segbytes;
 			errinfo->wre_read = readbytes;
 			errinfo->wre_off = startoff;
+			errinfo->wre_lsn = recptr;
 			errinfo->wre_seg = state->seg;
 			return false;
 		}
diff --git a/src/backend/access/transam/xlogrecovery.c b/src/backend/access/transam/xlogrecovery.c
index cb07694aea..c6ffb29c05 100644
--- a/src/backend/access/transam/xlogrecovery.c
+++ b/src/backend/access/transam/xlogrecovery.c
@@ -3079,9 +3079,10 @@ ReadRecord(XLogPrefetcher *xlogprefetcher, int emode,
 			XLogFileName(fname, xlogreader->seg.ws_tli, segno,
 						 wal_segment_size);
 			ereport(emode_for_corrupt_record(emode, xlogreader->EndRecPtr),
-					(errmsg("unexpected timeline ID %u in WAL segment %s, offset %u",
+					(errmsg("unexpected timeline ID %u in WAL segment %s, LSN %X/%X, offset %u",
 							xlogreader->latestPageTLI,
 							fname,
+							LSN_FORMAT_ARGS(xlogreader->latestPagePtr),
 							offset)));
 			record = NULL;
 		}
@@ -3284,14 +3285,16 @@ retry:
 			errno = save_errno;
 			ereport(emode_for_corrupt_record(emode, targetPagePtr + reqLen),
 					(errcode_for_file_access(),
-					 errmsg("could not read from WAL segment %s, offset %u: %m",
-							fname, readOff)));
+					 errmsg("could not read from WAL segment %s, LSN %X/%X, offset %u: %m",
+							fname, LSN_FORMAT_ARGS(targetPagePtr),
+							readOff)));
 		}
 		else
 			ereport(emode_for_corrupt_record(emode, targetPagePtr + reqLen),
 					(errcode(ERRCODE_DATA_CORRUPTED),
-					 errmsg("could not read from WAL segment %s, offset %u: read %d of %zu",
-							fname, readOff, r, (Size) XLOG_BLCKSZ)));
+					 errmsg("could not read from WAL segment %s, LSN %X/%X, offset %u: read %d of %zu",
+							fname, LSN_FORMAT_ARGS(targetPagePtr),
+							readOff, r, (Size) XLOG_BLCKSZ)));
 		goto next_record_is_invalid;
 	}
 	pgstat_report_wait_end();
diff --git a/src/backend/access/transam/xlogutils.c b/src/backend/access/transam/xlogutils.c
index 563cba258d..9d171f2ad9 100644
--- a/src/backend/access/transam/xlogutils.c
+++ b/src/backend/access/transam/xlogutils.c
@@ -1051,15 +1051,17 @@ WALReadRaiseError(WALReadError *errinfo)
 		errno = errinfo->wre_errno;
 		ereport(ERROR,
 				(errcode_for_file_access(),
-				 errmsg("could not read from WAL segment %s, offset %d: %m",
-						fname, errinfo->wre_off)));
+				 errmsg("could not read from WAL segment %s, LSN %X/%X, offset %d: %m",
+						fname, LSN_FORMAT_ARGS(errinfo->wre_lsn),
+						errinfo->wre_off)));
 	}
 	else if (errinfo->wre_read == 0)
 	{
 		ereport(ERROR,
 				(errcode(ERRCODE_DATA_CORRUPTED),
-				 errmsg("could not read from WAL segment %s, offset %d: read %d of %d",
-						fname, errinfo->wre_off, errinfo->wre_read,
+				 errmsg("could not read from WAL segment %s, LSN %X/%X, offset %d: read %d of %d",
+						fname, LSN_FORMAT_ARGS(errinfo->wre_lsn),
+						errinfo->wre_off, errinfo->wre_read,
 						errinfo->wre_req)));
 	}
 }
diff --git a/src/include/access/xlogreader.h b/src/include/access/xlogreader.h
index e87f91316a..1e47169b5a 100644
--- a/src/include/access/xlogreader.h
+++ b/src/include/access/xlogreader.h
@@ -386,6 +386,7 @@ typedef struct WALReadError
 	int			wre_off;		/* Offset we tried to read from. */
 	int			wre_req;		/* Bytes requested to be read. */
 	int			wre_read;		/* Bytes read by the last read(). */
+	XLogRecPtr	wre_lsn;		/* WAL LSN being read. */
 	WALOpenSegment wre_seg;		/* Segment we tried to read from. */
 } WALReadError;
 
-- 
2.34.1

From f8ed45d9fa59690d8b3375d658c1baedb8510195 Mon Sep 17 00:00:00 2001
From: Nathan Bossart <nathandbossart@gmail.com>
Date: Mon, 19 Sep 2022 20:24:10 -0700
Subject: [PATCH v1 1/1] introduce pg_walfile_offset_lsn()

---
 doc/src/sgml/func.sgml                       | 14 +++++++
 src/backend/access/transam/xlogfuncs.c       | 39 ++++++++++++++++++++
 src/include/catalog/pg_proc.dat              |  5 +++
 src/test/regress/expected/misc_functions.out | 19 ++++++++++
 src/test/regress/sql/misc_functions.sql      |  9 +++++
 5 files changed, 86 insertions(+)

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 67eb380632..318ac22769 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -25891,6 +25891,20 @@ LOG:  Grand total: 1651920 bytes in 201 blocks; 622360 free (88 chunks); 1029560
        </para></entry>
       </row>
 
+      <row>
+       <entry role="func_table_entry"><para role="func_signature">
+        <indexterm>
+         <primary>pg_walfile_offset_lsn</primary>
+        </indexterm>
+        <function>pg_walfile_offset_lsn</function> ( <parameter>file_name</parameter> <type>text</type>, <parameter>file_offset</parameter> <type>integer</type> )
+        <returnvalue>pg_lsn</returnvalue>
+       </para>
+       <para>
+        Converts a WAL file name and byte offset within that file to a
+        write-ahead log location.
+       </para></entry>
+      </row>
+
       <row>
        <entry role="func_table_entry"><para role="func_signature">
         <indexterm>
diff --git a/src/backend/access/transam/xlogfuncs.c b/src/backend/access/transam/xlogfuncs.c
index 27aeb6e281..75f58cb118 100644
--- a/src/backend/access/transam/xlogfuncs.c
+++ b/src/backend/access/transam/xlogfuncs.c
@@ -313,6 +313,45 @@ pg_last_wal_replay_lsn(PG_FUNCTION_ARGS)
 	PG_RETURN_LSN(recptr);
 }
 
+/*
+ * Compute an LSN given a WAL file name and decimal byte offset.
+ */
+Datum
+pg_walfile_offset_lsn(PG_FUNCTION_ARGS)
+{
+	char	   *filename = text_to_cstring(PG_GETARG_TEXT_PP(0));
+	int			offset = PG_GETARG_INT32(1);
+	TimeLineID	tli;
+	XLogSegNo	segno;
+	XLogRecPtr	result;
+	uint32		log;
+	uint32		seg;
+
+	if (!IsXLogFileName(filename))
+		ereport(ERROR,
+				(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+				 errmsg("invalid WAL file name \"%s\"", filename)));
+
+	sscanf(filename, "%08X%08X%08X", &tli, &log, &seg);
+	if (seg >= XLogSegmentsPerXLogId(wal_segment_size) ||
+		(log == 0 && seg == 0) ||
+		tli == 0)
+		ereport(ERROR,
+				(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+				 errmsg("invalid WAL file name \"%s\"", filename)));
+
+	if (offset < 0 || offset >= wal_segment_size)
+		ereport(ERROR,
+				(errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE),
+				 errmsg("\"offset\" must not be negative or greater than or "
+						"equal to WAL segment size")));
+
+	XLogFromFileName(filename, &tli, &segno, wal_segment_size);
+	XLogSegNoOffsetToRecPtr(segno, offset, wal_segment_size, result);
+
+	PG_RETURN_LSN(result);
+}
+
 /*
  * Compute an xlog file name and decimal byte offset given a WAL location,
  * such as is returned by pg_backup_stop() or pg_switch_wal().
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index a07e737a33..224fe590ac 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -6328,6 +6328,11 @@
   proargtypes => 'pg_lsn', proallargtypes => '{pg_lsn,text,int4}',
   proargmodes => '{i,o,o}', proargnames => '{lsn,file_name,file_offset}',
   prosrc => 'pg_walfile_name_offset' },
+{ oid => '8205',
+  descr => 'wal location, given a wal filename and byte offset',
+  proname => 'pg_walfile_offset_lsn', prorettype => 'pg_lsn',
+  proargtypes => 'text int4', proargnames => '{file_name,file_offset}',
+  prosrc => 'pg_walfile_offset_lsn' },
 { oid => '2851', descr => 'wal filename, given a wal location',
   proname => 'pg_walfile_name', prorettype => 'text', proargtypes => 'pg_lsn',
   prosrc => 'pg_walfile_name' },
diff --git a/src/test/regress/expected/misc_functions.out b/src/test/regress/expected/misc_functions.out
index 9f106c2a10..b2d6f23ac1 100644
--- a/src/test/regress/expected/misc_functions.out
+++ b/src/test/regress/expected/misc_functions.out
@@ -594,3 +594,22 @@ SELECT * FROM tenk1 a JOIN my_gen_series(1,10) g ON a.unique1 = g;
          Index Cond: (unique1 = g.g)
 (4 rows)
 
+-- pg_walfile_offset_lsn
+SELECT pg_walfile_offset_lsn('invalid', 15);
+ERROR:  invalid WAL file name "invalid"
+SELECT pg_walfile_offset_lsn('0000000100000000FFFFFFFF', 15);
+ERROR:  invalid WAL file name "0000000100000000FFFFFFFF"
+SELECT pg_walfile_offset_lsn('000000010000000000000000', 15);
+ERROR:  invalid WAL file name "000000010000000000000000"
+SELECT pg_walfile_offset_lsn('000000000000000100000000', 15);
+ERROR:  invalid WAL file name "000000000000000100000000"
+SELECT pg_walfile_offset_lsn('000000010000000100000000', -1);
+ERROR:  "offset" must not be negative or greater than or equal to WAL segment size
+SELECT pg_walfile_offset_lsn('000000010000000100000000', 2000000000);
+ERROR:  "offset" must not be negative or greater than or equal to WAL segment size
+SELECT pg_walfile_offset_lsn('000000010000000100000000', 15);
+ pg_walfile_offset_lsn 
+-----------------------
+ 1/F
+(1 row)
+
diff --git a/src/test/regress/sql/misc_functions.sql b/src/test/regress/sql/misc_functions.sql
index 639e9b352c..cb54901029 100644
--- a/src/test/regress/sql/misc_functions.sql
+++ b/src/test/regress/sql/misc_functions.sql
@@ -223,3 +223,12 @@ SELECT * FROM tenk1 a JOIN my_gen_series(1,1000) g ON a.unique1 = g;
 
 EXPLAIN (COSTS OFF)
 SELECT * FROM tenk1 a JOIN my_gen_series(1,10) g ON a.unique1 = g;
+
+-- pg_walfile_offset_lsn
+SELECT pg_walfile_offset_lsn('invalid', 15);
+SELECT pg_walfile_offset_lsn('0000000100000000FFFFFFFF', 15);
+SELECT pg_walfile_offset_lsn('000000010000000000000000', 15);
+SELECT pg_walfile_offset_lsn('000000000000000100000000', 15);
+SELECT pg_walfile_offset_lsn('000000010000000100000000', -1);
+SELECT pg_walfile_offset_lsn('000000010000000100000000', 2000000000);
+SELECT pg_walfile_offset_lsn('000000010000000100000000', 15);
-- 
2.25.1

From 87da2d0ae8fb0f92d2d88d8638aee1ca58332522 Mon Sep 17 00:00:00 2001
From: Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com>
Date: Thu, 27 Oct 2022 09:06:27 +0000
Subject: [PATCH v2] Add LSN along with offset to error messages reported for
 WAL file read/write/validate header failures

---
 src/backend/access/transam/xlog.c         |  8 ++++++--
 src/backend/access/transam/xlogreader.c   | 16 +++++++++++-----
 src/backend/access/transam/xlogrecovery.c | 13 ++++++++-----
 src/backend/access/transam/xlogutils.c    | 10 ++++++----
 src/include/access/xlogreader.h           |  1 +
 5 files changed, 32 insertions(+), 16 deletions(-)

diff --git a/src/backend/access/transam/xlog.c b/src/backend/access/transam/xlog.c
index 8f10effe3a..1467001b4f 100644
--- a/src/backend/access/transam/xlog.c
+++ b/src/backend/access/transam/xlog.c
@@ -2214,6 +2214,7 @@ XLogWrite(XLogwrtRqst WriteRqst, TimeLineID tli, bool flexible)
 				{
 					char		xlogfname[MAXFNAMELEN];
 					int			save_errno;
+					XLogRecPtr	lsn;
 
 					if (errno == EINTR)
 						continue;
@@ -2222,11 +2223,14 @@ XLogWrite(XLogwrtRqst WriteRqst, TimeLineID tli, bool flexible)
 					XLogFileName(xlogfname, tli, openLogSegNo,
 								 wal_segment_size);
 					errno = save_errno;
+					XLogSegNoOffsetToRecPtr(openLogSegNo, startoffset,
+											wal_segment_size, lsn);
 					ereport(PANIC,
 							(errcode_for_file_access(),
 							 errmsg("could not write to log file %s "
-									"at offset %u, length %zu: %m",
-									xlogfname, startoffset, nleft)));
+									"at offset %u, LSN %X/%X, length %zu: %m",
+									xlogfname, startoffset,
+									LSN_FORMAT_ARGS(lsn), nleft)));
 				}
 				nleft -= written;
 				from += written;
diff --git a/src/backend/access/transam/xlogreader.c b/src/backend/access/transam/xlogreader.c
index 93f667b254..a321c55d8f 100644
--- a/src/backend/access/transam/xlogreader.c
+++ b/src/backend/access/transam/xlogreader.c
@@ -1226,9 +1226,10 @@ XLogReaderValidatePageHeader(XLogReaderState *state, XLogRecPtr recptr,
 		XLogFileName(fname, state->seg.ws_tli, segno, state->segcxt.ws_segsize);
 
 		report_invalid_record(state,
-							  "invalid magic number %04X in WAL segment %s, offset %u",
+							  "invalid magic number %04X in WAL segment %s, LSN %X/%X, offset %u",
 							  hdr->xlp_magic,
 							  fname,
+							  LSN_FORMAT_ARGS(recptr),
 							  offset);
 		return false;
 	}
@@ -1240,9 +1241,10 @@ XLogReaderValidatePageHeader(XLogReaderState *state, XLogRecPtr recptr,
 		XLogFileName(fname, state->seg.ws_tli, segno, state->segcxt.ws_segsize);
 
 		report_invalid_record(state,
-							  "invalid info bits %04X in WAL segment %s, offset %u",
+							  "invalid info bits %04X in WAL segment %s, LSN %X/%X, offset %u",
 							  hdr->xlp_info,
 							  fname,
+							  LSN_FORMAT_ARGS(recptr),
 							  offset);
 		return false;
 	}
@@ -1281,9 +1283,10 @@ XLogReaderValidatePageHeader(XLogReaderState *state, XLogRecPtr recptr,
 
 		/* hmm, first page of file doesn't have a long header? */
 		report_invalid_record(state,
-							  "invalid info bits %04X in WAL segment %s, offset %u",
+							  "invalid info bits %04X in WAL segment %s, LSN %X/%X, offset %u",
 							  hdr->xlp_info,
 							  fname,
+							  LSN_FORMAT_ARGS(recptr),
 							  offset);
 		return false;
 	}
@@ -1300,9 +1303,10 @@ XLogReaderValidatePageHeader(XLogReaderState *state, XLogRecPtr recptr,
 		XLogFileName(fname, state->seg.ws_tli, segno, state->segcxt.ws_segsize);
 
 		report_invalid_record(state,
-							  "unexpected pageaddr %X/%X in WAL segment %s, offset %u",
+							  "unexpected pageaddr %X/%X in WAL segment %s, LSN %X/%X, offset %u",
 							  LSN_FORMAT_ARGS(hdr->xlp_pageaddr),
 							  fname,
+							  LSN_FORMAT_ARGS(recptr),
 							  offset);
 		return false;
 	}
@@ -1325,10 +1329,11 @@ XLogReaderValidatePageHeader(XLogReaderState *state, XLogRecPtr recptr,
 			XLogFileName(fname, state->seg.ws_tli, segno, state->segcxt.ws_segsize);
 
 			report_invalid_record(state,
-								  "out-of-sequence timeline ID %u (after %u) in WAL segment %s, offset %u",
+								  "out-of-sequence timeline ID %u (after %u) in WAL segment %s, LSN %X/%X, offset %u",
 								  hdr->xlp_tli,
 								  state->latestPageTLI,
 								  fname,
+								  LSN_FORMAT_ARGS(recptr),
 								  offset);
 			return false;
 		}
@@ -1553,6 +1558,7 @@ WALRead(XLogReaderState *state,
 			errinfo->wre_req = segbytes;
 			errinfo->wre_read = readbytes;
 			errinfo->wre_off = startoff;
+			errinfo->wre_lsn = recptr;
 			errinfo->wre_seg = state->seg;
 			return false;
 		}
diff --git a/src/backend/access/transam/xlogrecovery.c b/src/backend/access/transam/xlogrecovery.c
index cb07694aea..c6ffb29c05 100644
--- a/src/backend/access/transam/xlogrecovery.c
+++ b/src/backend/access/transam/xlogrecovery.c
@@ -3079,9 +3079,10 @@ ReadRecord(XLogPrefetcher *xlogprefetcher, int emode,
 			XLogFileName(fname, xlogreader->seg.ws_tli, segno,
 						 wal_segment_size);
 			ereport(emode_for_corrupt_record(emode, xlogreader->EndRecPtr),
-					(errmsg("unexpected timeline ID %u in WAL segment %s, offset %u",
+					(errmsg("unexpected timeline ID %u in WAL segment %s, LSN %X/%X, offset %u",
 							xlogreader->latestPageTLI,
 							fname,
+							LSN_FORMAT_ARGS(xlogreader->latestPagePtr),
 							offset)));
 			record = NULL;
 		}
@@ -3284,14 +3285,16 @@ retry:
 			errno = save_errno;
 			ereport(emode_for_corrupt_record(emode, targetPagePtr + reqLen),
 					(errcode_for_file_access(),
-					 errmsg("could not read from WAL segment %s, offset %u: %m",
-							fname, readOff)));
+					 errmsg("could not read from WAL segment %s, LSN %X/%X, offset %u: %m",
+							fname, LSN_FORMAT_ARGS(targetPagePtr),
+							readOff)));
 		}
 		else
 			ereport(emode_for_corrupt_record(emode, targetPagePtr + reqLen),
 					(errcode(ERRCODE_DATA_CORRUPTED),
-					 errmsg("could not read from WAL segment %s, offset %u: read %d of %zu",
-							fname, readOff, r, (Size) XLOG_BLCKSZ)));
+					 errmsg("could not read from WAL segment %s, LSN %X/%X, offset %u: read %d of %zu",
+							fname, LSN_FORMAT_ARGS(targetPagePtr),
+							readOff, r, (Size) XLOG_BLCKSZ)));
 		goto next_record_is_invalid;
 	}
 	pgstat_report_wait_end();
diff --git a/src/backend/access/transam/xlogutils.c b/src/backend/access/transam/xlogutils.c
index 563cba258d..9d171f2ad9 100644
--- a/src/backend/access/transam/xlogutils.c
+++ b/src/backend/access/transam/xlogutils.c
@@ -1051,15 +1051,17 @@ WALReadRaiseError(WALReadError *errinfo)
 		errno = errinfo->wre_errno;
 		ereport(ERROR,
 				(errcode_for_file_access(),
-				 errmsg("could not read from WAL segment %s, offset %d: %m",
-						fname, errinfo->wre_off)));
+				 errmsg("could not read from WAL segment %s, LSN %X/%X, offset %d: %m",
+						fname, LSN_FORMAT_ARGS(errinfo->wre_lsn),
+						errinfo->wre_off)));
 	}
 	else if (errinfo->wre_read == 0)
 	{
 		ereport(ERROR,
 				(errcode(ERRCODE_DATA_CORRUPTED),
-				 errmsg("could not read from WAL segment %s, offset %d: read %d of %d",
-						fname, errinfo->wre_off, errinfo->wre_read,
+				 errmsg("could not read from WAL segment %s, LSN %X/%X, offset %d: read %d of %d",
+						fname, LSN_FORMAT_ARGS(errinfo->wre_lsn),
+						errinfo->wre_off, errinfo->wre_read,
 						errinfo->wre_req)));
 	}
 }
diff --git a/src/include/access/xlogreader.h b/src/include/access/xlogreader.h
index e87f91316a..1e47169b5a 100644
--- a/src/include/access/xlogreader.h
+++ b/src/include/access/xlogreader.h
@@ -386,6 +386,7 @@ typedef struct WALReadError
 	int			wre_off;		/* Offset we tried to read from. */
 	int			wre_req;		/* Bytes requested to be read. */
 	int			wre_read;		/* Bytes read by the last read(). */
+	XLogRecPtr	wre_lsn;		/* WAL LSN being read. */
 	WALOpenSegment wre_seg;		/* Segment we tried to read from. */
 } WALReadError;
 
-- 
2.34.1

From 187dec030e40649c0a7d8accc605a3b874608b14 Mon Sep 17 00:00:00 2001
From: Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com>
Date: Thu, 27 Oct 2022 09:23:19 +0000
Subject: [PATCH v2] Introduce pg_walfile_offset_lsn()

Author: Nathan Bossart
---
 doc/src/sgml/func.sgml                       | 14 +++++++
 src/backend/access/transam/xlogfuncs.c       | 39 ++++++++++++++++++++
 src/include/catalog/pg_proc.dat              |  5 +++
 src/test/regress/expected/misc_functions.out | 19 ++++++++++
 src/test/regress/sql/misc_functions.sql      |  9 +++++
 5 files changed, 86 insertions(+)

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 6e0425cb3d..1514d149e0 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -25900,6 +25900,20 @@ LOG:  Grand total: 1651920 bytes in 201 blocks; 622360 free (88 chunks); 1029560
        </para></entry>
       </row>
 
+      <row>
+       <entry role="func_table_entry"><para role="func_signature">
+        <indexterm>
+         <primary>pg_walfile_offset_lsn</primary>
+        </indexterm>
+        <function>pg_walfile_offset_lsn</function> ( <parameter>file_name</parameter> <type>text</type>, <parameter>file_offset</parameter> <type>integer</type> )
+        <returnvalue>pg_lsn</returnvalue>
+       </para>
+       <para>
+        Converts a WAL file name and byte offset within that file to a
+        write-ahead log location.
+       </para></entry>
+      </row>
+
       <row>
        <entry role="func_table_entry"><para role="func_signature">
         <indexterm>
diff --git a/src/backend/access/transam/xlogfuncs.c b/src/backend/access/transam/xlogfuncs.c
index 487d5d9cac..ee0c63bcf4 100644
--- a/src/backend/access/transam/xlogfuncs.c
+++ b/src/backend/access/transam/xlogfuncs.c
@@ -339,6 +339,45 @@ pg_last_wal_replay_lsn(PG_FUNCTION_ARGS)
 	PG_RETURN_LSN(recptr);
 }
 
+/*
+ * Compute an LSN given a WAL file name and decimal byte offset.
+ */
+Datum
+pg_walfile_offset_lsn(PG_FUNCTION_ARGS)
+{
+	char	   *filename = text_to_cstring(PG_GETARG_TEXT_PP(0));
+	int			offset = PG_GETARG_INT32(1);
+	TimeLineID	tli;
+	XLogSegNo	segno;
+	XLogRecPtr	result;
+	uint32		log;
+	uint32		seg;
+
+	if (!IsXLogFileName(filename))
+		ereport(ERROR,
+				(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+				 errmsg("invalid WAL file name \"%s\"", filename)));
+
+	sscanf(filename, "%08X%08X%08X", &tli, &log, &seg);
+	if (seg >= XLogSegmentsPerXLogId(wal_segment_size) ||
+		(log == 0 && seg == 0) ||
+		tli == 0)
+		ereport(ERROR,
+				(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+				 errmsg("invalid WAL file name \"%s\"", filename)));
+
+	if (offset < 0 || offset >= wal_segment_size)
+		ereport(ERROR,
+				(errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE),
+				 errmsg("\"offset\" must not be negative or greater than or "
+						"equal to WAL segment size")));
+
+	XLogFromFileName(filename, &tli, &segno, wal_segment_size);
+	XLogSegNoOffsetToRecPtr(segno, offset, wal_segment_size, result);
+
+	PG_RETURN_LSN(result);
+}
+
 /*
  * Compute an xlog file name and decimal byte offset given a WAL location,
  * such as is returned by pg_backup_stop() or pg_switch_wal().
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 20f5aa56ea..264dc86635 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -6336,6 +6336,11 @@
   proargtypes => 'pg_lsn', proallargtypes => '{pg_lsn,text,int4}',
   proargmodes => '{i,o,o}', proargnames => '{lsn,file_name,file_offset}',
   prosrc => 'pg_walfile_name_offset' },
+{ oid => '8205',
+  descr => 'wal location, given a wal filename and byte offset',
+  proname => 'pg_walfile_offset_lsn', prorettype => 'pg_lsn',
+  proargtypes => 'text int4', proargnames => '{file_name,file_offset}',
+  prosrc => 'pg_walfile_offset_lsn' },
 { oid => '2851', descr => 'wal filename, given a wal location',
   proname => 'pg_walfile_name', prorettype => 'text', proargtypes => 'pg_lsn',
   prosrc => 'pg_walfile_name' },
diff --git a/src/test/regress/expected/misc_functions.out b/src/test/regress/expected/misc_functions.out
index 88bb696ded..066f1446be 100644
--- a/src/test/regress/expected/misc_functions.out
+++ b/src/test/regress/expected/misc_functions.out
@@ -619,3 +619,22 @@ SELECT count(*) > 0 AS ok FROM pg_control_system();
  t
 (1 row)
 
+-- pg_walfile_offset_lsn
+SELECT pg_walfile_offset_lsn('invalid', 15);
+ERROR:  invalid WAL file name "invalid"
+SELECT pg_walfile_offset_lsn('0000000100000000FFFFFFFF', 15);
+ERROR:  invalid WAL file name "0000000100000000FFFFFFFF"
+SELECT pg_walfile_offset_lsn('000000010000000000000000', 15);
+ERROR:  invalid WAL file name "000000010000000000000000"
+SELECT pg_walfile_offset_lsn('000000000000000100000000', 15);
+ERROR:  invalid WAL file name "000000000000000100000000"
+SELECT pg_walfile_offset_lsn('000000010000000100000000', -1);
+ERROR:  "offset" must not be negative or greater than or equal to WAL segment size
+SELECT pg_walfile_offset_lsn('000000010000000100000000', 2000000000);
+ERROR:  "offset" must not be negative or greater than or equal to WAL segment size
+SELECT pg_walfile_offset_lsn('000000010000000100000000', 15);
+ pg_walfile_offset_lsn 
+-----------------------
+ 1/F
+(1 row)
+
diff --git a/src/test/regress/sql/misc_functions.sql b/src/test/regress/sql/misc_functions.sql
index b07e9e8dbb..8c6665aba3 100644
--- a/src/test/regress/sql/misc_functions.sql
+++ b/src/test/regress/sql/misc_functions.sql
@@ -229,3 +229,12 @@ SELECT count(*) > 0 AS ok FROM pg_control_checkpoint();
 SELECT count(*) > 0 AS ok FROM pg_control_init();
 SELECT count(*) > 0 AS ok FROM pg_control_recovery();
 SELECT count(*) > 0 AS ok FROM pg_control_system();
+
+-- pg_walfile_offset_lsn
+SELECT pg_walfile_offset_lsn('invalid', 15);
+SELECT pg_walfile_offset_lsn('0000000100000000FFFFFFFF', 15);
+SELECT pg_walfile_offset_lsn('000000010000000000000000', 15);
+SELECT pg_walfile_offset_lsn('000000000000000100000000', 15);
+SELECT pg_walfile_offset_lsn('000000010000000100000000', -1);
+SELECT pg_walfile_offset_lsn('000000010000000100000000', 2000000000);
+SELECT pg_walfile_offset_lsn('000000010000000100000000', 15);
-- 
2.34.1

From 1b83f37b789ecf1f6ad1fa3dd71673c369584b6d Mon Sep 17 00:00:00 2001
From: Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com>
Date: Tue, 15 Nov 2022 06:04:57 +0000
Subject: [PATCH v3] Add LSN along with offset to error messages reported for
 WAL file read/write/validate header failures

Author: Bharath Rupireddy
---
 src/backend/access/transam/xlog.c         |  8 ++++++--
 src/backend/access/transam/xlogreader.c   | 16 +++++++++++-----
 src/backend/access/transam/xlogrecovery.c | 13 ++++++++-----
 src/backend/access/transam/xlogutils.c    | 10 ++++++----
 src/include/access/xlogreader.h           |  1 +
 5 files changed, 32 insertions(+), 16 deletions(-)

diff --git a/src/backend/access/transam/xlog.c b/src/backend/access/transam/xlog.c
index a31fbbff78..42ee51f380 100644
--- a/src/backend/access/transam/xlog.c
+++ b/src/backend/access/transam/xlog.c
@@ -2214,6 +2214,7 @@ XLogWrite(XLogwrtRqst WriteRqst, TimeLineID tli, bool flexible)
 				{
 					char		xlogfname[MAXFNAMELEN];
 					int			save_errno;
+					XLogRecPtr	lsn;
 
 					if (errno == EINTR)
 						continue;
@@ -2222,11 +2223,14 @@ XLogWrite(XLogwrtRqst WriteRqst, TimeLineID tli, bool flexible)
 					XLogFileName(xlogfname, tli, openLogSegNo,
 								 wal_segment_size);
 					errno = save_errno;
+					XLogSegNoOffsetToRecPtr(openLogSegNo, startoffset,
+											wal_segment_size, lsn);
 					ereport(PANIC,
 							(errcode_for_file_access(),
 							 errmsg("could not write to log file %s "
-									"at offset %u, length %zu: %m",
-									xlogfname, startoffset, nleft)));
+									"at offset %u, LSN %X/%X, length %zu: %m",
+									xlogfname, startoffset,
+									LSN_FORMAT_ARGS(lsn), nleft)));
 				}
 				nleft -= written;
 				from += written;
diff --git a/src/backend/access/transam/xlogreader.c b/src/backend/access/transam/xlogreader.c
index 93f667b254..a321c55d8f 100644
--- a/src/backend/access/transam/xlogreader.c
+++ b/src/backend/access/transam/xlogreader.c
@@ -1226,9 +1226,10 @@ XLogReaderValidatePageHeader(XLogReaderState *state, XLogRecPtr recptr,
 		XLogFileName(fname, state->seg.ws_tli, segno, state->segcxt.ws_segsize);
 
 		report_invalid_record(state,
-							  "invalid magic number %04X in WAL segment %s, offset %u",
+							  "invalid magic number %04X in WAL segment %s, LSN %X/%X, offset %u",
 							  hdr->xlp_magic,
 							  fname,
+							  LSN_FORMAT_ARGS(recptr),
 							  offset);
 		return false;
 	}
@@ -1240,9 +1241,10 @@ XLogReaderValidatePageHeader(XLogReaderState *state, XLogRecPtr recptr,
 		XLogFileName(fname, state->seg.ws_tli, segno, state->segcxt.ws_segsize);
 
 		report_invalid_record(state,
-							  "invalid info bits %04X in WAL segment %s, offset %u",
+							  "invalid info bits %04X in WAL segment %s, LSN %X/%X, offset %u",
 							  hdr->xlp_info,
 							  fname,
+							  LSN_FORMAT_ARGS(recptr),
 							  offset);
 		return false;
 	}
@@ -1281,9 +1283,10 @@ XLogReaderValidatePageHeader(XLogReaderState *state, XLogRecPtr recptr,
 
 		/* hmm, first page of file doesn't have a long header? */
 		report_invalid_record(state,
-							  "invalid info bits %04X in WAL segment %s, offset %u",
+							  "invalid info bits %04X in WAL segment %s, LSN %X/%X, offset %u",
 							  hdr->xlp_info,
 							  fname,
+							  LSN_FORMAT_ARGS(recptr),
 							  offset);
 		return false;
 	}
@@ -1300,9 +1303,10 @@ XLogReaderValidatePageHeader(XLogReaderState *state, XLogRecPtr recptr,
 		XLogFileName(fname, state->seg.ws_tli, segno, state->segcxt.ws_segsize);
 
 		report_invalid_record(state,
-							  "unexpected pageaddr %X/%X in WAL segment %s, offset %u",
+							  "unexpected pageaddr %X/%X in WAL segment %s, LSN %X/%X, offset %u",
 							  LSN_FORMAT_ARGS(hdr->xlp_pageaddr),
 							  fname,
+							  LSN_FORMAT_ARGS(recptr),
 							  offset);
 		return false;
 	}
@@ -1325,10 +1329,11 @@ XLogReaderValidatePageHeader(XLogReaderState *state, XLogRecPtr recptr,
 			XLogFileName(fname, state->seg.ws_tli, segno, state->segcxt.ws_segsize);
 
 			report_invalid_record(state,
-								  "out-of-sequence timeline ID %u (after %u) in WAL segment %s, offset %u",
+								  "out-of-sequence timeline ID %u (after %u) in WAL segment %s, LSN %X/%X, offset %u",
 								  hdr->xlp_tli,
 								  state->latestPageTLI,
 								  fname,
+								  LSN_FORMAT_ARGS(recptr),
 								  offset);
 			return false;
 		}
@@ -1553,6 +1558,7 @@ WALRead(XLogReaderState *state,
 			errinfo->wre_req = segbytes;
 			errinfo->wre_read = readbytes;
 			errinfo->wre_off = startoff;
+			errinfo->wre_lsn = recptr;
 			errinfo->wre_seg = state->seg;
 			return false;
 		}
diff --git a/src/backend/access/transam/xlogrecovery.c b/src/backend/access/transam/xlogrecovery.c
index cb07694aea..c6ffb29c05 100644
--- a/src/backend/access/transam/xlogrecovery.c
+++ b/src/backend/access/transam/xlogrecovery.c
@@ -3079,9 +3079,10 @@ ReadRecord(XLogPrefetcher *xlogprefetcher, int emode,
 			XLogFileName(fname, xlogreader->seg.ws_tli, segno,
 						 wal_segment_size);
 			ereport(emode_for_corrupt_record(emode, xlogreader->EndRecPtr),
-					(errmsg("unexpected timeline ID %u in WAL segment %s, offset %u",
+					(errmsg("unexpected timeline ID %u in WAL segment %s, LSN %X/%X, offset %u",
 							xlogreader->latestPageTLI,
 							fname,
+							LSN_FORMAT_ARGS(xlogreader->latestPagePtr),
 							offset)));
 			record = NULL;
 		}
@@ -3284,14 +3285,16 @@ retry:
 			errno = save_errno;
 			ereport(emode_for_corrupt_record(emode, targetPagePtr + reqLen),
 					(errcode_for_file_access(),
-					 errmsg("could not read from WAL segment %s, offset %u: %m",
-							fname, readOff)));
+					 errmsg("could not read from WAL segment %s, LSN %X/%X, offset %u: %m",
+							fname, LSN_FORMAT_ARGS(targetPagePtr),
+							readOff)));
 		}
 		else
 			ereport(emode_for_corrupt_record(emode, targetPagePtr + reqLen),
 					(errcode(ERRCODE_DATA_CORRUPTED),
-					 errmsg("could not read from WAL segment %s, offset %u: read %d of %zu",
-							fname, readOff, r, (Size) XLOG_BLCKSZ)));
+					 errmsg("could not read from WAL segment %s, LSN %X/%X, offset %u: read %d of %zu",
+							fname, LSN_FORMAT_ARGS(targetPagePtr),
+							readOff, r, (Size) XLOG_BLCKSZ)));
 		goto next_record_is_invalid;
 	}
 	pgstat_report_wait_end();
diff --git a/src/backend/access/transam/xlogutils.c b/src/backend/access/transam/xlogutils.c
index 563cba258d..9d171f2ad9 100644
--- a/src/backend/access/transam/xlogutils.c
+++ b/src/backend/access/transam/xlogutils.c
@@ -1051,15 +1051,17 @@ WALReadRaiseError(WALReadError *errinfo)
 		errno = errinfo->wre_errno;
 		ereport(ERROR,
 				(errcode_for_file_access(),
-				 errmsg("could not read from WAL segment %s, offset %d: %m",
-						fname, errinfo->wre_off)));
+				 errmsg("could not read from WAL segment %s, LSN %X/%X, offset %d: %m",
+						fname, LSN_FORMAT_ARGS(errinfo->wre_lsn),
+						errinfo->wre_off)));
 	}
 	else if (errinfo->wre_read == 0)
 	{
 		ereport(ERROR,
 				(errcode(ERRCODE_DATA_CORRUPTED),
-				 errmsg("could not read from WAL segment %s, offset %d: read %d of %d",
-						fname, errinfo->wre_off, errinfo->wre_read,
+				 errmsg("could not read from WAL segment %s, LSN %X/%X, offset %d: read %d of %d",
+						fname, LSN_FORMAT_ARGS(errinfo->wre_lsn),
+						errinfo->wre_off, errinfo->wre_read,
 						errinfo->wre_req)));
 	}
 }
diff --git a/src/include/access/xlogreader.h b/src/include/access/xlogreader.h
index e87f91316a..1e47169b5a 100644
--- a/src/include/access/xlogreader.h
+++ b/src/include/access/xlogreader.h
@@ -386,6 +386,7 @@ typedef struct WALReadError
 	int			wre_off;		/* Offset we tried to read from. */
 	int			wre_req;		/* Bytes requested to be read. */
 	int			wre_read;		/* Bytes read by the last read(). */
+	XLogRecPtr	wre_lsn;		/* WAL LSN being read. */
 	WALOpenSegment wre_seg;		/* Segment we tried to read from. */
 } WALReadError;
 
-- 
2.34.1

From 4923356f6143ecdb68c4a9ac41c05dcbba1253a3 Mon Sep 17 00:00:00 2001
From: Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com>
Date: Tue, 15 Nov 2022 09:25:14 +0000
Subject: [PATCH v3] Introduce pg_walfile_offset_lsn()

Author: Nathan Bossart, Bharath Rupireddy
---
 doc/src/sgml/func.sgml                       | 16 +++++
 src/backend/access/transam/xlogfuncs.c       | 62 ++++++++++++++++++++
 src/include/access/xlog_internal.h           |  8 +++
 src/include/catalog/pg_proc.dat              |  6 ++
 src/test/regress/expected/misc_functions.out | 26 ++++++++
 src/test/regress/sql/misc_functions.sql      | 12 ++++
 6 files changed, 130 insertions(+)

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 6e0425cb3d..26970345fd 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -25900,6 +25900,22 @@ LOG:  Grand total: 1651920 bytes in 201 blocks; 622360 free (88 chunks); 1029560
        </para></entry>
       </row>
 
+      <row>
+       <entry role="func_table_entry"><para role="func_signature">
+        <indexterm>
+         <primary>pg_walfile_offset_lsn</primary>
+        </indexterm>
+        <function>pg_walfile_offset_lsn</function> ( <parameter>file_name</parameter> <type>text</type>, <parameter>file_offset</parameter> <type>integer</type> )
+        <returnvalue>record</returnvalue>
+        ( <parameter>lsn</parameter> <type>pg_lsn</type>,
+        <parameter>timeline_id</parameter> <type>integer</type> )
+       </para>
+       <para>
+        Computes write-ahead log location and timline ID from a given WAL file
+        name and byte offset within that file.
+       </para></entry>
+      </row>
+
       <row>
        <entry role="func_table_entry"><para role="func_signature">
         <indexterm>
diff --git a/src/backend/access/transam/xlogfuncs.c b/src/backend/access/transam/xlogfuncs.c
index 487d5d9cac..ed2690b49a 100644
--- a/src/backend/access/transam/xlogfuncs.c
+++ b/src/backend/access/transam/xlogfuncs.c
@@ -339,6 +339,68 @@ pg_last_wal_replay_lsn(PG_FUNCTION_ARGS)
 	PG_RETURN_LSN(recptr);
 }
 
+/*
+ * Compute an LSN and timline ID given a WAL file name and decimal byte offset.
+ */
+Datum
+pg_walfile_offset_lsn(PG_FUNCTION_ARGS)
+{
+	char	   *fname = text_to_cstring(PG_GETARG_TEXT_PP(0));
+	int			offset = PG_GETARG_INT32(1);
+	TimeLineID	tli;
+	XLogSegNo	segno;
+	XLogRecPtr	lsn;
+	uint32		log;
+	uint32		seg;
+	Datum		values[2] = {0};
+	bool		isnull[2] = {0};
+	TupleDesc	resultTupleDesc;
+	HeapTuple	resultHeapTuple;
+	Datum		result;
+
+	if (!IsXLogFileName(fname))
+		ereport(ERROR,
+				(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+				 errmsg("invalid WAL file name \"%s\"", fname)));
+
+	XLogIdFromFileName(fname, &tli, &segno, &log, &seg, wal_segment_size);
+
+	if (seg >= XLogSegmentsPerXLogId(wal_segment_size) ||
+		(log == 0 && seg == 0) ||
+		segno == 0 ||
+		tli == 0)
+		ereport(ERROR,
+				(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+				 errmsg("invalid WAL file name \"%s\"", fname)));
+
+	if (offset < 0 || offset >= wal_segment_size)
+		ereport(ERROR,
+				(errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE),
+				 errmsg("offset must not be negative or greater than or equal to WAL segment size")));
+
+	/*
+	 * Construct a tuple descriptor for the result row.  This must match this
+	 * function's pg_proc entry!
+	 */
+	resultTupleDesc = CreateTemplateTupleDesc(2);
+	TupleDescInitEntry(resultTupleDesc, (AttrNumber) 1, "lsn",
+					   PG_LSNOID, -1, 0);
+	TupleDescInitEntry(resultTupleDesc, (AttrNumber) 2, "timeline_id",
+					   INT4OID, -1, 0);
+
+	resultTupleDesc = BlessTupleDesc(resultTupleDesc);
+
+	XLogSegNoOffsetToRecPtr(segno, offset, wal_segment_size, lsn);
+
+	values[0] = LSNGetDatum(lsn);
+	values[1] = UInt32GetDatum(tli);
+
+	resultHeapTuple = heap_form_tuple(resultTupleDesc, values, isnull);
+	result = HeapTupleGetDatum(resultHeapTuple);
+
+	PG_RETURN_DATUM(result);
+}
+
 /*
  * Compute an xlog file name and decimal byte offset given a WAL location,
  * such as is returned by pg_backup_stop() or pg_switch_wal().
diff --git a/src/include/access/xlog_internal.h b/src/include/access/xlog_internal.h
index 44291b337b..0ffe4142be 100644
--- a/src/include/access/xlog_internal.h
+++ b/src/include/access/xlog_internal.h
@@ -206,6 +206,14 @@ XLogFromFileName(const char *fname, TimeLineID *tli, XLogSegNo *logSegNo, int wa
 	*logSegNo = (uint64) log * XLogSegmentsPerXLogId(wal_segsz_bytes) + seg;
 }
 
+static inline void
+XLogIdFromFileName(const char *fname, TimeLineID *tli, XLogSegNo *logSegNo,
+				   uint32 *log, uint32 *seg, int wal_segsz_bytes)
+{
+	sscanf(fname, "%08X%08X%08X", tli, log, seg);
+	*logSegNo = (uint64) (*log) * XLogSegmentsPerXLogId(wal_segsz_bytes) + *seg;
+}
+
 static inline void
 XLogFilePath(char *path, TimeLineID tli, XLogSegNo logSegNo, int wal_segsz_bytes)
 {
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 9dbe9ec801..f1a732c172 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -6336,6 +6336,12 @@
   proargtypes => 'pg_lsn', proallargtypes => '{pg_lsn,text,int4}',
   proargmodes => '{i,o,o}', proargnames => '{lsn,file_name,file_offset}',
   prosrc => 'pg_walfile_name_offset' },
+{ oid => '8205',
+  descr => 'wal location and timeline ID given a wal filename and byte offset',
+  proname => 'pg_walfile_offset_lsn', prorettype => 'record',
+  proargtypes => 'text int4', proallargtypes => '{text,int4,pg_lsn,int4}',
+  proargmodes => '{i,i,o,o}', proargnames => '{file_name,file_offset,lsn,timeline_id}',
+  prosrc => 'pg_walfile_offset_lsn' },
 { oid => '2851', descr => 'wal filename, given a wal location',
   proname => 'pg_walfile_name', prorettype => 'text', proargtypes => 'pg_lsn',
   prosrc => 'pg_walfile_name' },
diff --git a/src/test/regress/expected/misc_functions.out b/src/test/regress/expected/misc_functions.out
index 88bb696ded..1b2ab4f25b 100644
--- a/src/test/regress/expected/misc_functions.out
+++ b/src/test/regress/expected/misc_functions.out
@@ -619,3 +619,29 @@ SELECT count(*) > 0 AS ok FROM pg_control_system();
  t
 (1 row)
 
+-- pg_walfile_name_offset
+SELECT * FROM pg_walfile_name_offset('1/F');
+        file_name         | file_offset 
+--------------------------+-------------
+ 000000010000000100000000 |          15
+(1 row)
+
+-- pg_walfile_offset_lsn
+SELECT * FROM pg_walfile_offset_lsn('invalid', 15);
+ERROR:  invalid WAL file name "invalid"
+SELECT * FROM pg_walfile_offset_lsn('0000000100000000FFFFFFFF', 15);
+ERROR:  invalid WAL file name "0000000100000000FFFFFFFF"
+SELECT * FROM pg_walfile_offset_lsn('000000010000000000000000', 15);
+ERROR:  invalid WAL file name "000000010000000000000000"
+SELECT * FROM pg_walfile_offset_lsn('000000000000000100000000', 15);
+ERROR:  invalid WAL file name "000000000000000100000000"
+SELECT * FROM pg_walfile_offset_lsn('000000010000000100000000', -1);
+ERROR:  offset must not be negative or greater than or equal to WAL segment size
+SELECT * FROM pg_walfile_offset_lsn('000000010000000100000000', 2000000000);
+ERROR:  offset must not be negative or greater than or equal to WAL segment size
+SELECT * FROM pg_walfile_offset_lsn('000000010000000100000000', 15);
+ lsn | timeline_id 
+-----+-------------
+ 1/F |           1
+(1 row)
+
diff --git a/src/test/regress/sql/misc_functions.sql b/src/test/regress/sql/misc_functions.sql
index b07e9e8dbb..9cb59cecf7 100644
--- a/src/test/regress/sql/misc_functions.sql
+++ b/src/test/regress/sql/misc_functions.sql
@@ -229,3 +229,15 @@ SELECT count(*) > 0 AS ok FROM pg_control_checkpoint();
 SELECT count(*) > 0 AS ok FROM pg_control_init();
 SELECT count(*) > 0 AS ok FROM pg_control_recovery();
 SELECT count(*) > 0 AS ok FROM pg_control_system();
+
+-- pg_walfile_name_offset
+SELECT * FROM pg_walfile_name_offset('1/F');
+
+-- pg_walfile_offset_lsn
+SELECT * FROM pg_walfile_offset_lsn('invalid', 15);
+SELECT * FROM pg_walfile_offset_lsn('0000000100000000FFFFFFFF', 15);
+SELECT * FROM pg_walfile_offset_lsn('000000010000000000000000', 15);
+SELECT * FROM pg_walfile_offset_lsn('000000000000000100000000', 15);
+SELECT * FROM pg_walfile_offset_lsn('000000010000000100000000', -1);
+SELECT * FROM pg_walfile_offset_lsn('000000010000000100000000', 2000000000);
+SELECT * FROM pg_walfile_offset_lsn('000000010000000100000000', 15);
-- 
2.34.1

From 452f6adc4617230f8843a9339331170cb6f85723 Mon Sep 17 00:00:00 2001
From: Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com>
Date: Sat, 3 Dec 2022 03:13:57 +0000
Subject: [PATCH v4] Emit record LSN in WAL read and validate error messages

The error messages reported during any failures in WAL record read
or validation currently contains offset within the WAL file which
is a bit hard to decode it to WAL LSN while debugging. Reporting
the WAL record LSN at which the WAL read or validation failure
occurrs saves time and makes life easier here.

Author: Bharath Rupireddy
Reviewed-by: Maxim Orlov, Michael Paquier
Reviewed-by: Kyotaro Horiguchi
Discussion: https://www.postgresql.org/message-id/CALj2ACWV=FCddsxcGbVOA=cvPyMr75YCFbSQT6g4KDj=gcJK4g@mail.gmail.com
---
 src/backend/access/transam/xlogreader.c   | 15 ++++++++++-----
 src/backend/access/transam/xlogrecovery.c | 13 ++++++++-----
 2 files changed, 18 insertions(+), 10 deletions(-)

diff --git a/src/backend/access/transam/xlogreader.c b/src/backend/access/transam/xlogreader.c
index 93f667b254..a38a80e049 100644
--- a/src/backend/access/transam/xlogreader.c
+++ b/src/backend/access/transam/xlogreader.c
@@ -1226,9 +1226,10 @@ XLogReaderValidatePageHeader(XLogReaderState *state, XLogRecPtr recptr,
 		XLogFileName(fname, state->seg.ws_tli, segno, state->segcxt.ws_segsize);
 
 		report_invalid_record(state,
-							  "invalid magic number %04X in WAL segment %s, offset %u",
+							  "invalid magic number %04X in WAL segment %s, LSN %X/%X, offset %u",
 							  hdr->xlp_magic,
 							  fname,
+							  LSN_FORMAT_ARGS(recptr),
 							  offset);
 		return false;
 	}
@@ -1240,9 +1241,10 @@ XLogReaderValidatePageHeader(XLogReaderState *state, XLogRecPtr recptr,
 		XLogFileName(fname, state->seg.ws_tli, segno, state->segcxt.ws_segsize);
 
 		report_invalid_record(state,
-							  "invalid info bits %04X in WAL segment %s, offset %u",
+							  "invalid info bits %04X in WAL segment %s, LSN %X/%X, offset %u",
 							  hdr->xlp_info,
 							  fname,
+							  LSN_FORMAT_ARGS(recptr),
 							  offset);
 		return false;
 	}
@@ -1281,9 +1283,10 @@ XLogReaderValidatePageHeader(XLogReaderState *state, XLogRecPtr recptr,
 
 		/* hmm, first page of file doesn't have a long header? */
 		report_invalid_record(state,
-							  "invalid info bits %04X in WAL segment %s, offset %u",
+							  "invalid info bits %04X in WAL segment %s, LSN %X/%X, offset %u",
 							  hdr->xlp_info,
 							  fname,
+							  LSN_FORMAT_ARGS(recptr),
 							  offset);
 		return false;
 	}
@@ -1300,9 +1303,10 @@ XLogReaderValidatePageHeader(XLogReaderState *state, XLogRecPtr recptr,
 		XLogFileName(fname, state->seg.ws_tli, segno, state->segcxt.ws_segsize);
 
 		report_invalid_record(state,
-							  "unexpected pageaddr %X/%X in WAL segment %s, offset %u",
+							  "unexpected pageaddr %X/%X in WAL segment %s, LSN %X/%X, offset %u",
 							  LSN_FORMAT_ARGS(hdr->xlp_pageaddr),
 							  fname,
+							  LSN_FORMAT_ARGS(recptr),
 							  offset);
 		return false;
 	}
@@ -1325,10 +1329,11 @@ XLogReaderValidatePageHeader(XLogReaderState *state, XLogRecPtr recptr,
 			XLogFileName(fname, state->seg.ws_tli, segno, state->segcxt.ws_segsize);
 
 			report_invalid_record(state,
-								  "out-of-sequence timeline ID %u (after %u) in WAL segment %s, offset %u",
+								  "out-of-sequence timeline ID %u (after %u) in WAL segment %s, LSN %X/%X, offset %u",
 								  hdr->xlp_tli,
 								  state->latestPageTLI,
 								  fname,
+								  LSN_FORMAT_ARGS(recptr),
 								  offset);
 			return false;
 		}
diff --git a/src/backend/access/transam/xlogrecovery.c b/src/backend/access/transam/xlogrecovery.c
index 41ffc57da9..97b882564f 100644
--- a/src/backend/access/transam/xlogrecovery.c
+++ b/src/backend/access/transam/xlogrecovery.c
@@ -3075,9 +3075,10 @@ ReadRecord(XLogPrefetcher *xlogprefetcher, int emode,
 			XLogFileName(fname, xlogreader->seg.ws_tli, segno,
 						 wal_segment_size);
 			ereport(emode_for_corrupt_record(emode, xlogreader->EndRecPtr),
-					(errmsg("unexpected timeline ID %u in WAL segment %s, offset %u",
+					(errmsg("unexpected timeline ID %u in WAL segment %s, LSN %X/%X, offset %u",
 							xlogreader->latestPageTLI,
 							fname,
+							LSN_FORMAT_ARGS(xlogreader->latestPagePtr),
 							offset)));
 			record = NULL;
 		}
@@ -3280,14 +3281,16 @@ retry:
 			errno = save_errno;
 			ereport(emode_for_corrupt_record(emode, targetPagePtr + reqLen),
 					(errcode_for_file_access(),
-					 errmsg("could not read from WAL segment %s, offset %u: %m",
-							fname, readOff)));
+					 errmsg("could not read from WAL segment %s, LSN %X/%X, offset %u: %m",
+							fname, LSN_FORMAT_ARGS(targetPagePtr),
+							readOff)));
 		}
 		else
 			ereport(emode_for_corrupt_record(emode, targetPagePtr + reqLen),
 					(errcode(ERRCODE_DATA_CORRUPTED),
-					 errmsg("could not read from WAL segment %s, offset %u: read %d of %zu",
-							fname, readOff, r, (Size) XLOG_BLCKSZ)));
+					 errmsg("could not read from WAL segment %s, LSN %X/%X, offset %u: read %d of %zu",
+							fname, LSN_FORMAT_ARGS(targetPagePtr),
+							readOff, r, (Size) XLOG_BLCKSZ)));
 		goto next_record_is_invalid;
 	}
 	pgstat_report_wait_end();
-- 
2.34.1

From a76f7187cc54d099abc5de86f2aadb77c72c2f21 Mon Sep 17 00:00:00 2001
From: Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com>
Date: Sat, 3 Dec 2022 03:17:34 +0000
Subject: [PATCH v4] Add new a function to convert offset to LSN

Introduce a new utility function pg_walfile_offset_lsn() that
converts a given offset within the WAL file to a valid WAL LSN.
This aids developers in debugging.

Authors: Nathan Bossart, Bharath Rupireddy
Reviewed-by: Maxim Orlov, Michael Paquier
Reviewed-by: Alvaro Herrera
Discussion: https://www.postgresql.org/message-id/CALj2ACWV=FCddsxcGbVOA=cvPyMr75YCFbSQT6g4KDj=gcJK4g@mail.gmail.com
---
 doc/src/sgml/func.sgml                       | 16 +++++
 src/backend/access/transam/xlogfuncs.c       | 62 ++++++++++++++++++++
 src/include/access/xlog_internal.h           |  8 +++
 src/include/catalog/pg_proc.dat              |  6 ++
 src/test/regress/expected/misc_functions.out | 26 ++++++++
 src/test/regress/sql/misc_functions.sql      | 12 ++++
 6 files changed, 130 insertions(+)

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 2052d3c844..cb99fc64d7 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -25894,6 +25894,22 @@ LOG:  Grand total: 1651920 bytes in 201 blocks; 622360 free (88 chunks); 1029560
        </para></entry>
       </row>
 
+      <row>
+       <entry role="func_table_entry"><para role="func_signature">
+        <indexterm>
+         <primary>pg_walfile_offset_lsn</primary>
+        </indexterm>
+        <function>pg_walfile_offset_lsn</function> ( <parameter>file_name</parameter> <type>text</type>, <parameter>file_offset</parameter> <type>integer</type> )
+        <returnvalue>record</returnvalue>
+        ( <parameter>lsn</parameter> <type>pg_lsn</type>,
+        <parameter>timeline_id</parameter> <type>integer</type> )
+       </para>
+       <para>
+        Computes write-ahead log location and timline ID from a given WAL file
+        name and byte offset within that file.
+       </para></entry>
+      </row>
+
       <row>
        <entry role="func_table_entry"><para role="func_signature">
         <indexterm>
diff --git a/src/backend/access/transam/xlogfuncs.c b/src/backend/access/transam/xlogfuncs.c
index 487d5d9cac..ed2690b49a 100644
--- a/src/backend/access/transam/xlogfuncs.c
+++ b/src/backend/access/transam/xlogfuncs.c
@@ -339,6 +339,68 @@ pg_last_wal_replay_lsn(PG_FUNCTION_ARGS)
 	PG_RETURN_LSN(recptr);
 }
 
+/*
+ * Compute an LSN and timline ID given a WAL file name and decimal byte offset.
+ */
+Datum
+pg_walfile_offset_lsn(PG_FUNCTION_ARGS)
+{
+	char	   *fname = text_to_cstring(PG_GETARG_TEXT_PP(0));
+	int			offset = PG_GETARG_INT32(1);
+	TimeLineID	tli;
+	XLogSegNo	segno;
+	XLogRecPtr	lsn;
+	uint32		log;
+	uint32		seg;
+	Datum		values[2] = {0};
+	bool		isnull[2] = {0};
+	TupleDesc	resultTupleDesc;
+	HeapTuple	resultHeapTuple;
+	Datum		result;
+
+	if (!IsXLogFileName(fname))
+		ereport(ERROR,
+				(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+				 errmsg("invalid WAL file name \"%s\"", fname)));
+
+	XLogIdFromFileName(fname, &tli, &segno, &log, &seg, wal_segment_size);
+
+	if (seg >= XLogSegmentsPerXLogId(wal_segment_size) ||
+		(log == 0 && seg == 0) ||
+		segno == 0 ||
+		tli == 0)
+		ereport(ERROR,
+				(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+				 errmsg("invalid WAL file name \"%s\"", fname)));
+
+	if (offset < 0 || offset >= wal_segment_size)
+		ereport(ERROR,
+				(errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE),
+				 errmsg("offset must not be negative or greater than or equal to WAL segment size")));
+
+	/*
+	 * Construct a tuple descriptor for the result row.  This must match this
+	 * function's pg_proc entry!
+	 */
+	resultTupleDesc = CreateTemplateTupleDesc(2);
+	TupleDescInitEntry(resultTupleDesc, (AttrNumber) 1, "lsn",
+					   PG_LSNOID, -1, 0);
+	TupleDescInitEntry(resultTupleDesc, (AttrNumber) 2, "timeline_id",
+					   INT4OID, -1, 0);
+
+	resultTupleDesc = BlessTupleDesc(resultTupleDesc);
+
+	XLogSegNoOffsetToRecPtr(segno, offset, wal_segment_size, lsn);
+
+	values[0] = LSNGetDatum(lsn);
+	values[1] = UInt32GetDatum(tli);
+
+	resultHeapTuple = heap_form_tuple(resultTupleDesc, values, isnull);
+	result = HeapTupleGetDatum(resultHeapTuple);
+
+	PG_RETURN_DATUM(result);
+}
+
 /*
  * Compute an xlog file name and decimal byte offset given a WAL location,
  * such as is returned by pg_backup_stop() or pg_switch_wal().
diff --git a/src/include/access/xlog_internal.h b/src/include/access/xlog_internal.h
index e5fc66966b..4405036c3e 100644
--- a/src/include/access/xlog_internal.h
+++ b/src/include/access/xlog_internal.h
@@ -206,6 +206,14 @@ XLogFromFileName(const char *fname, TimeLineID *tli, XLogSegNo *logSegNo, int wa
 	*logSegNo = (uint64) log * XLogSegmentsPerXLogId(wal_segsz_bytes) + seg;
 }
 
+static inline void
+XLogIdFromFileName(const char *fname, TimeLineID *tli, XLogSegNo *logSegNo,
+				   uint32 *log, uint32 *seg, int wal_segsz_bytes)
+{
+	sscanf(fname, "%08X%08X%08X", tli, log, seg);
+	*logSegNo = (uint64) (*log) * XLogSegmentsPerXLogId(wal_segsz_bytes) + *seg;
+}
+
 static inline void
 XLogFilePath(char *path, TimeLineID tli, XLogSegNo logSegNo, int wal_segsz_bytes)
 {
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index f9301b2627..f32b77d174 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -6362,6 +6362,12 @@
   proargtypes => 'pg_lsn', proallargtypes => '{pg_lsn,text,int4}',
   proargmodes => '{i,o,o}', proargnames => '{lsn,file_name,file_offset}',
   prosrc => 'pg_walfile_name_offset' },
+{ oid => '8205',
+  descr => 'wal location and timeline ID given a wal filename and byte offset',
+  proname => 'pg_walfile_offset_lsn', prorettype => 'record',
+  proargtypes => 'text int4', proallargtypes => '{text,int4,pg_lsn,int4}',
+  proargmodes => '{i,i,o,o}', proargnames => '{file_name,file_offset,lsn,timeline_id}',
+  prosrc => 'pg_walfile_offset_lsn' },
 { oid => '2851', descr => 'wal filename, given a wal location',
   proname => 'pg_walfile_name', prorettype => 'text', proargtypes => 'pg_lsn',
   prosrc => 'pg_walfile_name' },
diff --git a/src/test/regress/expected/misc_functions.out b/src/test/regress/expected/misc_functions.out
index 88bb696ded..1b2ab4f25b 100644
--- a/src/test/regress/expected/misc_functions.out
+++ b/src/test/regress/expected/misc_functions.out
@@ -619,3 +619,29 @@ SELECT count(*) > 0 AS ok FROM pg_control_system();
  t
 (1 row)
 
+-- pg_walfile_name_offset
+SELECT * FROM pg_walfile_name_offset('1/F');
+        file_name         | file_offset 
+--------------------------+-------------
+ 000000010000000100000000 |          15
+(1 row)
+
+-- pg_walfile_offset_lsn
+SELECT * FROM pg_walfile_offset_lsn('invalid', 15);
+ERROR:  invalid WAL file name "invalid"
+SELECT * FROM pg_walfile_offset_lsn('0000000100000000FFFFFFFF', 15);
+ERROR:  invalid WAL file name "0000000100000000FFFFFFFF"
+SELECT * FROM pg_walfile_offset_lsn('000000010000000000000000', 15);
+ERROR:  invalid WAL file name "000000010000000000000000"
+SELECT * FROM pg_walfile_offset_lsn('000000000000000100000000', 15);
+ERROR:  invalid WAL file name "000000000000000100000000"
+SELECT * FROM pg_walfile_offset_lsn('000000010000000100000000', -1);
+ERROR:  offset must not be negative or greater than or equal to WAL segment size
+SELECT * FROM pg_walfile_offset_lsn('000000010000000100000000', 2000000000);
+ERROR:  offset must not be negative or greater than or equal to WAL segment size
+SELECT * FROM pg_walfile_offset_lsn('000000010000000100000000', 15);
+ lsn | timeline_id 
+-----+-------------
+ 1/F |           1
+(1 row)
+
diff --git a/src/test/regress/sql/misc_functions.sql b/src/test/regress/sql/misc_functions.sql
index b07e9e8dbb..9cb59cecf7 100644
--- a/src/test/regress/sql/misc_functions.sql
+++ b/src/test/regress/sql/misc_functions.sql
@@ -229,3 +229,15 @@ SELECT count(*) > 0 AS ok FROM pg_control_checkpoint();
 SELECT count(*) > 0 AS ok FROM pg_control_init();
 SELECT count(*) > 0 AS ok FROM pg_control_recovery();
 SELECT count(*) > 0 AS ok FROM pg_control_system();
+
+-- pg_walfile_name_offset
+SELECT * FROM pg_walfile_name_offset('1/F');
+
+-- pg_walfile_offset_lsn
+SELECT * FROM pg_walfile_offset_lsn('invalid', 15);
+SELECT * FROM pg_walfile_offset_lsn('0000000100000000FFFFFFFF', 15);
+SELECT * FROM pg_walfile_offset_lsn('000000010000000000000000', 15);
+SELECT * FROM pg_walfile_offset_lsn('000000000000000100000000', 15);
+SELECT * FROM pg_walfile_offset_lsn('000000010000000100000000', -1);
+SELECT * FROM pg_walfile_offset_lsn('000000010000000100000000', 2000000000);
+SELECT * FROM pg_walfile_offset_lsn('000000010000000100000000', 15);
-- 
2.34.1

From e5f102911dd7670c96d9826b2e5dd377235f1717 Mon Sep 17 00:00:00 2001
From: Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com>
Date: Tue, 13 Dec 2022 12:05:11 +0000
Subject: [PATCH v5] Add utility functions to disset WAL file name and compute
 offset

---
 doc/src/sgml/func.sgml                       | 33 +++++++++++
 src/backend/access/transam/xlogfuncs.c       | 62 ++++++++++++++++++++
 src/backend/catalog/system_functions.sql     | 10 ++++
 src/include/access/xlog_internal.h           |  8 +++
 src/include/catalog/pg_proc.dat              | 12 ++++
 src/test/regress/expected/misc_functions.out | 53 +++++++++++++++++
 src/test/regress/sql/misc_functions.sql      | 17 ++++++
 7 files changed, 195 insertions(+)

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index ad31fdb737..894f36bcb6 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -26099,6 +26099,39 @@ LOG:  Grand total: 1651920 bytes in 201 blocks; 622360 free (88 chunks); 1029560
        </para></entry>
       </row>
 
+      <row>
+       <entry role="func_table_entry"><para role="func_signature">
+        <indexterm>
+         <primary>pg_dissect_walfile_name</primary>
+        </indexterm>
+        <function>pg_dissect_walfile_name</function> ( <parameter>file_name</parameter> <type>text</type> )
+        <returnvalue>record</returnvalue>
+        ( <parameter>segno</parameter> <type>numeric</type>,
+        <parameter>timeline_id</parameter> <type>integer</type> )
+       </para>
+       <para>
+        Computes physical write-ahead log (WAL) file sequence number and
+        timeline ID from a given WAL file name.
+       </para></entry>
+      </row>
+
+      <row>
+       <entry role="func_table_entry"><para role="func_signature">
+        <indexterm>
+         <primary>pg_walfile_offset_lsn</primary>
+        </indexterm>
+        <function>pg_walfile_offset_lsn</function> ( <parameter>file_name</parameter> <type>text</type>, <parameter>file_offset</parameter> <type>integer</type> )
+        <returnvalue>pg_lsn</returnvalue>
+       </para>
+       <para>
+        Computes write-ahead log (WAL) location from a given WAL file name
+        and byte offset within that file. When
+        <parameter>file_offset</parameter> is negative or greater than
+        equal to <varname>wal_segment_size</varname>, this function returns
+        <literal>NULL</literal>.
+       </para></entry>
+      </row>
+
       <row>
        <entry role="func_table_entry"><para role="func_signature">
         <indexterm>
diff --git a/src/backend/access/transam/xlogfuncs.c b/src/backend/access/transam/xlogfuncs.c
index 487d5d9cac..32cf431aca 100644
--- a/src/backend/access/transam/xlogfuncs.c
+++ b/src/backend/access/transam/xlogfuncs.c
@@ -432,6 +432,68 @@ pg_walfile_name(PG_FUNCTION_ARGS)
 	PG_RETURN_TEXT_P(cstring_to_text(xlogfilename));
 }
 
+/*
+ * Compute segno (physical WAL file sequence number) and timeline ID given a
+ * WAL file name.
+ */
+Datum
+pg_dissect_walfile_name(PG_FUNCTION_ARGS)
+{
+#define PG_DISSECT_WALFILE_NAME_COLS 2
+	char	   *fname = text_to_cstring(PG_GETARG_TEXT_PP(0));
+	char	   *fname_upper;
+	char	   *p;
+	TimeLineID	tli;
+	XLogSegNo	segno;
+	uint32		log;
+	uint32		seg;
+	Datum		values[PG_DISSECT_WALFILE_NAME_COLS] = {0};
+	bool		isnull[PG_DISSECT_WALFILE_NAME_COLS] = {0};
+	TupleDesc	tupdesc;
+	HeapTuple	tuple;
+	char		buf[256];
+	Datum		result;
+
+	fname_upper = pstrdup(fname);
+
+	/* Capitalize WAL file name. */
+	for (p = fname_upper; *p; p++)
+		*p = pg_toupper((unsigned char) *p);
+
+	if (!IsXLogFileName(fname_upper))
+		ereport(ERROR,
+				(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+				 errmsg("invalid WAL file name \"%s\"", fname)));
+
+	XLogIdFromFileName(fname_upper, &tli, &segno, &log, &seg, wal_segment_size);
+
+	if (seg >= XLogSegmentsPerXLogId(wal_segment_size) ||
+		segno == 0 ||
+		tli == 0)
+		ereport(ERROR,
+				(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+				 errmsg("invalid WAL file name \"%s\"", fname)));
+
+	if (get_call_result_type(fcinfo, NULL, &tupdesc) != TYPEFUNC_COMPOSITE)
+		elog(ERROR, "return type must be a row type");
+
+	/* Convert to numeric. */
+	snprintf(buf, sizeof buf, UINT64_FORMAT, segno);
+	values[0] = DirectFunctionCall3(numeric_in,
+									CStringGetDatum(buf),
+									ObjectIdGetDatum(0),
+									Int32GetDatum(-1));
+
+	values[1] = UInt32GetDatum(tli);
+
+	tuple = heap_form_tuple(tupdesc, values, isnull);
+	result = HeapTupleGetDatum(tuple);
+
+	PG_RETURN_DATUM(result);
+
+#undef PG_DISSECT_WALFILE_NAME_COLS
+}
+
 /*
  * pg_wal_replay_pause - Request to pause recovery
  *
diff --git a/src/backend/catalog/system_functions.sql b/src/backend/catalog/system_functions.sql
index 52517a6531..5dde5007ce 100644
--- a/src/backend/catalog/system_functions.sql
+++ b/src/backend/catalog/system_functions.sql
@@ -397,6 +397,16 @@ CREATE OR REPLACE FUNCTION
   RETURNS boolean STRICT VOLATILE LANGUAGE INTERNAL AS 'pg_terminate_backend'
   PARALLEL SAFE;
 
+CREATE OR REPLACE FUNCTION pg_walfile_offset_lsn(IN file_name TEXT,
+    IN file_offset INT4,
+    OUT lsn pg_lsn)
+AS $$ SELECT CASE WHEN $2 < 0 THEN NULL
+              WHEN $2 >= ps.setting::int THEN NULL
+              ELSE '0/0'::pg_lsn + pd.segno * ps.setting::int + $2 END
+    FROM pg_dissect_walfile_name($1) pd, pg_show_all_settings() ps
+    WHERE ps.name = 'wal_segment_size'; $$
+LANGUAGE SQL STRICT STABLE PARALLEL SAFE;
+
 -- legacy definition for compatibility with 9.3
 CREATE OR REPLACE FUNCTION
   json_populate_record(base anyelement, from_json json, use_json_as_text boolean DEFAULT false)
diff --git a/src/include/access/xlog_internal.h b/src/include/access/xlog_internal.h
index e5fc66966b..4405036c3e 100644
--- a/src/include/access/xlog_internal.h
+++ b/src/include/access/xlog_internal.h
@@ -206,6 +206,14 @@ XLogFromFileName(const char *fname, TimeLineID *tli, XLogSegNo *logSegNo, int wa
 	*logSegNo = (uint64) log * XLogSegmentsPerXLogId(wal_segsz_bytes) + seg;
 }
 
+static inline void
+XLogIdFromFileName(const char *fname, TimeLineID *tli, XLogSegNo *logSegNo,
+				   uint32 *log, uint32 *seg, int wal_segsz_bytes)
+{
+	sscanf(fname, "%08X%08X%08X", tli, log, seg);
+	*logSegNo = (uint64) (*log) * XLogSegmentsPerXLogId(wal_segsz_bytes) + *seg;
+}
+
 static inline void
 XLogFilePath(char *path, TimeLineID tli, XLogSegNo logSegNo, int wal_segsz_bytes)
 {
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 719599649a..af5e6ce8f6 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -6365,6 +6365,18 @@
 { oid => '2851', descr => 'wal filename, given a wal location',
   proname => 'pg_walfile_name', prorettype => 'text', proargtypes => 'pg_lsn',
   prosrc => 'pg_walfile_name' },
+{ oid => '8205',
+  descr => 'physical wal file sequence number and timeline ID given a wal filename',
+  proname => 'pg_dissect_walfile_name', prorettype => 'record',
+  proargtypes => 'text', proallargtypes => '{text,numeric,int4}',
+  provolatile => 's', proargmodes => '{i,o,o}',
+  proargnames => '{file_name,segno,timeline_id}',
+  prosrc => 'pg_dissect_walfile_name' },
+{ oid => '8206',
+  descr => 'wal location given a wal filename and byte offset',
+  proname => 'pg_walfile_offset_lsn', prolang => 'sql',
+  prorettype => 'pg_lsn', proargtypes => 'text int4',
+  provolatile => 's', prosrc => 'see system_functions.sql' },
 
 { oid => '3165', descr => 'difference in bytes, given two wal locations',
   proname => 'pg_wal_lsn_diff', prorettype => 'numeric',
diff --git a/src/test/regress/expected/misc_functions.out b/src/test/regress/expected/misc_functions.out
index 88bb696ded..de603e5d9c 100644
--- a/src/test/regress/expected/misc_functions.out
+++ b/src/test/regress/expected/misc_functions.out
@@ -619,3 +619,56 @@ SELECT count(*) > 0 AS ok FROM pg_control_system();
  t
 (1 row)
 
+-- pg_walfile_name_offset
+SELECT * FROM pg_walfile_name_offset('1/F');
+        file_name         | file_offset 
+--------------------------+-------------
+ 000000010000000100000000 |          15
+(1 row)
+
+-- pg_dissect_walfile_name
+SELECT count(*) > 0 AS ok FROM pg_dissect_walfile_name('invalid');  -- ERROR
+ERROR:  invalid WAL file name "invalid"
+SELECT count(*) > 0 AS ok FROM pg_dissect_walfile_name('000000010000000100000000'); -- OK
+ ok 
+----
+ t
+(1 row)
+
+SELECT count(*) > 0 AS ok FROM pg_dissect_walfile_name('0000000100000001000000af'); -- OK
+ ok 
+----
+ t
+(1 row)
+
+-- pg_walfile_offset_lsn
+SELECT * FROM pg_walfile_offset_lsn('invalid', 15); -- ERROR
+ERROR:  invalid WAL file name "invalid"
+CONTEXT:  SQL function "pg_walfile_offset_lsn" statement 1
+SELECT * FROM pg_walfile_offset_lsn('0000000100000000FFFFFFFF', 15);  -- ERROR
+ERROR:  invalid WAL file name "0000000100000000FFFFFFFF"
+CONTEXT:  SQL function "pg_walfile_offset_lsn" statement 1
+SELECT * FROM pg_walfile_offset_lsn('000000010000000000000000', 15);  -- ERROR
+ERROR:  invalid WAL file name "000000010000000000000000"
+CONTEXT:  SQL function "pg_walfile_offset_lsn" statement 1
+SELECT * FROM pg_walfile_offset_lsn('000000000000000100000000', 15);  -- ERROR
+ERROR:  invalid WAL file name "000000000000000100000000"
+CONTEXT:  SQL function "pg_walfile_offset_lsn" statement 1
+SELECT * FROM pg_walfile_offset_lsn('000000010000000100000000', -1);  -- OK, RETURNS NULL
+ lsn 
+-----
+ 
+(1 row)
+
+SELECT * FROM pg_walfile_offset_lsn('000000010000000100000000', 2000000000);  -- OK, RETURNS NULL
+ lsn 
+-----
+ 
+(1 row)
+
+SELECT * FROM pg_walfile_offset_lsn('000000010000000100000000', 15);  -- OK
+ lsn 
+-----
+ 1/F
+(1 row)
+
diff --git a/src/test/regress/sql/misc_functions.sql b/src/test/regress/sql/misc_functions.sql
index b07e9e8dbb..e59819c1f2 100644
--- a/src/test/regress/sql/misc_functions.sql
+++ b/src/test/regress/sql/misc_functions.sql
@@ -229,3 +229,20 @@ SELECT count(*) > 0 AS ok FROM pg_control_checkpoint();
 SELECT count(*) > 0 AS ok FROM pg_control_init();
 SELECT count(*) > 0 AS ok FROM pg_control_recovery();
 SELECT count(*) > 0 AS ok FROM pg_control_system();
+
+-- pg_walfile_name_offset
+SELECT * FROM pg_walfile_name_offset('1/F');
+
+-- pg_dissect_walfile_name
+SELECT count(*) > 0 AS ok FROM pg_dissect_walfile_name('invalid');  -- ERROR
+SELECT count(*) > 0 AS ok FROM pg_dissect_walfile_name('000000010000000100000000'); -- OK
+SELECT count(*) > 0 AS ok FROM pg_dissect_walfile_name('0000000100000001000000af'); -- OK
+
+-- pg_walfile_offset_lsn
+SELECT * FROM pg_walfile_offset_lsn('invalid', 15); -- ERROR
+SELECT * FROM pg_walfile_offset_lsn('0000000100000000FFFFFFFF', 15);  -- ERROR
+SELECT * FROM pg_walfile_offset_lsn('000000010000000000000000', 15);  -- ERROR
+SELECT * FROM pg_walfile_offset_lsn('000000000000000100000000', 15);  -- ERROR
+SELECT * FROM pg_walfile_offset_lsn('000000010000000100000000', -1);  -- OK, RETURNS NULL
+SELECT * FROM pg_walfile_offset_lsn('000000010000000100000000', 2000000000);  -- OK, RETURNS NULL
+SELECT * FROM pg_walfile_offset_lsn('000000010000000100000000', 15);  -- OK
-- 
2.34.1

From 25fb0848cf3220a5fc9638c2cfd116e5390be887 Mon Sep 17 00:00:00 2001
From: Michael Paquier <michael@paquier.xyz>
Date: Mon, 19 Dec 2022 17:02:16 +0900
Subject: [PATCH v6] Add utility functions to disset WAL file name and compute
 offset

---
 src/include/catalog/pg_proc.dat              |  7 +++
 src/backend/access/transam/xlogfuncs.c       | 53 ++++++++++++++++++++
 src/test/regress/expected/misc_functions.out | 18 +++++++
 src/test/regress/sql/misc_functions.sql      |  8 +++
 doc/src/sgml/func.sgml                       | 16 ++++++
 5 files changed, 102 insertions(+)

diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 719599649a..79cfd1378d 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -6365,6 +6365,13 @@
 { oid => '2851', descr => 'wal filename, given a wal location',
   proname => 'pg_walfile_name', prorettype => 'text', proargtypes => 'pg_lsn',
   prosrc => 'pg_walfile_name' },
+{ oid => '8205',
+  descr => 'sequence number and timeline ID given a wal filename',
+  proname => 'pg_dissect_walfile_name', prorettype => 'record',
+  proargtypes => 'text', proallargtypes => '{text,numeric,int8}',
+  provolatile => 's', proargmodes => '{i,o,o}',
+  proargnames => '{file_name,segno,timeline_id}',
+  prosrc => 'pg_dissect_walfile_name' },
 
 { oid => '3165', descr => 'difference in bytes, given two wal locations',
   proname => 'pg_wal_lsn_diff', prorettype => 'numeric',
diff --git a/src/backend/access/transam/xlogfuncs.c b/src/backend/access/transam/xlogfuncs.c
index 487d5d9cac..0a31837ef1 100644
--- a/src/backend/access/transam/xlogfuncs.c
+++ b/src/backend/access/transam/xlogfuncs.c
@@ -432,6 +432,59 @@ pg_walfile_name(PG_FUNCTION_ARGS)
 	PG_RETURN_TEXT_P(cstring_to_text(xlogfilename));
 }
 
+/*
+ * Extract the sequence number and the timeline ID from given a WAL file
+ * name.
+ */
+Datum
+pg_dissect_walfile_name(PG_FUNCTION_ARGS)
+{
+#define PG_DISSECT_WALFILE_NAME_COLS 2
+	char	   *fname = text_to_cstring(PG_GETARG_TEXT_PP(0));
+	char	   *fname_upper;
+	char	   *p;
+	TimeLineID	tli;
+	XLogSegNo	segno;
+	Datum		values[PG_DISSECT_WALFILE_NAME_COLS] = {0};
+	bool		isnull[PG_DISSECT_WALFILE_NAME_COLS] = {0};
+	TupleDesc	tupdesc;
+	HeapTuple	tuple;
+	char		buf[256];
+	Datum		result;
+
+	fname_upper = pstrdup(fname);
+
+	/* Capitalize WAL file name. */
+	for (p = fname_upper; *p; p++)
+		*p = pg_toupper((unsigned char) *p);
+
+	if (!IsXLogFileName(fname_upper))
+		ereport(ERROR,
+				(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+				 errmsg("invalid WAL file name \"%s\"", fname)));
+
+	XLogFromFileName(fname_upper, &tli, &segno, wal_segment_size);
+
+	if (get_call_result_type(fcinfo, NULL, &tupdesc) != TYPEFUNC_COMPOSITE)
+		elog(ERROR, "return type must be a row type");
+
+	/* Convert to numeric. */
+	snprintf(buf, sizeof buf, UINT64_FORMAT, segno);
+	values[0] = DirectFunctionCall3(numeric_in,
+									CStringGetDatum(buf),
+									ObjectIdGetDatum(0),
+									Int32GetDatum(-1));
+
+	values[1] = Int64GetDatum(tli);
+
+	tuple = heap_form_tuple(tupdesc, values, isnull);
+	result = HeapTupleGetDatum(tuple);
+
+	PG_RETURN_DATUM(result);
+
+#undef PG_DISSECT_WALFILE_NAME_COLS
+}
+
 /*
  * pg_wal_replay_pause - Request to pause recovery
  *
diff --git a/src/test/regress/expected/misc_functions.out b/src/test/regress/expected/misc_functions.out
index 88bb696ded..fb676e9971 100644
--- a/src/test/regress/expected/misc_functions.out
+++ b/src/test/regress/expected/misc_functions.out
@@ -619,3 +619,21 @@ SELECT count(*) > 0 AS ok FROM pg_control_system();
  t
 (1 row)
 
+-- pg_dissect_walfile_name
+SELECT segno > 0 AS ok_segno, timeline_id
+  FROM pg_dissect_walfile_name('invalid');
+ERROR:  invalid WAL file name "invalid"
+SELECT segno > 0 AS ok_segno, timeline_id
+  FROM pg_dissect_walfile_name('000000010000000100000000');
+ ok_segno | timeline_id 
+----------+-------------
+ t        |           1
+(1 row)
+
+SELECT segno > 0 AS ok_segno, timeline_id
+  FROM pg_dissect_walfile_name('FFFFFFFF00000001000000af');
+ ok_segno | timeline_id 
+----------+-------------
+ t        |  4294967295
+(1 row)
+
diff --git a/src/test/regress/sql/misc_functions.sql b/src/test/regress/sql/misc_functions.sql
index b07e9e8dbb..74cfc31913 100644
--- a/src/test/regress/sql/misc_functions.sql
+++ b/src/test/regress/sql/misc_functions.sql
@@ -229,3 +229,11 @@ SELECT count(*) > 0 AS ok FROM pg_control_checkpoint();
 SELECT count(*) > 0 AS ok FROM pg_control_init();
 SELECT count(*) > 0 AS ok FROM pg_control_recovery();
 SELECT count(*) > 0 AS ok FROM pg_control_system();
+
+-- pg_dissect_walfile_name
+SELECT segno > 0 AS ok_segno, timeline_id
+  FROM pg_dissect_walfile_name('invalid');
+SELECT segno > 0 AS ok_segno, timeline_id
+  FROM pg_dissect_walfile_name('000000010000000100000000');
+SELECT segno > 0 AS ok_segno, timeline_id
+  FROM pg_dissect_walfile_name('FFFFFFFF00000001000000af');
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 1cd8b11334..2f05b06f14 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -26098,6 +26098,22 @@ LOG:  Grand total: 1651920 bytes in 201 blocks; 622360 free (88 chunks); 1029560
        </para></entry>
       </row>
 
+      <row>
+       <entry role="func_table_entry"><para role="func_signature">
+        <indexterm>
+         <primary>pg_dissect_walfile_name</primary>
+        </indexterm>
+        <function>pg_dissect_walfile_name</function> ( <parameter>file_name</parameter> <type>text</type> )
+        <returnvalue>record</returnvalue>
+        ( <parameter>segno</parameter> <type>numeric</type>,
+        <parameter>timeline_id</parameter> <type>bigint</type> )
+       </para>
+       <para>
+        Extract the file sequence number and timeline ID from a WAL file
+        name.
+       </para></entry>
+      </row>
+
       <row>
        <entry role="func_table_entry"><para role="func_signature">
         <indexterm>
-- 
2.39.0

From 85232f5ec56e37097146223d4b3841dc3bac4005 Mon Sep 17 00:00:00 2001
From: Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com>
Date: Tue, 20 Dec 2022 12:32:38 +0000
Subject: [PATCH v1] Rename pg_dissect_walfile_name() to
 pg_split_walfile_name()

---
 doc/src/sgml/func.sgml                       |  8 ++++----
 src/backend/access/transam/xlogfuncs.c       | 10 +++++-----
 src/include/catalog/pg_proc.dat              |  4 ++--
 src/test/regress/expected/misc_functions.out | 10 +++++-----
 src/test/regress/sql/misc_functions.sql      | 10 +++++-----
 5 files changed, 21 insertions(+), 21 deletions(-)

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 1f63dc6dba..b1b742e4e0 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -26101,9 +26101,9 @@ LOG:  Grand total: 1651920 bytes in 201 blocks; 622360 free (88 chunks); 1029560
       <row>
        <entry role="func_table_entry"><para role="func_signature">
         <indexterm>
-         <primary>pg_dissect_walfile_name</primary>
+         <primary>pg_split_walfile_name</primary>
         </indexterm>
-        <function>pg_dissect_walfile_name</function> ( <parameter>file_name</parameter> <type>text</type> )
+        <function>pg_split_walfile_name</function> ( <parameter>file_name</parameter> <type>text</type> )
         <returnvalue>record</returnvalue>
         ( <parameter>segno</parameter> <type>numeric</type>,
         <parameter>timeline_id</parameter> <type>bigint</type> )
@@ -26172,13 +26172,13 @@ postgres=# SELECT * FROM pg_walfile_name_offset((pg_backup_stop()).lsn);
    </para>
 
    <para>
-    <function>pg_dissect_walfile_name</function> is useful to compute a
+    <function>pg_split_walfile_name</function> is useful to compute a
     <acronym>LSN</acronym> from a file offset and WAL file name, for example:
 <programlisting>
 postgres=# \set file_name '000000010000000100C000AB'
 postgres=# \set offset 256
 postgres=# SELECT '0/0'::pg_lsn + pd.segno * ps.setting::int + :offset AS lsn
-  FROM pg_dissect_walfile_name(:'file_name') pd,
+  FROM pg_split_walfile_name(:'file_name') pd,
        pg_show_all_settings() ps
   WHERE ps.name = 'wal_segment_size';
       lsn
diff --git a/src/backend/access/transam/xlogfuncs.c b/src/backend/access/transam/xlogfuncs.c
index 0a31837ef1..5c0ec0b662 100644
--- a/src/backend/access/transam/xlogfuncs.c
+++ b/src/backend/access/transam/xlogfuncs.c
@@ -437,16 +437,16 @@ pg_walfile_name(PG_FUNCTION_ARGS)
  * name.
  */
 Datum
-pg_dissect_walfile_name(PG_FUNCTION_ARGS)
+pg_split_walfile_name(PG_FUNCTION_ARGS)
 {
-#define PG_DISSECT_WALFILE_NAME_COLS 2
+#define PG_SPLIT_WALFILE_NAME_COLS 2
 	char	   *fname = text_to_cstring(PG_GETARG_TEXT_PP(0));
 	char	   *fname_upper;
 	char	   *p;
 	TimeLineID	tli;
 	XLogSegNo	segno;
-	Datum		values[PG_DISSECT_WALFILE_NAME_COLS] = {0};
-	bool		isnull[PG_DISSECT_WALFILE_NAME_COLS] = {0};
+	Datum		values[PG_SPLIT_WALFILE_NAME_COLS] = {0};
+	bool		isnull[PG_SPLIT_WALFILE_NAME_COLS] = {0};
 	TupleDesc	tupdesc;
 	HeapTuple	tuple;
 	char		buf[256];
@@ -482,7 +482,7 @@ pg_dissect_walfile_name(PG_FUNCTION_ARGS)
 
 	PG_RETURN_DATUM(result);
 
-#undef PG_DISSECT_WALFILE_NAME_COLS
+#undef PG_SPLIT_WALFILE_NAME_COLS
 }
 
 /*
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 98d90d9338..5dead1a3b0 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -6374,11 +6374,11 @@
   prosrc => 'pg_walfile_name' },
 { oid => '8205',
   descr => 'sequence number and timeline ID given a wal filename',
-  proname => 'pg_dissect_walfile_name', provolatile => 's',
+  proname => 'pg_split_walfile_name', provolatile => 's',
   prorettype => 'record', proargtypes => 'text',
   proallargtypes => '{text,numeric,int8}', proargmodes => '{i,o,o}',
   proargnames => '{file_name,segno,timeline_id}',
-  prosrc => 'pg_dissect_walfile_name' },
+  prosrc => 'pg_split_walfile_name' },
 
 { oid => '3165', descr => 'difference in bytes, given two wal locations',
   proname => 'pg_wal_lsn_diff', prorettype => 'numeric',
diff --git a/src/test/regress/expected/misc_functions.out b/src/test/regress/expected/misc_functions.out
index 2907f779a7..e391bfef9e 100644
--- a/src/test/regress/expected/misc_functions.out
+++ b/src/test/regress/expected/misc_functions.out
@@ -619,24 +619,24 @@ SELECT count(*) > 0 AS ok FROM pg_control_system();
  t
 (1 row)
 
--- pg_dissect_walfile_name
-SELECT * FROM pg_dissect_walfile_name(NULL);
+-- pg_split_walfile_name
+SELECT * FROM pg_split_walfile_name(NULL);
  segno | timeline_id 
 -------+-------------
        |            
 (1 row)
 
-SELECT * FROM pg_dissect_walfile_name('invalid');
+SELECT * FROM pg_split_walfile_name('invalid');
 ERROR:  invalid WAL file name "invalid"
 SELECT segno > 0 AS ok_segno, timeline_id
-  FROM pg_dissect_walfile_name('000000010000000100000000');
+  FROM pg_split_walfile_name('000000010000000100000000');
  ok_segno | timeline_id 
 ----------+-------------
  t        |           1
 (1 row)
 
 SELECT segno > 0 AS ok_segno, timeline_id
-  FROM pg_dissect_walfile_name('ffffffFF00000001000000af');
+  FROM pg_split_walfile_name('ffffffFF00000001000000af');
  ok_segno | timeline_id 
 ----------+-------------
  t        |  4294967295
diff --git a/src/test/regress/sql/misc_functions.sql b/src/test/regress/sql/misc_functions.sql
index 0c3d75fd1a..78fb2dc5ea 100644
--- a/src/test/regress/sql/misc_functions.sql
+++ b/src/test/regress/sql/misc_functions.sql
@@ -230,10 +230,10 @@ SELECT count(*) > 0 AS ok FROM pg_control_init();
 SELECT count(*) > 0 AS ok FROM pg_control_recovery();
 SELECT count(*) > 0 AS ok FROM pg_control_system();
 
--- pg_dissect_walfile_name
-SELECT * FROM pg_dissect_walfile_name(NULL);
-SELECT * FROM pg_dissect_walfile_name('invalid');
+-- pg_split_walfile_name
+SELECT * FROM pg_split_walfile_name(NULL);
+SELECT * FROM pg_split_walfile_name('invalid');
 SELECT segno > 0 AS ok_segno, timeline_id
-  FROM pg_dissect_walfile_name('000000010000000100000000');
+  FROM pg_split_walfile_name('000000010000000100000000');
 SELECT segno > 0 AS ok_segno, timeline_id
-  FROM pg_dissect_walfile_name('ffffffFF00000001000000af');
+  FROM pg_split_walfile_name('ffffffFF00000001000000af');
-- 
2.34.1