Skip to content

Commit cca1863

Browse files
michaelpqmichaelpq
committed
Add pg_dissect_walfile_name()
This function takes in input a WAL segment name and returns a tuple made of the segment sequence number (dependent on the WAL segment size of the cluster) and its timeline, as of a thin SQL wrapper around the existing XLogFromFileName(). This function has multiple usages, like being able to compile a LSN from a file name and an offset, or finding the timeline of a segment without having to do to some maths based on the first eight characters of the segment. Bump catalog version. Author: Bharath Rupireddy Reviewed-by: Nathan Bossart, Kyotaro Horiguchi, Maxim Orlov, Michael Paquier Discussion: https://postgr.es/m/CALj2ACWV=FCddsxcGbVOA=cvPyMr75YCFbSQT6g4KDj=gcJK4g@mail.gmail.com
1 parent b3bb7d1 commit cca1863

File tree

6 files changed

+125
-1
lines changed

6 files changed

+125
-1
lines changed

‎doc/src/sgml/func.sgml

Lines changed: 33 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -26098,6 +26098,22 @@ LOG: Grand total: 1651920 bytes in 201 blocks; 622360 free (88 chunks); 1029560
2609826098
</para></entry>
2609926099
</row>
2610026100

26101+
<row>
26102+
<entry role="func_table_entry"><para role="func_signature">
26103+
<indexterm>
26104+
<primary>pg_dissect_walfile_name</primary>
26105+
</indexterm>
26106+
<function>pg_dissect_walfile_name</function> ( <parameter>file_name</parameter> <type>text</type> )
26107+
<returnvalue>record</returnvalue>
26108+
( <parameter>segno</parameter> <type>numeric</type>,
26109+
<parameter>timeline_id</parameter> <type>bigint</type> )
26110+
</para>
26111+
<para>
26112+
Extracts the file sequence number and timeline ID from a WAL file
26113+
name.
26114+
</para></entry>
26115+
</row>
26116+
2610126117
<row>
2610226118
<entry role="func_table_entry"><para role="func_signature">
2610326119
<indexterm>
@@ -26155,6 +26171,23 @@ postgres=# SELECT * FROM pg_walfile_name_offset((pg_backup_stop()).lsn);
2615526171
needs to be archived.
2615626172
</para>
2615726173

26174+
<para>
26175+
<function>pg_dissect_walfile_name</function> is useful to compute a
26176+
<acronym>LSN</acronym> from a file offset and WAL file name, for example:
26177+
<programlisting>
26178+
postgres=# \set file_name '000000010000000100C000AB'
26179+
postgres=# \set offset 256
26180+
postgres=# SELECT '0/0'::pg_lsn + pd.segno * ps.setting::int + :offset AS lsn
26181+
FROM pg_dissect_walfile_name(:'file_name') pd,
26182+
pg_show_all_settings() ps
26183+
WHERE ps.name = 'wal_segment_size';
26184+
lsn
26185+
---------------
26186+
C001/AB000100
26187+
(1 row)
26188+
</programlisting>
26189+
</para>
26190+
2615826191
</sect2>
2615926192

2616026193
<sect2 id="functions-recovery-control">

‎src/backend/access/transam/xlogfuncs.c

Lines changed: 53 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -432,6 +432,59 @@ pg_walfile_name(PG_FUNCTION_ARGS)
432432
PG_RETURN_TEXT_P(cstring_to_text(xlogfilename));
433433
}
434434

435+
/*
436+
* Extract the sequence number and the timeline ID from given a WAL file
437+
* name.
438+
*/
439+
Datum
440+
pg_dissect_walfile_name(PG_FUNCTION_ARGS)
441+
{
442+
#define PG_DISSECT_WALFILE_NAME_COLS 2
443+
char *fname = text_to_cstring(PG_GETARG_TEXT_PP(0));
444+
char *fname_upper;
445+
char *p;
446+
TimeLineID tli;
447+
XLogSegNo segno;
448+
Datum values[PG_DISSECT_WALFILE_NAME_COLS] = {0};
449+
bool isnull[PG_DISSECT_WALFILE_NAME_COLS] = {0};
450+
TupleDesc tupdesc;
451+
HeapTuple tuple;
452+
char buf[256];
453+
Datum result;
454+
455+
fname_upper = pstrdup(fname);
456+
457+
/* Capitalize WAL file name. */
458+
for (p = fname_upper; *p; p++)
459+
*p = pg_toupper((unsigned char) *p);
460+
461+
if (!IsXLogFileName(fname_upper))
462+
ereport(ERROR,
463+
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
464+
errmsg("invalid WAL file name \"%s\"", fname)));
465+
466+
XLogFromFileName(fname_upper, &tli, &segno, wal_segment_size);
467+
468+
if (get_call_result_type(fcinfo, NULL, &tupdesc) != TYPEFUNC_COMPOSITE)
469+
elog(ERROR, "return type must be a row type");
470+
471+
/* Convert to numeric. */
472+
snprintf(buf, sizeof buf, UINT64_FORMAT, segno);
473+
values[0] = DirectFunctionCall3(numeric_in,
474+
CStringGetDatum(buf),
475+
ObjectIdGetDatum(0),
476+
Int32GetDatum(-1));
477+
478+
values[1] = Int64GetDatum(tli);
479+
480+
tuple = heap_form_tuple(tupdesc, values, isnull);
481+
result = HeapTupleGetDatum(tuple);
482+
483+
PG_RETURN_DATUM(result);
484+
485+
#undef PG_DISSECT_WALFILE_NAME_COLS
486+
}
487+
435488
/*
436489
* pg_wal_replay_pause - Request to pause recovery
437490
*

‎src/include/catalog/catversion.h

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -57,6 +57,6 @@
5757
*/
5858

5959
/* yyyymmddN */
60-
#define CATALOG_VERSION_NO 202212191
60+
#define CATALOG_VERSION_NO 202212201
6161

6262
#endif

‎src/include/catalog/pg_proc.dat

Lines changed: 7 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -6372,6 +6372,13 @@
63726372
{ oid => '2851', descr => 'wal filename, given a wal location',
63736373
proname => 'pg_walfile_name', prorettype => 'text', proargtypes => 'pg_lsn',
63746374
prosrc => 'pg_walfile_name' },
6375+
{ oid => '8205',
6376+
descr => 'sequence number and timeline ID given a wal filename',
6377+
proname => 'pg_dissect_walfile_name', provolatile => 's',
6378+
prorettype => 'record', proargtypes => 'text',
6379+
proallargtypes => '{text,numeric,int8}', proargmodes => '{i,o,o}',
6380+
proargnames => '{file_name,segno,timeline_id}',
6381+
prosrc => 'pg_dissect_walfile_name' },
63756382

63766383
{ oid => '3165', descr => 'difference in bytes, given two wal locations',
63776384
proname => 'pg_wal_lsn_diff', prorettype => 'numeric',

‎src/test/regress/expected/misc_functions.out

Lines changed: 23 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -619,3 +619,26 @@ SELECT count(*) > 0 AS ok FROM pg_control_system();
619619
t
620620
(1 row)
621621

622+
-- pg_dissect_walfile_name
623+
SELECT * FROM pg_dissect_walfile_name(NULL);
624+
segno | timeline_id
625+
-------+-------------
626+
|
627+
(1 row)
628+
629+
SELECT * FROM pg_dissect_walfile_name('invalid');
630+
ERROR: invalid WAL file name "invalid"
631+
SELECT segno > 0 AS ok_segno, timeline_id
632+
FROM pg_dissect_walfile_name('000000010000000100000000');
633+
ok_segno | timeline_id
634+
----------+-------------
635+
t | 1
636+
(1 row)
637+
638+
SELECT segno > 0 AS ok_segno, timeline_id
639+
FROM pg_dissect_walfile_name('ffffffFF00000001000000af');
640+
ok_segno | timeline_id
641+
----------+-------------
642+
t | 4294967295
643+
(1 row)
644+

‎src/test/regress/sql/misc_functions.sql

Lines changed: 8 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -229,3 +229,11 @@ SELECT count(*) > 0 AS ok FROM pg_control_checkpoint();
229229
SELECT count(*) > 0 AS ok FROM pg_control_init();
230230
SELECT count(*) > 0 AS ok FROM pg_control_recovery();
231231
SELECT count(*) > 0 AS ok FROM pg_control_system();
232+
233+
-- pg_dissect_walfile_name
234+
SELECT * FROM pg_dissect_walfile_name(NULL);
235+
SELECT * FROM pg_dissect_walfile_name('invalid');
236+
SELECT segno > 0 AS ok_segno, timeline_id
237+
FROM pg_dissect_walfile_name('000000010000000100000000');
238+
SELECT segno > 0 AS ok_segno, timeline_id
239+
FROM pg_dissect_walfile_name('ffffffFF00000001000000af');

0 commit comments

Comments
 (0)