Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xC4Ti-00000003XYP-3jSH for pgsql-hackers@arkaria.postgresql.org; Thu, 01 Oct 2026 00:16:51 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.98.2) (envelope-from ) id 1xC4Tg-00000004XZM-3uSR for pgsql-hackers@arkaria.postgresql.org; Thu, 01 Oct 2026 00:16:48 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xC4Tg-00000004XZD-1nC6 for pgsql-hackers@lists.postgresql.org; Thu, 01 Oct 2026 00:16:48 +0000 Received: from mail-pz2-x10.google.com ([2607:f8b0:4864:3b::10]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.98.2) (envelope-from ) id 1xC4Td-00000002BYD-0xXW for pgsql-hackers@lists.postgresql.org; Thu, 01 Oct 2026 00:16:47 +0000 Received: by mail-pz2-x10.google.com with SMTP id d2e1a72fcca58-8631d0023daso3012439b3a.2 for ; Wed, 30 Sep 2026 17:16:44 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20251104; t=1790813802; x=1791418602; darn=lists.postgresql.org; h=references:to:cc:in-reply-to:date:subject:mime-version:content-type :message-id:from:from:to:cc:subject:date:message-id:reply-to :content-type; bh=hyWzjxUVdv0U8dMHpaMuuFKWhuryQSwogz47zLPF2Zg=; b=qUUUbfaaPeC/CHnBxvqjr9J1aI1W9FI4SzN5L5nSCmUoo625NO1TRa6GO8vq9kjExE u4IAwR+geagz2ap+9qrWxfXd9gV8dni5W5DYf0UBp2ETGRxfAnSUVDELU0d3+PSE55ip mJMqBl1kXB81SmPbYT5c64ci/Qz2Bh9Ayt1Q3Xts+zLbQGi4MRLFDVaw6ymvyVJL1iu+ rPbXRkT0jFOIAtm6zmmAjRoghtOsbwiVoXZd36jldXyiU9tvNVbOa/Q5mq8jbDJuwVjI D5WluvZqbyO3u81vDiZUNVGu3j41F0iJ7wikiHzX0G8fpDxiz9Rs6/tUASLWMUUn/jOk pymQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20260707; t=1790813802; x=1791418602; h=references:to:cc:in-reply-to:date:subject:mime-version:content-type :message-id:from:x-gm-gg:x-gm-message-state:from:to:cc:subject:date :message-id:reply-to:content-type; bh=hyWzjxUVdv0U8dMHpaMuuFKWhuryQSwogz47zLPF2Zg=; b=Lsvboy8jlqaYtaDJfS1mABx+DSspqQDmem6og2/7/o0JzerjtDQEYT2v8lJa83nCMa 1QpZXiye4cnnaYioCVwRNof6Zi9rEqE6AQSLGza5S7fx+sXfrq5rGx5RHdOyLPll7v2d tJpuzB/8t0oLnHrbW6Zlll5VOadgwepSg6MfvhAqCbpD8FbIrqVuDKhrz6N7Xm0xenXE 4iC/IctOhHLfPhSHlBx3woLbtEkyyciVtwGU7rC4dc4ToUF2V2sbRFdnuTnnWL/9sgbk /uDxWE4vAiV06mxxLJmqUQ0XVkduej39/RhmJidjozgrxplCctbjrPEwAvNihWA2xt9j yXwg== X-Gm-Message-State: AFuF++kInYl6L+dGMILMB9sTG/Coj/LhtgT8wWTXwBHU60sRm9KHgUTN +jXcfma0yCGFnWSdzL9cINjWhlWUUEXfooYuLG8VdBZpQRW8r3lwarD2 X-Gm-Gg: AYBFou0jduVjaZ85S+nJWM57nBWUX23vajkeBPInuQ9OSYysJLCFGfYMx+fW7bAWAXE IuCWSlyBBoEV6kKSZTBGMxIG1mkMGngEfvNZ6bHMRI4J4CCgwzsZJVm46TWh2uTx6Mc6F7t1Ob0 UXrP1oqEt8+plS7wUb9jef0r4mHpE9hwg98uUkvqEyKm/OxF8GAgaJVRKR26LiM/aIQkMiMy5Qw PyfXLO8hTlAbO1wS2RB2aTzR5mdRMsHJMWGyH6duNw0vJWZplwhbKY9f6fjmkhEAI0+OVHaSZvV n1K5g8VgaeXOSW5vsesMD3G9t0RUVng0F9t0SPvdNkdcDk1DciaF9lUgKpR653SvT7wjwkdVvpG AEIYjBK7bjQMPqiDN+tfB17NoqnmgJM/leUj7zqxz73Bc8BVKEuRiUMM7Hl81qHeVwSzt/UogVT tShwFo3jnZFMQwtjAeSrP68eOct73DzDliZSwcyZBcm6dub2sfdZiJK5sXBq0LENV9DbXv784JY RqEyxQ= X-Received: by 2002:a05:6a00:1c88:b0:878:3783:8a4d with SMTP id d2e1a72fcca58-8874cebce92mr2329783b3a.53.1790813801477; Wed, 30 Sep 2026 17:16:41 -0700 (PDT) Received: from smtpclient.apple ([185.135.79.161]) by smtp.gmail.com with ESMTPSA id d2e1a72fcca58-88818deef26sm245472b3a.24.2026.09.30.17.16.38 (version=TLS1_2 cipher=ECDHE-ECDSA-AES128-GCM-SHA256 bits=128/128); Wed, 30 Sep 2026 17:16:40 -0700 (PDT) From: Chao Li Message-Id: Content-Type: multipart/mixed; boundary="Apple-Mail=_D1745098-B8B4-48AB-8FF9-8232B81AF591" Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3864.700.51.1.1\)) Subject: Re: pg_walinspect: add functions to locate and list WAL by time and LSN Date: Thu, 1 Oct 2026 08:16:04 +0800 In-Reply-To: Cc: PostgreSQL Hackers To: surya poondla References: <80E9F0AD-CFC5-4BE5-81DE-D8FE35E10A1C@gmail.com> X-Mailer: Apple Mail (2.3864.700.51.1.1) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --Apple-Mail=_D1745098-B8B4-48AB-8FF9-8232B81AF591 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 > On Sep 30, 2026, at 06:31, surya poondla = wrote: >=20 > Hi Chao, >=20 > 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. >=20 > I have some comments below, starting with pg_get_wal_files(), >=20 > Regrading pg_get_wal_files(): > 1. A future end_lsn is rejected, which contradicts what this patch = edits. >=20 > =46rom the expected output: >=20 > SELECT * FROM pg_get_wal_files(pg_current_wal_lsn(), = 'FFFFFFFF/FFFFFFFF'); > ERROR: WAL start LSN must be less than end LSN >=20 > 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.=20 > The cause is that point_lookup is computed before ValidateInputLSNs() = caps end_lsn: >=20 > ValidateInputLSNs(start_lsn, &end_lsn); > point_lookup =3D (start_lsn =3D=3D end_lsn); >=20 > With that order, the extra "start_lsn >=3D 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. > =20 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. >=20 Make sense. >=20 > Regarding the pg_get_wal_location_at_time(): >=20 > 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? >=20 > 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? >=20 > 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. >=20 > 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=E2=80=99s 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). >=20 I was straggling whether or not to add a TAP test and ended up not = adding one because the module didn=E2=80=99t 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. >=20 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?=20 > 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. >=20 > 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/ --Apple-Mail=_D1745098-B8B4-48AB-8FF9-8232B81AF591 Content-Disposition: attachment; filename=v2-0001-pg_walinspect-add-function-to-list-WAL-files-by-L.patch Content-Type: application/octet-stream; x-unix-mode=0644; name="v2-0001-pg_walinspect-add-function-to-list-WAL-files-by-L.patch" Content-Transfer-Encoding: quoted-printable =46rom=204538e2ea3d687f1cac5ddbccf4364b73c093e8da=20Mon=20Sep=2017=20= 00:00:00=202001=0AFrom:=20"Chao=20Li=20(Evan)"=20=0A= Date:=20Thu,=201=20Oct=202026=2006:52:59=20+0800=0ASubject:=20[PATCH=20= v2=201/2]=20pg_walinspect:=20add=20function=20to=20list=20WAL=20files=20= by=20LSN=0A=0AAdd=20pg_get_wal_files(),=20which=20returns=20the=20= retained=20WAL=20segment=20files=0Aintersecting=20an=20LSN=20range,=20= together=20with=20each=20segment's=20complete=20LSN=0Aboundaries.=20A=20= point=20lookup=20can=20be=20requested=20by=20omitting=20end_lsn=20or=0A= making=20it=20equal=20to=20start_lsn.=0A=0AChoose=20segment=20file=20= names=20from=20the=20server's=20current=20timeline=20history=0Aso=20that=20= ranges=20spanning=20a=20timeline=20switch=20are=20handled=20correctly.=0A= =0AAuthor:=20Chao=20Li=20=0AReviewed-by:=20Surya=20= Poondla=20=0ADiscussion:=20= https://postgr.es/m/80E9F0AD-CFC5-4BE5-81DE-D8FE35E10A1C@gmail.com=0A---=0A= =20contrib/pg_walinspect/Makefile=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20|=20=20=204=20+-=0A=20= .../pg_walinspect/expected/oldextversions.out=20|=20=20=208=20+-=0A=20= .../pg_walinspect/expected/pg_walinspect.out=20=20|=20=2073=20++++++=0A=20= contrib/pg_walinspect/meson.build=20=20=20=20=20=20=20=20=20=20=20=20=20= |=20=20=206=20+=0A=20.../pg_walinspect/pg_walinspect--1.1--1.2.sql=20|=20= =2022=20++=0A=20contrib/pg_walinspect/pg_walinspect.c=20=20=20=20=20=20=20= =20=20|=20209=20+++++++++++++++++-=0A=20= contrib/pg_walinspect/pg_walinspect.control=20=20=20|=20=20=202=20+-=0A=20= contrib/pg_walinspect/sql/oldextversions.sql=20=20|=20=20=205=20+-=0A=20= contrib/pg_walinspect/sql/pg_walinspect.sql=20=20=20|=20=2034=20+++=0A=20= contrib/pg_walinspect/t/001_timeline.pl=20=20=20=20=20=20=20|=20=2062=20= ++++++=0A=20doc/src/sgml/pgwalinspect.sgml=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20|=20=2034=20++-=0A=20src/tools/pgindent/typedefs.list=20=20= =20=20=20=20=20=20=20=20=20=20=20=20|=20=20=201=20+=0A=2012=20files=20= changed,=20441=20insertions(+),=2019=20deletions(-)=0A=20create=20mode=20= 100644=20contrib/pg_walinspect/pg_walinspect--1.1--1.2.sql=0A=20create=20= mode=20100644=20contrib/pg_walinspect/t/001_timeline.pl=0A=0Adiff=20= --git=20a/contrib/pg_walinspect/Makefile=20= b/contrib/pg_walinspect/Makefile=0Aindex=2022090f7716c..b7651231077=20= 100644=0A---=20a/contrib/pg_walinspect/Makefile=0A+++=20= b/contrib/pg_walinspect/Makefile=0A@@=20-7,9=20+7,11=20@@=20OBJS=20=3D=20= \=0A=20PGFILEDESC=20=3D=20"pg_walinspect=20-=20functions=20to=20inspect=20= contents=20of=20PostgreSQL=20Write-Ahead=20Log"=0A=20=0A=20EXTENSION=20=3D= =20pg_walinspect=0A-DATA=20=3D=20pg_walinspect--1.0.sql=20= pg_walinspect--1.0--1.1.sql=0A+DATA=20=3D=20pg_walinspect--1.0.sql=20= pg_walinspect--1.0--1.1.sql=20\=0A+=09pg_walinspect--1.1--1.2.sql=0A=20=0A= =20REGRESS=20=3D=20pg_walinspect=20oldextversions=0A+TAP_TESTS=20=3D=201=0A= =20=0A=20REGRESS_OPTS=20=3D=20--temp-config=20= $(top_srcdir)/contrib/pg_walinspect/walinspect.conf=0A=20=0Adiff=20--git=20= a/contrib/pg_walinspect/expected/oldextversions.out=20= b/contrib/pg_walinspect/expected/oldextversions.out=0Aindex=20= 89953af3d0e..311017d66ca=20100644=0A---=20= a/contrib/pg_walinspect/expected/oldextversions.out=0A+++=20= b/contrib/pg_walinspect/expected/oldextversions.out=0A@@=20-46,18=20= +46,20=20@@=20SELECT=20*=20FROM=20= pg_get_wal_records_info_till_end_of_wal('FFFFFFFF/FFFFFFFF');=0A=20= ERROR:=20=20WAL=20start=20LSN=20must=20be=20less=20than=20current=20LSN=0A= =20SELECT=20*=20FROM=20= pg_get_wal_stats_till_end_of_wal('FFFFFFFF/FFFFFFFF');=0A=20ERROR:=20=20= WAL=20start=20LSN=20must=20be=20less=20than=20current=20LSN=0A---=20Move=20= to=20new=20version=201.1.=0A+--=20Test=20each=20upgrade=20step=20through=20= the=20current=20version.=0A=20ALTER=20EXTENSION=20pg_walinspect=20UPDATE=20= TO=20'1.1';=0A---=20List=20what=20version=201.1=20contains.=0A+ALTER=20= EXTENSION=20pg_walinspect=20UPDATE=20TO=20'1.2';=0A+--=20List=20what=20= the=20current=20version=20contains.=0A=20\dx+=20pg_walinspect=0A=20=20=20= =20=20=20=20=20=20=20Objects=20in=20extension=20"pg_walinspect"=0A=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20Object=20description=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=0A=20= -------------------------------------------------------=0A=20=20function=20= pg_get_wal_block_info(pg_lsn,pg_lsn,boolean)=0A+=20function=20= pg_get_wal_files(pg_lsn,pg_lsn)=0A=20=20function=20= pg_get_wal_record_info(pg_lsn)=0A=20=20function=20= pg_get_wal_records_info(pg_lsn,pg_lsn)=0A=20=20function=20= pg_get_wal_stats(pg_lsn,pg_lsn,boolean)=0A-(4=20rows)=0A+(5=20rows)=0A=20= =0A=20SELECT=20pg_drop_replication_slot('regress_pg_walinspect_slot');=0A= =20=20pg_drop_replication_slot=20=0Adiff=20--git=20= a/contrib/pg_walinspect/expected/pg_walinspect.out=20= b/contrib/pg_walinspect/expected/pg_walinspect.out=0Aindex=20= f955ff5d3c5..34864c65ec7=20100644=0A---=20= a/contrib/pg_walinspect/expected/pg_walinspect.out=0A+++=20= b/contrib/pg_walinspect/expected/pg_walinspect.out=0A@@=20-90,6=20+90,54=20= @@=20SELECT=20COUNT(*)=20>=3D=201=20AS=20ok=20FROM=20= pg_get_wal_block_info(:'wal_lsn1',=20:'wal_lsn2');=0A=20=20t=0A=20(1=20= row)=0A=20=0A+--=20Return=20the=20retained=20WAL=20file=20and=20its=20= complete=20segment=20boundaries.=0A+SELECT=20wal_file=20=3D=20= pg_walfile_name(segment_start_lsn)=20AS=20file_ok,=0A+=20=20=20=20=20=20=20= segment_start_lsn=20<=3D=20:'wal_lsn1'::pg_lsn=20AS=20start_ok,=0A+=20=20= =20=20=20=20=20segment_end_lsn=20>=20:'wal_lsn1'::pg_lsn=20AS=20end_ok=0A= +FROM=20pg_get_wal_files(:'wal_lsn1',=20:'wal_lsn1'::pg_lsn=20+=201);=0A= +=20file_ok=20|=20start_ok=20|=20end_ok=20=0A= +---------+----------+--------=0A+=20t=20=20=20=20=20=20=20|=20t=20=20=20= =20=20=20=20=20|=20t=0A+(1=20row)=0A+=0A+--=20An=20end=20LSN=20at=20a=20= segment=20boundary=20must=20not=20include=20the=20next=20segment.=0A= +WITH=20segment=20AS=0A+(=0A+=20=20SELECT=20*=20FROM=20= pg_get_wal_files(:'wal_lsn1',=20:'wal_lsn1'::pg_lsn=20+=201)=0A+)=0A= +SELECT=20count(*)=20=3D=201=20AS=20one_file=0A+FROM=20segment,=0A+=20=20= =20=20=20LATERAL=20pg_get_wal_files(segment_start_lsn,=20= segment_end_lsn);=0A+=20one_file=20=0A+----------=0A+=20t=0A+(1=20row)=0A= +=0A+SELECT=20count(*)=20=3D=201=20AS=20equal_lsn_ok=0A+FROM=20= pg_get_wal_files(:'wal_lsn1',=20:'wal_lsn1');=0A+=20equal_lsn_ok=20=0A= +--------------=0A+=20t=0A+(1=20row)=0A+=0A+SELECT=20count(*)=20=3D=201=20= AS=20default_end_ok=0A+FROM=20pg_get_wal_files(:'wal_lsn1');=0A+=20= default_end_ok=20=0A+----------------=0A+=20t=0A+(1=20row)=0A+=0A+SELECT=20= *=20FROM=20pg_get_wal_files(:'wal_lsn1'::pg_lsn=20+=201,=20:'wal_lsn1');=0A= +ERROR:=20=20WAL=20start=20LSN=20must=20be=20less=20than=20end=20LSN=0A= +SELECT=20count(*)=20=3D=201=20AS=20future_end_ok=0A+FROM=20= pg_get_wal_files(pg_current_wal_lsn(),=20'FFFFFFFF/FFFFFFFF');=0A+=20= future_end_ok=20=0A+---------------=0A+=20t=0A+(1=20row)=0A+=0A+SELECT=20= *=20FROM=20pg_get_wal_files('0/0',=20'0/1');=0A+ERROR:=20=20could=20not=20= read=20WAL=20at=20LSN=200/00000000=0A=20--=20= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=0A=20--=20Test=20for=20= filtering=20out=20WAL=20records=20of=20a=20particular=20table=0A=20--=20= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=0A@@=20-183,6=20= +231,13=20@@=20SELECT=20has_function_privilege('regress_pg_walinspect',=0A= =20=20f=0A=20(1=20row)=0A=20=0A+SELECT=20= has_function_privilege('regress_pg_walinspect',=0A+=20=20= 'pg_get_wal_files(pg_lsn,=20pg_lsn)',=20'EXECUTE');=20--=20no=0A+=20= has_function_privilege=20=0A+------------------------=0A+=20f=0A+(1=20= row)=0A+=0A=20--=20Functions=20accessible=20by=20users=20with=20role=20= pg_read_server_files.=0A=20GRANT=20pg_read_server_files=20TO=20= regress_pg_walinspect;=0A=20SELECT=20= has_function_privilege('regress_pg_walinspect',=0A@@=20-213,6=20+268,13=20= @@=20SELECT=20has_function_privilege('regress_pg_walinspect',=0A=20=20t=0A= =20(1=20row)=0A=20=0A+SELECT=20= has_function_privilege('regress_pg_walinspect',=0A+=20=20= 'pg_get_wal_files(pg_lsn,=20pg_lsn)',=20'EXECUTE');=20--=20yes=0A+=20= has_function_privilege=20=0A+------------------------=0A+=20t=0A+(1=20= row)=0A+=0A=20REVOKE=20pg_read_server_files=20FROM=20= regress_pg_walinspect;=0A=20--=20Superuser=20can=20grant=20execute=20to=20= other=20users.=0A=20GRANT=20EXECUTE=20ON=20FUNCTION=20= pg_get_wal_record_info(pg_lsn)=0A@@=20-223,6=20+285,8=20@@=20GRANT=20= EXECUTE=20ON=20FUNCTION=20pg_get_wal_stats(pg_lsn,=20pg_lsn,=20boolean)=0A= =20=20=20TO=20regress_pg_walinspect;=0A=20GRANT=20EXECUTE=20ON=20= FUNCTION=20pg_get_wal_block_info(pg_lsn,=20pg_lsn,=20boolean)=0A=20=20=20= TO=20regress_pg_walinspect;=0A+GRANT=20EXECUTE=20ON=20FUNCTION=20= pg_get_wal_files(pg_lsn,=20pg_lsn)=0A+=20=20TO=20regress_pg_walinspect;=0A= =20SELECT=20has_function_privilege('regress_pg_walinspect',=0A=20=20=20= 'pg_get_wal_record_info(pg_lsn)',=20'EXECUTE');=20--=20yes=0A=20=20= has_function_privilege=20=0A@@=20-251,6=20+315,13=20@@=20SELECT=20= has_function_privilege('regress_pg_walinspect',=0A=20=20t=0A=20(1=20row)=0A= =20=0A+SELECT=20has_function_privilege('regress_pg_walinspect',=0A+=20=20= 'pg_get_wal_files(pg_lsn,=20pg_lsn)',=20'EXECUTE');=20--=20yes=0A+=20= has_function_privilege=20=0A+------------------------=0A+=20t=0A+(1=20= row)=0A+=0A=20REVOKE=20EXECUTE=20ON=20FUNCTION=20= pg_get_wal_record_info(pg_lsn)=0A=20=20=20FROM=20regress_pg_walinspect;=0A= =20REVOKE=20EXECUTE=20ON=20FUNCTION=20pg_get_wal_records_info(pg_lsn,=20= pg_lsn)=0A@@=20-259,6=20+330,8=20@@=20REVOKE=20EXECUTE=20ON=20FUNCTION=20= pg_get_wal_stats(pg_lsn,=20pg_lsn,=20boolean)=0A=20=20=20FROM=20= regress_pg_walinspect;=0A=20REVOKE=20EXECUTE=20ON=20FUNCTION=20= pg_get_wal_block_info(pg_lsn,=20pg_lsn,=20boolean)=0A=20=20=20FROM=20= regress_pg_walinspect;=0A+REVOKE=20EXECUTE=20ON=20FUNCTION=20= pg_get_wal_files(pg_lsn,=20pg_lsn)=0A+=20=20FROM=20= regress_pg_walinspect;=0A=20--=20= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=0A=20--=20Clean=20up=0A= =20--=20= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=0Adiff=20--git=20= a/contrib/pg_walinspect/meson.build=20= b/contrib/pg_walinspect/meson.build=0Aindex=2082ab186c340..6f8b4a6359a=20= 100644=0A---=20a/contrib/pg_walinspect/meson.build=0A+++=20= b/contrib/pg_walinspect/meson.build=0A@@=20-20,6=20+20,7=20@@=20= install_data(=0A=20=20=20'pg_walinspect.control',=0A=20=20=20= 'pg_walinspect--1.0.sql',=0A=20=20=20'pg_walinspect--1.0--1.1.sql',=0A+=20= =20'pg_walinspect--1.1--1.2.sql',=0A=20=20=20kwargs:=20= contrib_data_args,=0A=20)=0A=20=0A@@=20-37,4=20+38,9=20@@=20tests=20+=3D=20= {=0A=20=20=20=20=20'regress_args':=20['--temp-config',=20= files('walinspect.conf')],=0A=20=20=20=20=20'runningcheck':=20false,=0A=20= =20=20},=0A+=20=20'tap':=20{=0A+=20=20=20=20'tests':=20[=0A+=20=20=20=20=20= =20't/001_timeline.pl',=0A+=20=20=20=20],=0A+=20=20},=0A=20}=0Adiff=20= --git=20a/contrib/pg_walinspect/pg_walinspect--1.1--1.2.sql=20= b/contrib/pg_walinspect/pg_walinspect--1.1--1.2.sql=0Anew=20file=20mode=20= 100644=0Aindex=2000000000000..9cc9523b4dc=0A---=20/dev/null=0A+++=20= b/contrib/pg_walinspect/pg_walinspect--1.1--1.2.sql=0A@@=20-0,0=20+1,22=20= @@=0A+/*=20contrib/pg_walinspect/pg_walinspect--1.1--1.2.sql=20*/=0A+=0A= +--=20complain=20if=20script=20is=20sourced=20in=20psql,=20rather=20than=20= via=20ALTER=20EXTENSION=0A+\echo=20Use=20"ALTER=20EXTENSION=20= pg_walinspect=20UPDATE=20TO=20'1.2'"=20to=20load=20this=20file.=20\quit=0A= +=0A+--=0A+--=20pg_get_wal_files()=0A+--=0A+CREATE=20FUNCTION=20= pg_get_wal_files(=0A+=20=20=20=20IN=20start_lsn=20pg_lsn,=0A+=20=20=20=20= IN=20end_lsn=20pg_lsn=20DEFAULT=20NULL,=0A+=20=20=20=20OUT=20wal_file=20= text,=0A+=20=20=20=20OUT=20segment_start_lsn=20pg_lsn,=0A+=20=20=20=20= OUT=20segment_end_lsn=20pg_lsn=0A+)=0A+RETURNS=20SETOF=20record=0A+AS=20= 'MODULE_PATHNAME',=20'pg_get_wal_files'=0A+LANGUAGE=20C=20PARALLEL=20= SAFE;=0A+=0A+REVOKE=20EXECUTE=20ON=20FUNCTION=20pg_get_wal_files(pg_lsn,=20= pg_lsn)=20FROM=20PUBLIC;=0A+GRANT=20EXECUTE=20ON=20FUNCTION=20= pg_get_wal_files(pg_lsn,=20pg_lsn)=0A+=20=20TO=20pg_read_server_files;=0A= diff=20--git=20a/contrib/pg_walinspect/pg_walinspect.c=20= b/contrib/pg_walinspect/pg_walinspect.c=0Aindex=20= a172f9e2b40..42f366bdb4e=20100644=0A---=20= a/contrib/pg_walinspect/pg_walinspect.c=0A+++=20= b/contrib/pg_walinspect/pg_walinspect.c=0A@@=20-13,6=20+13,7=20@@=0A=20= #include=20"postgres.h"=0A=20=0A=20#include=20"access/htup_details.h"=0A= +#include=20"access/timeline.h"=0A=20#include=20"access/xlog.h"=0A=20= #include=20"access/xlog_internal.h"=0A=20#include=20= "access/xlogreader.h"=0A@@=20-22,6=20+23,7=20@@=0A=20#include=20= "funcapi.h"=0A=20#include=20"miscadmin.h"=0A=20#include=20= "port/pg_bitutils.h"=0A+#include=20"storage/fd.h"=0A=20#include=20= "utils/array.h"=0A=20#include=20"utils/builtins.h"=0A=20#include=20= "utils/pg_lsn.h"=0A@@=20-38,6=20+40,7=20@@=20PG_MODULE_MAGIC_EXT(=0A=20= );=0A=20=0A=20PG_FUNCTION_INFO_V1(pg_get_wal_block_info);=0A= +PG_FUNCTION_INFO_V1(pg_get_wal_files);=0A=20= PG_FUNCTION_INFO_V1(pg_get_wal_record_info);=0A=20= PG_FUNCTION_INFO_V1(pg_get_wal_records_info);=0A=20= PG_FUNCTION_INFO_V1(pg_get_wal_records_info_till_end_of_wal);=0A@@=20= -45,7=20+48,7=20@@=20PG_FUNCTION_INFO_V1(pg_get_wal_stats);=0A=20= PG_FUNCTION_INFO_V1(pg_get_wal_stats_till_end_of_wal);=0A=20=0A=20static=20= void=20ValidateInputLSNs(XLogRecPtr=20start_lsn,=20XLogRecPtr=20= *end_lsn);=0A-static=20XLogRecPtr=20GetCurrentLSN(void);=0A+static=20= XLogRecPtr=20GetCurrentLSN(TimeLineID=20*current_tli);=0A=20static=20= XLogReaderState=20*InitXLogReaderState(XLogRecPtr=20lsn);=0A=20static=20= XLogRecord=20*ReadNextXLogRecord(XLogReaderState=20*xlogreader);=0A=20= static=20void=20GetWALRecordInfo(XLogReaderState=20*record,=20Datum=20= *values,=0A@@=20-68,11=20+71,21=20@@=20static=20void=20= GetWalStats(FunctionCallInfo=20fcinfo,=0A=20static=20void=20= GetWALBlockInfo(FunctionCallInfo=20fcinfo,=20XLogReaderState=20*record,=0A= =20=09=09=09=09=09=09=09bool=20show_data);=0A=20=0A+/*=20Metadata=20for=20= one=20retained=20segment=20in=20the=20current=20timeline's=20history.=20= */=0A+typedef=20struct=20WalTimeSegment=0A+{=0A+=09XLogSegNo=09segno;=09=09= =09/*=20segment=20number,=20used=20for=20WAL=20ordering=20*/=0A+=09= TimeLineID=09tli;=09=09=09/*=20timeline=20containing=20this=20segment=20= */=0A+}=20WalTimeSegment;=0A+=0A+static=20int=09= wal_time_segment_cmp(const=20void=20*a,=20const=20void=20*b);=0A+static=20= WalTimeSegment=20*GetWalTimeSegments(int=20*nsegments);=0A+=0A=20/*=0A-=20= *=20Return=20the=20LSN=20up=20to=20which=20the=20server=20has=20WAL.=0A+=20= *=20Return=20the=20LSN=20up=20to=20which=20the=20server=20has=20WAL,=20= and=20optionally=20its=20timeline.=0A=20=20*/=0A=20static=20XLogRecPtr=0A= -GetCurrentLSN(void)=0A+GetCurrentLSN(TimeLineID=20*current_tli)=0A=20{=0A= =20=09XLogRecPtr=09curr_lsn;=0A=20=0A@@=20-81,9=20+94,15=20@@=20= GetCurrentLSN(void)=0A=20=09=20*=20callback=20= read_local_xlog_page_no_wait=20does.=0A=20=09=20*/=0A=20=09if=20= (!RecoveryInProgress())=0A-=09=09curr_lsn=20=3D=20GetFlushRecPtr(NULL);=0A= +=09=09curr_lsn=20=3D=20GetFlushRecPtr(current_tli);=0A=20=09else=0A-=09=09= curr_lsn=20=3D=20GetXLogReplayRecPtr(NULL);=0A+=09{=0A+=09=09curr_lsn=20= =3D=20GetXLogReplayRecPtr(current_tli);=0A+=09=09if=20(current_tli=20!=3D=20= NULL=20&&=20*current_tli=20=3D=3D=200)=0A+=09=09{=0A+=09=09=09= *current_tli=20=3D=20GetWALInsertionTimeLineIfSet();=0A+=09=09}=0A+=09}=0A= =20=0A=20=09Assert(XLogRecPtrIsValid(curr_lsn));=0A=20=0A@@=20-190,6=20= +209,94=20@@=20ReadNextXLogRecord(XLogReaderState=20*xlogreader)=0A=20=09= return=20record;=0A=20}=0A=20=0A+/*=20qsort=20comparator=20that=20puts=20= segments=20in=20WAL=20order.=20*/=0A+static=20int=0A= +wal_time_segment_cmp(const=20void=20*a,=20const=20void=20*b)=0A+{=0A+=09= const=20WalTimeSegment=20*seg1=20=3D=20(const=20WalTimeSegment=20*)=20a;=0A= +=09const=20WalTimeSegment=20*seg2=20=3D=20(const=20WalTimeSegment=20*)=20= b;=0A+=0A+=09if=20(seg1->segno=20<=20seg2->segno)=0A+=09=09return=20-1;=0A= +=09if=20(seg1->segno=20>=20seg2->segno)=0A+=09=09return=201;=0A+=09= return=200;=0A+}=0A+=0A+/*=0A+=20*=20Get=20the=20WAL=20segments=20that=20= form=20the=20history=20of=20the=20server's=20current=0A+=20*=20timeline.=20= =20Other=20timelines=20may=20have=20files=20with=20the=20same=20segment=20= number=20in=0A+=20*=20pg_wal,=20so=20choose=20the=20timeline=20that=20is=20= valid=20at=20the=20end=20of=20each=20segment,=0A+=20*=20as=20= read_local_xlog_page_no_wait()=20does.=20=20Return=20the=20segments=20in=20= WAL=20order.=0A+=20*/=0A+static=20WalTimeSegment=20*=0A= +GetWalTimeSegments(int=20*nsegments)=0A+{=0A+=09WalTimeSegment=20= *segments=20=3D=20NULL;=0A+=09DIR=09=09=20=20=20*dir;=0A+=09struct=20= dirent=20*de;=0A+=09List=09=20=20=20*history;=0A+=09XLogRecPtr=09= current_lsn;=0A+=09TimeLineID=09current_tli;=0A+=09int=09=09=09allocated=20= =3D=200;=0A+=09int=09=09=09count=20=3D=200;=0A+=0A+=09current_lsn=20=3D=20= GetCurrentLSN(¤t_tli);=0A+=0A+=09history=20=3D=20= readTimeLineHistory(current_tli);=0A+=09dir=20=3D=20= AllocateDir(XLOGDIR);=0A+=09while=20((de=20=3D=20ReadDir(dir,=20= XLOGDIR))=20!=3D=20NULL)=0A+=09{=0A+=09=09TimeLineID=09file_tli;=0A+=09=09= XLogSegNo=09segno;=0A+=09=09XLogRecPtr=09seg_start;=0A+=09=09XLogRecPtr=09= seg_end;=0A+=0A+=09=09if=20(!IsXLogFileName(de->d_name))=0A+=09=09=09= continue;=0A+=0A+=09=09XLogFromFileName(de->d_name,=20&file_tli,=20= &segno,=20wal_segment_size);=0A+=09=09seg_start=20=3D=20segno=20*=20= wal_segment_size;=0A+=09=09seg_end=20=3D=20seg_start=20+=20= wal_segment_size=20-=201;=0A+=0A+=09=09if=20(seg_start=20>=3D=20= current_lsn=20||=0A+=09=09=09file_tli=20!=3D=20= tliOfPointInHistory(seg_end,=20history))=0A+=09=09=09continue;=0A+=0A+=09= =09if=20(count=20=3D=3D=20allocated)=0A+=09=09{=0A+=09=09=09allocated=20= =3D=20allocated=20?=20allocated=20*=202=20:=2016;=0A+=09=09=09if=20= (segments=20=3D=3D=20NULL)=0A+=09=09=09=09segments=20=3D=20= palloc_array(WalTimeSegment,=20allocated);=0A+=09=09=09else=0A+=09=09=09=09= segments=20=3D=20repalloc_array(segments,=20WalTimeSegment,=20= allocated);=0A+=09=09}=0A+=0A+=09=09MemSet(&segments[count],=200,=20= sizeof(WalTimeSegment));=0A+=09=09segments[count].segno=20=3D=20segno;=0A= +=09=09segments[count].tli=20=3D=20file_tli;=0A+=09=09count++;=0A+=09}=0A= +=09FreeDir(dir);=0A+=09list_free_deep(history);=0A+=0A+=09if=20(count=20= =3D=3D=200)=0A+=09=09ereport(ERROR,=0A+=09=09=09=09= errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),=0A+=09=09=09=09= errmsg("no=20retained=20WAL=20segments=20are=20available"));=0A+=0A+=09= qsort(segments,=20count,=20sizeof(WalTimeSegment),=20= wal_time_segment_cmp);=0A+=0A+#ifdef=20USE_ASSERT_CHECKING=0A+=09/*=20A=20= segment=20number=20identifies=20at=20most=20one=20file=20in=20this=20= timeline=20history.=20*/=0A+=09for=20(int=20i=20=3D=201;=20i=20<=20= count;=20i++)=0A+=09=09Assert(segments[i=20-=201].segno=20!=3D=20= segments[i].segno);=0A+#endif=0A+=0A+=09*nsegments=20=3D=20count;=0A+=09= return=20segments;=0A+}=0A+=0A=20/*=0A=20=20*=20Output=20values=20that=20= make=20up=20a=20row=20describing=20caller's=20WAL=20record.=0A=20=20*=0A= @@=20-483,7=20+590,7=20@@=20pg_get_wal_record_info(PG_FUNCTION_ARGS)=0A=20= =09HeapTuple=09tuple;=0A=20=0A=20=09lsn=20=3D=20PG_GETARG_LSN(0);=0A-=09= curr_lsn=20=3D=20GetCurrentLSN();=0A+=09curr_lsn=20=3D=20= GetCurrentLSN(NULL);=0A=20=0A=20=09if=20(lsn=20>=20curr_lsn)=0A=20=09=09= ereport(ERROR,=0A@@=20-516,6=20+623,90=20@@=20= pg_get_wal_record_info(PG_FUNCTION_ARGS)=0A=20#undef=20= PG_GET_WAL_RECORD_INFO_COLS=0A=20}=0A=20=0A+/*=0A+=20*=20List=20the=20= retained=20WAL=20segment=20files=20intersecting=20[start_lsn,=20= end_lsn),=20or=0A+=20*=20the=20segment=20containing=20start_lsn=20when=20= end_lsn=20is=20omitted=20or=20equal=20to=20it.=0A+=20*=20Report=20= complete=20segment=20boundaries,=20not=20the=20range=20clipped=20to=20= the=20inputs.=0A+=20*/=0A+Datum=0A+pg_get_wal_files(PG_FUNCTION_ARGS)=0A= +{=0A+#define=20PG_GET_WAL_FILES_COLS=203=0A+=09XLogRecPtr=09start_lsn;=0A= +=09XLogRecPtr=09end_lsn;=0A+=09XLogSegNo=09first_segno;=0A+=09XLogSegNo=09= last_segno;=0A+=09bool=09=09point_lookup;=0A+=09WalTimeSegment=20= *segments;=0A+=09ReturnSetInfo=20*rsinfo=20=3D=20(ReturnSetInfo=20*)=20= fcinfo->resultinfo;=0A+=09int=09=09=09nsegments;=0A+=09int=09=09=09= segment_index=20=3D=200;=0A+=0A+=09InitMaterializedSRF(fcinfo,=200);=0A+=0A= +=09/*=20A=20NULL=20start=20produces=20an=20empty=20set.=20*/=0A+=09if=20= (PG_ARGISNULL(0))=0A+=09=09PG_RETURN_VOID();=0A+=0A+=09start_lsn=20=3D=20= PG_GETARG_LSN(0);=0A+=09end_lsn=20=3D=20PG_ARGISNULL(1)=20?=20start_lsn=20= :=20PG_GETARG_LSN(1);=0A+=0A+=09if=20(start_lsn=20<=20XLOG_BLCKSZ)=0A+=09= =09ereport(ERROR,=0A+=09=09=09=09= errcode(ERRCODE_INVALID_PARAMETER_VALUE),=0A+=09=09=09=09errmsg("could=20= not=20read=20WAL=20at=20LSN=20%X/%08X",=0A+=09=09=09=09=09=20=20=20= LSN_FORMAT_ARGS(start_lsn)));=0A+=0A+=09ValidateInputLSNs(start_lsn,=20= &end_lsn);=0A+=09point_lookup=20=3D=20start_lsn=20=3D=3D=20end_lsn;=0A+=0A= +=0A+=09XLByteToSeg(start_lsn,=20first_segno,=20wal_segment_size);=0A+=09= if=20(point_lookup)=0A+=09=09last_segno=20=3D=20first_segno;=0A+=09else=0A= +=09=09XLByteToPrevSeg(end_lsn,=20last_segno,=20wal_segment_size);=0A+=09= segments=20=3D=20GetWalTimeSegments(&nsegments);=0A+=0A+=09for=20= (XLogSegNo=20segno=20=3D=20first_segno;;=20segno++)=0A+=09{=0A+=09=09= Datum=09=09values[PG_GET_WAL_FILES_COLS];=0A+=09=09bool=09=09= nulls[PG_GET_WAL_FILES_COLS]=20=3D=20{0};=0A+=09=09XLogRecPtr=09= segment_start_lsn=20=3D=20segno=20*=20wal_segment_size;=0A+=09=09= XLogRecPtr=09segment_end_lsn=20=3D=20segment_start_lsn=20+=20= wal_segment_size;=0A+=09=09char=09=09fname[MAXFNAMELEN];=0A+=0A+=09=09= while=20(segment_index=20<=20nsegments=20&&=0A+=09=09=09=20=20=20= segments[segment_index].segno=20<=20segno)=0A+=09=09=09segment_index++;=0A= +=0A+=09=09if=20(segment_index=20>=3D=20nsegments=20||=0A+=09=09=09= segments[segment_index].segno=20!=3D=20segno)=0A+=09=09=09ereport(ERROR,=0A= +=09=09=09=09=09errcode(ERRCODE_DATA_EXCEPTION),=0A+=09=09=09=09=09= errmsg("WAL=20segment=20needed=20for=20the=20requested=20range=20is=20= missing"),=0A+=09=09=09=09=09errdetail("Segment=20"=20UINT64_FORMAT=20"=20= is=20not=20present=20in=20pg_wal.",=0A+=09=09=09=09=09=09=09=20=20= (uint64)=20segno));=0A+=0A+=09=09XLogFileName(fname,=20= segments[segment_index].tli,=20segno,=0A+=09=09=09=09=09=20= wal_segment_size);=0A+=09=09values[0]=20=3D=20= CStringGetTextDatum(fname);=0A+=09=09values[1]=20=3D=20= LSNGetDatum(segment_start_lsn);=0A+=09=09values[2]=20=3D=20= LSNGetDatum(segment_end_lsn);=0A+=09=09= tuplestore_putvalues(rsinfo->setResult,=20rsinfo->setDesc,=0A+=09=09=09=09= =09=09=09=20values,=20nulls);=0A+=0A+=09=09if=20(segno=20=3D=3D=20= last_segno)=0A+=09=09=09break;=0A+=0A+=09=09CHECK_FOR_INTERRUPTS();=0A+=09= }=0A+=0A+=09pfree(segments);=0A+=09PG_RETURN_VOID();=0A+#undef=20= PG_GET_WAL_FILES_COLS=0A+}=0A+=0A=20/*=0A=20=20*=20Validate=20start=20= and=20end=20LSNs=20coming=20from=20the=20function=20inputs.=0A=20=20*=0A= @@=20-525,7=20+716,7=20@@=20pg_get_wal_record_info(PG_FUNCTION_ARGS)=0A=20= static=20void=0A=20ValidateInputLSNs(XLogRecPtr=20start_lsn,=20= XLogRecPtr=20*end_lsn)=0A=20{=0A-=09XLogRecPtr=09curr_lsn=20=3D=20= GetCurrentLSN();=0A+=09XLogRecPtr=09curr_lsn=20=3D=20= GetCurrentLSN(NULL);=0A=20=0A=20=09if=20(start_lsn=20>=20curr_lsn)=0A=20=09= =09ereport(ERROR,=0A@@=20-831,7=20+1022,7=20@@=20Datum=0A=20= pg_get_wal_records_info_till_end_of_wal(PG_FUNCTION_ARGS)=0A=20{=0A=20=09= XLogRecPtr=09start_lsn=20=3D=20PG_GETARG_LSN(0);=0A-=09XLogRecPtr=09= end_lsn=20=3D=20GetCurrentLSN();=0A+=09XLogRecPtr=09end_lsn=20=3D=20= GetCurrentLSN(NULL);=0A=20=0A=20=09if=20(start_lsn=20>=20end_lsn)=0A=20=09= =09ereport(ERROR,=0A@@=20-849,7=20+1040,7=20@@=20Datum=0A=20= pg_get_wal_stats_till_end_of_wal(PG_FUNCTION_ARGS)=0A=20{=0A=20=09= XLogRecPtr=09start_lsn=20=3D=20PG_GETARG_LSN(0);=0A-=09XLogRecPtr=09= end_lsn=20=3D=20GetCurrentLSN();=0A+=09XLogRecPtr=09end_lsn=20=3D=20= GetCurrentLSN(NULL);=0A=20=09bool=09=09stats_per_record=20=3D=20= PG_GETARG_BOOL(1);=0A=20=0A=20=09if=20(start_lsn=20>=20end_lsn)=0Adiff=20= --git=20a/contrib/pg_walinspect/pg_walinspect.control=20= b/contrib/pg_walinspect/pg_walinspect.control=0Aindex=20= efa3cb2cfe5..5f574b865bb=20100644=0A---=20= a/contrib/pg_walinspect/pg_walinspect.control=0A+++=20= b/contrib/pg_walinspect/pg_walinspect.control=0A@@=20-1,5=20+1,5=20@@=0A=20= #=20pg_walinspect=20extension=0A=20comment=20=3D=20'functions=20to=20= inspect=20contents=20of=20PostgreSQL=20Write-Ahead=20Log'=0A= -default_version=20=3D=20'1.1'=0A+default_version=20=3D=20'1.2'=0A=20= module_pathname=20=3D=20'$libdir/pg_walinspect'=0A=20relocatable=20=3D=20= true=0Adiff=20--git=20a/contrib/pg_walinspect/sql/oldextversions.sql=20= b/contrib/pg_walinspect/sql/oldextversions.sql=0Aindex=20= e35c4f3ed15..b935a067b3e=20100644=0A---=20= a/contrib/pg_walinspect/sql/oldextversions.sql=0A+++=20= b/contrib/pg_walinspect/sql/oldextversions.sql=0A@@=20-27,10=20+27,11=20= @@=20SELECT=20COUNT(*)=20>=3D=201=20AS=20ok=20FROM=20= pg_get_wal_stats_till_end_of_wal(:'wal_lsn1');=0A=20SELECT=20*=20FROM=20= pg_get_wal_records_info_till_end_of_wal('FFFFFFFF/FFFFFFFF');=0A=20= SELECT=20*=20FROM=20= pg_get_wal_stats_till_end_of_wal('FFFFFFFF/FFFFFFFF');=0A=20=0A---=20= Move=20to=20new=20version=201.1.=0A+--=20Test=20each=20upgrade=20step=20= through=20the=20current=20version.=0A=20ALTER=20EXTENSION=20= pg_walinspect=20UPDATE=20TO=20'1.1';=0A+ALTER=20EXTENSION=20= pg_walinspect=20UPDATE=20TO=20'1.2';=0A=20=0A---=20List=20what=20version=20= 1.1=20contains.=0A+--=20List=20what=20the=20current=20version=20= contains.=0A=20\dx+=20pg_walinspect=0A=20=0A=20SELECT=20= pg_drop_replication_slot('regress_pg_walinspect_slot');=0Adiff=20--git=20= a/contrib/pg_walinspect/sql/pg_walinspect.sql=20= b/contrib/pg_walinspect/sql/pg_walinspect.sql=0Aindex=20= 1e64a22d29a..3176a17d855=20100644=0A---=20= a/contrib/pg_walinspect/sql/pg_walinspect.sql=0A+++=20= b/contrib/pg_walinspect/sql/pg_walinspect.sql=0A@@=20-51,6=20+51,30=20@@=20= SELECT=20COUNT(*)=20>=3D=201=20AS=20ok=20FROM=20= pg_get_wal_records_info(:'wal_lsn1',=20:'wal_lsn2'=0A=20SELECT=20= COUNT(*)=20>=3D=201=20AS=20ok=20FROM=20pg_get_wal_stats(:'wal_lsn1',=20= :'wal_lsn2');=0A=20SELECT=20COUNT(*)=20>=3D=201=20AS=20ok=20FROM=20= pg_get_wal_block_info(:'wal_lsn1',=20:'wal_lsn2');=0A=20=0A+--=20Return=20= the=20retained=20WAL=20file=20and=20its=20complete=20segment=20= boundaries.=0A+SELECT=20wal_file=20=3D=20= pg_walfile_name(segment_start_lsn)=20AS=20file_ok,=0A+=20=20=20=20=20=20=20= segment_start_lsn=20<=3D=20:'wal_lsn1'::pg_lsn=20AS=20start_ok,=0A+=20=20= =20=20=20=20=20segment_end_lsn=20>=20:'wal_lsn1'::pg_lsn=20AS=20end_ok=0A= +FROM=20pg_get_wal_files(:'wal_lsn1',=20:'wal_lsn1'::pg_lsn=20+=201);=0A= +=0A+--=20An=20end=20LSN=20at=20a=20segment=20boundary=20must=20not=20= include=20the=20next=20segment.=0A+WITH=20segment=20AS=0A+(=0A+=20=20= SELECT=20*=20FROM=20pg_get_wal_files(:'wal_lsn1',=20:'wal_lsn1'::pg_lsn=20= +=201)=0A+)=0A+SELECT=20count(*)=20=3D=201=20AS=20one_file=0A+FROM=20= segment,=0A+=20=20=20=20=20LATERAL=20pg_get_wal_files(segment_start_lsn,=20= segment_end_lsn);=0A+=0A+SELECT=20count(*)=20=3D=201=20AS=20equal_lsn_ok=0A= +FROM=20pg_get_wal_files(:'wal_lsn1',=20:'wal_lsn1');=0A+SELECT=20= count(*)=20=3D=201=20AS=20default_end_ok=0A+FROM=20= pg_get_wal_files(:'wal_lsn1');=0A+SELECT=20*=20FROM=20= pg_get_wal_files(:'wal_lsn1'::pg_lsn=20+=201,=20:'wal_lsn1');=0A+SELECT=20= count(*)=20=3D=201=20AS=20future_end_ok=0A+FROM=20= pg_get_wal_files(pg_current_wal_lsn(),=20'FFFFFFFF/FFFFFFFF');=0A+SELECT=20= *=20FROM=20pg_get_wal_files('0/0',=20'0/1');=0A+=0A=20--=20= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=0A=20--=20Test=20for=20= filtering=20out=20WAL=20records=20of=20a=20particular=20table=0A=20--=20= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=0A@@=20-110,6=20= +134,8=20@@=20SELECT=20has_function_privilege('regress_pg_walinspect',=0A= =20=20=20'pg_get_wal_stats(pg_lsn,=20pg_lsn,=20boolean)=20',=20= 'EXECUTE');=20--=20no=0A=20SELECT=20= has_function_privilege('regress_pg_walinspect',=0A=20=20=20= 'pg_get_wal_block_info(pg_lsn,=20pg_lsn,=20boolean)=20',=20'EXECUTE');=20= --=20no=0A+SELECT=20has_function_privilege('regress_pg_walinspect',=0A+=20= =20'pg_get_wal_files(pg_lsn,=20pg_lsn)',=20'EXECUTE');=20--=20no=0A=20=0A= =20--=20Functions=20accessible=20by=20users=20with=20role=20= pg_read_server_files.=0A=20GRANT=20pg_read_server_files=20TO=20= regress_pg_walinspect;=0A@@=20-122,6=20+148,8=20@@=20SELECT=20= has_function_privilege('regress_pg_walinspect',=0A=20=20=20= 'pg_get_wal_stats(pg_lsn,=20pg_lsn,=20boolean)=20',=20'EXECUTE');=20--=20= yes=0A=20SELECT=20has_function_privilege('regress_pg_walinspect',=0A=20=20= =20'pg_get_wal_block_info(pg_lsn,=20pg_lsn,=20boolean)=20',=20= 'EXECUTE');=20--=20yes=0A+SELECT=20= has_function_privilege('regress_pg_walinspect',=0A+=20=20= 'pg_get_wal_files(pg_lsn,=20pg_lsn)',=20'EXECUTE');=20--=20yes=0A=20=0A=20= REVOKE=20pg_read_server_files=20FROM=20regress_pg_walinspect;=0A=20=0A@@=20= -134,6=20+162,8=20@@=20GRANT=20EXECUTE=20ON=20FUNCTION=20= pg_get_wal_stats(pg_lsn,=20pg_lsn,=20boolean)=0A=20=20=20TO=20= regress_pg_walinspect;=0A=20GRANT=20EXECUTE=20ON=20FUNCTION=20= pg_get_wal_block_info(pg_lsn,=20pg_lsn,=20boolean)=0A=20=20=20TO=20= regress_pg_walinspect;=0A+GRANT=20EXECUTE=20ON=20FUNCTION=20= pg_get_wal_files(pg_lsn,=20pg_lsn)=0A+=20=20TO=20regress_pg_walinspect;=0A= =20=0A=20SELECT=20has_function_privilege('regress_pg_walinspect',=0A=20=20= =20'pg_get_wal_record_info(pg_lsn)',=20'EXECUTE');=20--=20yes=0A@@=20= -143,6=20+173,8=20@@=20SELECT=20= has_function_privilege('regress_pg_walinspect',=0A=20=20=20= 'pg_get_wal_stats(pg_lsn,=20pg_lsn,=20boolean)=20',=20'EXECUTE');=20--=20= yes=0A=20SELECT=20has_function_privilege('regress_pg_walinspect',=0A=20=20= =20'pg_get_wal_block_info(pg_lsn,=20pg_lsn,=20boolean)=20',=20= 'EXECUTE');=20--=20yes=0A+SELECT=20= has_function_privilege('regress_pg_walinspect',=0A+=20=20= 'pg_get_wal_files(pg_lsn,=20pg_lsn)',=20'EXECUTE');=20--=20yes=0A=20=0A=20= REVOKE=20EXECUTE=20ON=20FUNCTION=20pg_get_wal_record_info(pg_lsn)=0A=20=20= =20FROM=20regress_pg_walinspect;=0A@@=20-152,6=20+184,8=20@@=20REVOKE=20= EXECUTE=20ON=20FUNCTION=20pg_get_wal_stats(pg_lsn,=20pg_lsn,=20boolean)=0A= =20=20=20FROM=20regress_pg_walinspect;=0A=20REVOKE=20EXECUTE=20ON=20= FUNCTION=20pg_get_wal_block_info(pg_lsn,=20pg_lsn,=20boolean)=0A=20=20=20= FROM=20regress_pg_walinspect;=0A+REVOKE=20EXECUTE=20ON=20FUNCTION=20= pg_get_wal_files(pg_lsn,=20pg_lsn)=0A+=20=20FROM=20= regress_pg_walinspect;=0A=20=0A=20--=20= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=0A=20--=20Clean=20up=0A= diff=20--git=20a/contrib/pg_walinspect/t/001_timeline.pl=20= b/contrib/pg_walinspect/t/001_timeline.pl=0Anew=20file=20mode=20100644=0A= index=2000000000000..1abf7131bb7=0A---=20/dev/null=0A+++=20= b/contrib/pg_walinspect/t/001_timeline.pl=0A@@=20-0,0=20+1,62=20@@=0A+#=20= Copyright=20(c)=202026,=20PostgreSQL=20Global=20Development=20Group=0A+=0A= +#=20Test=20that=20pg_get_wal_files()=20follows=20the=20current=20= timeline's=20history=20across=0A+#=20a=20promotion.=0A+use=20strict;=0A= +use=20warnings=20FATAL=20=3D>=20'all';=0A+=0A+use=20= PostgreSQL::Test::Cluster;=0A+use=20PostgreSQL::Test::Utils;=0A+use=20= Test::More;=0A+=0A+sub=20test_timeline_history=0A+{=0A+=09my=20($suffix,=20= $archive_mode)=20=3D=20@_;=0A+=09my=20$primary=20=3D=20= PostgreSQL::Test::Cluster->new("primary_$suffix");=0A+=09my=20$standby=20= =3D=20PostgreSQL::Test::Cluster->new("standby_$suffix");=0A+=0A+=09= $primary->init(allows_streaming=20=3D>=201);=0A+=09= $primary->append_conf('postgresql.conf',=20"autovacuum=20=3D=20off\n");=0A= +=09if=20($archive_mode)=0A+=09{=0A+=09=09$primary->append_conf(=0A+=09=09= =09'postgresql.conf',=0A+=09=09=09"archive_mode=20=3D=20= on\narchive_command=20=3D=20'true'\n");=0A+=09}=0A+=09$primary->start;=0A= +=09$primary->safe_psql('postgres',=0A+=09=09'CREATE=20EXTENSION=20= pg_walinspect;=20CREATE=20TABLE=20test_table=20(a=20int);');=0A+=0A+=09= my=20$backup_name=20=3D=20"backup_$suffix";=0A+=09= $primary->backup($backup_name);=0A+=09= $standby->init_from_backup($primary,=20$backup_name,=20has_streaming=20= =3D>=201);=0A+=09$standby->start;=0A+=0A+=09= $primary->safe_psql('postgres',=20'INSERT=20INTO=20test_table=20VALUES=20= (1)');=0A+=09my=20$start_lsn=20=3D=0A+=09=20=20= $primary->safe_psql('postgres',=20'SELECT=20= pg_current_wal_insert_lsn()');=0A+=09$primary->safe_psql('postgres',=20= 'SELECT=20pg_switch_wal()');=0A+=09$primary->safe_psql('postgres',=20= 'INSERT=20INTO=20test_table=20VALUES=20(2)');=0A+=09= $primary->wait_for_catchup($standby);=0A+=0A+=09$standby->promote;=0A+=09= $standby->safe_psql('postgres',=20'INSERT=20INTO=20test_table=20VALUES=20= (3)');=0A+=0A+=09my=20$result=20=3D=20$standby->safe_psql(=0A+=09=09= 'postgres',=20qq{=0A+SELECT=20count(DISTINCT=20left(wal_file,=208))=20=3D=20= 2,=0A+=20=20=20=20=20=20=20min(left(wal_file,=208))=20=3D=20'00000001',=0A= +=20=20=20=20=20=20=20max(left(wal_file,=208))=20=3D=20'00000002'=0A= +FROM=20pg_get_wal_files('$start_lsn',=20pg_current_wal_lsn())});=0A+=09= is($result,=20't|t|t',=0A+=09=09"WAL=20files=20span=20the=20timeline=20= switch=20with=20archive_mode=3D$archive_mode");=0A+=0A+=09= $standby->stop;=0A+=09$primary->stop;=0A+=09return;=0A+}=0A+=0A= +test_timeline_history('archive_off',=200);=0A= +test_timeline_history('archive_on',=201);=0A+=0A+done_testing();=0Adiff=20= --git=20a/doc/src/sgml/pgwalinspect.sgml=20= b/doc/src/sgml/pgwalinspect.sgml=0Aindex=2079c3ead40bc..b4ecae8cfb1=20= 100644=0A---=20a/doc/src/sgml/pgwalinspect.sgml=0A+++=20= b/doc/src/sgml/pgwalinspect.sgml=0A@@=20-34,9=20+34,8=20@@=0A=20=20= =0A=20=20=0A=20=20=20=0A-=20=20=20All=20of=20the=20= pg_walinspect=20functions=20that=20show=0A-=20=20=20= information=20about=20records=20that=20fall=20within=20a=20certain=20LSN=20= range=20are=0A-=20=20=20permissive=20about=20accepting=20= end_lsn=0A+=20=20=20All=20of=20the=20= pg_walinspect=20functions=20that=20accept=20an=20= LSN=0A+=20=20=20range=20are=20permissive=20about=20accepting=20= end_lsn=0A=20=20=20=20arguments=20that=20are=20= after=20the=20server's=20current=20LSN.=20=20Using=20an=0A=20=20=20=20= end_lsn=20from=20the=20future=0A= =20=20=20=20will=20not=20raise=20an=20error.=0A@@=20-60,6=20+59,35=20@@=0A= =20=20=20General=20Functions=0A=20=0A=20=20=20= =0A+=20=20=20=0A+=20=20=20=20=0A+=20=20= =20=20=20pg_get_wal_files(start_lsn=20pg_lsn,=20end_lsn=20= pg_lsn=20DEFAULT=20NULL)=20returns=20setof=20record=0A+=20=20=20= =20=0A+=0A+=20=20=20=20=0A+=20=20=20=20=20=0A+=20=20= =20=20=20=20If=20end_lsn=20is=20specified=20= and=20differs=20from=0A+=20=20=20=20=20=20= start_lsn,=20returns=20each=20retained=20WAL=20= segment=0A+=20=20=20=20=20=20file=20that=20intersects=20the=20half-open=20= WAL=20range=0A+=20=20=20=20=20=20[start_lsn,=0A= +=20=20=20=20=20=20end_lsn).=20=20If=0A+=20=20= =20=20=20=20end_lsn=20is=20omitted,=20is=20= null,=20or=20equals=0A+=20=20=20=20=20=20= start_lsn,=20returns=20the=20segment=20= containing=0A+=20=20=20=20=20=20start_lsn.=20=20= The=0A+=20=20=20=20=20=20wal_file=20column=20= contains=20the=20file=20name,=20while=0A+=20=20=20=20=20=20= segment_start_lsn=20and=0A+=20=20=20=20=20=20= segment_end_lsn=20contain=20the=20complete=20= segment=0A+=20=20=20=20=20=20boundaries=20rather=20than=20boundaries=20= clipped=20to=20the=20requested=20range.=0A+=20=20=20=20=20=0A+=20=20= =20=20=20=0A+=20=20=20=20=20=20File=20names=20use=20the=20timeline=20= valid=20for=20each=20segment=20in=20the=20current=0A+=20=20=20=20=20=20= timeline's=20history.=20=20This=20allows=20a=20returned=20range=20to=20= cross=20a=20timeline=0A+=20=20=20=20=20=20switch.=20=20The=20function=20= reports=20an=20error=20if=20any=20required=20segment=20file=20is=0A+=20=20= =20=20=20=20not=20currently=20retained=20in=20= pg_wal.=0A+=20=20=20=20=20=0A+=20=20=20=20= =0A+=20=20=20=0A+=0A=20=20=20=20=0A=20=20=20=20=20= =0A=20=20=20=20=20=20pg_get_wal_record_info(in_lsn=20= pg_lsn)=20returns=20record=0Adiff=20--git=20= a/src/tools/pgindent/typedefs.list=20b/src/tools/pgindent/typedefs.list=0A= index=20656f1f60862..1f1bcf8d527=20100644=0A---=20= a/src/tools/pgindent/typedefs.list=0A+++=20= b/src/tools/pgindent/typedefs.list=0A@@=20-3434,6=20+3434,7=20@@=20= WalSummarizerData=0A=20WalSummaryFile=0A=20WalSummaryIO=0A=20= WalTimeSample=0A+WalTimeSegment=0A=20WalUsage=0A=20WalWriteMethod=0A=20= WalWriteMethodOps=0A--=20=0A2.50.1=20(Apple=20Git-155)=0A=0A= --Apple-Mail=_D1745098-B8B4-48AB-8FF9-8232B81AF591 Content-Disposition: attachment; filename=v2-0002-pg_walinspect-add-function-to-locate-WAL-by-time.patch Content-Type: application/octet-stream; x-unix-mode=0644; name="v2-0002-pg_walinspect-add-function-to-locate-WAL-by-time.patch" Content-Transfer-Encoding: quoted-printable =46rom=20d939eef1f5ddfa2a122024b6d7371b4a67a6564f=20Mon=20Sep=2017=20= 00:00:00=202001=0AFrom:=20"Chao=20Li=20(Evan)"=20=0A= Date:=20Thu,=201=20Oct=202026=2006:52:59=20+0800=0ASubject:=20[PATCH=20= v2=202/2]=20pg_walinspect:=20add=20function=20to=20locate=20WAL=20by=20= time=0A=0AAdd=20pg_get_wal_location_at_time(),=20which=20uses=20= timestamped=20WAL=20records=20to=20locate=0Aa=20useful=20WAL=20range=20= around=20a=20requested=20wall-clock=20time.=20Use=20an=20approximate=0A= binary=20search=20over=20retained=20segments=20and=20allow=20callers=20= to=20widen=20the=20requested=0Ainterval=20when=20timestamp=20reordering=20= makes=20the=20initial=20result=20insufficient.=0A=0AMove=20WAL=20record=20= timestamp=20extraction=20from=20recovery=20into=20xlogreader=0Aso=20= pg_walinspect=20can=20recognize=20the=20same=20timestamp-bearing=20= record=20types=20without=0Aduplicating=20that=20logic.=0A=0AAuthor:=20= Chao=20Li=20=0AReviewed-by:=20Surya=20Poondla=20= =0ADiscussion:=20= https://postgr.es/m/80E9F0AD-CFC5-4BE5-81DE-D8FE35E10A1C@gmail.com=0A---=0A= =20.../pg_walinspect/expected/oldextversions.out=20|=20=20=209=20+-=0A=20= .../pg_walinspect/expected/pg_walinspect.out=20=20|=20161=20+++++++=0A=20= contrib/pg_walinspect/meson.build=20=20=20=20=20=20=20=20=20=20=20=20=20= |=20=20=201=20+=0A=20.../pg_walinspect/pg_walinspect--1.1--1.2.sql=20|=20= =2021=20+=0A=20contrib/pg_walinspect/pg_walinspect.c=20=20=20=20=20=20=20= =20=20|=20422=20++++++++++++++++++=0A=20= contrib/pg_walinspect/sql/pg_walinspect.sql=20=20=20|=20118=20+++++=0A=20= contrib/pg_walinspect/t/002_time_search.pl=20=20=20=20|=20=2051=20+++=0A=20= doc/src/sgml/pgwalinspect.sgml=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20|=20107=20+++++=0A=20src/backend/access/transam/xlogreader.c=20=20=20=20= =20=20=20|=20=2036=20++=0A=20src/backend/access/transam/xlogrecovery.c=20= =20=20=20=20|=20=2048=20+-=0A=20src/include/access/xlogreader.h=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20|=20=20=203=20+=0A=20= src/tools/pgindent/typedefs.list=20=20=20=20=20=20=20=20=20=20=20=20=20=20= |=20=20=201=20+=0A=2012=20files=20changed,=20932=20insertions(+),=2046=20= deletions(-)=0A=20create=20mode=20100644=20= contrib/pg_walinspect/t/002_time_search.pl=0A=0Adiff=20--git=20= a/contrib/pg_walinspect/expected/oldextversions.out=20= b/contrib/pg_walinspect/expected/oldextversions.out=0Aindex=20= 311017d66ca..6228ad9ef39=20100644=0A---=20= a/contrib/pg_walinspect/expected/oldextversions.out=0A+++=20= b/contrib/pg_walinspect/expected/oldextversions.out=0A@@=20-51,15=20= +51,16=20@@=20ALTER=20EXTENSION=20pg_walinspect=20UPDATE=20TO=20'1.1';=0A= =20ALTER=20EXTENSION=20pg_walinspect=20UPDATE=20TO=20'1.2';=0A=20--=20= List=20what=20the=20current=20version=20contains.=0A=20\dx+=20= pg_walinspect=0A-=20=20=20=20=20=20=20=20=20Objects=20in=20extension=20= "pg_walinspect"=0A-=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= Object=20description=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=0A--------------------------------------------------------=0A+=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20Objects=20in=20= extension=20"pg_walinspect"=0A+=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20Object=20= description=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=0A= +-------------------------------------------------------------------------= ---------=0A=20=20function=20= pg_get_wal_block_info(pg_lsn,pg_lsn,boolean)=0A=20=20function=20= pg_get_wal_files(pg_lsn,pg_lsn)=0A+=20function=20= pg_get_wal_location_at_time(timestamp=20with=20time=20= zone,interval,interval)=0A=20=20function=20= pg_get_wal_record_info(pg_lsn)=0A=20=20function=20= pg_get_wal_records_info(pg_lsn,pg_lsn)=0A=20=20function=20= pg_get_wal_stats(pg_lsn,pg_lsn,boolean)=0A-(5=20rows)=0A+(6=20rows)=0A=20= =0A=20SELECT=20pg_drop_replication_slot('regress_pg_walinspect_slot');=0A= =20=20pg_drop_replication_slot=20=0Adiff=20--git=20= a/contrib/pg_walinspect/expected/pg_walinspect.out=20= b/contrib/pg_walinspect/expected/pg_walinspect.out=0Aindex=20= 34864c65ec7..5933c80071d=20100644=0A---=20= a/contrib/pg_walinspect/expected/pg_walinspect.out=0A+++=20= b/contrib/pg_walinspect/expected/pg_walinspect.out=0A@@=20-139,6=20= +139,142=20@@=20FROM=20pg_get_wal_files(pg_current_wal_lsn(),=20= 'FFFFFFFF/FFFFFFFF');=0A=20SELECT=20*=20FROM=20pg_get_wal_files('0/0',=20= '0/1');=0A=20ERROR:=20=20could=20not=20read=20WAL=20at=20LSN=20= 0/00000000=0A=20--=20= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=0A+--=20Tests=20for=20= locating=20WAL=20by=20timestamps=20stored=20in=20WAL=20records=0A+--=20= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=0A+--=20Put=20two=20= WAL-logged=20transaction=20commits=20between=20the=20requested=20= boundaries.=0A+INSERT=20INTO=20sample_tbl=20VALUES=20(5,=205);=0A+SELECT=20= clock_timestamp()=20AS=20wal_time_lower=20\gset=0A+INSERT=20INTO=20= sample_tbl=20VALUES=20(6,=206);=0A+INSERT=20INTO=20sample_tbl=20VALUES=20= (10,=2010);=0A+SELECT=20clock_timestamp()=20AS=20wal_time_upper=20\gset=0A= +SELECT=20start_timestamp=20>=3D=20:'wal_time_lower'::timestamptz=0A+=20=20= =20=20=20=20=20=20=20AS=20start_ok,=0A+=20=20=20=20=20=20=20= end_timestamp=20>=20:'wal_time_lower'::timestamptz=20AND=0A+=20=20=20=20=20= =20=20=20=20end_timestamp=20<=3D=20:'wal_time_upper'::timestamptz=20AS=20= end_ok,=0A+=20=20=20=20=20=20=20start_lsn=20<=20end_lsn=20AS=20lsn_ok=0A= +FROM=20pg_get_wal_location_at_time(:'wal_time_lower',=0A+=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20before=20=3D>=20interval=20'1=20microsecond',=0A+=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20after=20=3D>=20:'wal_time_upper'::timestamptz=20-=0A+=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20:'wal_time_lower'::timestamptz);=0A= +=20start_ok=20|=20end_ok=20|=20lsn_ok=20=0A= +----------+--------+--------=0A+=20t=20=20=20=20=20=20=20=20|=20t=20=20=20= =20=20=20|=20t=0A+(1=20row)=0A+=0A+--=20A=20single=20matching=20= timestamped=20record=20is=20returned=20for=20both=20boundaries.=0A= +SELECT=20clock_timestamp()=20AS=20single_time_lower=20\gset=0A+INSERT=20= INTO=20sample_tbl=20VALUES=20(12,=2012);=0A+SELECT=20clock_timestamp()=20= AS=20single_time_upper=20\gset=0A+SELECT=20location.start_timestamp=20=3D=20= location.end_timestamp=20AS=20timestamp_ok,=0A+=20=20=20=20=20=20=20= location.start_lsn=20=3D=20location.end_lsn=20AS=20lsn_ok,=0A+=20=20=20=20= =20=20=20info.record_type=20=3D=20'COMMIT'=20AS=20record_ok=0A+FROM=20= pg_get_wal_location_at_time(=0A+=20=20=20=20=20=20=20= :'single_time_lower'::timestamptz=20+=20interval=20'1=20microsecond',=0A= +=20=20=20=20=20=20=20interval=20'1=20microsecond',=0A+=20=20=20=20=20=20= =20:'single_time_upper'::timestamptz=20-=0A+=20=20=20=20=20=20=20=20=20= :'single_time_lower'::timestamptz=20-=20interval=20'1=20microsecond')=20= AS=20location,=0A+=20=20=20=20=20LATERAL=20= pg_get_wal_record_info(location.start_lsn)=20AS=20info;=0A+=20= timestamp_ok=20|=20lsn_ok=20|=20record_ok=20=0A= +--------------+--------+-----------=0A+=20t=20=20=20=20=20=20=20=20=20=20= =20=20|=20t=20=20=20=20=20=20|=20t=0A+(1=20row)=0A+=0A+--=20Exercise=20a=20= time=20window=20containing=20a=20COMMIT=20PREPARED=20record.=20=20= Concurrent=0A+--=20activity=20can=20cause=20another=20timestamped=20= record=20to=20be=20selected=20as=20the=20anchor.=0A+INSERT=20INTO=20= sample_tbl=20VALUES=20(11,=2011);=0A+BEGIN;=0A+INSERT=20INTO=20= sample_tbl=20VALUES=20(7,=207);=0A+PREPARE=20TRANSACTION=20= 'regress_pg_walinspect_time';=0A+SELECT=20clock_timestamp()=20AS=20= prepared_time_target=20\gset=0A+COMMIT=20PREPARED=20= 'regress_pg_walinspect_time';=0A+SELECT=20clock_timestamp()=20AS=20= prepared_time_upper=20\gset=0A+SELECT=20record_type=20IN=20('COMMIT',=20= 'COMMIT_PREPARED',=20'ABORT',=0A+=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20'ABORT_PREPARED',=20'RESTORE_POINT')=20AS=20= timestamped_ok=0A+FROM=20= pg_get_wal_location_at_time(:'prepared_time_target',=0A+=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20:'prepared_time_target'::timestamptz=20-=0A+=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20:'wal_time_upper'::timestamptz,=0A+=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= :'prepared_time_upper'::timestamptz=20-=0A+=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= :'prepared_time_target'::timestamptz)=20AS=20location,=0A+=20=20=20=20=20= LATERAL=20pg_get_wal_record_info(location.end_lsn);=0A+=20timestamped_ok=20= =0A+----------------=0A+=20t=0A+(1=20row)=0A+=0A+--=20Exercise=20a=20= time=20window=20containing=20restore=20points,=20allowing=20for=20other=0A= +--=20timestamped=20WAL=20records=20generated=20concurrently.=0A+SELECT=20= pg_create_restore_point('regress_wal_time_lower')=20AS=20restore_lsn=20= \gset=0A+SELECT=20clock_timestamp()=20AS=20restore_time_target=20\gset=0A= +SELECT=20pg_create_restore_point('regress_wal_time_upper')=20AS=20= restore_lsn=20\gset=0A+SELECT=20clock_timestamp()=20AS=20= restore_time_upper=20\gset=0A+INSERT=20INTO=20sample_tbl=20VALUES=20(8,=20= 8);=0A+SELECT=20start_info.record_type=20IN=20('COMMIT',=20= 'COMMIT_PREPARED',=20'ABORT',=0A+=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= 'ABORT_PREPARED',=20'RESTORE_POINT')=20AS=20start_ok,=0A+=20=20=20=20=20=20= =20end_info.record_type=20IN=20('COMMIT',=20'COMMIT_PREPARED',=20= 'ABORT',=0A+=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20'ABORT_PREPARED',=20'RESTORE_POINT')=20= AS=20end_ok=0A+FROM=20= pg_get_wal_location_at_time(:'restore_time_target',=0A+=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20:'restore_time_target'::timestamptz=20-=0A+=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20:'wal_time_upper'::timestamptz,=0A+=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= :'restore_time_upper'::timestamptz=20-=0A+=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= :'restore_time_target'::timestamptz)=20AS=20location,=0A+=20=20=20=20=20= LATERAL=20pg_get_wal_record_info(location.start_lsn)=20AS=20start_info,=0A= +=20=20=20=20=20LATERAL=20pg_get_wal_record_info(location.end_lsn)=20AS=20= end_info;=0A+=20start_ok=20|=20end_ok=20=0A+----------+--------=0A+=20t=20= =20=20=20=20=20=20=20|=20t=0A+(1=20row)=0A+=0A+--=20A=20future=20upper=20= bound=20uses=20a=20timestamped=20WAL=20record=20at=20or=20before=20now.=0A= +SELECT=20clock_timestamp()=20AS=20current_time_target=20\gset=0A+INSERT=20= INTO=20sample_tbl=20VALUES=20(9,=209);=0A+SELECT=20= location.start_timestamp=20>=3D=20:'restore_time_upper'::timestamptz=20= AND=0A+=20=20=20=20=20=20=20=20=20location.start_timestamp=20<=3D=20= :'current_time_target'::timestamptz=0A+=20=20=20=20=20=20=20=20=20AS=20= start_ok,=0A+=20=20=20=20=20=20=20location.end_timestamp=20>=3D=20= :'current_time_target'::timestamptz=20AND=0A+=20=20=20=20=20=20=20=20=20= location.end_timestamp=20<=3D=20clock_timestamp()=20AS=20capped_end_ok,=0A= +=20=20=20=20=20=20=20location.start_lsn=20<=20location.end_lsn=20AS=20= lsn_ok,=0A+=20=20=20=20=20=20=20end_info.record_type=20=3D=20'COMMIT'=20= AS=20real_record_ok=0A+FROM=20= pg_get_wal_location_at_time(:'current_time_target',=0A+=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20:'current_time_target'::timestamptz=20-=0A+=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20:'restore_time_upper'::timestamptz,=0A+=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= interval=20'1=20day')=20AS=20location,=0A+=20=20=20=20=20LATERAL=20= pg_get_wal_record_info(location.end_lsn)=20AS=20end_info;=0A+=20start_ok=20= |=20capped_end_ok=20|=20lsn_ok=20|=20real_record_ok=20=0A= +----------+---------------+--------+----------------=0A+=20t=20=20=20=20= =20=20=20=20|=20t=20=20=20=20=20=20=20=20=20=20=20=20=20|=20t=20=20=20=20= =20=20|=20t=0A+(1=20row)=0A+=0A+SELECT=20pg_get_function_arguments(=0A+=20= =20'pg_get_wal_location_at_time(timestamptz,=20interval,=20= interval)'::regprocedure)=0A+=20=20LIKE=20'%before=20interval=20DEFAULT=20= ''@=201=20min''::interval,=20after=20interval=20DEFAULT=20''@=201=20= min''::interval%'=0A+=20=20AS=20defaults_ok;=0A+=20defaults_ok=20=0A= +-------------=0A+=20t=0A+(1=20row)=0A+=0A+SELECT=20*=20FROM=20= pg_get_wal_location_at_time(:'wal_time_lower',=0A+=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20interval=20'0',=20interval=20'1=20second');=0A= +ERROR:=20=20before=20interval=20must=20be=20finite=20and=20greater=20= than=20zero=0A+SELECT=20*=20FROM=20= pg_get_wal_location_at_time(:'wal_time_lower',=0A+=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20interval=20'1=20second',=20interval=20'0');=0A= +ERROR:=20=20after=20interval=20must=20be=20finite=20and=20greater=20= than=20zero=0A+SELECT=20*=20FROM=20= pg_get_wal_location_at_time(:'wal_time_lower',=20interval=20'-1=20= second');=0A+ERROR:=20=20before=20interval=20must=20be=20finite=20and=20= greater=20than=20zero=0A+SELECT=20*=20FROM=20= pg_get_wal_location_at_time(:'wal_time_lower',=0A+=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20after=20=3D>=20interval=20'-1=20second');=0A= +ERROR:=20=20after=20interval=20must=20be=20finite=20and=20greater=20= than=20zero=0A+SELECT=20*=20FROM=20= pg_get_wal_location_at_time(:'wal_time_lower',=0A+=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20before=20=3D>=20interval=20'1=20day=201=20= microsecond');=0A+ERROR:=20=20before=20interval=20must=20not=20exceed=20= one=20day=0A+SELECT=20*=20FROM=20= pg_get_wal_location_at_time(:'wal_time_lower',=0A+=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20after=20=3D>=20interval=20'1=20day=201=20= microsecond');=0A+ERROR:=20=20after=20interval=20must=20not=20exceed=20= one=20day=0A+SELECT=20*=20FROM=20= pg_get_wal_location_at_time(clock_timestamp()=20-=20interval=20'100=20= years');=0A+ERROR:=20=20could=20not=20locate=20a=20WAL=20time=20boundary=0A= +SELECT=20*=20FROM=20pg_get_wal_location_at_time(clock_timestamp()=20+=20= interval=20'100=20years');=0A+ERROR:=20=20requested=20time=20follows=20= the=20available=20WAL=20range=0A+--=20= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=0A=20--=20Test=20for=20= filtering=20out=20WAL=20records=20of=20a=20particular=20table=0A=20--=20= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=0A=20SELECT=20oid=20= AS=20sample_tbl_oid=20FROM=20pg_class=20WHERE=20relname=20=3D=20= 'sample_tbl'=20\gset=0A@@=20-231,6=20+367,13=20@@=20SELECT=20= has_function_privilege('regress_pg_walinspect',=0A=20=20f=0A=20(1=20row)=0A= =20=0A+SELECT=20has_function_privilege('regress_pg_walinspect',=0A+=20=20= 'pg_get_wal_location_at_time(timestamptz,=20interval,=20interval)',=20= 'EXECUTE');=20--=20no=0A+=20has_function_privilege=20=0A= +------------------------=0A+=20f=0A+(1=20row)=0A+=0A=20SELECT=20= has_function_privilege('regress_pg_walinspect',=0A=20=20=20= 'pg_get_wal_files(pg_lsn,=20pg_lsn)',=20'EXECUTE');=20--=20no=0A=20=20= has_function_privilege=20=0A@@=20-268,6=20+411,13=20@@=20SELECT=20= has_function_privilege('regress_pg_walinspect',=0A=20=20t=0A=20(1=20row)=0A= =20=0A+SELECT=20has_function_privilege('regress_pg_walinspect',=0A+=20=20= 'pg_get_wal_location_at_time(timestamptz,=20interval,=20interval)',=20= 'EXECUTE');=20--=20yes=0A+=20has_function_privilege=20=0A= +------------------------=0A+=20t=0A+(1=20row)=0A+=0A=20SELECT=20= has_function_privilege('regress_pg_walinspect',=0A=20=20=20= 'pg_get_wal_files(pg_lsn,=20pg_lsn)',=20'EXECUTE');=20--=20yes=0A=20=20= has_function_privilege=20=0A@@=20-285,6=20+435,8=20@@=20GRANT=20EXECUTE=20= ON=20FUNCTION=20pg_get_wal_stats(pg_lsn,=20pg_lsn,=20boolean)=0A=20=20=20= TO=20regress_pg_walinspect;=0A=20GRANT=20EXECUTE=20ON=20FUNCTION=20= pg_get_wal_block_info(pg_lsn,=20pg_lsn,=20boolean)=0A=20=20=20TO=20= regress_pg_walinspect;=0A+GRANT=20EXECUTE=20ON=20FUNCTION=20= pg_get_wal_location_at_time(timestamptz,=20interval,=20interval)=0A+=20=20= TO=20regress_pg_walinspect;=0A=20GRANT=20EXECUTE=20ON=20FUNCTION=20= pg_get_wal_files(pg_lsn,=20pg_lsn)=0A=20=20=20TO=20= regress_pg_walinspect;=0A=20SELECT=20= has_function_privilege('regress_pg_walinspect',=0A@@=20-315,6=20+467,13=20= @@=20SELECT=20has_function_privilege('regress_pg_walinspect',=0A=20=20t=0A= =20(1=20row)=0A=20=0A+SELECT=20= has_function_privilege('regress_pg_walinspect',=0A+=20=20= 'pg_get_wal_location_at_time(timestamptz,=20interval,=20interval)',=20= 'EXECUTE');=20--=20yes=0A+=20has_function_privilege=20=0A= +------------------------=0A+=20t=0A+(1=20row)=0A+=0A=20SELECT=20= has_function_privilege('regress_pg_walinspect',=0A=20=20=20= 'pg_get_wal_files(pg_lsn,=20pg_lsn)',=20'EXECUTE');=20--=20yes=0A=20=20= has_function_privilege=20=0A@@=20-330,6=20+489,8=20@@=20REVOKE=20EXECUTE=20= ON=20FUNCTION=20pg_get_wal_stats(pg_lsn,=20pg_lsn,=20boolean)=0A=20=20=20= FROM=20regress_pg_walinspect;=0A=20REVOKE=20EXECUTE=20ON=20FUNCTION=20= pg_get_wal_block_info(pg_lsn,=20pg_lsn,=20boolean)=0A=20=20=20FROM=20= regress_pg_walinspect;=0A+REVOKE=20EXECUTE=20ON=20FUNCTION=20= pg_get_wal_location_at_time(timestamptz,=20interval,=20interval)=0A+=20=20= FROM=20regress_pg_walinspect;=0A=20REVOKE=20EXECUTE=20ON=20FUNCTION=20= pg_get_wal_files(pg_lsn,=20pg_lsn)=0A=20=20=20FROM=20= regress_pg_walinspect;=0A=20--=20= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=0Adiff=20--git=20= a/contrib/pg_walinspect/meson.build=20= b/contrib/pg_walinspect/meson.build=0Aindex=206f8b4a6359a..79e94e33e4d=20= 100644=0A---=20a/contrib/pg_walinspect/meson.build=0A+++=20= b/contrib/pg_walinspect/meson.build=0A@@=20-41,6=20+41,7=20@@=20tests=20= +=3D=20{=0A=20=20=20'tap':=20{=0A=20=20=20=20=20'tests':=20[=0A=20=20=20=20= =20=20=20't/001_timeline.pl',=0A+=20=20=20=20=20=20= 't/002_time_search.pl',=0A=20=20=20=20=20],=0A=20=20=20},=0A=20}=0Adiff=20= --git=20a/contrib/pg_walinspect/pg_walinspect--1.1--1.2.sql=20= b/contrib/pg_walinspect/pg_walinspect--1.1--1.2.sql=0Aindex=20= 9cc9523b4dc..f72d99a3ceb=20100644=0A---=20= a/contrib/pg_walinspect/pg_walinspect--1.1--1.2.sql=0A+++=20= b/contrib/pg_walinspect/pg_walinspect--1.1--1.2.sql=0A@@=20-3,6=20+3,27=20= @@=0A=20--=20complain=20if=20script=20is=20sourced=20in=20psql,=20rather=20= than=20via=20ALTER=20EXTENSION=0A=20\echo=20Use=20"ALTER=20EXTENSION=20= pg_walinspect=20UPDATE=20TO=20'1.2'"=20to=20load=20this=20file.=20\quit=0A= =20=0A+--=0A+--=20pg_get_wal_location_at_time()=0A+--=0A+CREATE=20= FUNCTION=20pg_get_wal_location_at_time(=0A+=20=20=20=20IN=20target_time=20= timestamptz,=0A+=20=20=20=20IN=20before=20interval=20DEFAULT=20'1=20= minute',=0A+=20=20=20=20IN=20after=20interval=20DEFAULT=20'1=20minute',=0A= +=20=20=20=20OUT=20start_timestamp=20timestamptz,=0A+=20=20=20=20OUT=20= start_lsn=20pg_lsn,=0A+=20=20=20=20OUT=20end_timestamp=20timestamptz,=0A= +=20=20=20=20OUT=20end_lsn=20pg_lsn=0A+)=0A+AS=20'MODULE_PATHNAME',=20= 'pg_get_wal_location_at_time'=0A+LANGUAGE=20C=20STRICT=20PARALLEL=20= SAFE;=0A+=0A+REVOKE=20EXECUTE=20ON=20FUNCTION=0A+=20=20= pg_get_wal_location_at_time(timestamptz,=20interval,=20interval)=20FROM=20= PUBLIC;=0A+GRANT=20EXECUTE=20ON=20FUNCTION=0A+=20=20= pg_get_wal_location_at_time(timestamptz,=20interval,=20interval)=0A+=20=20= TO=20pg_read_server_files;=0A+=0A=20--=0A=20--=20pg_get_wal_files()=0A=20= --=0Adiff=20--git=20a/contrib/pg_walinspect/pg_walinspect.c=20= b/contrib/pg_walinspect/pg_walinspect.c=0Aindex=20= 42f366bdb4e..0511bb591e7=20100644=0A---=20= a/contrib/pg_walinspect/pg_walinspect.c=0A+++=20= b/contrib/pg_walinspect/pg_walinspect.c=0A@@=20-12,6=20+12,8=20@@=0A=20=20= */=0A=20#include=20"postgres.h"=0A=20=0A+#include=20=0A+=0A=20= #include=20"access/htup_details.h"=0A=20#include=20"access/timeline.h"=0A= =20#include=20"access/xlog.h"=0A@@=20-26,7=20+28,9=20@@=0A=20#include=20= "storage/fd.h"=0A=20#include=20"utils/array.h"=0A=20#include=20= "utils/builtins.h"=0A+#include=20"utils/fmgrprotos.h"=0A=20#include=20= "utils/pg_lsn.h"=0A+#include=20"utils/timestamp.h"=0A=20#include=20= "utils/tuplestore.h"=0A=20=0A=20/*=0A@@=20-41,6=20+45,7=20@@=20= PG_MODULE_MAGIC_EXT(=0A=20=0A=20= PG_FUNCTION_INFO_V1(pg_get_wal_block_info);=0A=20= PG_FUNCTION_INFO_V1(pg_get_wal_files);=0A= +PG_FUNCTION_INFO_V1(pg_get_wal_location_at_time);=0A=20= PG_FUNCTION_INFO_V1(pg_get_wal_record_info);=0A=20= PG_FUNCTION_INFO_V1(pg_get_wal_records_info);=0A=20= PG_FUNCTION_INFO_V1(pg_get_wal_records_info_till_end_of_wal);=0A@@=20= -71,15=20+76,51=20@@=20static=20void=20GetWalStats(FunctionCallInfo=20= fcinfo,=0A=20static=20void=20GetWALBlockInfo(FunctionCallInfo=20fcinfo,=20= XLogReaderState=20*record,=0A=20=09=09=09=09=09=09=09bool=20show_data);=0A= =20=0A+/*=20Selected=20WAL=20position=20and=20timestamp=20for=20one=20= time=20boundary.=20*/=0A+typedef=20struct=20WalTimeBoundary=0A+{=0A+=09= bool=09=09found;=09=09=09/*=20matching=20record=20was=20found=20*/=0A+=09= TimestampTz=20time;=09=09=09/*=20record's=20local=20event=20time=20*/=0A= +=09XLogRecPtr=09lsn;=09=09=09/*=20record's=20start=20LSN=20*/=0A+=09int=09= =09=09segment_index;=09/*=20index=20of=20the=20segment=20containing=20= the=20record=20*/=0A+}=20WalTimeBoundary;=0A+=0A=20/*=20Metadata=20for=20= one=20retained=20segment=20in=20the=20current=20timeline's=20history.=20= */=0A=20typedef=20struct=20WalTimeSegment=0A=20{=0A=20=09XLogSegNo=09= segno;=09=09=09/*=20segment=20number,=20used=20for=20WAL=20ordering=20*/=0A= =20=09TimeLineID=09tli;=09=09=09/*=20timeline=20containing=20this=20= segment=20*/=0A+=09bool=09=09scanned;=09=09/*=20segment=20has=20already=20= been=20decoded=20*/=0A+=09bool=09=09has_timestamp;=09/*=20segment=20= contains=20timestamped=20records=20*/=0A+=09TimestampTz=20min_time;=09=09= /*=20minimum=20record=20timestamp=20in=20segment=20*/=0A+=09TimestampTz=20= max_time;=09=09/*=20maximum=20record=20timestamp=20in=20segment=20*/=0A+=09= WalTimeBoundary=20lower;=09=09/*=20best=20lower=20anchor=20in=20segment=20= */=0A+=09WalTimeBoundary=20upper;=09=09/*=20best=20upper=20anchor=20in=20= segment=20*/=0A=20}=20WalTimeSegment;=0A=20=0A=20static=20int=09= wal_time_segment_cmp(const=20void=20*a,=20const=20void=20*b);=0A=20= static=20WalTimeSegment=20*GetWalTimeSegments(int=20*nsegments);=0A= +static=20int=09FindWalTimeCandidate(WalTimeSegment=20*segments,=0A+=09=09= =09=09=09=09=09=09=20int=20*search_segments,=0A+=09=09=09=09=09=09=09=09=20= int=20*nsearch_segments,=0A+=09=09=09=09=09=09=09=09=20TimestampTz=20= target_time,=0A+=09=09=09=09=09=09=09=09=20bool=20search_left,=0A+=09=09=09= =09=09=09=09=09=20TimestampTz=20wanted_start,=0A+=09=09=09=09=09=09=09=09= =20TimestampTz=20wanted_end,=0A+=09=09=09=09=09=09=09=09=20int=20= *candidate_position,=0A+=09=09=09=09=09=09=09=09=20int=20*nscanned);=0A= +static=20void=20ScanWalTimeSegment(WalTimeSegment=20*segment,=20int=20= segment_index,=0A+=09=09=09=09=09=09=09=20=20=20TimestampTz=20= wanted_start,=0A+=09=09=09=09=09=09=09=20=20=20TimestampTz=20= wanted_end);=0A+static=20void=20MergeWalTimeSegment(WalTimeSegment=20= *segment,=0A+=09=09=09=09=09=09=09=09WalTimeBoundary=20*lower,=0A+=09=09=09= =09=09=09=09=09WalTimeBoundary=20*upper);=0A+static=20void=20= ProbeLowerBoundary(WalTimeBoundary=20*boundary,=0A+=09=09=09=09=09=09=09=20= =20=20TimestampTz=20time,=20XLogRecPtr=20lsn,=0A+=09=09=09=09=09=09=09=20= =20=20int=20segment_index);=0A+static=20void=20= ProbeUpperBoundary(WalTimeBoundary=20*boundary,=0A+=09=09=09=09=09=09=09=20= =20=20TimestampTz=20time,=20XLogRecPtr=20lsn,=0A+=09=09=09=09=09=09=09=20= =20=20int=20segment_index);=0A=20=0A=20/*=0A=20=20*=20Return=20the=20LSN=20= up=20to=20which=20the=20server=20has=20WAL,=20and=20optionally=20its=20= timeline.=0A@@=20-223,6=20+264,44=20@@=20wal_time_segment_cmp(const=20= void=20*a,=20const=20void=20*b)=0A=20=09return=200;=0A=20}=0A=20=0A+/*=0A= +=20*=20Probe=20a=20timestamped=20record=20at=20or=20after=20a=20= requested=20time=20boundary.=20=20Among=0A+=20*=20records=20examined=20= so=20far,=20prefer=20the=20earliest=20time,=20breaking=20ties=20in=20= favor=0A+=20*=20of=20the=20earlier=20WAL=20record.=0A+=20*/=0A+static=20= void=0A+ProbeLowerBoundary(WalTimeBoundary=20*boundary,=20TimestampTz=20= time,=0A+=09=09=09=09=20=20=20XLogRecPtr=20lsn,=20int=20segment_index)=0A= +{=0A+=09if=20(!boundary->found=20||=20time=20<=20boundary->time=20||=0A= +=09=09(time=20=3D=3D=20boundary->time=20&&=20lsn=20<=20boundary->lsn))=0A= +=09{=0A+=09=09boundary->found=20=3D=20true;=0A+=09=09boundary->time=20=3D= =20time;=0A+=09=09boundary->lsn=20=3D=20lsn;=0A+=09=09= boundary->segment_index=20=3D=20segment_index;=0A+=09}=0A+}=0A+=0A+/*=0A= +=20*=20Probe=20a=20timestamped=20record=20at=20or=20before=20a=20= requested=20time=20boundary.=20=20Among=0A+=20*=20records=20examined=20= so=20far,=20prefer=20the=20latest=20time,=20breaking=20ties=20in=20favor=20= of=0A+=20*=20the=20later=20WAL=20record.=0A+=20*/=0A+static=20void=0A= +ProbeUpperBoundary(WalTimeBoundary=20*boundary,=20TimestampTz=20time,=0A= +=09=09=09=09=20=20=20XLogRecPtr=20lsn,=20int=20segment_index)=0A+{=0A+=09= if=20(!boundary->found=20||=20time=20>=20boundary->time=20||=0A+=09=09= (time=20=3D=3D=20boundary->time=20&&=20lsn=20>=20boundary->lsn))=0A+=09{=0A= +=09=09boundary->found=20=3D=20true;=0A+=09=09boundary->time=20=3D=20= time;=0A+=09=09boundary->lsn=20=3D=20lsn;=0A+=09=09= boundary->segment_index=20=3D=20segment_index;=0A+=09}=0A+}=0A+=0A=20/*=0A= =20=20*=20Get=20the=20WAL=20segments=20that=20form=20the=20history=20of=20= the=20server's=20current=0A=20=20*=20timeline.=20=20Other=20timelines=20= may=20have=20files=20with=20the=20same=20segment=20number=20in=0A@@=20= -297,6=20+376,140=20@@=20GetWalTimeSegments(int=20*nsegments)=0A=20=09= return=20segments;=0A=20}=0A=20=0A+/*=0A+=20*=20Approximately=20locate=20= the=20segment=20containing=20target_time=20by=20binary=20search.=0A+=20*=20= Record=20timestamps=20are=20expected=20to=20generally=20increase=20with=20= WAL=20position.=0A+=20*=20Remove=20segments=20without=20timestamped=20= records=20from=20the=20search=20candidates,=0A+=20*=20since=20they=20= provide=20no=20value=20with=20which=20to=20choose=20either=20half.=0A+=20= */=0A+static=20int=0A+FindWalTimeCandidate(WalTimeSegment=20*segments,=20= int=20*search_segments,=0A+=09=09=09=09=09=20int=20*nsearch_segments,=0A= +=09=09=09=09=09=20TimestampTz=20target_time,=20bool=20search_left,=0A+=09= =09=09=09=09=20TimestampTz=20wanted_start,=20TimestampTz=20wanted_end,=0A= +=09=09=09=09=09=20int=20*candidate_position,=20int=20*nscanned)=0A+{=0A= +=09int=09=09=09left=20=3D=200;=0A+=09int=09=09=09right=20=3D=20= *nsearch_segments=20-=201;=0A+=09int=09=09=09result;=0A+=0A+=09while=20= (left=20<=3D=20right)=0A+=09{=0A+=09=09int=09=09=09middle=20=3D=20left=20= +=20(right=20-=20left)=20/=202;=0A+=09=09int=09=09=09segment_index=20=3D=20= search_segments[middle];=0A+=09=09WalTimeSegment=20*segment=20=3D=20= &segments[segment_index];=0A+=0A+=09=09if=20(!segment->scanned)=0A+=09=09= {=0A+=09=09=09ScanWalTimeSegment(segment,=20segment_index,=20= wanted_start,=20wanted_end);=0A+=09=09=09(*nscanned)++;=0A+=09=09}=0A+=0A= +=09=09if=20(!segment->has_timestamp)=0A+=09=09{=0A+=09=09=09= memmove(&search_segments[middle],=20&search_segments[middle=20+=201],=0A= +=09=09=09=09=09(*nsearch_segments=20-=20middle=20-=201)=20*=20= sizeof(int));=0A+=09=09=09(*nsearch_segments)--;=0A+=09=09=09right--;=0A= +=09=09=09continue;=0A+=09=09}=0A+=09=09else=20if=20(target_time=20<=20= segment->min_time)=0A+=09=09=09right=20=3D=20middle=20-=201;=0A+=09=09= else=20if=20(target_time=20>=20segment->max_time)=0A+=09=09=09left=20=3D=20= middle=20+=201;=0A+=09=09else=0A+=09=09{=0A+=09=09=09if=20= (candidate_position=20!=3D=20NULL)=0A+=09=09=09=09*candidate_position=20= =3D=20middle;=0A+=09=09=09return=20search_segments[middle];=0A+=09=09}=0A= +=09}=0A+=0A+=09if=20(*nsearch_segments=20=3D=3D=200)=0A+=09=09= ereport(ERROR,=0A+=09=09=09=09errcode(ERRCODE_INVALID_PARAMETER_VALUE),=0A= +=09=09=09=09errmsg("could=20not=20locate=20a=20WAL=20time=20boundary"),=0A= +=09=09=09=09errdetail("No=20retained=20WAL=20segment=20contains=20a=20= timestamped=20record."));=0A+=0A+=09if=20(search_left)=0A+=09=09result=20= =3D=20Max(right,=200);=0A+=09else=0A+=09=09result=20=3D=20Min(left,=20= *nsearch_segments=20-=201);=0A+=0A+=09if=20(candidate_position=20!=3D=20= NULL)=0A+=09=09*candidate_position=20=3D=20result;=0A+=09return=20= search_segments[result];=0A+}=0A+=0A+/*=0A+=20*=20Inspect=20timestamped=20= records=20beginning=20in=20one=20segment=20and=20save=20its=20time=0A+=20= *=20range=20and=20best=20boundary=20candidates.=20=20XLogReadRecord()=20= may=20read=20the=20following=0A+=20*=20segment=20to=20assemble=20a=20= record=20crossing=20the=20boundary,=20but=20that=20record=20is=0A+=20*=20= attributed=20to=20the=20segment=20containing=20its=20starting=20LSN.=0A+=20= */=0A+static=20void=0A+ScanWalTimeSegment(WalTimeSegment=20*segment,=20= int=20segment_index,=0A+=09=09=09=09=20=20=20TimestampTz=20wanted_start,=20= TimestampTz=20wanted_end)=0A+{=0A+=09XLogRecPtr=09seg_start=20=3D=20= segment->segno=20*=20wal_segment_size;=0A+=09XLogRecPtr=09seg_end=20=3D=20= seg_start=20+=20wal_segment_size;=0A+=09XLogReaderState=20*xlogreader;=0A= +=0A+=09Assert(!segment->scanned);=0A+=09segment->scanned=20=3D=20true;=0A= +=09xlogreader=20=3D=20InitXLogReaderState(seg_start);=0A+=0A+=09while=20= (ReadNextXLogRecord(xlogreader))=0A+=09{=0A+=09=09TimestampTz=20= record_time;=0A+=0A+=09=09if=20(xlogreader->ReadRecPtr=20>=3D=20seg_end)=0A= +=09=09=09break;=0A+=0A+=09=09if=20(!GetXLogRecordTimestamp(xlogreader,=20= &record_time))=0A+=09=09=09continue;=0A+=0A+=09=09if=20= (!segment->has_timestamp)=0A+=09=09{=0A+=09=09=09segment->has_timestamp=20= =3D=20true;=0A+=09=09=09segment->min_time=20=3D=20segment->max_time=20=3D=20= record_time;=0A+=09=09}=0A+=09=09else=0A+=09=09{=0A+=09=09=09= segment->min_time=20=3D=20Min(segment->min_time,=20record_time);=0A+=09=09= =09segment->max_time=20=3D=20Max(segment->max_time,=20record_time);=0A+=09= =09}=0A+=0A+=09=09if=20(record_time=20>=3D=20wanted_start)=0A+=09=09=09= ProbeLowerBoundary(&segment->lower,=20record_time,=0A+=09=09=09=09=09=09=09= =20=20=20xlogreader->ReadRecPtr,=20segment_index);=0A+=0A+=09=09if=20= (record_time=20<=3D=20wanted_end)=0A+=09=09=09= ProbeUpperBoundary(&segment->upper,=20record_time,=0A+=09=09=09=09=09=09=09= =20=20=20xlogreader->ReadRecPtr,=20segment_index);=0A+=0A+=09=09= CHECK_FOR_INTERRUPTS();=0A+=09}=0A+=0A+=09= pfree(xlogreader->private_data);=0A+=09XLogReaderFree(xlogreader);=0A+}=0A= +=0A+/*=20Merge=20one=20segment's=20boundary=20candidates=20into=20the=20= result.=20*/=0A+static=20void=0A+MergeWalTimeSegment(WalTimeSegment=20= *segment,=20WalTimeBoundary=20*lower,=0A+=09=09=09=09=09WalTimeBoundary=20= *upper)=0A+{=0A+=09Assert(segment->scanned);=0A+=0A+=09if=20(lower=20!=3D=20= NULL=20&&=20segment->lower.found)=0A+=09=09ProbeLowerBoundary(lower,=20= segment->lower.time,=20segment->lower.lsn,=0A+=09=09=09=09=09=09=20=20=20= segment->lower.segment_index);=0A+=09if=20(upper=20!=3D=20NULL=20&&=20= segment->upper.found)=0A+=09=09ProbeUpperBoundary(upper,=20= segment->upper.time,=20segment->upper.lsn,=0A+=09=09=09=09=09=09=20=20=20= segment->upper.segment_index);=0A+}=0A+=0A=20/*=0A=20=20*=20Output=20= values=20that=20make=20up=20a=20row=20describing=20caller's=20WAL=20= record.=0A=20=20*=0A@@=20-623,6=20+836,215=20@@=20= pg_get_wal_record_info(PG_FUNCTION_ARGS)=0A=20#undef=20= PG_GET_WAL_RECORD_INFO_COLS=0A=20}=0A=20=0A+/*=0A+=20*=20Locate=20a=20= WAL=20region=20around=20a=20wall-clock=20time=20range=20using=20= timestamped=20WAL=0A+=20*=20records.=0A+=20*=0A+=20*=20Locate=20each=20= boundary=20by=20an=20approximate=20binary=20search=20over=20retained=20= WAL=0A+=20*=20segments,=20assuming=20that=20record=20timestamps=20= generally=20increase=20with=20WAL=0A+=20*=20position.=20=20Also=20= examine=20one=20preceding=20segment=20for=20the=20lower=20boundary=20and=0A= +=20*=20one=20following=20segment=20for=20the=20upper=20boundary=20to=20= allow=20for=20limited=20timestamp=0A+=20*=20reordering.=20=20The=20lower=20= boundary=20uses=20the=20earliest=20timestamped=20record=20at=20or=0A+=20= *=20after=20the=20requested=20start=20time,=20while=20the=20upper=20= boundary=20uses=20the=20latest=0A+=20*=20one=20at=20or=20before=20the=20= requested=20end=20time.=20=20A=20future=20upper=20bound=20is=20first=0A+=20= *=20capped=20at=20the=20current=20time.=20=20On=20success,=20the=20= boundaries=20form=20a=20forward=20WAL=0A+=20*=20range,=20but=20are=20not=20= necessarily=20the=20globally=20closest=20timestamped=20records=20to=0A+=20= *=20the=20requested=20bounds.=0A+=20*/=0A+Datum=0A= +pg_get_wal_location_at_time(PG_FUNCTION_ARGS)=0A+{=0A+#define=20= PG_GET_WAL_LOCATION_AT_TIME_COLS=204=0A+=09TimestampTz=20target_time=20=3D= =20PG_GETARG_TIMESTAMPTZ(0);=0A+=09Interval=20=20=20*before=20=3D=20= PG_GETARG_INTERVAL_P(1);=0A+=09Interval=20=20=20*after=20=3D=20= PG_GETARG_INTERVAL_P(2);=0A+=09Interval=09zero=20=3D=20{0};=0A+=09= Interval=09one_day=20=3D=20{.day=20=3D=201};=0A+=09TimestampTz=20= wanted_start;=0A+=09TimestampTz=20wanted_end;=0A+=09TimestampTz=20= current_time;=0A+=09WalTimeSegment=20*segments;=0A+=09WalTimeBoundary=20= lower=20=3D=20{0};=0A+=09WalTimeBoundary=20upper=20=3D=20{0};=0A+=09= Datum=09=09values[PG_GET_WAL_LOCATION_AT_TIME_COLS];=0A+=09bool=09=09= nulls[PG_GET_WAL_LOCATION_AT_TIME_COLS]=20=3D=20{0};=0A+=09TupleDesc=09= tupdesc;=0A+=09HeapTuple=09tuple;=0A+=09int=09=09=09nsegments;=0A+=09int=09= =09=20=20=20*search_segments;=0A+=09int=09=09=09nsearch_segments;=0A+=09= int=09=09=09lower_candidate;=0A+=09int=09=09=09upper_candidate;=0A+=09= int=09=09=09lower_candidate_position;=0A+=09int=09=09=09= nupper_search_segments;=0A+=09int=09=09=09lower_start;=0A+=09int=09=09=09= upper_end;=0A+=09int=09=09=09nscanned=20=3D=200;=0A+=09char=09=09= lower_fname[MAXFNAMELEN];=0A+=09char=09=09upper_fname[MAXFNAMELEN];=0A+=0A= +=09if=20(TIMESTAMP_NOT_FINITE(target_time))=0A+=09=09ereport(ERROR,=0A+=09= =09=09=09errcode(ERRCODE_INVALID_PARAMETER_VALUE),=0A+=09=09=09=09= errmsg("target=20time=20must=20be=20finite"));=0A+=09if=20= (INTERVAL_NOT_FINITE(before)=20||=0A+=09=09= DatumGetInt32(DirectFunctionCall2(interval_cmp,=0A+=09=09=09=09=09=09=09=09= =09=09=20=20IntervalPGetDatum(before),=0A+=09=09=09=09=09=09=09=09=09=09=20= =20IntervalPGetDatum(&zero)))=20<=3D=200)=0A+=09=09ereport(ERROR,=0A+=09=09= =09=09errcode(ERRCODE_INVALID_PARAMETER_VALUE),=0A+=09=09=09=09= errmsg("before=20interval=20must=20be=20finite=20and=20greater=20than=20= zero"));=0A+=09if=20(DatumGetInt32(DirectFunctionCall2(interval_cmp,=0A+=09= =09=09=09=09=09=09=09=09=09=20=20IntervalPGetDatum(before),=0A+=09=09=09=09= =09=09=09=09=09=09=20=20IntervalPGetDatum(&one_day)))=20>=200)=0A+=09=09= ereport(ERROR,=0A+=09=09=09=09errcode(ERRCODE_INVALID_PARAMETER_VALUE),=0A= +=09=09=09=09errmsg("before=20interval=20must=20not=20exceed=20one=20= day"));=0A+=09if=20(INTERVAL_NOT_FINITE(after)=20||=0A+=09=09= DatumGetInt32(DirectFunctionCall2(interval_cmp,=0A+=09=09=09=09=09=09=09=09= =09=09=20=20IntervalPGetDatum(after),=0A+=09=09=09=09=09=09=09=09=09=09=20= =20IntervalPGetDatum(&zero)))=20<=3D=200)=0A+=09=09ereport(ERROR,=0A+=09=09= =09=09errcode(ERRCODE_INVALID_PARAMETER_VALUE),=0A+=09=09=09=09= errmsg("after=20interval=20must=20be=20finite=20and=20greater=20than=20= zero"));=0A+=09if=20(DatumGetInt32(DirectFunctionCall2(interval_cmp,=0A+=09= =09=09=09=09=09=09=09=09=09=20=20IntervalPGetDatum(after),=0A+=09=09=09=09= =09=09=09=09=09=09=20=20IntervalPGetDatum(&one_day)))=20>=200)=0A+=09=09= ereport(ERROR,=0A+=09=09=09=09errcode(ERRCODE_INVALID_PARAMETER_VALUE),=0A= +=09=09=09=09errmsg("after=20interval=20must=20not=20exceed=20one=20= day"));=0A+=0A+=09/*=20The=20timestamp=20functions=20provide=20the=20= normal=20overflow=20checks.=20*/=0A+=09wanted_start=20=3D=20= DatumGetTimestampTz(DirectFunctionCall2(timestamptz_mi_interval,=0A+=09=09= =09=09=09=09=09=09=09=09=09=09=09=09=20=20=20= TimestampTzGetDatum(target_time),=0A+=09=09=09=09=09=09=09=09=09=09=09=09= =09=09=20=20=20IntervalPGetDatum(before)));=0A+=09wanted_end=20=3D=20= DatumGetTimestampTz(DirectFunctionCall2(timestamptz_pl_interval,=0A+=09=09= =09=09=09=09=09=09=09=09=09=09=09=09=20TimestampTzGetDatum(target_time),=0A= +=09=09=09=09=09=09=09=09=09=09=09=09=09=09=20= IntervalPGetDatum(after)));=0A+=09current_time=20=3D=20= GetCurrentTimestamp();=0A+=0A+=09/*=20Cap=20a=20future=20upper=20bound=20= at=20the=20time=20currently=20available.=20*/=0A+=09if=20(wanted_end=20>=20= current_time)=0A+=09{=0A+=09=09if=20(wanted_start=20>=3D=20current_time)=0A= +=09=09=09ereport(ERROR,=0A+=09=09=09=09=09= errcode(ERRCODE_INVALID_PARAMETER_VALUE),=0A+=09=09=09=09=09= errmsg("requested=20time=20follows=20the=20available=20WAL=20range"),=0A= +=09=09=09=09=09errdetail("The=20requested=20lower=20bound=20is=20not=20= earlier=20than=20the=20current=20time."));=0A+=0A+=09=09wanted_end=20=3D=20= current_time;=0A+=09}=0A+=0A+=09if=20(get_call_result_type(fcinfo,=20= NULL,=20&tupdesc)=20!=3D=20TYPEFUNC_COMPOSITE)=0A+=09=09elog(ERROR,=20= "return=20type=20must=20be=20a=20row=20type");=0A+=0A+=09segments=20=3D=20= GetWalTimeSegments(&nsegments);=0A+=09search_segments=20=3D=20= palloc_array(int,=20nsegments);=0A+=09for=20(int=20i=20=3D=200;=20i=20<=20= nsegments;=20i++)=0A+=09=09search_segments[i]=20=3D=20i;=0A+=09= nsearch_segments=20=3D=20nsegments;=0A+=09lower_candidate=20=3D=20= FindWalTimeCandidate(segments,=20search_segments,=0A+=09=09=09=09=09=09=09= =09=09=09=20=20=20&nsearch_segments,=20wanted_start,=0A+=09=09=09=09=09=09= =09=09=09=09=20=20=20false,=20wanted_start,=20wanted_end,=0A+=09=09=09=09= =09=09=09=09=09=09=20=20=20&lower_candidate_position,=0A+=09=09=09=09=09=09= =09=09=09=09=20=20=20&nscanned);=0A+=0A+=09/*=20Search=20for=20the=20= later=20upper=20boundary=20from=20the=20lower=20candidate=20onward.=20*/=0A= +=09nupper_search_segments=20=3D=20nsearch_segments=20-=20= lower_candidate_position;=0A+=09upper_candidate=20=3D=20= FindWalTimeCandidate(segments,=0A+=09=09=09=09=09=09=09=09=09=09=20=20=20= search_segments=20+=20lower_candidate_position,=0A+=09=09=09=09=09=09=09=09= =09=09=20=20=20&nupper_search_segments,=20wanted_end,=0A+=09=09=09=09=09=09= =09=09=09=09=20=20=20true,=20wanted_start,=20wanted_end,=0A+=09=09=09=09=09= =09=09=09=09=09=20=20=20NULL,=0A+=09=09=09=09=09=09=09=09=09=09=20=20=20= &nscanned);=0A+=09XLogFileName(lower_fname,=20= segments[lower_candidate].tli,=0A+=09=09=09=09=20= segments[lower_candidate].segno,=20wal_segment_size);=0A+=09= XLogFileName(upper_fname,=20segments[upper_candidate].tli,=0A+=09=09=09=09= =20segments[upper_candidate].segno,=20wal_segment_size);=0A+=09= ereport(DEBUG1,=0A+=09=09=09errmsg_internal("WAL=20time=20search=20= selected=20lower=20segment=20%s=20and=20upper=20segment=20%s=20from=20%d=20= retained=20segments",=0A+=09=09=09=09=09=09=09lower_fname,=20= upper_fname,=0A+=09=09=09=09=09=09=09nsegments));=0A+=0A+=09/*=0A+=09=20= *=20Allow=20for=20limited=20timestamp=20reordering=20by=20examining=20= one=20preceding=0A+=09=20*=20segment=20for=20the=20lower=20boundary=20= and=20one=20following=20segment=20for=20the=20upper=0A+=09=20*=20= boundary.=20=20Do=20not=20extend=20across=20a=20missing=20segment.=0A+=09= =20*/=0A+=09lower_start=20=3D=20lower_candidate;=0A+=09if=20(lower_start=20= >=200=20&&=0A+=09=09segments[lower_start=20-=201].segno=20+=201=20=3D=3D=20= segments[lower_start].segno)=0A+=09=09lower_start--;=0A+=09upper_end=20=3D= =20upper_candidate;=0A+=09if=20(upper_end=20+=201=20<=20nsegments=20&&=0A= +=09=09segments[upper_end].segno=20+=201=20=3D=3D=20segments[upper_end=20= +=201].segno)=0A+=09=09upper_end++;=0A+=0A+=09for=20(int=20i=20=3D=20= lower_start;=20i=20<=3D=20lower_candidate;=20i++)=0A+=09{=0A+=09=09if=20= (!segments[i].scanned)=0A+=09=09{=0A+=09=09=09= ScanWalTimeSegment(&segments[i],=20i,=20wanted_start,=20wanted_end);=0A+=09= =09=09nscanned++;=0A+=09=09}=0A+=09=09MergeWalTimeSegment(&segments[i],=20= &lower,=20NULL);=0A+=09}=0A+=09for=20(int=20i=20=3D=20upper_candidate;=20= i=20<=3D=20upper_end;=20i++)=0A+=09{=0A+=09=09if=20= (!segments[i].scanned)=0A+=09=09{=0A+=09=09=09= ScanWalTimeSegment(&segments[i],=20i,=20wanted_start,=20wanted_end);=0A+=09= =09=09nscanned++;=0A+=09=09}=0A+=09=09MergeWalTimeSegment(&segments[i],=20= NULL,=20&upper);=0A+=09}=0A+=0A+=09ereport(DEBUG1,=0A+=09=09=09= errmsg_internal("WAL=20time=20search=20decoded=20%d=20of=20%d=20retained=20= segments",=0A+=09=09=09=09=09=09=09nscanned,=20nsegments));=0A+=0A+=09if=20= (!lower.found)=0A+=09=09ereport(ERROR,=0A+=09=09=09=09= errcode(ERRCODE_INVALID_PARAMETER_VALUE),=0A+=09=09=09=09errmsg("could=20= not=20locate=20a=20WAL=20time=20boundary"),=0A+=09=09=09=09= errdetail("The=20WAL=20search=20did=20not=20find=20a=20timestamped=20= record=20at=20or=20after=20the=20requested=20lower=20bound."));=0A+=09if=20= (!upper.found)=0A+=09=09ereport(ERROR,=0A+=09=09=09=09= errcode(ERRCODE_INVALID_PARAMETER_VALUE),=0A+=09=09=09=09errmsg("could=20= not=20locate=20a=20WAL=20time=20boundary"),=0A+=09=09=09=09= errdetail("The=20WAL=20search=20did=20not=20find=20a=20timestamped=20= record=20at=20or=20before=20the=20requested=20upper=20bound."));=0A+=0A+=09= if=20(lower.lsn=20>=20upper.lsn)=0A+=09=09ereport(ERROR,=0A+=09=09=09=09= errcode(ERRCODE_DATA_EXCEPTION),=0A+=09=09=09=09errmsg("could=20not=20= find=20a=20valid=20WAL=20range=20for=20the=20requested=20time=20= window"),=0A+=09=09=09=09errdetail("The=20matching=20lower=20boundary=20= follows=20the=20upper=20boundary=20in=20WAL."));=0A+=0A+=09= Assert(lower.segment_index=20>=3D=200=20&&=20lower.segment_index=20<=20= nsegments);=0A+=09Assert(upper.segment_index=20>=3D=200=20&&=20= upper.segment_index=20<=20nsegments);=0A+=09for=20(int=20i=20=3D=20= lower.segment_index=20+=201;=20i=20<=3D=20upper.segment_index;=20i++)=0A= +=09{=0A+=09=09if=20(segments[i=20-=201].segno=20+=201=20!=3D=20= segments[i].segno)=0A+=09=09=09ereport(ERROR,=0A+=09=09=09=09=09= errcode(ERRCODE_DATA_EXCEPTION),=0A+=09=09=09=09=09errmsg("WAL=20segment=20= needed=20for=20the=20time=20search=20is=20missing"),=0A+=09=09=09=09=09= errdetail("The=20start=20LSN=20is=20%X/%08X,=20the=20end=20LSN=20is=20= %X/%08X,=20and=20segment=20"=20UINT64_FORMAT=20"=20is=20not=20present=20= in=20pg_wal.",=0A+=09=09=09=09=09=09=09=20=20LSN_FORMAT_ARGS(lower.lsn),=0A= +=09=09=09=09=09=09=09=20=20LSN_FORMAT_ARGS(upper.lsn),=0A+=09=09=09=09=09= =09=09=20=20(uint64)=20(segments[i=20-=201].segno=20+=201)));=0A+=09}=0A= +=0A+=09values[0]=20=3D=20TimestampTzGetDatum(lower.time);=0A+=09= values[1]=20=3D=20LSNGetDatum(lower.lsn);=0A+=09values[2]=20=3D=20= TimestampTzGetDatum(upper.time);=0A+=09values[3]=20=3D=20= LSNGetDatum(upper.lsn);=0A+=0A+=09pfree(segments);=0A+=09tuple=20=3D=20= heap_form_tuple(tupdesc,=20values,=20nulls);=0A+=09= PG_RETURN_DATUM(HeapTupleGetDatum(tuple));=0A+#undef=20= PG_GET_WAL_LOCATION_AT_TIME_COLS=0A+}=0A+=0A=20/*=0A=20=20*=20List=20the=20= retained=20WAL=20segment=20files=20intersecting=20[start_lsn,=20= end_lsn),=20or=0A=20=20*=20the=20segment=20containing=20start_lsn=20when=20= end_lsn=20is=20omitted=20or=20equal=20to=20it.=0Adiff=20--git=20= a/contrib/pg_walinspect/sql/pg_walinspect.sql=20= b/contrib/pg_walinspect/sql/pg_walinspect.sql=0Aindex=20= 3176a17d855..dbc3240ae30=20100644=0A---=20= a/contrib/pg_walinspect/sql/pg_walinspect.sql=0A+++=20= b/contrib/pg_walinspect/sql/pg_walinspect.sql=0A@@=20-75,6=20+75,114=20= @@=20SELECT=20count(*)=20=3D=201=20AS=20future_end_ok=0A=20FROM=20= pg_get_wal_files(pg_current_wal_lsn(),=20'FFFFFFFF/FFFFFFFF');=0A=20= SELECT=20*=20FROM=20pg_get_wal_files('0/0',=20'0/1');=0A=20=0A+--=20= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=0A+--=20Tests=20for=20= locating=20WAL=20by=20timestamps=20stored=20in=20WAL=20records=0A+--=20= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=0A+=0A+--=20Put=20= two=20WAL-logged=20transaction=20commits=20between=20the=20requested=20= boundaries.=0A+INSERT=20INTO=20sample_tbl=20VALUES=20(5,=205);=0A+SELECT=20= clock_timestamp()=20AS=20wal_time_lower=20\gset=0A+INSERT=20INTO=20= sample_tbl=20VALUES=20(6,=206);=0A+INSERT=20INTO=20sample_tbl=20VALUES=20= (10,=2010);=0A+SELECT=20clock_timestamp()=20AS=20wal_time_upper=20\gset=0A= +=0A+SELECT=20start_timestamp=20>=3D=20:'wal_time_lower'::timestamptz=0A= +=20=20=20=20=20=20=20=20=20AS=20start_ok,=0A+=20=20=20=20=20=20=20= end_timestamp=20>=20:'wal_time_lower'::timestamptz=20AND=0A+=20=20=20=20=20= =20=20=20=20end_timestamp=20<=3D=20:'wal_time_upper'::timestamptz=20AS=20= end_ok,=0A+=20=20=20=20=20=20=20start_lsn=20<=20end_lsn=20AS=20lsn_ok=0A= +FROM=20pg_get_wal_location_at_time(:'wal_time_lower',=0A+=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20before=20=3D>=20interval=20'1=20microsecond',=0A+=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20after=20=3D>=20:'wal_time_upper'::timestamptz=20-=0A+=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20:'wal_time_lower'::timestamptz);=0A= +=0A+--=20A=20single=20matching=20timestamped=20record=20is=20returned=20= for=20both=20boundaries.=0A+SELECT=20clock_timestamp()=20AS=20= single_time_lower=20\gset=0A+INSERT=20INTO=20sample_tbl=20VALUES=20(12,=20= 12);=0A+SELECT=20clock_timestamp()=20AS=20single_time_upper=20\gset=0A= +SELECT=20location.start_timestamp=20=3D=20location.end_timestamp=20AS=20= timestamp_ok,=0A+=20=20=20=20=20=20=20location.start_lsn=20=3D=20= location.end_lsn=20AS=20lsn_ok,=0A+=20=20=20=20=20=20=20info.record_type=20= =3D=20'COMMIT'=20AS=20record_ok=0A+FROM=20pg_get_wal_location_at_time(=0A= +=20=20=20=20=20=20=20:'single_time_lower'::timestamptz=20+=20interval=20= '1=20microsecond',=0A+=20=20=20=20=20=20=20interval=20'1=20microsecond',=0A= +=20=20=20=20=20=20=20:'single_time_upper'::timestamptz=20-=0A+=20=20=20=20= =20=20=20=20=20:'single_time_lower'::timestamptz=20-=20interval=20'1=20= microsecond')=20AS=20location,=0A+=20=20=20=20=20LATERAL=20= pg_get_wal_record_info(location.start_lsn)=20AS=20info;=0A+=0A+--=20= Exercise=20a=20time=20window=20containing=20a=20COMMIT=20PREPARED=20= record.=20=20Concurrent=0A+--=20activity=20can=20cause=20another=20= timestamped=20record=20to=20be=20selected=20as=20the=20anchor.=0A+INSERT=20= INTO=20sample_tbl=20VALUES=20(11,=2011);=0A+BEGIN;=0A+INSERT=20INTO=20= sample_tbl=20VALUES=20(7,=207);=0A+PREPARE=20TRANSACTION=20= 'regress_pg_walinspect_time';=0A+SELECT=20clock_timestamp()=20AS=20= prepared_time_target=20\gset=0A+COMMIT=20PREPARED=20= 'regress_pg_walinspect_time';=0A+SELECT=20clock_timestamp()=20AS=20= prepared_time_upper=20\gset=0A+=0A+SELECT=20record_type=20IN=20= ('COMMIT',=20'COMMIT_PREPARED',=20'ABORT',=0A+=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20'ABORT_PREPARED',=20= 'RESTORE_POINT')=20AS=20timestamped_ok=0A+FROM=20= pg_get_wal_location_at_time(:'prepared_time_target',=0A+=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20:'prepared_time_target'::timestamptz=20-=0A+=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20:'wal_time_upper'::timestamptz,=0A+=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= :'prepared_time_upper'::timestamptz=20-=0A+=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= :'prepared_time_target'::timestamptz)=20AS=20location,=0A+=20=20=20=20=20= LATERAL=20pg_get_wal_record_info(location.end_lsn);=0A+=0A+--=20Exercise=20= a=20time=20window=20containing=20restore=20points,=20allowing=20for=20= other=0A+--=20timestamped=20WAL=20records=20generated=20concurrently.=0A= +SELECT=20pg_create_restore_point('regress_wal_time_lower')=20AS=20= restore_lsn=20\gset=0A+SELECT=20clock_timestamp()=20AS=20= restore_time_target=20\gset=0A+SELECT=20= pg_create_restore_point('regress_wal_time_upper')=20AS=20restore_lsn=20= \gset=0A+SELECT=20clock_timestamp()=20AS=20restore_time_upper=20\gset=0A= +INSERT=20INTO=20sample_tbl=20VALUES=20(8,=208);=0A+=0A+SELECT=20= start_info.record_type=20IN=20('COMMIT',=20'COMMIT_PREPARED',=20'ABORT',=0A= +=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20'ABORT_PREPARED',=20'RESTORE_POINT')=20AS=20= start_ok,=0A+=20=20=20=20=20=20=20end_info.record_type=20IN=20('COMMIT',=20= 'COMMIT_PREPARED',=20'ABORT',=0A+=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20'ABORT_PREPARED',=20= 'RESTORE_POINT')=20AS=20end_ok=0A+FROM=20= pg_get_wal_location_at_time(:'restore_time_target',=0A+=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20:'restore_time_target'::timestamptz=20-=0A+=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20:'wal_time_upper'::timestamptz,=0A+=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= :'restore_time_upper'::timestamptz=20-=0A+=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= :'restore_time_target'::timestamptz)=20AS=20location,=0A+=20=20=20=20=20= LATERAL=20pg_get_wal_record_info(location.start_lsn)=20AS=20start_info,=0A= +=20=20=20=20=20LATERAL=20pg_get_wal_record_info(location.end_lsn)=20AS=20= end_info;=0A+=0A+--=20A=20future=20upper=20bound=20uses=20a=20= timestamped=20WAL=20record=20at=20or=20before=20now.=0A+SELECT=20= clock_timestamp()=20AS=20current_time_target=20\gset=0A+INSERT=20INTO=20= sample_tbl=20VALUES=20(9,=209);=0A+SELECT=20location.start_timestamp=20= >=3D=20:'restore_time_upper'::timestamptz=20AND=0A+=20=20=20=20=20=20=20=20= =20location.start_timestamp=20<=3D=20:'current_time_target'::timestamptz=0A= +=20=20=20=20=20=20=20=20=20AS=20start_ok,=0A+=20=20=20=20=20=20=20= location.end_timestamp=20>=3D=20:'current_time_target'::timestamptz=20= AND=0A+=20=20=20=20=20=20=20=20=20location.end_timestamp=20<=3D=20= clock_timestamp()=20AS=20capped_end_ok,=0A+=20=20=20=20=20=20=20= location.start_lsn=20<=20location.end_lsn=20AS=20lsn_ok,=0A+=20=20=20=20=20= =20=20end_info.record_type=20=3D=20'COMMIT'=20AS=20real_record_ok=0A= +FROM=20pg_get_wal_location_at_time(:'current_time_target',=0A+=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20:'current_time_target'::timestamptz=20-=0A+=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20:'restore_time_upper'::timestamptz,=0A+=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= interval=20'1=20day')=20AS=20location,=0A+=20=20=20=20=20LATERAL=20= pg_get_wal_record_info(location.end_lsn)=20AS=20end_info;=0A+=0A+SELECT=20= pg_get_function_arguments(=0A+=20=20= 'pg_get_wal_location_at_time(timestamptz,=20interval,=20= interval)'::regprocedure)=0A+=20=20LIKE=20'%before=20interval=20DEFAULT=20= ''@=201=20min''::interval,=20after=20interval=20DEFAULT=20''@=201=20= min''::interval%'=0A+=20=20AS=20defaults_ok;=0A+SELECT=20*=20FROM=20= pg_get_wal_location_at_time(:'wal_time_lower',=0A+=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20interval=20'0',=20interval=20'1=20second');=0A= +SELECT=20*=20FROM=20pg_get_wal_location_at_time(:'wal_time_lower',=0A+=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20interval=20'1=20second',=20= interval=20'0');=0A+SELECT=20*=20FROM=20= pg_get_wal_location_at_time(:'wal_time_lower',=20interval=20'-1=20= second');=0A+SELECT=20*=20FROM=20= pg_get_wal_location_at_time(:'wal_time_lower',=0A+=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20after=20=3D>=20interval=20'-1=20second');=0A= +SELECT=20*=20FROM=20pg_get_wal_location_at_time(:'wal_time_lower',=0A+=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20before=20=3D>=20interval=20= '1=20day=201=20microsecond');=0A+SELECT=20*=20FROM=20= pg_get_wal_location_at_time(:'wal_time_lower',=0A+=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20after=20=3D>=20interval=20'1=20day=201=20= microsecond');=0A+SELECT=20*=20FROM=20= pg_get_wal_location_at_time(clock_timestamp()=20-=20interval=20'100=20= years');=0A+SELECT=20*=20FROM=20= pg_get_wal_location_at_time(clock_timestamp()=20+=20interval=20'100=20= years');=0A+=0A=20--=20= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=0A=20--=20Test=20for=20= filtering=20out=20WAL=20records=20of=20a=20particular=20table=0A=20--=20= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=0A@@=20-134,6=20= +242,8=20@@=20SELECT=20has_function_privilege('regress_pg_walinspect',=0A= =20=20=20'pg_get_wal_stats(pg_lsn,=20pg_lsn,=20boolean)=20',=20= 'EXECUTE');=20--=20no=0A=20SELECT=20= has_function_privilege('regress_pg_walinspect',=0A=20=20=20= 'pg_get_wal_block_info(pg_lsn,=20pg_lsn,=20boolean)=20',=20'EXECUTE');=20= --=20no=0A+SELECT=20has_function_privilege('regress_pg_walinspect',=0A+=20= =20'pg_get_wal_location_at_time(timestamptz,=20interval,=20interval)',=20= 'EXECUTE');=20--=20no=0A=20SELECT=20= has_function_privilege('regress_pg_walinspect',=0A=20=20=20= 'pg_get_wal_files(pg_lsn,=20pg_lsn)',=20'EXECUTE');=20--=20no=0A=20=0A@@=20= -148,6=20+258,8=20@@=20SELECT=20= has_function_privilege('regress_pg_walinspect',=0A=20=20=20= 'pg_get_wal_stats(pg_lsn,=20pg_lsn,=20boolean)=20',=20'EXECUTE');=20--=20= yes=0A=20SELECT=20has_function_privilege('regress_pg_walinspect',=0A=20=20= =20'pg_get_wal_block_info(pg_lsn,=20pg_lsn,=20boolean)=20',=20= 'EXECUTE');=20--=20yes=0A+SELECT=20= has_function_privilege('regress_pg_walinspect',=0A+=20=20= 'pg_get_wal_location_at_time(timestamptz,=20interval,=20interval)',=20= 'EXECUTE');=20--=20yes=0A=20SELECT=20= has_function_privilege('regress_pg_walinspect',=0A=20=20=20= 'pg_get_wal_files(pg_lsn,=20pg_lsn)',=20'EXECUTE');=20--=20yes=0A=20=0A= @@=20-162,6=20+274,8=20@@=20GRANT=20EXECUTE=20ON=20FUNCTION=20= pg_get_wal_stats(pg_lsn,=20pg_lsn,=20boolean)=0A=20=20=20TO=20= regress_pg_walinspect;=0A=20GRANT=20EXECUTE=20ON=20FUNCTION=20= pg_get_wal_block_info(pg_lsn,=20pg_lsn,=20boolean)=0A=20=20=20TO=20= regress_pg_walinspect;=0A+GRANT=20EXECUTE=20ON=20FUNCTION=20= pg_get_wal_location_at_time(timestamptz,=20interval,=20interval)=0A+=20=20= TO=20regress_pg_walinspect;=0A=20GRANT=20EXECUTE=20ON=20FUNCTION=20= pg_get_wal_files(pg_lsn,=20pg_lsn)=0A=20=20=20TO=20= regress_pg_walinspect;=0A=20=0A@@=20-173,6=20+287,8=20@@=20SELECT=20= has_function_privilege('regress_pg_walinspect',=0A=20=20=20= 'pg_get_wal_stats(pg_lsn,=20pg_lsn,=20boolean)=20',=20'EXECUTE');=20--=20= yes=0A=20SELECT=20has_function_privilege('regress_pg_walinspect',=0A=20=20= =20'pg_get_wal_block_info(pg_lsn,=20pg_lsn,=20boolean)=20',=20= 'EXECUTE');=20--=20yes=0A+SELECT=20= has_function_privilege('regress_pg_walinspect',=0A+=20=20= 'pg_get_wal_location_at_time(timestamptz,=20interval,=20interval)',=20= 'EXECUTE');=20--=20yes=0A=20SELECT=20= has_function_privilege('regress_pg_walinspect',=0A=20=20=20= 'pg_get_wal_files(pg_lsn,=20pg_lsn)',=20'EXECUTE');=20--=20yes=0A=20=0A= @@=20-184,6=20+300,8=20@@=20REVOKE=20EXECUTE=20ON=20FUNCTION=20= pg_get_wal_stats(pg_lsn,=20pg_lsn,=20boolean)=0A=20=20=20FROM=20= regress_pg_walinspect;=0A=20REVOKE=20EXECUTE=20ON=20FUNCTION=20= pg_get_wal_block_info(pg_lsn,=20pg_lsn,=20boolean)=0A=20=20=20FROM=20= regress_pg_walinspect;=0A+REVOKE=20EXECUTE=20ON=20FUNCTION=20= pg_get_wal_location_at_time(timestamptz,=20interval,=20interval)=0A+=20=20= FROM=20regress_pg_walinspect;=0A=20REVOKE=20EXECUTE=20ON=20FUNCTION=20= pg_get_wal_files(pg_lsn,=20pg_lsn)=0A=20=20=20FROM=20= regress_pg_walinspect;=0A=20=0Adiff=20--git=20= a/contrib/pg_walinspect/t/002_time_search.pl=20= b/contrib/pg_walinspect/t/002_time_search.pl=0Anew=20file=20mode=20= 100644=0Aindex=2000000000000..85ed7fb7bf4=0A---=20/dev/null=0A+++=20= b/contrib/pg_walinspect/t/002_time_search.pl=0A@@=20-0,0=20+1,51=20@@=0A= +#=20Copyright=20(c)=202026,=20PostgreSQL=20Global=20Development=20Group=0A= +=0A+#=20Test=20the=20bounded=20binary=20search=20for=20WAL=20time=20= boundaries=20across=20segments.=0A+use=20strict;=0A+use=20warnings=20= FATAL=20=3D>=20'all';=0A+=0A+use=20PostgreSQL::Test::Cluster;=0A+use=20= PostgreSQL::Test::Utils;=0A+use=20Test::More;=0A+=0A+my=20$node=20=3D=20= PostgreSQL::Test::Cluster->new('time_search');=0A+$node->init(extra=20=3D>= =20['--wal-segsize=3D1']);=0A+$node->append_conf('postgresql.conf',=20= "autovacuum=20=3D=20off\n");=0A+$node->start;=0A= +$node->safe_psql('postgres',=0A+=09'CREATE=20EXTENSION=20pg_walinspect;=20= CREATE=20TABLE=20test_table=20(a=20int);');=0A= +$node->safe_psql('postgres',=20'SELECT=20pg_switch_wal()');=0A+=0A+my=20= ($lower_time,=20$upper_time);=0A+for=20my=20$i=20(0=20..=208)=0A+{=0A+=09= $node->safe_psql('postgres',=20"INSERT=20INTO=20test_table=20VALUES=20= ($i)");=0A+=09$lower_time=20=3D=20$node->safe_psql('postgres',=20'SELECT=20= clock_timestamp()')=0A+=09=20=20if=20$i=20=3D=3D=203;=0A+=09$upper_time=20= =3D=20$node->safe_psql('postgres',=20'SELECT=20clock_timestamp()')=0A+=09= =20=20if=20$i=20=3D=3D=205;=0A+=09$node->safe_psql('postgres',=20'SELECT=20= pg_switch_wal()')=20if=20$i=20<=208;=0A+}=0A+=0A+my=20$log_offset=20=3D=20= -s=20$node->logfile;=0A+my=20$result=20=3D=20$node->safe_psql(=0A+=09= 'postgres',=20qq{=0A+SET=20log_min_messages=20=3D=20debug1;=0A+SELECT=20= start_timestamp=20>=3D=20'$lower_time'::timestamptz=20-=20interval=20'1=20= microsecond',=0A+=20=20=20=20=20=20=20end_timestamp=20>=20= '$lower_time'::timestamptz,=0A+=20=20=20=20=20=20=20end_timestamp=20<=3D=20= '$upper_time'::timestamptz,=0A+=20=20=20=20=20=20=20start_lsn=20<=20= end_lsn,=0A+=20=20=20=20=20=20=20pg_walfile_name(start_lsn)=20<>=20= pg_walfile_name(end_lsn)=0A+FROM=20= pg_get_wal_location_at_time('$lower_time',=0A+=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= interval=20'1=20microsecond',=0A+=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= '$upper_time'::timestamptz=20-=0A+=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= '$lower_time'::timestamptz)});=0A+is($result,=20't|t|t|t|t',=20'binary=20= search=20finds=20boundaries=20across=20WAL=20segments');=0A+=0A+my=20= $log=20=3D=20slurp_file($node->logfile,=20$log_offset);=0A+$log=20=3D~=20= /WAL=20time=20search=20decoded=20(\d+)=20of=20(\d+)=20retained=20= segments/=0A+=20=20or=20die=20"could=20not=20find=20WAL=20time=20search=20= statistics=20in=20server=20log";=0A+cmp_ok($1,=20'<',=20$2,=20'binary=20= search=20does=20not=20decode=20all=20retained=20WAL');=0A+=0A= +$node->stop;=0A+done_testing();=0Adiff=20--git=20= a/doc/src/sgml/pgwalinspect.sgml=20b/doc/src/sgml/pgwalinspect.sgml=0A= index=20b4ecae8cfb1..e00beb7d657=20100644=0A---=20= a/doc/src/sgml/pgwalinspect.sgml=0A+++=20= b/doc/src/sgml/pgwalinspect.sgml=0A@@=20-88,6=20+88,113=20@@=0A=20=20=20=20= =20=0A=20=20=20=20=0A=20=0A+=20=20=20= =0A= +=20=20=20=20=0A+=20=20=20=20=20= pg_get_wal_location_at_time(target_time=20timestamptz,=20= before=20interval=20DEFAULT=20'1=20minute',=20after=20interval=20DEFAULT=20= '1=20minute')=20returns=20record=0A+=20=20=20=20=0A+=0A= +=20=20=20=20=0A+=20=20=20=20=20=0A+=20=20=20=20=20=20= Locates=20a=20nearby=20WAL=20region=20using=20records=20containing=20= local=20event=0A+=20=20=20=20=20=20timestamps=20as=20historical=20time=20= anchors.=20=20The=20requested=20time=20range=20extends=0A+=20=20=20=20=20= =20from=20target_time=20minus=0A+=20=20=20=20=20= =20before=20through=0A+=20=20=20=20=20=20= target_time=20plus=0A+=20=20=20=20=20=20= after.=20=20It=20returns=0A+=20=20=20=20=20=20= start_timestamp,=0A+=20=20=20=20=20=20= start_lsn,=0A+=20=20=20=20=20=20= end_timestamp,=20and=0A+=20=20=20=20=20=20= end_lsn.=20=20On=20successful=20return,=20the=20= following=0A+=20=20=20=20=20=20relationships=20hold:=0A+=0A= +start_timestamp=20>=3D=20target_time=20-=20before=0A+end_timestamp=20= =20=20<=3D=20target_time=20+=20after=0A+start_lsn=20=20=20=20=20=20=20= <=3D=20end_lsn=0A+=0A+=20=20=20=20=20=20The=20start=20= boundary=20is=20the=20earliest=20timestamped=20record=20found=20at=20or=20= after=0A+=20=20=20=20=20=20the=20requested=20start=20time,=20and=20the=20= end=20boundary=20is=20the=20latest=20timestamped=0A+=20=20=20=20=20=20= record=20found=20at=20or=20before=20the=20requested=20end=20time.=20=20= If=0A+=20=20=20=20=20=20target_time=20plus=0A= +=20=20=20=20=20=20after=20is=20later=20than=20= the=20current=20time,=20the=0A+=20=20=20=20=20=20requested=20upper=20= bound=20is=20first=20capped=20at=20the=20current=20time.=20=20The=20= function=0A+=20=20=20=20=20=20does=20not=20construct=20an=20end=20= boundary=20from=20the=20current=20WAL=20position.=20=20Thus,=0A+=20=20=20= =20=20=20both=20returned=20boundaries=20come=20from=20actual=20= timestamped=20WAL=20records.=20=20If=0A+=20=20=20=20=20=20only=20one=20= timestamped=20record=20matches=20the=20requested=20time=20range,=20both=0A= +=20=20=20=20=20=20boundaries=20identify=20that=20record=20and=20the=20= returned=20timestamps=20and=20LSNs=0A+=20=20=20=20=20=20are=20equal.=20=20= Both=20interval=20arguments=20must=20be=20greater=20than=20zero=20and=20= not=0A+=20=20=20=20=20=20greater=20than=20one=20day.=0A+=20=20=20=20=20= =0A+=20=20=20=20=20=0A+=20=20=20=20=20=20Most=20WAL=20= records=20do=20not=20contain=20a=20wall-clock=20timestamp.=20=20= Historical=0A+=20=20=20=20=20=20anchors=20can=20be=20transaction=20= COMMIT,=0A+=20=20=20=20=20=20COMMIT=20= PREPARED,=20ABORT,=0A+=20=20=20=20=20=20or=20= ABORT=20PREPARED=20records,=20as=20well=20as=20= restore=20point=0A+=20=20=20=20=20=20records.=20=20These=20are=20the=20= WAL=20record=20types=20from=20which=20PostgreSQL=20recovery=0A+=20=20=20=20= =20=20can=20extract=20timestamps.=20=20Their=20timestamps=20describe=20= the=20corresponding=0A+=20=20=20=20=20=20event;=20they=20do=20not=20= assign=20timestamps=20to=20heap,=20index,=20or=20other=20records.=0A+=20=20= =20=20=20=0A+=20=20=20=20=20=0A+=20=20=20=20=20=20In=20= particular,=20a=20commit=20timestamp=20describes=20when=20a=20= transaction=0A+=20=20=20=20=20=20committed,=20not=20when=20its=20earlier=20= changes=20generated=20WAL.=20=20For=20example,=20a=0A+=20=20=20=20=20=20= transaction=20might=20update=20a=20row=20at=2014:00=20and=20commit=20at=20= 15:00;=20the=20heap=0A+=20=20=20=20=20=20WAL=20was=20generated=20around=20= 14:00,=20while=20the=20commit=20record=20is=20timestamped=0A+=20=20=20=20= =20=2015:00.=20=20The=20returned=20LSNs=20therefore=20do=20not=20= identify=20the=20exact=20generation=0A+=20=20=20=20=20=20time=20of=20= heap,=20index,=20or=20other=20WAL=20records,=20nor=20do=20they=20= guarantee=20that=0A+=20=20=20=20=20=20every=20record=20generated=20= during=20the=20requested=20wall-clock=20range=20lies=0A+=20=20=20=20=20=20= between=20the=20returned=20LSNs.=20=20On=20a=20cluster=20with=20little=20= timestamped=0A+=20=20=20=20=20=20activity,=20the=20returned=20timestamps=20= can=20be=20far=20from=20the=20requested=20bounds.=0A+=20=20=20=20=20= =0A+=20=20=20=20=20=0A+=20=20=20=20=20=20The=20function=20= assumes=20that=20WAL=20record=20timestamps=20generally=20increase=20with=0A= +=20=20=20=20=20=20WAL=20position=20and=20uses=20an=20approximate=20= binary=20search=20to=20locate=20each=20time=0A+=20=20=20=20=20=20= boundary.=20=20WAL=20record=20timestamps=20are=20not=20guaranteed=20to=20= follow=20WAL=20order,=0A+=20=20=20=20=20=20for=20example=20because=20of=20= concurrent=20activity,=20clock=20adjustments,=20or=0A+=20=20=20=20=20=20= delayed=20WAL=20insertion.=20=20Such=20timestamp=20reordering=20can=20= cause=20relevant=0A+=20=20=20=20=20=20records=20to=20fall=20outside=20= the=20returned=20range.=20=20Therefore,=20the=20returned=0A+=20=20=20=20=20= =20records=20are=20not=20guaranteed=20to=20be=20the=20globally=20closest=20= timestamped=0A+=20=20=20=20=20=20records=20to=20either=20requested=20= time=20bound.=20=20If=20the=20returned=20range=20does=20not=0A+=20=20=20=20= =20=20contain=20the=20records=20of=20interest,=20the=20user=20can=20= widen=20the=20search=20range=20and=0A+=20=20=20=20=20=20repeat=20the=20= search.=20=20If=20the=20matching=20lower=20boundary=20follows=20the=20= upper=0A+=20=20=20=20=20=20boundary=20in=20WAL,=20the=20function=20= reports=20an=20error=20rather=20than=20returning=20a=0A+=20=20=20=20=20=20= range=20with=20the=20LSNs=20reversed.=0A+=20=20=20=20=20=0A+=20=20= =20=20=20=0A+=20=20=20=20=20=20Only=20WAL=20that=20is=20currently=20= retained=20in=20pg_wal=20can=0A+=20=20=20=20=20=20= be=20searched.=20=20Unlike=20the=20other=20= pg_walinspect=0A+=20=20=20=20=20=20functions,=20= this=20function=20may=20inspect=20retained=20WAL=20from=20the=20current=0A= +=20=20=20=20=20=20timeline's=20history=20as=20well=20as=20the=20current=20= timeline.=20=20The=20function=0A+=20=20=20=20=20=20reports=20an=20error=20= when=20either=20boundary=20cannot=20be=20found=20by=20the=20bounded=0A+=20= =20=20=20=20=20search=20or=20when=20the=20resulting=20range=20would=20= cross=20a=20missing=20WAL=20segment.=0A+=20=20=20=20=20=0A+=20=20=20= =20=20=0A+=20=20=20=20=20=20For=20example,=20a=20DBA=20can=20= locate=20WAL=20around=20activity=20near=20a=20given=20time=20and=0A+=20=20= =20=20=20=20then=20inspect=20the=20returned=20range:=0A+=0A= +SELECT=20*=0A+FROM=20pg_get_wal_location_at_time(=0A+=20=20=20=20= '2026-08-01=2014:35:00+08',=0A+=20=20=20=20before=20=3D>=20'10=20= minutes',=0A+=20=20=20=20after=20=20=3D>=20'10=20minutes');=0A+=0A= +SELECT=20*=0A+FROM=20= pg_get_wal_files('start_lsn',=0A+=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= 'end_lsn');=0A+=0A+SELECT=20*=0A+FROM=20= pg_get_wal_records_info('start_lsn',=0A+=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20'end_lsn');=0A+=0A+=20=20= =20=20=20=20The=20corresponding=20retained=20WAL=20files=20can=20be=20= obtained=20with=0A+=20=20=20=20=20=20= pg_get_wal_files.=20=20To=20widen=20the=20search=20= range,=0A+=20=20=20=20=20=20increase=20before=20= if=20the=20relevant=20activity=0A+=20=20=20=20=20=20might=20have=20= occurred=20earlier,=20or=20increase=0A+=20=20=20=20=20=20= after=20if=20it=20might=20have=20occurred=20= later,=20and=0A+=20=20=20=20=20=20repeat=20the=20search.=20=20Failure=20= to=20find=20a=20record=20in=20the=20first=20returned=20range=0A+=20=20=20= =20=20=20does=20not=20necessarily=20mean=20that=20the=20record=20is=20= absent=20from=20retained=20WAL.=0A+=20=20=20=20=20=0A+=20=20=20=20= =0A+=20=20=20=0A+=0A=20=20=20=20=0A=20=20=20=20=20= =0A=20=20=20=20=20=20pg_get_wal_record_info(in_lsn=20= pg_lsn)=20returns=20record=0Adiff=20--git=20= a/src/backend/access/transam/xlogreader.c=20= b/src/backend/access/transam/xlogreader.c=0Aindex=20= 7db7c273b0c..02f49f64b3b=20100644=0A---=20= a/src/backend/access/transam/xlogreader.c=0A+++=20= b/src/backend/access/transam/xlogreader.c=0A@@=20-26,6=20+26,7=20@@=0A=20= #endif=0A=20=0A=20#include=20"access/transam.h"=0A+#include=20= "access/xact.h"=0A=20#include=20"access/xlog_internal.h"=0A=20#include=20= "access/xlogreader.h"=0A=20#include=20"access/xlogrecord.h"=0A@@=20-65,6=20= +66,41=20@@=20static=20void=20WALOpenSegmentInit(WALOpenSegment=20*seg,=20= WALSegmentContext=20*segcxt,=0A=20=20*/=0A=20#define=20= DEFAULT_DECODE_BUFFER_SIZE=20(64=20*=201024)=0A=20=0A+/*=0A+=20*=20= Extract=20timestamp=20from=20WAL=20record.=0A+=20*=0A+=20*=20If=20the=20= record=20contains=20a=20timestamp,=20returns=20true,=20and=20saves=20the=20= timestamp=0A+=20*=20in=20*recordXtime.=20If=20the=20record=20type=20has=20= no=20timestamp,=20returns=20false.=0A+=20*=20Currently,=20only=20= transaction=20commit/abort=20records=20and=20restore=20points=20contain=0A= +=20*=20timestamps.=0A+=20*/=0A+bool=0A= +GetXLogRecordTimestamp(XLogReaderState=20*record,=20TimestampTz=20= *recordXtime)=0A+{=0A+=09uint8=09=09info=20=3D=20XLogRecGetInfo(record)=20= &=20~XLR_INFO_MASK;=0A+=09uint8=09=09xact_info=20=3D=20info=20&=20= XLOG_XACT_OPMASK;=0A+=09uint8=09=09rmid=20=3D=20XLogRecGetRmid(record);=0A= +=0A+=09if=20(rmid=20=3D=3D=20RM_XLOG_ID=20&&=20info=20=3D=3D=20= XLOG_RESTORE_POINT)=0A+=09{=0A+=09=09*recordXtime=20=3D=20= ((xl_restore_point=20*)=20XLogRecGetData(record))->rp_time;=0A+=09=09= return=20true;=0A+=09}=0A+=09if=20(rmid=20=3D=3D=20RM_XACT_ID=20&&=20= (xact_info=20=3D=3D=20XLOG_XACT_COMMIT=20||=0A+=09=09=09=09=09=09=09=20=20= =20xact_info=20=3D=3D=20XLOG_XACT_COMMIT_PREPARED))=0A+=09{=0A+=09=09= *recordXtime=20=3D=20((xl_xact_commit=20*)=20= XLogRecGetData(record))->xact_time;=0A+=09=09return=20true;=0A+=09}=0A+=09= if=20(rmid=20=3D=3D=20RM_XACT_ID=20&&=20(xact_info=20=3D=3D=20= XLOG_XACT_ABORT=20||=0A+=09=09=09=09=09=09=09=20=20=20xact_info=20=3D=3D=20= XLOG_XACT_ABORT_PREPARED))=0A+=09{=0A+=09=09*recordXtime=20=3D=20= ((xl_xact_abort=20*)=20XLogRecGetData(record))->xact_time;=0A+=09=09= return=20true;=0A+=09}=0A+=09return=20false;=0A+}=0A+=0A=20/*=0A=20=20*=20= Construct=20a=20string=20in=20state->errormsg_buf=20explaining=20what's=20= wrong=20with=0A=20=20*=20the=20current=20record=20being=20read.=0Adiff=20= --git=20a/src/backend/access/transam/xlogrecovery.c=20= b/src/backend/access/transam/xlogrecovery.c=0Aindex=20= 54aaec9529f..06a5b10d092=20100644=0A---=20= a/src/backend/access/transam/xlogrecovery.c=0A+++=20= b/src/backend/access/transam/xlogrecovery.c=0A@@=20-356,7=20+356,6=20@@=20= static=20void=20xlog_outrec(StringInfo=20buf,=20XLogReaderState=20= *record);=0A=20static=20void=20xlog_block_info(StringInfo=20buf,=20= XLogReaderState=20*record);=0A=20static=20void=20= checkTimeLineSwitch(XLogRecPtr=20lsn,=20TimeLineID=20newTLI,=0A=20=09=09=09= =09=09=09=09=09TimeLineID=20prevTLI,=20TimeLineID=20replayTLI);=0A= -static=20bool=20getRecordTimestamp(XLogReaderState=20*record,=20= TimestampTz=20*recordXtime);=0A=20static=20void=20= verifyBackupPageConsistency(XLogReaderState=20*record);=0A=20=0A=20= static=20bool=20recoveryStopsBefore(XLogReaderState=20*record);=0A@@=20= -2405,41=20+2404,6=20@@=20checkTimeLineSwitch(XLogRecPtr=20lsn,=20= TimeLineID=20newTLI,=20TimeLineID=20prevTLI,=0A=20}=0A=20=0A=20=0A-/*=0A= -=20*=20Extract=20timestamp=20from=20WAL=20record.=0A-=20*=0A-=20*=20If=20= the=20record=20contains=20a=20timestamp,=20returns=20true,=20and=20saves=20= the=20timestamp=0A-=20*=20in=20*recordXtime.=20If=20the=20record=20type=20= has=20no=20timestamp,=20returns=20false.=0A-=20*=20Currently,=20only=20= transaction=20commit/abort=20records=20and=20restore=20points=20contain=0A= -=20*=20timestamps.=0A-=20*/=0A-static=20bool=0A= -getRecordTimestamp(XLogReaderState=20*record,=20TimestampTz=20= *recordXtime)=0A-{=0A-=09uint8=09=09info=20=3D=20XLogRecGetInfo(record)=20= &=20~XLR_INFO_MASK;=0A-=09uint8=09=09xact_info=20=3D=20info=20&=20= XLOG_XACT_OPMASK;=0A-=09uint8=09=09rmid=20=3D=20XLogRecGetRmid(record);=0A= -=0A-=09if=20(rmid=20=3D=3D=20RM_XLOG_ID=20&&=20info=20=3D=3D=20= XLOG_RESTORE_POINT)=0A-=09{=0A-=09=09*recordXtime=20=3D=20= ((xl_restore_point=20*)=20XLogRecGetData(record))->rp_time;=0A-=09=09= return=20true;=0A-=09}=0A-=09if=20(rmid=20=3D=3D=20RM_XACT_ID=20&&=20= (xact_info=20=3D=3D=20XLOG_XACT_COMMIT=20||=0A-=09=09=09=09=09=09=09=20=20= =20xact_info=20=3D=3D=20XLOG_XACT_COMMIT_PREPARED))=0A-=09{=0A-=09=09= *recordXtime=20=3D=20((xl_xact_commit=20*)=20= XLogRecGetData(record))->xact_time;=0A-=09=09return=20true;=0A-=09}=0A-=09= if=20(rmid=20=3D=3D=20RM_XACT_ID=20&&=20(xact_info=20=3D=3D=20= XLOG_XACT_ABORT=20||=0A-=09=09=09=09=09=09=09=20=20=20xact_info=20=3D=3D=20= XLOG_XACT_ABORT_PREPARED))=0A-=09{=0A-=09=09*recordXtime=20=3D=20= ((xl_xact_abort=20*)=20XLogRecGetData(record))->xact_time;=0A-=09=09= return=20true;=0A-=09}=0A-=09return=20false;=0A-}=0A-=0A=20/*=0A=20=20*=20= Checks=20whether=20the=20current=20buffer=20page=20and=20backup=20page=20= stored=20in=20the=0A=20=20*=20WAL=20record=20are=20consistent=20or=20= not.=20Before=20comparing=20the=20two=20pages,=20a=0A@@=20-2663,10=20= +2627,10=20@@=20recoveryStopsBefore(XLogReaderState=20*record)=0A=20=0A=20= =09/*=0A=20=09=20*=20Note:=20we=20must=20fetch=20recordXtime=20= regardless=20of=20recoveryTarget=20setting.=0A-=09=20*=20We=20don't=20= expect=20getRecordTimestamp=20ever=20to=20fail,=20since=20we=20already=20= know=0A-=09=20*=20this=20is=20a=20commit=20or=20abort=20record;=20but=20= test=20its=20result=20anyway.=0A+=09=20*=20We=20don't=20expect=20= GetXLogRecordTimestamp=20ever=20to=20fail,=20since=20we=20already=0A+=09=20= *=20know=20this=20is=20a=20commit=20or=20abort=20record;=20but=20test=20= its=20result=20anyway.=0A=20=09=20*/=0A-=09if=20= (getRecordTimestamp(record,=20&recordXtime)=20&&=0A+=09if=20= (GetXLogRecordTimestamp(record,=20&recordXtime)=20&&=0A=20=09=09= recoveryTarget=20=3D=3D=20RECOVERY_TARGET_TIME)=0A=20=09{=0A=20=09=09/*=0A= @@=20-2747,7=20+2711,7=20@@=20recoveryStopsAfter(XLogReaderState=20= *record)=0A=20=09=09=09recoveryStopAfter=20=3D=20true;=0A=20=09=09=09= recoveryStopXid=20=3D=20InvalidTransactionId;=0A=20=09=09=09= recoveryStopLSN=20=3D=20InvalidXLogRecPtr;=0A-=09=09=09(void)=20= getRecordTimestamp(record,=20&recoveryStopTime);=0A+=09=09=09(void)=20= GetXLogRecordTimestamp(record,=20&recoveryStopTime);=0A=20=09=09=09= strlcpy(recoveryStopName,=20recordRestorePointData->rp_name,=20= MAXFNAMELEN);=0A=20=0A=20=09=09=09ereport(LOG,=0A@@=20-2787,7=20+2751,7=20= @@=20recoveryStopsAfter(XLogReaderState=20*record)=0A=20=09=09= TransactionId=20recordXid;=0A=20=0A=20=09=09/*=20Update=20the=20last=20= applied=20transaction=20timestamp=20*/=0A-=09=09if=20= (getRecordTimestamp(record,=20&recordXtime))=0A+=09=09if=20= (GetXLogRecordTimestamp(record,=20&recordXtime))=0A=20=09=09=09= SetLatestXTime(recordXtime);=0A=20=0A=20=09=09/*=20Extract=20the=20XID=20= of=20the=20committed/aborted=20transaction=20*/=0A@@=20-3006,7=20+2970,7=20= @@=20recoveryApplyDelay(XLogReaderState=20*record)=0A=20=09=09xact_info=20= !=3D=20XLOG_XACT_COMMIT_PREPARED)=0A=20=09=09return=20false;=0A=20=0A-=09= if=20(!getRecordTimestamp(record,=20&xtime))=0A+=09if=20= (!GetXLogRecordTimestamp(record,=20&xtime))=0A=20=09=09return=20false;=0A= =20=0A=20=09delayUntil=20=3D=20TimestampTzPlusMilliseconds(xtime,=20= recovery_min_apply_delay);=0Adiff=20--git=20= a/src/include/access/xlogreader.h=20b/src/include/access/xlogreader.h=0A= index=204a9a687e879..3016ae4d5da=20100644=0A---=20= a/src/include/access/xlogreader.h=0A+++=20= b/src/include/access/xlogreader.h=0A@@=20-39,6=20+39,7=20@@=0A=20#endif=0A= =20=0A=20#include=20"access/xlogrecord.h"=0A+#include=20= "datatype/timestamp.h"=0A=20#include=20"storage/buf.h"=0A=20=0A=20/*=20= WALOpenSegment=20represents=20a=20WAL=20segment=20being=20read.=20*/=0A= @@=20-440,5=20+441,7=20@@=20extern=20bool=20= XLogRecGetBlockTagExtended(XLogReaderState=20*record,=20uint8=20= block_id,=0A=20=09=09=09=09=09=09=09=09=09=20=20=20RelFileLocator=20= *rlocator,=20ForkNumber=20*forknum,=0A=20=09=09=09=09=09=09=09=09=09=20=20= =20BlockNumber=20*blknum,=0A=20=09=09=09=09=09=09=09=09=09=20=20=20= Buffer=20*prefetch_buffer);=0A+extern=20bool=20= GetXLogRecordTimestamp(XLogReaderState=20*record,=0A+=09=09=09=09=09=09=09= =09=20=20=20TimestampTz=20*recordXtime);=0A=20=0A=20#endif=09=09=09=09=09= =09=09/*=20XLOGREADER_H=20*/=0Adiff=20--git=20= a/src/tools/pgindent/typedefs.list=20b/src/tools/pgindent/typedefs.list=0A= index=201f1bcf8d527..c85279cf423=20100644=0A---=20= a/src/tools/pgindent/typedefs.list=0A+++=20= b/src/tools/pgindent/typedefs.list=0A@@=20-3433,6=20+3433,7=20@@=20= WalSndState=0A=20WalSummarizerData=0A=20WalSummaryFile=0A=20WalSummaryIO=0A= +WalTimeBoundary=0A=20WalTimeSample=0A=20WalTimeSegment=0A=20WalUsage=0A= --=20=0A2.50.1=20(Apple=20Git-155)=0A=0A= --Apple-Mail=_D1745098-B8B4-48AB-8FF9-8232B81AF591--