On Tue, Dec 6, 2022 at 4:46 PM Bharath Rupireddy
<bharath.rupireddyforpostg...@gmail.com> wrote:
>
> That said, I think we can have a single function
> pg_dissect_walfile_name(wal_file_name, offset optional) returning
> segno (XLogSegNo - physical log file sequence number), tli, lsn (if
> offset is given). This way there is no need for another SQL-callable
> function using pg_settings. Thoughts?
>
> > (If we assume that the file names are typed in letter-by-letter, I
> > rather prefer to allow lower-case letters:p)
>
> It's easily doable if we convert the entered WAL file name to
> uppercase using pg_toupper() and then pass it to IsXLogFileName().
Okay, here's the v5 patch that I could come up with. It basically adds
functions for dissecting WAL file names and computing offset from lsn.
Thoughts?
--
Bharath Rupireddy
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com
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
