On Mon, Sep 19, 2022 at 03:16:42PM -0700, Nathan Bossart wrote:
> It seems like you want the opposite of pg_walfile_name_offset(). Perhaps
> we could add a function like pg_walfile_offset_lsn() that accepts a WAL
> file name and byte offset and returns the LSN.
Like so...
--
Nathan Bossart
Amazon Web Services: https://aws.amazon.com
>From f8ed45d9fa59690d8b3375d658c1baedb8510195 Mon Sep 17 00:00:00 2001
From: Nathan Bossart <nathandboss...@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
