pg.ddx.io  pgsql-hackers@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Chao Li <li.evan.chao@gmail.com>
To: surya poondla <suryapoondla4@gmail.com>
Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Subject: Re: pg_walinspect: add functions to locate and list WAL by time and LSN
Date: Thu, 1 Oct 2026 08:16:04 +0800
Message-ID: <D677A87B-75D9-47A4-BDA6-AF88ABE76F87@gmail.com> (raw)
In-Reply-To: <CAOVWO5pa8pphE8qXJMZ4-7mUMvv8zKPmRDqWTn93_HV6w_1GmA@mail.gmail.com>
References: <80E9F0AD-CFC5-4BE5-81DE-D8FE35E10A1C@gmail.com>
	<CAOVWO5pa8pphE8qXJMZ4-7mUMvv8zKPmRDqWTn93_HV6w_1GmA@mail.gmail.com>



> On Sep 30, 2026, at 06:31, surya poondla <suryapoondla4@gmail.com> wrote:
> 
> Hi Chao,
> 
> Thanks for the patch. I think the use case is real. v1 applies cleanly to current master (dca6a9e320e) and builds
> without warnings; the pg_walinspect regression tests pass.

Hi Surya,

Thank you so much for being the first reviewer of this patch.

> 
> I have some comments below, starting with pg_get_wal_files(),
> 
> Regrading pg_get_wal_files():
> 1. A future end_lsn is rejected, which contradicts what this patch edits.
> 
> From the expected output:
> 
>   SELECT * FROM pg_get_wal_files(pg_current_wal_lsn(), 'FFFFFFFF/FFFFFFFF');
>   ERROR:  WAL start LSN must be less than end LSN
> 
> The patch widens the doc tip to "All of the pg_walinspect functions that accept an LSN range are permissive about accepting end_lsn arguments that
> are after the server's current LSN", and the next paragraph says FFFFFFFF/FFFFFFFF is equivalent to the current LSN. Neither statement holds here. 
> The cause is that point_lookup is computed before ValidateInputLSNs() caps end_lsn:
> 
>   ValidateInputLSNs(start_lsn, &end_lsn);
>   point_lookup = (start_lsn == end_lsn);
> 
> With that order, the extra "start_lsn >= end_lsn" check can go. It also adds a second copy of "WAL start LSN must be less than end LSN", the
> message the other thread is changing to "less than or equal to", so dropping it avoids two near-identical messages with different meanings.
>  

Accepted.

> 2. pg_get_wal_files('0/0', '0/1') reports "WAL segment needed for the requested range is missing" / "Segment 0 is not present in pg_wal".
> Segment 0 never exists, so this sends the user looking for a retention problem. The other functions reject the same input with "could not read WAL
> at LSN 0/00000000", via the lsn < XLOG_BLCKSZ check in InitXLogReaderState(). The same check would fit here.
> 

Make sense.

> 
> Regarding the pg_get_wal_location_at_time():
> 
> 1. If the upper bound is capped at the current time and no timestamped record exists between target_time - before and now, both anchors resolve to the
> same record and the function fails with "could not find a valid WAL range for the requested time window". On a quiet system, asking about something
> that happened 30 seconds ago hits this. "Nothing timestamped since then" isn't really an error. Returning the lower anchor with the current flush
> LSN as the end seems more useful, even though the docs deliberately avoid building an end boundary from the WAL position. What was the reasoning for that?
> 
> 2. Regarding the cost, some paths aren't bounded at all. If the lower anchor isn't found, the left scan runs back to the start of the contiguous retained range, and the rescue scan then decodes every segment that remains. So any target older than the retained WAL, such as the clock_timestamp() - interval '100 years' regression test, decodes all of pg_wal only to report "requested time precedes the available WAL range". On a cluster that keeps a lot of WAL, that's a lot of I/O to produce an error. Could the rescue scan be limited, or skipped when the lower anchor can't be found?
> 
> Similarly, the "WAL segment needed for the time search is missing" check for a capped upper bound depends only on last_segment and nsegments, which
> are known before any decoding. Moving it ahead of the scan avoids a long scan that is certain to end in that error.
> 
> A related case: reading the code, if no retained record carries a timestamp at all (a long-running bulk transaction that hasn't committed
> yet, for example), the outward scan runs through the whole contiguous range and the function then reports "requested time precedes the
> available WAL range", even when target_time is well inside retained WAL. If pg_wal also has a gap, it reports a missing segment instead, though
> restoring that segment wouldn't help. This is also the most expensive case, since the whole range has to be decoded to reach it, so a distinct
> message such as "no retained WAL record contains a timestamp" would at least tell the user what's actually wrong.

I thought more about the design and made some significant changes in v2.

First, I switched to binary search for the lower and upper boundaries. I initially avoided this because timestamps of timestamped WAL records are not guaranteed to be ordered monotonically by WAL position. For various reasons, a record with an earlier timestamp can appear later in WAL, so binary search may return an imprecise result. However, considering that (1) large timestamp reordering should be uncommon on production clusters, and (2) the user-specified time range will most likely be an estimate, I think we can tolerate limited timestamp reordering in exchange for decoding significantly less WAL. The search also examines one additional segment in each direction to accommodate limited reordering. If the returned range does not contain the expected records, the user can widen the time range and repeat the search.

Second, when the search finds only one timestamped WAL record within the specified time range, the function no longer fails. Instead, it returns that record’s LSN as both start_lsn and end_lsn.

Third, to simplify the boundary semantics and implementation, the returned boundary timestamps are now within the specified time range. On successful return, start_timestamp is at or after the lower bound, while end_timestamp is at or before the upper bound. Because the search is approximate, the returned records are not guaranteed to be the globally closest timestamped records to those bounds. For example, suppose the requested upper bound is 10:00, and the timestamped records are:

WAL segment 10: 09:55
WAL segment 11: 10:05
WAL segment 12: 10:06
WAL segment 13: 09:59

The binary search may select segment 10 and examine the adjacent segment 11. It would return 09:55 as the upper boundary. However, the globally closest record at or before 10:00 is the 09:59 record in segment 13, which is missed because its timestamp is reordered by more than one segment. As mentioned earlier, this degree of timestamp reordering should be very uncommon on a production cluster. If the user expects the relevant record to be in segment 13, they can widen the upper boundary, for example to 10:10, and repeat the search.

> 3. Regarding Timelines, GetWalTimeSegments() picks each segment's timeline with tliOfPointInHistory(seg_end, history). Taking the timeline valid at the
> segment's end looks right to me: after a promotion, the new timeline's copy of the switch segment contains all WAL up to the switch point. Without
> archiving, the old timeline's copy isn't renamed to .partial and stays in pg_wal under the same segment number, so this filter is what keeps the two
> apart. However, none of this is exercised. The regression tests run only on timeline 1, and the module has no TAP suite. Given that both the commit
> message and the docs claim ranges can cross a timeline switch, I think this needs a TAP test that promotes a standby (with and without archiving).
> 

I was straggling whether or not to add a TAP test and ended up not adding one because the module didn’t have other TAP tests. I can add a TAP test for this timeline switch case.

> 4. The prepared_ok test and the two RESTORE_POINT checks assert that one specific record is the chosen anchor. Any other timestamped record in the
> same window, such as a commit from another session under installcheck or from an autovacuum worker, changes the anchor and fails the test. Checking
> that the record type is one of the timestamp-bearing types would be more robust.

Agreed.

> Also, the four location queries after wal_time_target make the same
> call. The fourth query's assertions imply the first three, and the first differs only in passing the intervals positionally, so the second and third can go.
> 

Yep, a bit redundant. Removed the fourth.

> Minor Nits:
> 1) GetXLogRecordTimestamp() only inspects the decoded record, and none of its logic is specific to recovery. Would xlogreader.c be a better home for it than xlogrecovery.c? 
> xlogreader.c is also built for frontend programs (pg_waldump compiles it with -DFRONTEND), and the headers it would need, access/xact.h and access/xlog_internal.h, are already included by the
> rmgrdesc code that pg_waldump builds. Exporting it from xlogrecovery.c makes it backend-only, so a later time-based filter in pg_waldump, which today has
> none, couldn't reuse it.

Good point. Accepted.

> 2) The "duplicate WAL segment in current timeline history" ereport looks unreachable, because the tliOfPointInHistory() filter leaves at most one
> file per segment number. It could be an Assert() or elog() rather than a user-facing ERRCODE_DATA_EXCEPTION.

Agreed.

> 3) GetWalTimeSegments(0, &nsegments, NULL) in pg_get_wal_files() passes a target_time that is never used. Moving the mtime-based candidate selection into its own function would remove that argument.

Agreed.

> 4) GetWalTimeSegments() repeats GetCurrentLSN()'s flush/replay LSN logic and adds the insert-timeline selection from read_local_xlog_page_guts(). Giving GetCurrentLSN() an optional TLI out-parameter would keep all three in sync.

Agreed.

> 5) "Preserve the empty-set behavior that STRICT provided for a NULL start": since this is a new function, there's no earlier behavior to preserve.

Agreed.

> 
> Finally, pg_get_wal_files() is useful on its own and much closer to ready. Would you consider splitting the two functions into separate patches, so it can move forward while the time-search semantics are worked out?

Good suggestion. I have split the two functions into two commits.

PFA v2.

Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/

Attachments: download all (2 files, .tar.gz)

v2-0001-pg_walinspect-add-function-to-list-WAL-files-by-L.patch (application/octet-stream, 27.8K) download From 4538e2ea3d687f1cac5ddbccf4364b73c093e8da Mon Sep 17 00:00:00 2001 From: "Chao Li (Evan)" <lic@highgo.com> Date: Thu, 1 Oct 2026 06:52:59 +0800 Subject: [PATCH v2 1/2] pg_walinspect: add function to list WAL files by LSN Add pg_get_wal_files(), which returns the retained WAL segment files intersecting an LSN range, together with each segment's complete LSN boundaries. A point lookup can be requested by omitting end_lsn or making it equal to start_lsn. Choose segment file names from the server's current timeline history so that ranges spanning a timeline switch are handled correctly. Author: Chao Li <lic@highgo.com> Reviewed-by: Surya Poondla <suryapoondla4@gmail.com> Discussion: https://postgr.es/m/80E9F0AD-CFC5-4BE5-81DE-D8FE35E10A1C@gmail.com --- contrib/pg_walinspect/Makefile | 4 +- .../pg_walinspect/expected/oldextversions.out | 8 +- .../pg_walinspect/expected/pg_walinspect.out | 73 ++++++ contrib/pg_walinspect/meson.build | 6 + .../pg_walinspect/pg_walinspect--1.1--1.2.sql | 22 ++ contrib/pg_walinspect/pg_walinspect.c | 209 +++++++++++++++++- contrib/pg_walinspect/pg_walinspect.control | 2 +- contrib/pg_walinspect/sql/oldextversions.sql | 5 +- contrib/pg_walinspect/sql/pg_walinspect.sql | 34 +++ contrib/pg_walinspect/t/001_timeline.pl | 62 ++++++ doc/src/sgml/pgwalinspect.sgml | 34 ++- src/tools/pgindent/typedefs.list | 1 + 12 files changed, 441 insertions(+), 19 deletions(-) create mode 100644 contrib/pg_walinspect/pg_walinspect--1.1--1.2.sql create mode 100644 contrib/pg_walinspect/t/001_timeline.pl diff --git a/contrib/pg_walinspect/Makefile b/contrib/pg_walinspect/Makefile index 22090f7716c..b7651231077 100644 --- a/contrib/pg_walinspect/Makefile +++ b/contrib/pg_walinspect/Makefile @@ -7,9 +7,11 @@ OBJS = \ PGFILEDESC = "pg_walinspect - functions to inspect contents of PostgreSQL Write-Ahead Log" EXTENSION = pg_walinspect -DATA = pg_walinspect--1.0.sql pg_walinspect--1.0--1.1.sql +DATA = pg_walinspect--1.0.sql pg_walinspect--1.0--1.1.sql \ + pg_walinspect--1.1--1.2.sql REGRESS = pg_walinspect oldextversions +TAP_TESTS = 1 REGRESS_OPTS = --temp-config $(top_srcdir)/contrib/pg_walinspect/walinspect.conf diff --git a/contrib/pg_walinspect/expected/oldextversions.out b/contrib/pg_walinspect/expected/oldextversions.out index 89953af3d0e..311017d66ca 100644 --- a/contrib/pg_walinspect/expected/oldextversions.out +++ b/contrib/pg_walinspect/expected/oldextversions.out @@ -46,18 +46,20 @@ SELECT * FROM pg_get_wal_records_info_till_end_of_wal('FFFFFFFF/FFFFFFFF'); ERROR: WAL start LSN must be less than current LSN SELECT * FROM pg_get_wal_stats_till_end_of_wal('FFFFFFFF/FFFFFFFF'); ERROR: WAL start LSN must be less than current LSN --- Move to new version 1.1. +-- Test each upgrade step through the current version. ALTER EXTENSION pg_walinspect UPDATE TO '1.1'; --- List what version 1.1 contains. +ALTER EXTENSION pg_walinspect UPDATE TO '1.2'; +-- List what the current version contains. \dx+ pg_walinspect Objects in extension "pg_walinspect" Object description ------------------------------------------------------- function pg_get_wal_block_info(pg_lsn,pg_lsn,boolean) + function pg_get_wal_files(pg_lsn,pg_lsn) function pg_get_wal_record_info(pg_lsn) function pg_get_wal_records_info(pg_lsn,pg_lsn) function pg_get_wal_stats(pg_lsn,pg_lsn,boolean) -(4 rows) +(5 rows) SELECT pg_drop_replication_slot('regress_pg_walinspect_slot'); pg_drop_replication_slot diff --git a/contrib/pg_walinspect/expected/pg_walinspect.out b/contrib/pg_walinspect/expected/pg_walinspect.out index f955ff5d3c5..34864c65ec7 100644 --- a/contrib/pg_walinspect/expected/pg_walinspect.out +++ b/contrib/pg_walinspect/expected/pg_walinspect.out @@ -90,6 +90,54 @@ SELECT COUNT(*) >= 1 AS ok FROM pg_get_wal_block_info(:'wal_lsn1', :'wal_lsn2'); t (1 row) +-- Return the retained WAL file and its complete segment boundaries. +SELECT wal_file = pg_walfile_name(segment_start_lsn) AS file_ok, + segment_start_lsn <= :'wal_lsn1'::pg_lsn AS start_ok, + segment_end_lsn > :'wal_lsn1'::pg_lsn AS end_ok +FROM pg_get_wal_files(:'wal_lsn1', :'wal_lsn1'::pg_lsn + 1); + file_ok | start_ok | end_ok +---------+----------+-------- + t | t | t +(1 row) + +-- An end LSN at a segment boundary must not include the next segment. +WITH segment AS +( + SELECT * FROM pg_get_wal_files(:'wal_lsn1', :'wal_lsn1'::pg_lsn + 1) +) +SELECT count(*) = 1 AS one_file +FROM segment, + LATERAL pg_get_wal_files(segment_start_lsn, segment_end_lsn); + one_file +---------- + t +(1 row) + +SELECT count(*) = 1 AS equal_lsn_ok +FROM pg_get_wal_files(:'wal_lsn1', :'wal_lsn1'); + equal_lsn_ok +-------------- + t +(1 row) + +SELECT count(*) = 1 AS default_end_ok +FROM pg_get_wal_files(:'wal_lsn1'); + default_end_ok +---------------- + t +(1 row) + +SELECT * FROM pg_get_wal_files(:'wal_lsn1'::pg_lsn + 1, :'wal_lsn1'); +ERROR: WAL start LSN must be less than end LSN +SELECT count(*) = 1 AS future_end_ok +FROM pg_get_wal_files(pg_current_wal_lsn(), 'FFFFFFFF/FFFFFFFF'); + future_end_ok +--------------- + t +(1 row) + +SELECT * FROM pg_get_wal_files('0/0', '0/1'); +ERROR: could not read WAL at LSN 0/00000000 -- =================================================================== -- Test for filtering out WAL records of a particular table -- =================================================================== @@ -183,6 +231,13 @@ SELECT has_function_privilege('regress_pg_walinspect', f (1 row) +SELECT has_function_privilege('regress_pg_walinspect', + 'pg_get_wal_files(pg_lsn, pg_lsn)', 'EXECUTE'); -- no + has_function_privilege +------------------------ + f +(1 row) + -- Functions accessible by users with role pg_read_server_files. GRANT pg_read_server_files TO regress_pg_walinspect; SELECT has_function_privilege('regress_pg_walinspect', @@ -213,6 +268,13 @@ SELECT has_function_privilege('regress_pg_walinspect', t (1 row) +SELECT has_function_privilege('regress_pg_walinspect', + 'pg_get_wal_files(pg_lsn, pg_lsn)', 'EXECUTE'); -- yes + has_function_privilege +------------------------ + t +(1 row) + REVOKE pg_read_server_files FROM regress_pg_walinspect; -- Superuser can grant execute to other users. GRANT EXECUTE ON FUNCTION pg_get_wal_record_info(pg_lsn) @@ -223,6 +285,8 @@ GRANT EXECUTE ON FUNCTION pg_get_wal_stats(pg_lsn, pg_lsn, boolean) TO regress_pg_walinspect; GRANT EXECUTE ON FUNCTION pg_get_wal_block_info(pg_lsn, pg_lsn, boolean) TO regress_pg_walinspect; +GRANT EXECUTE ON FUNCTION pg_get_wal_files(pg_lsn, pg_lsn) + TO regress_pg_walinspect; SELECT has_function_privilege('regress_pg_walinspect', 'pg_get_wal_record_info(pg_lsn)', 'EXECUTE'); -- yes has_function_privilege @@ -251,6 +315,13 @@ SELECT has_function_privilege('regress_pg_walinspect', t (1 row) +SELECT has_function_privilege('regress_pg_walinspect', + 'pg_get_wal_files(pg_lsn, pg_lsn)', 'EXECUTE'); -- yes + has_function_privilege +------------------------ + t +(1 row) + REVOKE EXECUTE ON FUNCTION pg_get_wal_record_info(pg_lsn) FROM regress_pg_walinspect; REVOKE EXECUTE ON FUNCTION pg_get_wal_records_info(pg_lsn, pg_lsn) @@ -259,6 +330,8 @@ REVOKE EXECUTE ON FUNCTION pg_get_wal_stats(pg_lsn, pg_lsn, boolean) FROM regress_pg_walinspect; REVOKE EXECUTE ON FUNCTION pg_get_wal_block_info(pg_lsn, pg_lsn, boolean) FROM regress_pg_walinspect; +REVOKE EXECUTE ON FUNCTION pg_get_wal_files(pg_lsn, pg_lsn) + FROM regress_pg_walinspect; -- =================================================================== -- Clean up -- =================================================================== diff --git a/contrib/pg_walinspect/meson.build b/contrib/pg_walinspect/meson.build index 82ab186c340..6f8b4a6359a 100644 --- a/contrib/pg_walinspect/meson.build +++ b/contrib/pg_walinspect/meson.build @@ -20,6 +20,7 @@ install_data( 'pg_walinspect.control', 'pg_walinspect--1.0.sql', 'pg_walinspect--1.0--1.1.sql', + 'pg_walinspect--1.1--1.2.sql', kwargs: contrib_data_args, ) @@ -37,4 +38,9 @@ tests += { 'regress_args': ['--temp-config', files('walinspect.conf')], 'runningcheck': false, }, + 'tap': { + 'tests': [ + 't/001_timeline.pl', + ], + }, } diff --git a/contrib/pg_walinspect/pg_walinspect--1.1--1.2.sql b/contrib/pg_walinspect/pg_walinspect--1.1--1.2.sql new file mode 100644 index 00000000000..9cc9523b4dc --- /dev/null +++ b/contrib/pg_walinspect/pg_walinspect--1.1--1.2.sql @@ -0,0 +1,22 @@ +/* contrib/pg_walinspect/pg_walinspect--1.1--1.2.sql */ + +-- complain if script is sourced in psql, rather than via ALTER EXTENSION +\echo Use "ALTER EXTENSION pg_walinspect UPDATE TO '1.2'" to load this file. \quit + +-- +-- pg_get_wal_files() +-- +CREATE FUNCTION pg_get_wal_files( + IN start_lsn pg_lsn, + IN end_lsn pg_lsn DEFAULT NULL, + OUT wal_file text, + OUT segment_start_lsn pg_lsn, + OUT segment_end_lsn pg_lsn +) +RETURNS SETOF record +AS 'MODULE_PATHNAME', 'pg_get_wal_files' +LANGUAGE C PARALLEL SAFE; + +REVOKE EXECUTE ON FUNCTION pg_get_wal_files(pg_lsn, pg_lsn) FROM PUBLIC; +GRANT EXECUTE ON FUNCTION pg_get_wal_files(pg_lsn, pg_lsn) + TO pg_read_server_files; diff --git a/contrib/pg_walinspect/pg_walinspect.c b/contrib/pg_walinspect/pg_walinspect.c index a172f9e2b40..42f366bdb4e 100644 --- a/contrib/pg_walinspect/pg_walinspect.c +++ b/contrib/pg_walinspect/pg_walinspect.c @@ -13,6 +13,7 @@ #include "postgres.h" #include "access/htup_details.h" +#include "access/timeline.h" #include "access/xlog.h" #include "access/xlog_internal.h" #include "access/xlogreader.h" @@ -22,6 +23,7 @@ #include "funcapi.h" #include "miscadmin.h" #include "port/pg_bitutils.h" +#include "storage/fd.h" #include "utils/array.h" #include "utils/builtins.h" #include "utils/pg_lsn.h" @@ -38,6 +40,7 @@ PG_MODULE_MAGIC_EXT( ); PG_FUNCTION_INFO_V1(pg_get_wal_block_info); +PG_FUNCTION_INFO_V1(pg_get_wal_files); PG_FUNCTION_INFO_V1(pg_get_wal_record_info); PG_FUNCTION_INFO_V1(pg_get_wal_records_info); PG_FUNCTION_INFO_V1(pg_get_wal_records_info_till_end_of_wal); @@ -45,7 +48,7 @@ PG_FUNCTION_INFO_V1(pg_get_wal_stats); PG_FUNCTION_INFO_V1(pg_get_wal_stats_till_end_of_wal); static void ValidateInputLSNs(XLogRecPtr start_lsn, XLogRecPtr *end_lsn); -static XLogRecPtr GetCurrentLSN(void); +static XLogRecPtr GetCurrentLSN(TimeLineID *current_tli); static XLogReaderState *InitXLogReaderState(XLogRecPtr lsn); static XLogRecord *ReadNextXLogRecord(XLogReaderState *xlogreader); static void GetWALRecordInfo(XLogReaderState *record, Datum *values, @@ -68,11 +71,21 @@ static void GetWalStats(FunctionCallInfo fcinfo, static void GetWALBlockInfo(FunctionCallInfo fcinfo, XLogReaderState *record, bool show_data); +/* Metadata for one retained segment in the current timeline's history. */ +typedef struct WalTimeSegment +{ + XLogSegNo segno; /* segment number, used for WAL ordering */ + TimeLineID tli; /* timeline containing this segment */ +} WalTimeSegment; + +static int wal_time_segment_cmp(const void *a, const void *b); +static WalTimeSegment *GetWalTimeSegments(int *nsegments); + /* - * Return the LSN up to which the server has WAL. + * Return the LSN up to which the server has WAL, and optionally its timeline. */ static XLogRecPtr -GetCurrentLSN(void) +GetCurrentLSN(TimeLineID *current_tli) { XLogRecPtr curr_lsn; @@ -81,9 +94,15 @@ GetCurrentLSN(void) * callback read_local_xlog_page_no_wait does. */ if (!RecoveryInProgress()) - curr_lsn = GetFlushRecPtr(NULL); + curr_lsn = GetFlushRecPtr(current_tli); else - curr_lsn = GetXLogReplayRecPtr(NULL); + { + curr_lsn = GetXLogReplayRecPtr(current_tli); + if (current_tli != NULL && *current_tli == 0) + { + *current_tli = GetWALInsertionTimeLineIfSet(); + } + } Assert(XLogRecPtrIsValid(curr_lsn)); @@ -190,6 +209,94 @@ ReadNextXLogRecord(XLogReaderState *xlogreader) return record; } +/* qsort comparator that puts segments in WAL order. */ +static int +wal_time_segment_cmp(const void *a, const void *b) +{ + const WalTimeSegment *seg1 = (const WalTimeSegment *) a; + const WalTimeSegment *seg2 = (const WalTimeSegment *) b; + + if (seg1->segno < seg2->segno) + return -1; + if (seg1->segno > seg2->segno) + return 1; + return 0; +} + +/* + * Get the WAL segments that form the history of the server's current + * timeline. Other timelines may have files with the same segment number in + * pg_wal, so choose the timeline that is valid at the end of each segment, + * as read_local_xlog_page_no_wait() does. Return the segments in WAL order. + */ +static WalTimeSegment * +GetWalTimeSegments(int *nsegments) +{ + WalTimeSegment *segments = NULL; + DIR *dir; + struct dirent *de; + List *history; + XLogRecPtr current_lsn; + TimeLineID current_tli; + int allocated = 0; + int count = 0; + + current_lsn = GetCurrentLSN(&current_tli); + + history = readTimeLineHistory(current_tli); + dir = AllocateDir(XLOGDIR); + while ((de = ReadDir(dir, XLOGDIR)) != NULL) + { + TimeLineID file_tli; + XLogSegNo segno; + XLogRecPtr seg_start; + XLogRecPtr seg_end; + + if (!IsXLogFileName(de->d_name)) + continue; + + XLogFromFileName(de->d_name, &file_tli, &segno, wal_segment_size); + seg_start = segno * wal_segment_size; + seg_end = seg_start + wal_segment_size - 1; + + if (seg_start >= current_lsn || + file_tli != tliOfPointInHistory(seg_end, history)) + continue; + + if (count == allocated) + { + allocated = allocated ? allocated * 2 : 16; + if (segments == NULL) + segments = palloc_array(WalTimeSegment, allocated); + else + segments = repalloc_array(segments, WalTimeSegment, allocated); + } + + MemSet(&segments[count], 0, sizeof(WalTimeSegment)); + segments[count].segno = segno; + segments[count].tli = file_tli; + count++; + } + FreeDir(dir); + list_free_deep(history); + + if (count == 0) + ereport(ERROR, + errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE), + errmsg("no retained WAL segments are available")); + + qsort(segments, count, sizeof(WalTimeSegment), wal_time_segment_cmp); + +#ifdef USE_ASSERT_CHECKING + /* A segment number identifies at most one file in this timeline history. */ + for (int i = 1; i < count; i++) + Assert(segments[i - 1].segno != segments[i].segno); +#endif + + *nsegments = count; + return segments; +} + /* * Output values that make up a row describing caller's WAL record. * @@ -483,7 +590,7 @@ pg_get_wal_record_info(PG_FUNCTION_ARGS) HeapTuple tuple; lsn = PG_GETARG_LSN(0); - curr_lsn = GetCurrentLSN(); + curr_lsn = GetCurrentLSN(NULL); if (lsn > curr_lsn) ereport(ERROR, @@ -516,6 +623,90 @@ pg_get_wal_record_info(PG_FUNCTION_ARGS) #undef PG_GET_WAL_RECORD_INFO_COLS } +/* + * List the retained WAL segment files intersecting [start_lsn, end_lsn), or + * the segment containing start_lsn when end_lsn is omitted or equal to it. + * Report complete segment boundaries, not the range clipped to the inputs. + */ +Datum +pg_get_wal_files(PG_FUNCTION_ARGS) +{ +#define PG_GET_WAL_FILES_COLS 3 + XLogRecPtr start_lsn; + XLogRecPtr end_lsn; + XLogSegNo first_segno; + XLogSegNo last_segno; + bool point_lookup; + WalTimeSegment *segments; + ReturnSetInfo *rsinfo = (ReturnSetInfo *) fcinfo->resultinfo; + int nsegments; + int segment_index = 0; + + InitMaterializedSRF(fcinfo, 0); + + /* A NULL start produces an empty set. */ + if (PG_ARGISNULL(0)) + PG_RETURN_VOID(); + + start_lsn = PG_GETARG_LSN(0); + end_lsn = PG_ARGISNULL(1) ? start_lsn : PG_GETARG_LSN(1); + + if (start_lsn < XLOG_BLCKSZ) + ereport(ERROR, + errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("could not read WAL at LSN %X/%08X", + LSN_FORMAT_ARGS(start_lsn))); + + ValidateInputLSNs(start_lsn, &end_lsn); + point_lookup = start_lsn == end_lsn; + + + XLByteToSeg(start_lsn, first_segno, wal_segment_size); + if (point_lookup) + last_segno = first_segno; + else + XLByteToPrevSeg(end_lsn, last_segno, wal_segment_size); + segments = GetWalTimeSegments(&nsegments); + + for (XLogSegNo segno = first_segno;; segno++) + { + Datum values[PG_GET_WAL_FILES_COLS]; + bool nulls[PG_GET_WAL_FILES_COLS] = {0}; + XLogRecPtr segment_start_lsn = segno * wal_segment_size; + XLogRecPtr segment_end_lsn = segment_start_lsn + wal_segment_size; + char fname[MAXFNAMELEN]; + + while (segment_index < nsegments && + segments[segment_index].segno < segno) + segment_index++; + + if (segment_index >= nsegments || + segments[segment_index].segno != segno) + ereport(ERROR, + errcode(ERRCODE_DATA_EXCEPTION), + errmsg("WAL segment needed for the requested range is missing"), + errdetail("Segment " UINT64_FORMAT " is not present in pg_wal.", + (uint64) segno)); + + XLogFileName(fname, segments[segment_index].tli, segno, + wal_segment_size); + values[0] = CStringGetTextDatum(fname); + values[1] = LSNGetDatum(segment_start_lsn); + values[2] = LSNGetDatum(segment_end_lsn); + tuplestore_putvalues(rsinfo->setResult, rsinfo->setDesc, + values, nulls); + + if (segno == last_segno) + break; + + CHECK_FOR_INTERRUPTS(); + } + + pfree(segments); + PG_RETURN_VOID(); +#undef PG_GET_WAL_FILES_COLS +} + /* * Validate start and end LSNs coming from the function inputs. * @@ -525,7 +716,7 @@ pg_get_wal_record_info(PG_FUNCTION_ARGS) static void ValidateInputLSNs(XLogRecPtr start_lsn, XLogRecPtr *end_lsn) { - XLogRecPtr curr_lsn = GetCurrentLSN(); + XLogRecPtr curr_lsn = GetCurrentLSN(NULL); if (start_lsn > curr_lsn) ereport(ERROR, @@ -831,7 +1022,7 @@ Datum pg_get_wal_records_info_till_end_of_wal(PG_FUNCTION_ARGS) { XLogRecPtr start_lsn = PG_GETARG_LSN(0); - XLogRecPtr end_lsn = GetCurrentLSN(); + XLogRecPtr end_lsn = GetCurrentLSN(NULL); if (start_lsn > end_lsn) ereport(ERROR, @@ -849,7 +1040,7 @@ Datum pg_get_wal_stats_till_end_of_wal(PG_FUNCTION_ARGS) { XLogRecPtr start_lsn = PG_GETARG_LSN(0); - XLogRecPtr end_lsn = GetCurrentLSN(); + XLogRecPtr end_lsn = GetCurrentLSN(NULL); bool stats_per_record = PG_GETARG_BOOL(1); if (start_lsn > end_lsn) diff --git a/contrib/pg_walinspect/pg_walinspect.control b/contrib/pg_walinspect/pg_walinspect.control index efa3cb2cfe5..5f574b865bb 100644 --- a/contrib/pg_walinspect/pg_walinspect.control +++ b/contrib/pg_walinspect/pg_walinspect.control @@ -1,5 +1,5 @@ # pg_walinspect extension comment = 'functions to inspect contents of PostgreSQL Write-Ahead Log' -default_version = '1.1' +default_version = '1.2' module_pathname = '$libdir/pg_walinspect' relocatable = true diff --git a/contrib/pg_walinspect/sql/oldextversions.sql b/contrib/pg_walinspect/sql/oldextversions.sql index e35c4f3ed15..b935a067b3e 100644 --- a/contrib/pg_walinspect/sql/oldextversions.sql +++ b/contrib/pg_walinspect/sql/oldextversions.sql @@ -27,10 +27,11 @@ SELECT COUNT(*) >= 1 AS ok FROM pg_get_wal_stats_till_end_of_wal(:'wal_lsn1'); SELECT * FROM pg_get_wal_records_info_till_end_of_wal('FFFFFFFF/FFFFFFFF'); SELECT * FROM pg_get_wal_stats_till_end_of_wal('FFFFFFFF/FFFFFFFF'); --- Move to new version 1.1. +-- Test each upgrade step through the current version. ALTER EXTENSION pg_walinspect UPDATE TO '1.1'; +ALTER EXTENSION pg_walinspect UPDATE TO '1.2'; --- List what version 1.1 contains. +-- List what the current version contains. \dx+ pg_walinspect SELECT pg_drop_replication_slot('regress_pg_walinspect_slot'); diff --git a/contrib/pg_walinspect/sql/pg_walinspect.sql b/contrib/pg_walinspect/sql/pg_walinspect.sql index 1e64a22d29a..3176a17d855 100644 --- a/contrib/pg_walinspect/sql/pg_walinspect.sql +++ b/contrib/pg_walinspect/sql/pg_walinspect.sql @@ -51,6 +51,30 @@ SELECT COUNT(*) >= 1 AS ok FROM pg_get_wal_records_info(:'wal_lsn1', :'wal_lsn2' SELECT COUNT(*) >= 1 AS ok FROM pg_get_wal_stats(:'wal_lsn1', :'wal_lsn2'); SELECT COUNT(*) >= 1 AS ok FROM pg_get_wal_block_info(:'wal_lsn1', :'wal_lsn2'); +-- Return the retained WAL file and its complete segment boundaries. +SELECT wal_file = pg_walfile_name(segment_start_lsn) AS file_ok, + segment_start_lsn <= :'wal_lsn1'::pg_lsn AS start_ok, + segment_end_lsn > :'wal_lsn1'::pg_lsn AS end_ok +FROM pg_get_wal_files(:'wal_lsn1', :'wal_lsn1'::pg_lsn + 1); + +-- An end LSN at a segment boundary must not include the next segment. +WITH segment AS +( + SELECT * FROM pg_get_wal_files(:'wal_lsn1', :'wal_lsn1'::pg_lsn + 1) +) +SELECT count(*) = 1 AS one_file +FROM segment, + LATERAL pg_get_wal_files(segment_start_lsn, segment_end_lsn); + +SELECT count(*) = 1 AS equal_lsn_ok +FROM pg_get_wal_files(:'wal_lsn1', :'wal_lsn1'); +SELECT count(*) = 1 AS default_end_ok +FROM pg_get_wal_files(:'wal_lsn1'); +SELECT * FROM pg_get_wal_files(:'wal_lsn1'::pg_lsn + 1, :'wal_lsn1'); +SELECT count(*) = 1 AS future_end_ok +FROM pg_get_wal_files(pg_current_wal_lsn(), 'FFFFFFFF/FFFFFFFF'); +SELECT * FROM pg_get_wal_files('0/0', '0/1'); + -- =================================================================== -- Test for filtering out WAL records of a particular table -- =================================================================== @@ -110,6 +134,8 @@ SELECT has_function_privilege('regress_pg_walinspect', 'pg_get_wal_stats(pg_lsn, pg_lsn, boolean) ', 'EXECUTE'); -- no SELECT has_function_privilege('regress_pg_walinspect', 'pg_get_wal_block_info(pg_lsn, pg_lsn, boolean) ', 'EXECUTE'); -- no +SELECT has_function_privilege('regress_pg_walinspect', + 'pg_get_wal_files(pg_lsn, pg_lsn)', 'EXECUTE'); -- no -- Functions accessible by users with role pg_read_server_files. GRANT pg_read_server_files TO regress_pg_walinspect; @@ -122,6 +148,8 @@ SELECT has_function_privilege('regress_pg_walinspect', 'pg_get_wal_stats(pg_lsn, pg_lsn, boolean) ', 'EXECUTE'); -- yes SELECT has_function_privilege('regress_pg_walinspect', 'pg_get_wal_block_info(pg_lsn, pg_lsn, boolean) ', 'EXECUTE'); -- yes +SELECT has_function_privilege('regress_pg_walinspect', + 'pg_get_wal_files(pg_lsn, pg_lsn)', 'EXECUTE'); -- yes REVOKE pg_read_server_files FROM regress_pg_walinspect; @@ -134,6 +162,8 @@ GRANT EXECUTE ON FUNCTION pg_get_wal_stats(pg_lsn, pg_lsn, boolean) TO regress_pg_walinspect; GRANT EXECUTE ON FUNCTION pg_get_wal_block_info(pg_lsn, pg_lsn, boolean) TO regress_pg_walinspect; +GRANT EXECUTE ON FUNCTION pg_get_wal_files(pg_lsn, pg_lsn) + TO regress_pg_walinspect; SELECT has_function_privilege('regress_pg_walinspect', 'pg_get_wal_record_info(pg_lsn)', 'EXECUTE'); -- yes @@ -143,6 +173,8 @@ SELECT has_function_privilege('regress_pg_walinspect', 'pg_get_wal_stats(pg_lsn, pg_lsn, boolean) ', 'EXECUTE'); -- yes SELECT has_function_privilege('regress_pg_walinspect', 'pg_get_wal_block_info(pg_lsn, pg_lsn, boolean) ', 'EXECUTE'); -- yes +SELECT has_function_privilege('regress_pg_walinspect', + 'pg_get_wal_files(pg_lsn, pg_lsn)', 'EXECUTE'); -- yes REVOKE EXECUTE ON FUNCTION pg_get_wal_record_info(pg_lsn) FROM regress_pg_walinspect; @@ -152,6 +184,8 @@ REVOKE EXECUTE ON FUNCTION pg_get_wal_stats(pg_lsn, pg_lsn, boolean) FROM regress_pg_walinspect; REVOKE EXECUTE ON FUNCTION pg_get_wal_block_info(pg_lsn, pg_lsn, boolean) FROM regress_pg_walinspect; +REVOKE EXECUTE ON FUNCTION pg_get_wal_files(pg_lsn, pg_lsn) + FROM regress_pg_walinspect; -- =================================================================== -- Clean up diff --git a/contrib/pg_walinspect/t/001_timeline.pl b/contrib/pg_walinspect/t/001_timeline.pl new file mode 100644 index 00000000000..1abf7131bb7 --- /dev/null +++ b/contrib/pg_walinspect/t/001_timeline.pl @@ -0,0 +1,62 @@ +# Copyright (c) 2026, PostgreSQL Global Development Group + +# Test that pg_get_wal_files() follows the current timeline's history across +# a promotion. +use strict; +use warnings FATAL => 'all'; + +use PostgreSQL::Test::Cluster; +use PostgreSQL::Test::Utils; +use Test::More; + +sub test_timeline_history +{ + my ($suffix, $archive_mode) = @_; + my $primary = PostgreSQL::Test::Cluster->new("primary_$suffix"); + my $standby = PostgreSQL::Test::Cluster->new("standby_$suffix"); + + $primary->init(allows_streaming => 1); + $primary->append_conf('postgresql.conf', "autovacuum = off\n"); + if ($archive_mode) + { + $primary->append_conf( + 'postgresql.conf', + "archive_mode = on\narchive_command = 'true'\n"); + } + $primary->start; + $primary->safe_psql('postgres', + 'CREATE EXTENSION pg_walinspect; CREATE TABLE test_table (a int);'); + + my $backup_name = "backup_$suffix"; + $primary->backup($backup_name); + $standby->init_from_backup($primary, $backup_name, has_streaming => 1); + $standby->start; + + $primary->safe_psql('postgres', 'INSERT INTO test_table VALUES (1)'); + my $start_lsn = + $primary->safe_psql('postgres', 'SELECT pg_current_wal_insert_lsn()'); + $primary->safe_psql('postgres', 'SELECT pg_switch_wal()'); + $primary->safe_psql('postgres', 'INSERT INTO test_table VALUES (2)'); + $primary->wait_for_catchup($standby); + + $standby->promote; + $standby->safe_psql('postgres', 'INSERT INTO test_table VALUES (3)'); + + my $result = $standby->safe_psql( + 'postgres', qq{ +SELECT count(DISTINCT left(wal_file, 8)) = 2, + min(left(wal_file, 8)) = '00000001', + max(left(wal_file, 8)) = '00000002' +FROM pg_get_wal_files('$start_lsn', pg_current_wal_lsn())}); + is($result, 't|t|t', + "WAL files span the timeline switch with archive_mode=$archive_mode"); + + $standby->stop; + $primary->stop; + return; +} + +test_timeline_history('archive_off', 0); +test_timeline_history('archive_on', 1); + +done_testing(); diff --git a/doc/src/sgml/pgwalinspect.sgml b/doc/src/sgml/pgwalinspect.sgml index 79c3ead40bc..b4ecae8cfb1 100644 --- a/doc/src/sgml/pgwalinspect.sgml +++ b/doc/src/sgml/pgwalinspect.sgml @@ -34,9 +34,8 @@ </note> <tip> <para> - All of the <filename>pg_walinspect</filename> functions that show - information about records that fall within a certain LSN range are - permissive about accepting <replaceable>end_lsn</replaceable> + All of the <filename>pg_walinspect</filename> functions that accept an LSN + range are permissive about accepting <replaceable>end_lsn</replaceable> arguments that are after the server's current LSN. Using an <replaceable>end_lsn</replaceable> <quote>from the future</quote> will not raise an error. @@ -60,6 +59,35 @@ <title>General Functions</title> <variablelist> + <varlistentry id="pgwalinspect-funcs-pg-get-wal-files"> + <term> + <function>pg_get_wal_files(start_lsn pg_lsn, end_lsn pg_lsn DEFAULT NULL) returns setof record</function> + </term> + + <listitem> + <para> + If <replaceable>end_lsn</replaceable> is specified and differs from + <replaceable>start_lsn</replaceable>, returns each retained WAL segment + file that intersects the half-open WAL range + [<replaceable>start_lsn</replaceable>, + <replaceable>end_lsn</replaceable>). If + <replaceable>end_lsn</replaceable> is omitted, is null, or equals + <replaceable>start_lsn</replaceable>, returns the segment containing + <replaceable>start_lsn</replaceable>. The + <structfield>wal_file</structfield> column contains the file name, while + <structfield>segment_start_lsn</structfield> and + <structfield>segment_end_lsn</structfield> contain the complete segment + boundaries rather than boundaries clipped to the requested range. + </para> + <para> + File names use the timeline valid for each segment in the current + timeline's history. This allows a returned range to cross a timeline + switch. The function reports an error if any required segment file is + not currently retained in <filename>pg_wal</filename>. + </para> + </listitem> + </varlistentry> + <varlistentry id="pgwalinspect-funcs-pg-get-wal-record-info"> <term> <function>pg_get_wal_record_info(in_lsn pg_lsn) returns record</function> diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list index 656f1f60862..1f1bcf8d527 100644 --- a/src/tools/pgindent/typedefs.list +++ b/src/tools/pgindent/typedefs.list @@ -3434,6 +3434,7 @@ WalSummarizerData WalSummaryFile WalSummaryIO WalTimeSample +WalTimeSegment WalUsage WalWriteMethod WalWriteMethodOps -- 2.50.1 (Apple Git-155) =
v2-0002-pg_walinspect-add-function-to-locate-WAL-by-time.patch (application/octet-stream, 54.5K) download From d939eef1f5ddfa2a122024b6d7371b4a67a6564f Mon Sep 17 00:00:00 2001 From: "Chao Li (Evan)" <lic@highgo.com> Date: Thu, 1 Oct 2026 06:52:59 +0800 Subject: [PATCH v2 2/2] pg_walinspect: add function to locate WAL by time Add pg_get_wal_location_at_time(), which uses timestamped WAL records to locate a useful WAL range around a requested wall-clock time. Use an approximate binary search over retained segments and allow callers to widen the requested interval when timestamp reordering makes the initial result insufficient. Move WAL record timestamp extraction from recovery into xlogreader so pg_walinspect can recognize the same timestamp-bearing record types without duplicating that logic. Author: Chao Li <lic@highgo.com> Reviewed-by: Surya Poondla <suryapoondla4@gmail.com> Discussion: https://postgr.es/m/80E9F0AD-CFC5-4BE5-81DE-D8FE35E10A1C@gmail.com --- .../pg_walinspect/expected/oldextversions.out | 9 +- .../pg_walinspect/expected/pg_walinspect.out | 161 +++++++ contrib/pg_walinspect/meson.build | 1 + .../pg_walinspect/pg_walinspect--1.1--1.2.sql | 21 + contrib/pg_walinspect/pg_walinspect.c | 422 ++++++++++++++++++ contrib/pg_walinspect/sql/pg_walinspect.sql | 118 +++++ contrib/pg_walinspect/t/002_time_search.pl | 51 +++ doc/src/sgml/pgwalinspect.sgml | 107 +++++ src/backend/access/transam/xlogreader.c | 36 ++ src/backend/access/transam/xlogrecovery.c | 48 +- src/include/access/xlogreader.h | 3 + src/tools/pgindent/typedefs.list | 1 + 12 files changed, 932 insertions(+), 46 deletions(-) create mode 100644 contrib/pg_walinspect/t/002_time_search.pl diff --git a/contrib/pg_walinspect/expected/oldextversions.out b/contrib/pg_walinspect/expected/oldextversions.out index 311017d66ca..6228ad9ef39 100644 --- a/contrib/pg_walinspect/expected/oldextversions.out +++ b/contrib/pg_walinspect/expected/oldextversions.out @@ -51,15 +51,16 @@ ALTER EXTENSION pg_walinspect UPDATE TO '1.1'; ALTER EXTENSION pg_walinspect UPDATE TO '1.2'; -- List what the current version contains. \dx+ pg_walinspect - Objects in extension "pg_walinspect" - Object description -------------------------------------------------------- + Objects in extension "pg_walinspect" + Object description +---------------------------------------------------------------------------------- function pg_get_wal_block_info(pg_lsn,pg_lsn,boolean) function pg_get_wal_files(pg_lsn,pg_lsn) + function pg_get_wal_location_at_time(timestamp with time zone,interval,interval) function pg_get_wal_record_info(pg_lsn) function pg_get_wal_records_info(pg_lsn,pg_lsn) function pg_get_wal_stats(pg_lsn,pg_lsn,boolean) -(5 rows) +(6 rows) SELECT pg_drop_replication_slot('regress_pg_walinspect_slot'); pg_drop_replication_slot diff --git a/contrib/pg_walinspect/expected/pg_walinspect.out b/contrib/pg_walinspect/expected/pg_walinspect.out index 34864c65ec7..5933c80071d 100644 --- a/contrib/pg_walinspect/expected/pg_walinspect.out +++ b/contrib/pg_walinspect/expected/pg_walinspect.out @@ -139,6 +139,142 @@ FROM pg_get_wal_files(pg_current_wal_lsn(), 'FFFFFFFF/FFFFFFFF'); SELECT * FROM pg_get_wal_files('0/0', '0/1'); ERROR: could not read WAL at LSN 0/00000000 -- =================================================================== +-- Tests for locating WAL by timestamps stored in WAL records +-- =================================================================== +-- Put two WAL-logged transaction commits between the requested boundaries. +INSERT INTO sample_tbl VALUES (5, 5); +SELECT clock_timestamp() AS wal_time_lower \gset +INSERT INTO sample_tbl VALUES (6, 6); +INSERT INTO sample_tbl VALUES (10, 10); +SELECT clock_timestamp() AS wal_time_upper \gset +SELECT start_timestamp >= :'wal_time_lower'::timestamptz + AS start_ok, + end_timestamp > :'wal_time_lower'::timestamptz AND + end_timestamp <= :'wal_time_upper'::timestamptz AS end_ok, + start_lsn < end_lsn AS lsn_ok +FROM pg_get_wal_location_at_time(:'wal_time_lower', + before => interval '1 microsecond', + after => :'wal_time_upper'::timestamptz - + :'wal_time_lower'::timestamptz); + start_ok | end_ok | lsn_ok +----------+--------+-------- + t | t | t +(1 row) + +-- A single matching timestamped record is returned for both boundaries. +SELECT clock_timestamp() AS single_time_lower \gset +INSERT INTO sample_tbl VALUES (12, 12); +SELECT clock_timestamp() AS single_time_upper \gset +SELECT location.start_timestamp = location.end_timestamp AS timestamp_ok, + location.start_lsn = location.end_lsn AS lsn_ok, + info.record_type = 'COMMIT' AS record_ok +FROM pg_get_wal_location_at_time( + :'single_time_lower'::timestamptz + interval '1 microsecond', + interval '1 microsecond', + :'single_time_upper'::timestamptz - + :'single_time_lower'::timestamptz - interval '1 microsecond') AS location, + LATERAL pg_get_wal_record_info(location.start_lsn) AS info; + timestamp_ok | lsn_ok | record_ok +--------------+--------+----------- + t | t | t +(1 row) + +-- Exercise a time window containing a COMMIT PREPARED record. Concurrent +-- activity can cause another timestamped record to be selected as the anchor. +INSERT INTO sample_tbl VALUES (11, 11); +BEGIN; +INSERT INTO sample_tbl VALUES (7, 7); +PREPARE TRANSACTION 'regress_pg_walinspect_time'; +SELECT clock_timestamp() AS prepared_time_target \gset +COMMIT PREPARED 'regress_pg_walinspect_time'; +SELECT clock_timestamp() AS prepared_time_upper \gset +SELECT record_type IN ('COMMIT', 'COMMIT_PREPARED', 'ABORT', + 'ABORT_PREPARED', 'RESTORE_POINT') AS timestamped_ok +FROM pg_get_wal_location_at_time(:'prepared_time_target', + :'prepared_time_target'::timestamptz - + :'wal_time_upper'::timestamptz, + :'prepared_time_upper'::timestamptz - + :'prepared_time_target'::timestamptz) AS location, + LATERAL pg_get_wal_record_info(location.end_lsn); + timestamped_ok +---------------- + t +(1 row) + +-- Exercise a time window containing restore points, allowing for other +-- timestamped WAL records generated concurrently. +SELECT pg_create_restore_point('regress_wal_time_lower') AS restore_lsn \gset +SELECT clock_timestamp() AS restore_time_target \gset +SELECT pg_create_restore_point('regress_wal_time_upper') AS restore_lsn \gset +SELECT clock_timestamp() AS restore_time_upper \gset +INSERT INTO sample_tbl VALUES (8, 8); +SELECT start_info.record_type IN ('COMMIT', 'COMMIT_PREPARED', 'ABORT', + 'ABORT_PREPARED', 'RESTORE_POINT') AS start_ok, + end_info.record_type IN ('COMMIT', 'COMMIT_PREPARED', 'ABORT', + 'ABORT_PREPARED', 'RESTORE_POINT') AS end_ok +FROM pg_get_wal_location_at_time(:'restore_time_target', + :'restore_time_target'::timestamptz - + :'wal_time_upper'::timestamptz, + :'restore_time_upper'::timestamptz - + :'restore_time_target'::timestamptz) AS location, + LATERAL pg_get_wal_record_info(location.start_lsn) AS start_info, + LATERAL pg_get_wal_record_info(location.end_lsn) AS end_info; + start_ok | end_ok +----------+-------- + t | t +(1 row) + +-- A future upper bound uses a timestamped WAL record at or before now. +SELECT clock_timestamp() AS current_time_target \gset +INSERT INTO sample_tbl VALUES (9, 9); +SELECT location.start_timestamp >= :'restore_time_upper'::timestamptz AND + location.start_timestamp <= :'current_time_target'::timestamptz + AS start_ok, + location.end_timestamp >= :'current_time_target'::timestamptz AND + location.end_timestamp <= clock_timestamp() AS capped_end_ok, + location.start_lsn < location.end_lsn AS lsn_ok, + end_info.record_type = 'COMMIT' AS real_record_ok +FROM pg_get_wal_location_at_time(:'current_time_target', + :'current_time_target'::timestamptz - + :'restore_time_upper'::timestamptz, + interval '1 day') AS location, + LATERAL pg_get_wal_record_info(location.end_lsn) AS end_info; + start_ok | capped_end_ok | lsn_ok | real_record_ok +----------+---------------+--------+---------------- + t | t | t | t +(1 row) + +SELECT pg_get_function_arguments( + 'pg_get_wal_location_at_time(timestamptz, interval, interval)'::regprocedure) + LIKE '%before interval DEFAULT ''@ 1 min''::interval, after interval DEFAULT ''@ 1 min''::interval%' + AS defaults_ok; + defaults_ok +------------- + t +(1 row) + +SELECT * FROM pg_get_wal_location_at_time(:'wal_time_lower', + interval '0', interval '1 second'); +ERROR: before interval must be finite and greater than zero +SELECT * FROM pg_get_wal_location_at_time(:'wal_time_lower', + interval '1 second', interval '0'); +ERROR: after interval must be finite and greater than zero +SELECT * FROM pg_get_wal_location_at_time(:'wal_time_lower', interval '-1 second'); +ERROR: before interval must be finite and greater than zero +SELECT * FROM pg_get_wal_location_at_time(:'wal_time_lower', + after => interval '-1 second'); +ERROR: after interval must be finite and greater than zero +SELECT * FROM pg_get_wal_location_at_time(:'wal_time_lower', + before => interval '1 day 1 microsecond'); +ERROR: before interval must not exceed one day +SELECT * FROM pg_get_wal_location_at_time(:'wal_time_lower', + after => interval '1 day 1 microsecond'); +ERROR: after interval must not exceed one day +SELECT * FROM pg_get_wal_location_at_time(clock_timestamp() - interval '100 years'); +ERROR: could not locate a WAL time boundary +SELECT * FROM pg_get_wal_location_at_time(clock_timestamp() + interval '100 years'); +ERROR: requested time follows the available WAL range +-- =================================================================== -- Test for filtering out WAL records of a particular table -- =================================================================== SELECT oid AS sample_tbl_oid FROM pg_class WHERE relname = 'sample_tbl' \gset @@ -231,6 +367,13 @@ SELECT has_function_privilege('regress_pg_walinspect', f (1 row) +SELECT has_function_privilege('regress_pg_walinspect', + 'pg_get_wal_location_at_time(timestamptz, interval, interval)', 'EXECUTE'); -- no + has_function_privilege +------------------------ + f +(1 row) + SELECT has_function_privilege('regress_pg_walinspect', 'pg_get_wal_files(pg_lsn, pg_lsn)', 'EXECUTE'); -- no has_function_privilege @@ -268,6 +411,13 @@ SELECT has_function_privilege('regress_pg_walinspect', t (1 row) +SELECT has_function_privilege('regress_pg_walinspect', + 'pg_get_wal_location_at_time(timestamptz, interval, interval)', 'EXECUTE'); -- yes + has_function_privilege +------------------------ + t +(1 row) + SELECT has_function_privilege('regress_pg_walinspect', 'pg_get_wal_files(pg_lsn, pg_lsn)', 'EXECUTE'); -- yes has_function_privilege @@ -285,6 +435,8 @@ GRANT EXECUTE ON FUNCTION pg_get_wal_stats(pg_lsn, pg_lsn, boolean) TO regress_pg_walinspect; GRANT EXECUTE ON FUNCTION pg_get_wal_block_info(pg_lsn, pg_lsn, boolean) TO regress_pg_walinspect; +GRANT EXECUTE ON FUNCTION pg_get_wal_location_at_time(timestamptz, interval, interval) + TO regress_pg_walinspect; GRANT EXECUTE ON FUNCTION pg_get_wal_files(pg_lsn, pg_lsn) TO regress_pg_walinspect; SELECT has_function_privilege('regress_pg_walinspect', @@ -315,6 +467,13 @@ SELECT has_function_privilege('regress_pg_walinspect', t (1 row) +SELECT has_function_privilege('regress_pg_walinspect', + 'pg_get_wal_location_at_time(timestamptz, interval, interval)', 'EXECUTE'); -- yes + has_function_privilege +------------------------ + t +(1 row) + SELECT has_function_privilege('regress_pg_walinspect', 'pg_get_wal_files(pg_lsn, pg_lsn)', 'EXECUTE'); -- yes has_function_privilege @@ -330,6 +489,8 @@ REVOKE EXECUTE ON FUNCTION pg_get_wal_stats(pg_lsn, pg_lsn, boolean) FROM regress_pg_walinspect; REVOKE EXECUTE ON FUNCTION pg_get_wal_block_info(pg_lsn, pg_lsn, boolean) FROM regress_pg_walinspect; +REVOKE EXECUTE ON FUNCTION pg_get_wal_location_at_time(timestamptz, interval, interval) + FROM regress_pg_walinspect; REVOKE EXECUTE ON FUNCTION pg_get_wal_files(pg_lsn, pg_lsn) FROM regress_pg_walinspect; -- =================================================================== diff --git a/contrib/pg_walinspect/meson.build b/contrib/pg_walinspect/meson.build index 6f8b4a6359a..79e94e33e4d 100644 --- a/contrib/pg_walinspect/meson.build +++ b/contrib/pg_walinspect/meson.build @@ -41,6 +41,7 @@ tests += { 'tap': { 'tests': [ 't/001_timeline.pl', + 't/002_time_search.pl', ], }, } diff --git a/contrib/pg_walinspect/pg_walinspect--1.1--1.2.sql b/contrib/pg_walinspect/pg_walinspect--1.1--1.2.sql index 9cc9523b4dc..f72d99a3ceb 100644 --- a/contrib/pg_walinspect/pg_walinspect--1.1--1.2.sql +++ b/contrib/pg_walinspect/pg_walinspect--1.1--1.2.sql @@ -3,6 +3,27 @@ -- complain if script is sourced in psql, rather than via ALTER EXTENSION \echo Use "ALTER EXTENSION pg_walinspect UPDATE TO '1.2'" to load this file. \quit +-- +-- pg_get_wal_location_at_time() +-- +CREATE FUNCTION pg_get_wal_location_at_time( + IN target_time timestamptz, + IN before interval DEFAULT '1 minute', + IN after interval DEFAULT '1 minute', + OUT start_timestamp timestamptz, + OUT start_lsn pg_lsn, + OUT end_timestamp timestamptz, + OUT end_lsn pg_lsn +) +AS 'MODULE_PATHNAME', 'pg_get_wal_location_at_time' +LANGUAGE C STRICT PARALLEL SAFE; + +REVOKE EXECUTE ON FUNCTION + pg_get_wal_location_at_time(timestamptz, interval, interval) FROM PUBLIC; +GRANT EXECUTE ON FUNCTION + pg_get_wal_location_at_time(timestamptz, interval, interval) + TO pg_read_server_files; + -- -- pg_get_wal_files() -- diff --git a/contrib/pg_walinspect/pg_walinspect.c b/contrib/pg_walinspect/pg_walinspect.c index 42f366bdb4e..0511bb591e7 100644 --- a/contrib/pg_walinspect/pg_walinspect.c +++ b/contrib/pg_walinspect/pg_walinspect.c @@ -12,6 +12,8 @@ */ #include "postgres.h" +#include <sys/stat.h> + #include "access/htup_details.h" #include "access/timeline.h" #include "access/xlog.h" @@ -26,7 +28,9 @@ #include "storage/fd.h" #include "utils/array.h" #include "utils/builtins.h" +#include "utils/fmgrprotos.h" #include "utils/pg_lsn.h" +#include "utils/timestamp.h" #include "utils/tuplestore.h" /* @@ -41,6 +45,7 @@ PG_MODULE_MAGIC_EXT( PG_FUNCTION_INFO_V1(pg_get_wal_block_info); PG_FUNCTION_INFO_V1(pg_get_wal_files); +PG_FUNCTION_INFO_V1(pg_get_wal_location_at_time); PG_FUNCTION_INFO_V1(pg_get_wal_record_info); PG_FUNCTION_INFO_V1(pg_get_wal_records_info); PG_FUNCTION_INFO_V1(pg_get_wal_records_info_till_end_of_wal); @@ -71,15 +76,51 @@ static void GetWalStats(FunctionCallInfo fcinfo, static void GetWALBlockInfo(FunctionCallInfo fcinfo, XLogReaderState *record, bool show_data); +/* Selected WAL position and timestamp for one time boundary. */ +typedef struct WalTimeBoundary +{ + bool found; /* matching record was found */ + TimestampTz time; /* record's local event time */ + XLogRecPtr lsn; /* record's start LSN */ + int segment_index; /* index of the segment containing the record */ +} WalTimeBoundary; + /* Metadata for one retained segment in the current timeline's history. */ typedef struct WalTimeSegment { XLogSegNo segno; /* segment number, used for WAL ordering */ TimeLineID tli; /* timeline containing this segment */ + bool scanned; /* segment has already been decoded */ + bool has_timestamp; /* segment contains timestamped records */ + TimestampTz min_time; /* minimum record timestamp in segment */ + TimestampTz max_time; /* maximum record timestamp in segment */ + WalTimeBoundary lower; /* best lower anchor in segment */ + WalTimeBoundary upper; /* best upper anchor in segment */ } WalTimeSegment; static int wal_time_segment_cmp(const void *a, const void *b); static WalTimeSegment *GetWalTimeSegments(int *nsegments); +static int FindWalTimeCandidate(WalTimeSegment *segments, + int *search_segments, + int *nsearch_segments, + TimestampTz target_time, + bool search_left, + TimestampTz wanted_start, + TimestampTz wanted_end, + int *candidate_position, + int *nscanned); +static void ScanWalTimeSegment(WalTimeSegment *segment, int segment_index, + TimestampTz wanted_start, + TimestampTz wanted_end); +static void MergeWalTimeSegment(WalTimeSegment *segment, + WalTimeBoundary *lower, + WalTimeBoundary *upper); +static void ProbeLowerBoundary(WalTimeBoundary *boundary, + TimestampTz time, XLogRecPtr lsn, + int segment_index); +static void ProbeUpperBoundary(WalTimeBoundary *boundary, + TimestampTz time, XLogRecPtr lsn, + int segment_index); /* * Return the LSN up to which the server has WAL, and optionally its timeline. @@ -223,6 +264,44 @@ wal_time_segment_cmp(const void *a, const void *b) return 0; } +/* + * Probe a timestamped record at or after a requested time boundary. Among + * records examined so far, prefer the earliest time, breaking ties in favor + * of the earlier WAL record. + */ +static void +ProbeLowerBoundary(WalTimeBoundary *boundary, TimestampTz time, + XLogRecPtr lsn, int segment_index) +{ + if (!boundary->found || time < boundary->time || + (time == boundary->time && lsn < boundary->lsn)) + { + boundary->found = true; + boundary->time = time; + boundary->lsn = lsn; + boundary->segment_index = segment_index; + } +} + +/* + * Probe a timestamped record at or before a requested time boundary. Among + * records examined so far, prefer the latest time, breaking ties in favor of + * the later WAL record. + */ +static void +ProbeUpperBoundary(WalTimeBoundary *boundary, TimestampTz time, + XLogRecPtr lsn, int segment_index) +{ + if (!boundary->found || time > boundary->time || + (time == boundary->time && lsn > boundary->lsn)) + { + boundary->found = true; + boundary->time = time; + boundary->lsn = lsn; + boundary->segment_index = segment_index; + } +} + /* * Get the WAL segments that form the history of the server's current * timeline. Other timelines may have files with the same segment number in @@ -297,6 +376,140 @@ GetWalTimeSegments(int *nsegments) return segments; } +/* + * Approximately locate the segment containing target_time by binary search. + * Record timestamps are expected to generally increase with WAL position. + * Remove segments without timestamped records from the search candidates, + * since they provide no value with which to choose either half. + */ +static int +FindWalTimeCandidate(WalTimeSegment *segments, int *search_segments, + int *nsearch_segments, + TimestampTz target_time, bool search_left, + TimestampTz wanted_start, TimestampTz wanted_end, + int *candidate_position, int *nscanned) +{ + int left = 0; + int right = *nsearch_segments - 1; + int result; + + while (left <= right) + { + int middle = left + (right - left) / 2; + int segment_index = search_segments[middle]; + WalTimeSegment *segment = &segments[segment_index]; + + if (!segment->scanned) + { + ScanWalTimeSegment(segment, segment_index, wanted_start, wanted_end); + (*nscanned)++; + } + + if (!segment->has_timestamp) + { + memmove(&search_segments[middle], &search_segments[middle + 1], + (*nsearch_segments - middle - 1) * sizeof(int)); + (*nsearch_segments)--; + right--; + continue; + } + else if (target_time < segment->min_time) + right = middle - 1; + else if (target_time > segment->max_time) + left = middle + 1; + else + { + if (candidate_position != NULL) + *candidate_position = middle; + return search_segments[middle]; + } + } + + if (*nsearch_segments == 0) + ereport(ERROR, + errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("could not locate a WAL time boundary"), + errdetail("No retained WAL segment contains a timestamped record.")); + + if (search_left) + result = Max(right, 0); + else + result = Min(left, *nsearch_segments - 1); + + if (candidate_position != NULL) + *candidate_position = result; + return search_segments[result]; +} + +/* + * Inspect timestamped records beginning in one segment and save its time + * range and best boundary candidates. XLogReadRecord() may read the following + * segment to assemble a record crossing the boundary, but that record is + * attributed to the segment containing its starting LSN. + */ +static void +ScanWalTimeSegment(WalTimeSegment *segment, int segment_index, + TimestampTz wanted_start, TimestampTz wanted_end) +{ + XLogRecPtr seg_start = segment->segno * wal_segment_size; + XLogRecPtr seg_end = seg_start + wal_segment_size; + XLogReaderState *xlogreader; + + Assert(!segment->scanned); + segment->scanned = true; + xlogreader = InitXLogReaderState(seg_start); + + while (ReadNextXLogRecord(xlogreader)) + { + TimestampTz record_time; + + if (xlogreader->ReadRecPtr >= seg_end) + break; + + if (!GetXLogRecordTimestamp(xlogreader, &record_time)) + continue; + + if (!segment->has_timestamp) + { + segment->has_timestamp = true; + segment->min_time = segment->max_time = record_time; + } + else + { + segment->min_time = Min(segment->min_time, record_time); + segment->max_time = Max(segment->max_time, record_time); + } + + if (record_time >= wanted_start) + ProbeLowerBoundary(&segment->lower, record_time, + xlogreader->ReadRecPtr, segment_index); + + if (record_time <= wanted_end) + ProbeUpperBoundary(&segment->upper, record_time, + xlogreader->ReadRecPtr, segment_index); + + CHECK_FOR_INTERRUPTS(); + } + + pfree(xlogreader->private_data); + XLogReaderFree(xlogreader); +} + +/* Merge one segment's boundary candidates into the result. */ +static void +MergeWalTimeSegment(WalTimeSegment *segment, WalTimeBoundary *lower, + WalTimeBoundary *upper) +{ + Assert(segment->scanned); + + if (lower != NULL && segment->lower.found) + ProbeLowerBoundary(lower, segment->lower.time, segment->lower.lsn, + segment->lower.segment_index); + if (upper != NULL && segment->upper.found) + ProbeUpperBoundary(upper, segment->upper.time, segment->upper.lsn, + segment->upper.segment_index); +} + /* * Output values that make up a row describing caller's WAL record. * @@ -623,6 +836,215 @@ pg_get_wal_record_info(PG_FUNCTION_ARGS) #undef PG_GET_WAL_RECORD_INFO_COLS } +/* + * Locate a WAL region around a wall-clock time range using timestamped WAL + * records. + * + * Locate each boundary by an approximate binary search over retained WAL + * segments, assuming that record timestamps generally increase with WAL + * position. Also examine one preceding segment for the lower boundary and + * one following segment for the upper boundary to allow for limited timestamp + * reordering. The lower boundary uses the earliest timestamped record at or + * after the requested start time, while the upper boundary uses the latest + * one at or before the requested end time. A future upper bound is first + * capped at the current time. On success, the boundaries form a forward WAL + * range, but are not necessarily the globally closest timestamped records to + * the requested bounds. + */ +Datum +pg_get_wal_location_at_time(PG_FUNCTION_ARGS) +{ +#define PG_GET_WAL_LOCATION_AT_TIME_COLS 4 + TimestampTz target_time = PG_GETARG_TIMESTAMPTZ(0); + Interval *before = PG_GETARG_INTERVAL_P(1); + Interval *after = PG_GETARG_INTERVAL_P(2); + Interval zero = {0}; + Interval one_day = {.day = 1}; + TimestampTz wanted_start; + TimestampTz wanted_end; + TimestampTz current_time; + WalTimeSegment *segments; + WalTimeBoundary lower = {0}; + WalTimeBoundary upper = {0}; + Datum values[PG_GET_WAL_LOCATION_AT_TIME_COLS]; + bool nulls[PG_GET_WAL_LOCATION_AT_TIME_COLS] = {0}; + TupleDesc tupdesc; + HeapTuple tuple; + int nsegments; + int *search_segments; + int nsearch_segments; + int lower_candidate; + int upper_candidate; + int lower_candidate_position; + int nupper_search_segments; + int lower_start; + int upper_end; + int nscanned = 0; + char lower_fname[MAXFNAMELEN]; + char upper_fname[MAXFNAMELEN]; + + if (TIMESTAMP_NOT_FINITE(target_time)) + ereport(ERROR, + errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("target time must be finite")); + if (INTERVAL_NOT_FINITE(before) || + DatumGetInt32(DirectFunctionCall2(interval_cmp, + IntervalPGetDatum(before), + IntervalPGetDatum(&zero))) <= 0) + ereport(ERROR, + errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("before interval must be finite and greater than zero")); + if (DatumGetInt32(DirectFunctionCall2(interval_cmp, + IntervalPGetDatum(before), + IntervalPGetDatum(&one_day))) > 0) + ereport(ERROR, + errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("before interval must not exceed one day")); + if (INTERVAL_NOT_FINITE(after) || + DatumGetInt32(DirectFunctionCall2(interval_cmp, + IntervalPGetDatum(after), + IntervalPGetDatum(&zero))) <= 0) + ereport(ERROR, + errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("after interval must be finite and greater than zero")); + if (DatumGetInt32(DirectFunctionCall2(interval_cmp, + IntervalPGetDatum(after), + IntervalPGetDatum(&one_day))) > 0) + ereport(ERROR, + errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("after interval must not exceed one day")); + + /* The timestamp functions provide the normal overflow checks. */ + wanted_start = DatumGetTimestampTz(DirectFunctionCall2(timestamptz_mi_interval, + TimestampTzGetDatum(target_time), + IntervalPGetDatum(before))); + wanted_end = DatumGetTimestampTz(DirectFunctionCall2(timestamptz_pl_interval, + TimestampTzGetDatum(target_time), + IntervalPGetDatum(after))); + current_time = GetCurrentTimestamp(); + + /* Cap a future upper bound at the time currently available. */ + if (wanted_end > current_time) + { + if (wanted_start >= current_time) + ereport(ERROR, + errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("requested time follows the available WAL range"), + errdetail("The requested lower bound is not earlier than the current time.")); + + wanted_end = current_time; + } + + if (get_call_result_type(fcinfo, NULL, &tupdesc) != TYPEFUNC_COMPOSITE) + elog(ERROR, "return type must be a row type"); + + segments = GetWalTimeSegments(&nsegments); + search_segments = palloc_array(int, nsegments); + for (int i = 0; i < nsegments; i++) + search_segments[i] = i; + nsearch_segments = nsegments; + lower_candidate = FindWalTimeCandidate(segments, search_segments, + &nsearch_segments, wanted_start, + false, wanted_start, wanted_end, + &lower_candidate_position, + &nscanned); + + /* Search for the later upper boundary from the lower candidate onward. */ + nupper_search_segments = nsearch_segments - lower_candidate_position; + upper_candidate = FindWalTimeCandidate(segments, + search_segments + lower_candidate_position, + &nupper_search_segments, wanted_end, + true, wanted_start, wanted_end, + NULL, + &nscanned); + XLogFileName(lower_fname, segments[lower_candidate].tli, + segments[lower_candidate].segno, wal_segment_size); + XLogFileName(upper_fname, segments[upper_candidate].tli, + segments[upper_candidate].segno, wal_segment_size); + ereport(DEBUG1, + errmsg_internal("WAL time search selected lower segment %s and upper segment %s from %d retained segments", + lower_fname, upper_fname, + nsegments)); + + /* + * Allow for limited timestamp reordering by examining one preceding + * segment for the lower boundary and one following segment for the upper + * boundary. Do not extend across a missing segment. + */ + lower_start = lower_candidate; + if (lower_start > 0 && + segments[lower_start - 1].segno + 1 == segments[lower_start].segno) + lower_start--; + upper_end = upper_candidate; + if (upper_end + 1 < nsegments && + segments[upper_end].segno + 1 == segments[upper_end + 1].segno) + upper_end++; + + for (int i = lower_start; i <= lower_candidate; i++) + { + if (!segments[i].scanned) + { + ScanWalTimeSegment(&segments[i], i, wanted_start, wanted_end); + nscanned++; + } + MergeWalTimeSegment(&segments[i], &lower, NULL); + } + for (int i = upper_candidate; i <= upper_end; i++) + { + if (!segments[i].scanned) + { + ScanWalTimeSegment(&segments[i], i, wanted_start, wanted_end); + nscanned++; + } + MergeWalTimeSegment(&segments[i], NULL, &upper); + } + + ereport(DEBUG1, + errmsg_internal("WAL time search decoded %d of %d retained segments", + nscanned, nsegments)); + + if (!lower.found) + ereport(ERROR, + errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("could not locate a WAL time boundary"), + errdetail("The WAL search did not find a timestamped record at or after the requested lower bound.")); + if (!upper.found) + ereport(ERROR, + errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("could not locate a WAL time boundary"), + errdetail("The WAL search did not find a timestamped record at or before the requested upper bound.")); + + if (lower.lsn > upper.lsn) + ereport(ERROR, + errcode(ERRCODE_DATA_EXCEPTION), + errmsg("could not find a valid WAL range for the requested time window"), + errdetail("The matching lower boundary follows the upper boundary in WAL.")); + + Assert(lower.segment_index >= 0 && lower.segment_index < nsegments); + Assert(upper.segment_index >= 0 && upper.segment_index < nsegments); + for (int i = lower.segment_index + 1; i <= upper.segment_index; i++) + { + if (segments[i - 1].segno + 1 != segments[i].segno) + ereport(ERROR, + errcode(ERRCODE_DATA_EXCEPTION), + errmsg("WAL segment needed for the time search is missing"), + errdetail("The start LSN is %X/%08X, the end LSN is %X/%08X, and segment " UINT64_FORMAT " is not present in pg_wal.", + LSN_FORMAT_ARGS(lower.lsn), + LSN_FORMAT_ARGS(upper.lsn), + (uint64) (segments[i - 1].segno + 1))); + } + + values[0] = TimestampTzGetDatum(lower.time); + values[1] = LSNGetDatum(lower.lsn); + values[2] = TimestampTzGetDatum(upper.time); + values[3] = LSNGetDatum(upper.lsn); + + pfree(segments); + tuple = heap_form_tuple(tupdesc, values, nulls); + PG_RETURN_DATUM(HeapTupleGetDatum(tuple)); +#undef PG_GET_WAL_LOCATION_AT_TIME_COLS +} + /* * List the retained WAL segment files intersecting [start_lsn, end_lsn), or * the segment containing start_lsn when end_lsn is omitted or equal to it. diff --git a/contrib/pg_walinspect/sql/pg_walinspect.sql b/contrib/pg_walinspect/sql/pg_walinspect.sql index 3176a17d855..dbc3240ae30 100644 --- a/contrib/pg_walinspect/sql/pg_walinspect.sql +++ b/contrib/pg_walinspect/sql/pg_walinspect.sql @@ -75,6 +75,114 @@ SELECT count(*) = 1 AS future_end_ok FROM pg_get_wal_files(pg_current_wal_lsn(), 'FFFFFFFF/FFFFFFFF'); SELECT * FROM pg_get_wal_files('0/0', '0/1'); +-- =================================================================== +-- Tests for locating WAL by timestamps stored in WAL records +-- =================================================================== + +-- Put two WAL-logged transaction commits between the requested boundaries. +INSERT INTO sample_tbl VALUES (5, 5); +SELECT clock_timestamp() AS wal_time_lower \gset +INSERT INTO sample_tbl VALUES (6, 6); +INSERT INTO sample_tbl VALUES (10, 10); +SELECT clock_timestamp() AS wal_time_upper \gset + +SELECT start_timestamp >= :'wal_time_lower'::timestamptz + AS start_ok, + end_timestamp > :'wal_time_lower'::timestamptz AND + end_timestamp <= :'wal_time_upper'::timestamptz AS end_ok, + start_lsn < end_lsn AS lsn_ok +FROM pg_get_wal_location_at_time(:'wal_time_lower', + before => interval '1 microsecond', + after => :'wal_time_upper'::timestamptz - + :'wal_time_lower'::timestamptz); + +-- A single matching timestamped record is returned for both boundaries. +SELECT clock_timestamp() AS single_time_lower \gset +INSERT INTO sample_tbl VALUES (12, 12); +SELECT clock_timestamp() AS single_time_upper \gset +SELECT location.start_timestamp = location.end_timestamp AS timestamp_ok, + location.start_lsn = location.end_lsn AS lsn_ok, + info.record_type = 'COMMIT' AS record_ok +FROM pg_get_wal_location_at_time( + :'single_time_lower'::timestamptz + interval '1 microsecond', + interval '1 microsecond', + :'single_time_upper'::timestamptz - + :'single_time_lower'::timestamptz - interval '1 microsecond') AS location, + LATERAL pg_get_wal_record_info(location.start_lsn) AS info; + +-- Exercise a time window containing a COMMIT PREPARED record. Concurrent +-- activity can cause another timestamped record to be selected as the anchor. +INSERT INTO sample_tbl VALUES (11, 11); +BEGIN; +INSERT INTO sample_tbl VALUES (7, 7); +PREPARE TRANSACTION 'regress_pg_walinspect_time'; +SELECT clock_timestamp() AS prepared_time_target \gset +COMMIT PREPARED 'regress_pg_walinspect_time'; +SELECT clock_timestamp() AS prepared_time_upper \gset + +SELECT record_type IN ('COMMIT', 'COMMIT_PREPARED', 'ABORT', + 'ABORT_PREPARED', 'RESTORE_POINT') AS timestamped_ok +FROM pg_get_wal_location_at_time(:'prepared_time_target', + :'prepared_time_target'::timestamptz - + :'wal_time_upper'::timestamptz, + :'prepared_time_upper'::timestamptz - + :'prepared_time_target'::timestamptz) AS location, + LATERAL pg_get_wal_record_info(location.end_lsn); + +-- Exercise a time window containing restore points, allowing for other +-- timestamped WAL records generated concurrently. +SELECT pg_create_restore_point('regress_wal_time_lower') AS restore_lsn \gset +SELECT clock_timestamp() AS restore_time_target \gset +SELECT pg_create_restore_point('regress_wal_time_upper') AS restore_lsn \gset +SELECT clock_timestamp() AS restore_time_upper \gset +INSERT INTO sample_tbl VALUES (8, 8); + +SELECT start_info.record_type IN ('COMMIT', 'COMMIT_PREPARED', 'ABORT', + 'ABORT_PREPARED', 'RESTORE_POINT') AS start_ok, + end_info.record_type IN ('COMMIT', 'COMMIT_PREPARED', 'ABORT', + 'ABORT_PREPARED', 'RESTORE_POINT') AS end_ok +FROM pg_get_wal_location_at_time(:'restore_time_target', + :'restore_time_target'::timestamptz - + :'wal_time_upper'::timestamptz, + :'restore_time_upper'::timestamptz - + :'restore_time_target'::timestamptz) AS location, + LATERAL pg_get_wal_record_info(location.start_lsn) AS start_info, + LATERAL pg_get_wal_record_info(location.end_lsn) AS end_info; + +-- A future upper bound uses a timestamped WAL record at or before now. +SELECT clock_timestamp() AS current_time_target \gset +INSERT INTO sample_tbl VALUES (9, 9); +SELECT location.start_timestamp >= :'restore_time_upper'::timestamptz AND + location.start_timestamp <= :'current_time_target'::timestamptz + AS start_ok, + location.end_timestamp >= :'current_time_target'::timestamptz AND + location.end_timestamp <= clock_timestamp() AS capped_end_ok, + location.start_lsn < location.end_lsn AS lsn_ok, + end_info.record_type = 'COMMIT' AS real_record_ok +FROM pg_get_wal_location_at_time(:'current_time_target', + :'current_time_target'::timestamptz - + :'restore_time_upper'::timestamptz, + interval '1 day') AS location, + LATERAL pg_get_wal_record_info(location.end_lsn) AS end_info; + +SELECT pg_get_function_arguments( + 'pg_get_wal_location_at_time(timestamptz, interval, interval)'::regprocedure) + LIKE '%before interval DEFAULT ''@ 1 min''::interval, after interval DEFAULT ''@ 1 min''::interval%' + AS defaults_ok; +SELECT * FROM pg_get_wal_location_at_time(:'wal_time_lower', + interval '0', interval '1 second'); +SELECT * FROM pg_get_wal_location_at_time(:'wal_time_lower', + interval '1 second', interval '0'); +SELECT * FROM pg_get_wal_location_at_time(:'wal_time_lower', interval '-1 second'); +SELECT * FROM pg_get_wal_location_at_time(:'wal_time_lower', + after => interval '-1 second'); +SELECT * FROM pg_get_wal_location_at_time(:'wal_time_lower', + before => interval '1 day 1 microsecond'); +SELECT * FROM pg_get_wal_location_at_time(:'wal_time_lower', + after => interval '1 day 1 microsecond'); +SELECT * FROM pg_get_wal_location_at_time(clock_timestamp() - interval '100 years'); +SELECT * FROM pg_get_wal_location_at_time(clock_timestamp() + interval '100 years'); + -- =================================================================== -- Test for filtering out WAL records of a particular table -- =================================================================== @@ -134,6 +242,8 @@ SELECT has_function_privilege('regress_pg_walinspect', 'pg_get_wal_stats(pg_lsn, pg_lsn, boolean) ', 'EXECUTE'); -- no SELECT has_function_privilege('regress_pg_walinspect', 'pg_get_wal_block_info(pg_lsn, pg_lsn, boolean) ', 'EXECUTE'); -- no +SELECT has_function_privilege('regress_pg_walinspect', + 'pg_get_wal_location_at_time(timestamptz, interval, interval)', 'EXECUTE'); -- no SELECT has_function_privilege('regress_pg_walinspect', 'pg_get_wal_files(pg_lsn, pg_lsn)', 'EXECUTE'); -- no @@ -148,6 +258,8 @@ SELECT has_function_privilege('regress_pg_walinspect', 'pg_get_wal_stats(pg_lsn, pg_lsn, boolean) ', 'EXECUTE'); -- yes SELECT has_function_privilege('regress_pg_walinspect', 'pg_get_wal_block_info(pg_lsn, pg_lsn, boolean) ', 'EXECUTE'); -- yes +SELECT has_function_privilege('regress_pg_walinspect', + 'pg_get_wal_location_at_time(timestamptz, interval, interval)', 'EXECUTE'); -- yes SELECT has_function_privilege('regress_pg_walinspect', 'pg_get_wal_files(pg_lsn, pg_lsn)', 'EXECUTE'); -- yes @@ -162,6 +274,8 @@ GRANT EXECUTE ON FUNCTION pg_get_wal_stats(pg_lsn, pg_lsn, boolean) TO regress_pg_walinspect; GRANT EXECUTE ON FUNCTION pg_get_wal_block_info(pg_lsn, pg_lsn, boolean) TO regress_pg_walinspect; +GRANT EXECUTE ON FUNCTION pg_get_wal_location_at_time(timestamptz, interval, interval) + TO regress_pg_walinspect; GRANT EXECUTE ON FUNCTION pg_get_wal_files(pg_lsn, pg_lsn) TO regress_pg_walinspect; @@ -173,6 +287,8 @@ SELECT has_function_privilege('regress_pg_walinspect', 'pg_get_wal_stats(pg_lsn, pg_lsn, boolean) ', 'EXECUTE'); -- yes SELECT has_function_privilege('regress_pg_walinspect', 'pg_get_wal_block_info(pg_lsn, pg_lsn, boolean) ', 'EXECUTE'); -- yes +SELECT has_function_privilege('regress_pg_walinspect', + 'pg_get_wal_location_at_time(timestamptz, interval, interval)', 'EXECUTE'); -- yes SELECT has_function_privilege('regress_pg_walinspect', 'pg_get_wal_files(pg_lsn, pg_lsn)', 'EXECUTE'); -- yes @@ -184,6 +300,8 @@ REVOKE EXECUTE ON FUNCTION pg_get_wal_stats(pg_lsn, pg_lsn, boolean) FROM regress_pg_walinspect; REVOKE EXECUTE ON FUNCTION pg_get_wal_block_info(pg_lsn, pg_lsn, boolean) FROM regress_pg_walinspect; +REVOKE EXECUTE ON FUNCTION pg_get_wal_location_at_time(timestamptz, interval, interval) + FROM regress_pg_walinspect; REVOKE EXECUTE ON FUNCTION pg_get_wal_files(pg_lsn, pg_lsn) FROM regress_pg_walinspect; diff --git a/contrib/pg_walinspect/t/002_time_search.pl b/contrib/pg_walinspect/t/002_time_search.pl new file mode 100644 index 00000000000..85ed7fb7bf4 --- /dev/null +++ b/contrib/pg_walinspect/t/002_time_search.pl @@ -0,0 +1,51 @@ +# Copyright (c) 2026, PostgreSQL Global Development Group + +# Test the bounded binary search for WAL time boundaries across segments. +use strict; +use warnings FATAL => 'all'; + +use PostgreSQL::Test::Cluster; +use PostgreSQL::Test::Utils; +use Test::More; + +my $node = PostgreSQL::Test::Cluster->new('time_search'); +$node->init(extra => ['--wal-segsize=1']); +$node->append_conf('postgresql.conf', "autovacuum = off\n"); +$node->start; +$node->safe_psql('postgres', + 'CREATE EXTENSION pg_walinspect; CREATE TABLE test_table (a int);'); +$node->safe_psql('postgres', 'SELECT pg_switch_wal()'); + +my ($lower_time, $upper_time); +for my $i (0 .. 8) +{ + $node->safe_psql('postgres', "INSERT INTO test_table VALUES ($i)"); + $lower_time = $node->safe_psql('postgres', 'SELECT clock_timestamp()') + if $i == 3; + $upper_time = $node->safe_psql('postgres', 'SELECT clock_timestamp()') + if $i == 5; + $node->safe_psql('postgres', 'SELECT pg_switch_wal()') if $i < 8; +} + +my $log_offset = -s $node->logfile; +my $result = $node->safe_psql( + 'postgres', qq{ +SET log_min_messages = debug1; +SELECT start_timestamp >= '$lower_time'::timestamptz - interval '1 microsecond', + end_timestamp > '$lower_time'::timestamptz, + end_timestamp <= '$upper_time'::timestamptz, + start_lsn < end_lsn, + pg_walfile_name(start_lsn) <> pg_walfile_name(end_lsn) +FROM pg_get_wal_location_at_time('$lower_time', + interval '1 microsecond', + '$upper_time'::timestamptz - + '$lower_time'::timestamptz)}); +is($result, 't|t|t|t|t', 'binary search finds boundaries across WAL segments'); + +my $log = slurp_file($node->logfile, $log_offset); +$log =~ /WAL time search decoded (\d+) of (\d+) retained segments/ + or die "could not find WAL time search statistics in server log"; +cmp_ok($1, '<', $2, 'binary search does not decode all retained WAL'); + +$node->stop; +done_testing(); diff --git a/doc/src/sgml/pgwalinspect.sgml b/doc/src/sgml/pgwalinspect.sgml index b4ecae8cfb1..e00beb7d657 100644 --- a/doc/src/sgml/pgwalinspect.sgml +++ b/doc/src/sgml/pgwalinspect.sgml @@ -88,6 +88,113 @@ </listitem> </varlistentry> + <varlistentry id="pgwalinspect-funcs-pg-get-wal-location-at-time"> + <term> + <function>pg_get_wal_location_at_time(target_time timestamptz, before interval DEFAULT '1 minute', after interval DEFAULT '1 minute') returns record</function> + </term> + + <listitem> + <para> + Locates a nearby WAL region using records containing local event + timestamps as historical time anchors. The requested time range extends + from <replaceable>target_time</replaceable> minus + <replaceable>before</replaceable> through + <replaceable>target_time</replaceable> plus + <replaceable>after</replaceable>. It returns + <structfield>start_timestamp</structfield>, + <structfield>start_lsn</structfield>, + <structfield>end_timestamp</structfield>, and + <structfield>end_lsn</structfield>. On successful return, the following + relationships hold: +<programlisting> +start_timestamp &gt;= target_time - before +end_timestamp &lt;= target_time + after +start_lsn &lt;= end_lsn +</programlisting> + The start boundary is the earliest timestamped record found at or after + the requested start time, and the end boundary is the latest timestamped + record found at or before the requested end time. If + <replaceable>target_time</replaceable> plus + <replaceable>after</replaceable> is later than the current time, the + requested upper bound is first capped at the current time. The function + does not construct an end boundary from the current WAL position. Thus, + both returned boundaries come from actual timestamped WAL records. If + only one timestamped record matches the requested time range, both + boundaries identify that record and the returned timestamps and LSNs + are equal. Both interval arguments must be greater than zero and not + greater than one day. + </para> + <para> + Most WAL records do not contain a wall-clock timestamp. Historical + anchors can be transaction <literal>COMMIT</literal>, + <literal>COMMIT PREPARED</literal>, <literal>ABORT</literal>, + or <literal>ABORT PREPARED</literal> records, as well as restore point + records. These are the WAL record types from which PostgreSQL recovery + can extract timestamps. Their timestamps describe the corresponding + event; they do not assign timestamps to heap, index, or other records. + </para> + <para> + In particular, a commit timestamp describes when a transaction + committed, not when its earlier changes generated WAL. For example, a + transaction might update a row at 14:00 and commit at 15:00; the heap + WAL was generated around 14:00, while the commit record is timestamped + 15:00. The returned LSNs therefore do not identify the exact generation + time of heap, index, or other WAL records, nor do they guarantee that + every record generated during the requested wall-clock range lies + between the returned LSNs. On a cluster with little timestamped + activity, the returned timestamps can be far from the requested bounds. + </para> + <para> + The function assumes that WAL record timestamps generally increase with + WAL position and uses an approximate binary search to locate each time + boundary. WAL record timestamps are not guaranteed to follow WAL order, + for example because of concurrent activity, clock adjustments, or + delayed WAL insertion. Such timestamp reordering can cause relevant + records to fall outside the returned range. Therefore, the returned + records are not guaranteed to be the globally closest timestamped + records to either requested time bound. If the returned range does not + contain the records of interest, the user can widen the search range and + repeat the search. If the matching lower boundary follows the upper + boundary in WAL, the function reports an error rather than returning a + range with the LSNs reversed. + </para> + <para> + Only WAL that is currently retained in <filename>pg_wal</filename> can + be searched. Unlike the other <application>pg_walinspect</application> + functions, this function may inspect retained WAL from the current + timeline's history as well as the current timeline. The function + reports an error when either boundary cannot be found by the bounded + search or when the resulting range would cross a missing WAL segment. + </para> + <para> + For example, a DBA can locate WAL around activity near a given time and + then inspect the returned range: +<programlisting> +SELECT * +FROM pg_get_wal_location_at_time( + '2026-08-01 14:35:00+08', + before =&gt; '10 minutes', + after =&gt; '10 minutes'); + +SELECT * +FROM pg_get_wal_files('<replaceable>start_lsn</replaceable>', + '<replaceable>end_lsn</replaceable>'); + +SELECT * +FROM pg_get_wal_records_info('<replaceable>start_lsn</replaceable>', + '<replaceable>end_lsn</replaceable>'); +</programlisting> + The corresponding retained WAL files can be obtained with + <function>pg_get_wal_files</function>. To widen the search range, + increase <replaceable>before</replaceable> if the relevant activity + might have occurred earlier, or increase + <replaceable>after</replaceable> if it might have occurred later, and + repeat the search. Failure to find a record in the first returned range + does not necessarily mean that the record is absent from retained WAL. + </para> + </listitem> + </varlistentry> + <varlistentry id="pgwalinspect-funcs-pg-get-wal-record-info"> <term> <function>pg_get_wal_record_info(in_lsn pg_lsn) returns record</function> diff --git a/src/backend/access/transam/xlogreader.c b/src/backend/access/transam/xlogreader.c index 7db7c273b0c..02f49f64b3b 100644 --- a/src/backend/access/transam/xlogreader.c +++ b/src/backend/access/transam/xlogreader.c @@ -26,6 +26,7 @@ #endif #include "access/transam.h" +#include "access/xact.h" #include "access/xlog_internal.h" #include "access/xlogreader.h" #include "access/xlogrecord.h" @@ -65,6 +66,41 @@ static void WALOpenSegmentInit(WALOpenSegment *seg, WALSegmentContext *segcxt, */ #define DEFAULT_DECODE_BUFFER_SIZE (64 * 1024) +/* + * Extract timestamp from WAL record. + * + * If the record contains a timestamp, returns true, and saves the timestamp + * in *recordXtime. If the record type has no timestamp, returns false. + * Currently, only transaction commit/abort records and restore points contain + * timestamps. + */ +bool +GetXLogRecordTimestamp(XLogReaderState *record, TimestampTz *recordXtime) +{ + uint8 info = XLogRecGetInfo(record) & ~XLR_INFO_MASK; + uint8 xact_info = info & XLOG_XACT_OPMASK; + uint8 rmid = XLogRecGetRmid(record); + + if (rmid == RM_XLOG_ID && info == XLOG_RESTORE_POINT) + { + *recordXtime = ((xl_restore_point *) XLogRecGetData(record))->rp_time; + return true; + } + if (rmid == RM_XACT_ID && (xact_info == XLOG_XACT_COMMIT || + xact_info == XLOG_XACT_COMMIT_PREPARED)) + { + *recordXtime = ((xl_xact_commit *) XLogRecGetData(record))->xact_time; + return true; + } + if (rmid == RM_XACT_ID && (xact_info == XLOG_XACT_ABORT || + xact_info == XLOG_XACT_ABORT_PREPARED)) + { + *recordXtime = ((xl_xact_abort *) XLogRecGetData(record))->xact_time; + return true; + } + return false; +} + /* * Construct a string in state->errormsg_buf explaining what's wrong with * the current record being read. diff --git a/src/backend/access/transam/xlogrecovery.c b/src/backend/access/transam/xlogrecovery.c index 54aaec9529f..06a5b10d092 100644 --- a/src/backend/access/transam/xlogrecovery.c +++ b/src/backend/access/transam/xlogrecovery.c @@ -356,7 +356,6 @@ static void xlog_outrec(StringInfo buf, XLogReaderState *record); static void xlog_block_info(StringInfo buf, XLogReaderState *record); static void checkTimeLineSwitch(XLogRecPtr lsn, TimeLineID newTLI, TimeLineID prevTLI, TimeLineID replayTLI); -static bool getRecordTimestamp(XLogReaderState *record, TimestampTz *recordXtime); static void verifyBackupPageConsistency(XLogReaderState *record); static bool recoveryStopsBefore(XLogReaderState *record); @@ -2405,41 +2404,6 @@ checkTimeLineSwitch(XLogRecPtr lsn, TimeLineID newTLI, TimeLineID prevTLI, } -/* - * Extract timestamp from WAL record. - * - * If the record contains a timestamp, returns true, and saves the timestamp - * in *recordXtime. If the record type has no timestamp, returns false. - * Currently, only transaction commit/abort records and restore points contain - * timestamps. - */ -static bool -getRecordTimestamp(XLogReaderState *record, TimestampTz *recordXtime) -{ - uint8 info = XLogRecGetInfo(record) & ~XLR_INFO_MASK; - uint8 xact_info = info & XLOG_XACT_OPMASK; - uint8 rmid = XLogRecGetRmid(record); - - if (rmid == RM_XLOG_ID && info == XLOG_RESTORE_POINT) - { - *recordXtime = ((xl_restore_point *) XLogRecGetData(record))->rp_time; - return true; - } - if (rmid == RM_XACT_ID && (xact_info == XLOG_XACT_COMMIT || - xact_info == XLOG_XACT_COMMIT_PREPARED)) - { - *recordXtime = ((xl_xact_commit *) XLogRecGetData(record))->xact_time; - return true; - } - if (rmid == RM_XACT_ID && (xact_info == XLOG_XACT_ABORT || - xact_info == XLOG_XACT_ABORT_PREPARED)) - { - *recordXtime = ((xl_xact_abort *) XLogRecGetData(record))->xact_time; - return true; - } - return false; -} - /* * Checks whether the current buffer page and backup page stored in the * WAL record are consistent or not. Before comparing the two pages, a @@ -2663,10 +2627,10 @@ recoveryStopsBefore(XLogReaderState *record) /* * Note: we must fetch recordXtime regardless of recoveryTarget setting. - * We don't expect getRecordTimestamp ever to fail, since we already know - * this is a commit or abort record; but test its result anyway. + * We don't expect GetXLogRecordTimestamp ever to fail, since we already + * know this is a commit or abort record; but test its result anyway. */ - if (getRecordTimestamp(record, &recordXtime) && + if (GetXLogRecordTimestamp(record, &recordXtime) && recoveryTarget == RECOVERY_TARGET_TIME) { /* @@ -2747,7 +2711,7 @@ recoveryStopsAfter(XLogReaderState *record) recoveryStopAfter = true; recoveryStopXid = InvalidTransactionId; recoveryStopLSN = InvalidXLogRecPtr; - (void) getRecordTimestamp(record, &recoveryStopTime); + (void) GetXLogRecordTimestamp(record, &recoveryStopTime); strlcpy(recoveryStopName, recordRestorePointData->rp_name, MAXFNAMELEN); ereport(LOG, @@ -2787,7 +2751,7 @@ recoveryStopsAfter(XLogReaderState *record) TransactionId recordXid; /* Update the last applied transaction timestamp */ - if (getRecordTimestamp(record, &recordXtime)) + if (GetXLogRecordTimestamp(record, &recordXtime)) SetLatestXTime(recordXtime); /* Extract the XID of the committed/aborted transaction */ @@ -3006,7 +2970,7 @@ recoveryApplyDelay(XLogReaderState *record) xact_info != XLOG_XACT_COMMIT_PREPARED) return false; - if (!getRecordTimestamp(record, &xtime)) + if (!GetXLogRecordTimestamp(record, &xtime)) return false; delayUntil = TimestampTzPlusMilliseconds(xtime, recovery_min_apply_delay); diff --git a/src/include/access/xlogreader.h b/src/include/access/xlogreader.h index 4a9a687e879..3016ae4d5da 100644 --- a/src/include/access/xlogreader.h +++ b/src/include/access/xlogreader.h @@ -39,6 +39,7 @@ #endif #include "access/xlogrecord.h" +#include "datatype/timestamp.h" #include "storage/buf.h" /* WALOpenSegment represents a WAL segment being read. */ @@ -440,5 +441,7 @@ extern bool XLogRecGetBlockTagExtended(XLogReaderState *record, uint8 block_id, RelFileLocator *rlocator, ForkNumber *forknum, BlockNumber *blknum, Buffer *prefetch_buffer); +extern bool GetXLogRecordTimestamp(XLogReaderState *record, + TimestampTz *recordXtime); #endif /* XLOGREADER_H */ diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list index 1f1bcf8d527..c85279cf423 100644 --- a/src/tools/pgindent/typedefs.list +++ b/src/tools/pgindent/typedefs.list @@ -3433,6 +3433,7 @@ WalSndState WalSummarizerData WalSummaryFile WalSummaryIO +WalTimeBoundary WalTimeSample WalTimeSegment WalUsage -- 2.50.1 (Apple Git-155) =

view thread (12+ messages)  latest in thread

Message-ID: <D677A87B-75D9-47A4-BDA6-AF88ABE76F87@gmail.com>
Permalink:  ../D677A87B-75D9-47A4-BDA6-AF88ABE76F87@gmail.com/
Also on:    postgresql.org/message-id/D677A87B-75D9-47A4-BDA6-AF88ABE76F87@gmail.com

 ·  · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-hackers@postgresql.org
  Cc: li.evan.chao@gmail.com, suryapoondla4@gmail.com
  Subject: Re: pg_walinspect: add functions to locate and list WAL by time and LSN
  In-Reply-To: <D677A87B-75D9-47A4-BDA6-AF88ABE76F87@gmail.com>
  References: <80E9F0AD-CFC5-4BE5-81DE-D8FE35E10A1C@gmail.com> <CAOVWO5pa8pphE8qXJMZ4-7mUMvv8zKPmRDqWTn93_HV6w_1GmA@mail.gmail.com> <D677A87B-75D9-47A4-BDA6-AF88ABE76F87@gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox