Re: pg_walinspect: add functions to locate and list WAL by time and LSN - Mailing list pgsql-hackers

> On Oct 7, 2026, at 07:11, surya poondla <suryapoondla4@gmail.com> wrote:
>
> Hi Chao,
>
> Thanks for the quick patch on v2, and for splitting the patch. I checked the changes against my earlier comments,
point1 is fixed (end_lsn is now capped before point_lookup is set), also point 2 (the invalid startLSN message), the
timelineTAP test (with archive_mode both off and on), the test robustness changes, the move of GetXLogRecordTimestamp()
to
> xlogreader.c, and all of the minor items.
>
> v2 applies cleanly to master (10b6e2a3d66) and builds without warnings, and the regression and TAP tests pass.

Thanks again for the follow up review and tests.

>
> 0001 (pg_get_wal_files) is close. One edge case from reading the code, GetWalTimeSegments() skips segments that start
ator after the current LSN. 
> So if the flush LSN sits exactly on a segment boundary, as on an idle server right after pg_switch_wal(),
pg_get_wal_files(pg_current_wal_lsn())becomes 
> a point lookup on a filtered-out segment and reports "WAL segment needed for the requested range is missing".

Good catch. Fixed in v3.

>
> For 0002, I think the change of boundary semantics breaks the main use case. Since v2, start_lsn is the earliest
timestampedrecord found at or after the 
> lower bound, and end_lsn is the latest found at or before the upper bound. But a transaction's data records come
beforeits commit record in WAL.  
> So when the commit of the transaction of interest is the first timestamped record in the window, start_lsn is that
commit,and the change itself falls 
> outside the returned range.
>
> I reproduced this with a scratch TAP test: autovacuum off, and no other commits within two seconds of the window.
>
>   SELECT clock_timestamp() AS window_start \gset
>   BEGIN;
>   SELECT pg_current_wal_insert_lsn() AS delete_lsn \gset
>   DELETE FROM important WHERE id = 5;
>   SELECT pg_sleep(0.2);
>   COMMIT;
>   SELECT clock_timestamp() AS window_end \gset
>
>   SELECT l.start_lsn, l.end_lsn, :'delete_lsn'::pg_lsn AS delete_lsn
>   FROM pg_get_wal_location_at_time(:'window_start',
>                                    interval '500 milliseconds',
>                                    :'window_end'::timestamptz -
>                                      :'window_start'::timestamptz) AS l;
>
>    start_lsn  |  end_lsn   | delete_lsn
>   ------------+------------+------------
>    0/017EEF78 | 0/017EEF78 | 0/017EEF40
>
>   The DELETE ran inside the requested window, and its WAL begins at
>   0/017EEF40, the insert position taken just before it. Its commit, 56 bytes
>   later, is the only timestamped record in the window, so it's returned as
>   both boundaries. Following the documented workflow,
>   pg_get_wal_records_info(start_lsn, end_lsn) then returns no rows at all,
>   because that function only emits records that end at or before end_lsn. So
>   the user sees neither the DELETE nor its commit. On a quiet system, which is
>   when someone is most likely to be investigating a single mistaken statement,
>   this is the usual outcome, not a corner case.
>
> Outer anchors (the latest timestamped record at or before the lower bound, and the earliest at or after the upper
bound)avoid this, but only with a 
> fallback for the end. In this reproducer nothing commits after the window, so by my reading v1 would have failed with
"requestedtime follows the 
> available WAL range" instead. pg_walinspect already treats the current WAL position as a valid end
(ValidateInputLSNs()caps end_lsn to it, and the 
> docs suggest FFFFFFFF/FFFFFFFF for exactly that), so falling back to the current flush/replay LSN, with end_timestamp
NULL,seems consistent with the 
> rest of the module. It would also cover the quiet-system case from my first review.
>
> I don't think the binary search requires inner anchors. It can locate the segment for each bound and then take the
outerrecord from that segment, or 
> from the nearest earlier (or later) segment that has a timestamped record.
> Outer anchors still won't cover a transaction that began before the lower anchor, but the docs already say as much,
andit is a much narrower case. 

I was thinking that, since the time range might be rough anyway, users could simply widen the window to search for more
records.But your point is also valid. In some cases, such as verifying the function using a small window, using outer
anchorsis more reasonable. 

Yes, outer anchors can still be used with binary search. I have changed v3 to use outer anchors, and your reproducer
nowgets: 
```
evantest=#   SELECT l.start_lsn, l.end_lsn, :'delete_lsn'::pg_lsn AS delete_lsn
evantest-#   FROM pg_get_wal_location_at_time(:'window_start',
evantest(#                                    interval '500 milliseconds',
evantest(#                                    :'window_end'::timestamptz -
evantest(#                                      :'window_start'::timestamptz) AS l;
 start_lsn  |  end_lsn   | delete_lsn
------------+------------+------------
 0/01C50430 | 0/01C504F0 | 0/01C50490
(1 row)
```

>
> Separately, reading the code, a window that contains no timestamped record,
> but has some on both sides, currently ends in "could not find a valid WAL
> range for the requested time window" with the detail "The matching lower boundary follows the upper boundary in WAL".
Thatreads like a WAL ordering 
> problem, when the real cause is that nothing in the window is timestamped.
> With outer anchors this case would just return a range.

Yes. I added a tested for that.

PFA v3 - addressed Surya’s review comments.

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