Hi,
Elsewhere [1] I required a way to estimate the time corresponding to a
particular LSN in the past. I devised the attached LSNTimeline, a data
structure mapping LSNs <-> timestamps with decreasing precision for
older time, LSN pairs. This can be used to locate and translate a
particular time to LSN or vice versa using linear interpolation.
I've added an instance of the LSNTimeline to PgStat_WalStats and insert
new values to it in background writer's main loop. This patch set also
introduces some new pageinspect functions exposing LSN <-> time
translations.
Outside of being useful to users wondering about the last modification
time of a particular block in a relation, the LSNTimeline can be put to
use in other Postgres sub-systems to govern behavior based on resource
consumption -- using the LSN consumption rate as a proxy.
As mentioned in [1], the LSNTimeline is a prerequisite for my
implementation of a new freeze heuristic which seeks to freeze only
pages which will remain unmodified for a certain amount of wall clock
time. But one can imagine other uses for such translation capabilities.
The pageinspect additions need a bit more work. I didn't bump the
pageinspect version (didn't add the new functions to a new pageinspect
version file). I also didn't exercise the new pageinspect functions in a
test. I was unsure how to write a test which would be guaranteed not to
flake. Because the background writer updates the timeline, it seemed a
remote possibility that the time or LSN returned by the functions would
be 0 and as such, I'm not sure even a test that SELECT time/lsn > 0
would always pass.
I also noticed the pageinspect functions don't have XML id attributes
for link discoverability. I planned to add that in a separate commit.
- Melanie
[1]
https://www.postgresql.org/message-id/CAAKRu_b3tpbdRPUPh1Q5h35gXhY%3DspH2ssNsEsJ9sDfw6%3DPEAg%40mail.gmail.com
From 75a48fec0e9f0909dd11a676bb51e494fa4ca61c Mon Sep 17 00:00:00 2001
From: Melanie Plageman <melanieplage...@gmail.com>
Date: Tue, 5 Dec 2023 07:29:39 -0500
Subject: [PATCH v1 1/5] Record LSN at postmaster startup
The insert_lsn at postmaster startup can be used along with PgStartTime
as seed values for a timeline mapping LSNs to time. Future commits will
add such a structure for LSN <-> time conversions. A start LSN allows
for such conversions before even inserting a value into the timeline.
The current time and current insert LSN can be used along with
PgStartTime and PgStartLSN.
This is WIP, as I'm not sure if I did this in the right place.
---
src/backend/access/transam/xlog.c | 2 ++
src/backend/postmaster/postmaster.c | 1 +
src/include/utils/builtins.h | 3 +++
3 files changed, 6 insertions(+)
diff --git a/src/backend/access/transam/xlog.c b/src/backend/access/transam/xlog.c
index 1264849883..aa71e502e4 100644
--- a/src/backend/access/transam/xlog.c
+++ b/src/backend/access/transam/xlog.c
@@ -146,6 +146,8 @@ bool XLOG_DEBUG = false;
int wal_segment_size = DEFAULT_XLOG_SEG_SIZE;
+XLogRecPtr PgStartLSN = InvalidXLogRecPtr;
+
/*
* Number of WAL insertion locks to use. A higher value allows more insertions
* to happen concurrently, but adds some CPU overhead to flushing the WAL,
diff --git a/src/backend/postmaster/postmaster.c b/src/backend/postmaster/postmaster.c
index b163e89cbb..d858e04454 100644
--- a/src/backend/postmaster/postmaster.c
+++ b/src/backend/postmaster/postmaster.c
@@ -1448,6 +1448,7 @@ PostmasterMain(int argc, char *argv[])
* Remember postmaster startup time
*/
PgStartTime = GetCurrentTimestamp();
+ PgStartLSN = GetXLogInsertRecPtr();
/*
* Report postmaster status in the postmaster.pid file, to allow pg_ctl to
diff --git a/src/include/utils/builtins.h b/src/include/utils/builtins.h
index 2f8b46d6da..0cb24e10e6 100644
--- a/src/include/utils/builtins.h
+++ b/src/include/utils/builtins.h
@@ -17,6 +17,7 @@
#include "fmgr.h"
#include "nodes/nodes.h"
#include "utils/fmgrprotos.h"
+#include "access/xlogdefs.h"
/* Sign + the most decimal digits an 8-byte number could have */
#define MAXINT8LEN 20
@@ -82,6 +83,8 @@ extern void generate_operator_clause(fmStringInfo buf,
Oid opoid,
const char *rightop, Oid rightoptype);
+extern PGDLLIMPORT XLogRecPtr PgStartLSN;
+
/* varchar.c */
extern int bpchartruelen(char *s, int len);
--
2.37.2
From aedbc5058e370705b4041732f671263a2050b997 Mon Sep 17 00:00:00 2001
From: Melanie Plageman <melanieplage...@gmail.com>
Date: Wed, 27 Dec 2023 16:40:27 -0500
Subject: [PATCH v1 2/5] Add LSNTimeline for converting LSN <-> time
Add a new structure, LSNTimeline, consisting of LSNTimes -- each an LSN,
time pair. Each LSNTime can represent multiple logical LSN, time pairs,
referred to as members. LSN <-> time conversions can be done using
linear interpolation with two LSNTimes on the LSNTimeline.
This commit does not add a global instance of LSNTimeline. It adds the
structures and functions needed to maintain and access such a timeline.
---
src/backend/utils/activity/pgstat_wal.c | 199 ++++++++++++++++++++++++
src/include/pgstat.h | 34 ++++
src/tools/pgindent/typedefs.list | 2 +
3 files changed, 235 insertions(+)
diff --git a/src/backend/utils/activity/pgstat_wal.c b/src/backend/utils/activity/pgstat_wal.c
index 6a81b78135..ba40aad258 100644
--- a/src/backend/utils/activity/pgstat_wal.c
+++ b/src/backend/utils/activity/pgstat_wal.c
@@ -17,8 +17,11 @@
#include "postgres.h"
+#include "access/xlog.h"
#include "utils/pgstat_internal.h"
#include "executor/instrument.h"
+#include "utils/builtins.h"
+#include "utils/timestamp.h"
PgStat_PendingWalStats PendingWalStats = {0};
@@ -32,6 +35,12 @@ PgStat_PendingWalStats PendingWalStats = {0};
static WalUsage prevWalUsage;
+static void lsntime_absorb(LSNTime *a, const LSNTime *b);
+void lsntime_insert(LSNTimeline *timeline, TimestampTz time, XLogRecPtr lsn);
+
+XLogRecPtr estimate_lsn_at_time(const LSNTimeline *timeline, TimestampTz time);
+TimestampTz estimate_time_at_lsn(const LSNTimeline *timeline, XLogRecPtr lsn);
+
/*
* Calculate how much WAL usage counters have increased and update
* shared WAL and IO statistics.
@@ -184,3 +193,193 @@ pgstat_wal_snapshot_cb(void)
sizeof(pgStatLocal.snapshot.wal));
LWLockRelease(&stats_shmem->lock);
}
+
+/*
+ * Set *a to be the earlier of *a or *b.
+ */
+static void
+lsntime_absorb(LSNTime *a, const LSNTime *b)
+{
+ LSNTime result;
+ int new_members = a->members + b->members;
+
+ if (a->time < b->time)
+ result = *a;
+ else if (b->time < a->time)
+ result = *b;
+ else if (a->lsn < b->lsn)
+ result = *a;
+ else if (b->lsn < a->lsn)
+ result = *b;
+ else
+ result = *a;
+
+ *a = result;
+ a->members = new_members;
+}
+
+/*
+ * Insert a new LSNTime into the LSNTimeline in the first element with spare
+ * capacity.
+ */
+void
+lsntime_insert(LSNTimeline *timeline, TimestampTz time,
+ XLogRecPtr lsn)
+{
+ LSNTime temp;
+ LSNTime carry = {.lsn = lsn,.time = time,.members = 1};
+
+ for (int i = 0; i < timeline->length; i++)
+ {
+ bool full;
+ LSNTime *cur = &timeline->data[i];
+
+ /*
+ * An array element's capacity to represent members is 2 ^ its
+ * position in the array.
+ */
+ full = cur->members >= (1 << i);
+
+ /*
+ * If the current element is not yet at capacity, then insert the
+ * passed-in LSNTime into this element by taking the smaller of the it
+ * and the current LSNTime element. This is required to ensure that
+ * time moves forward on the timeline.
+ */
+ if (!full)
+ {
+ Assert(cur->members == carry.members);
+ Assert(cur->members + carry.members <= 1 << i);
+ lsntime_absorb(cur, &carry);
+ return;
+ }
+
+ /*
+ * If the current element is full, ensure that the inserting LSNTime
+ * is larger than the current element. This must be true for time to
+ * move forward on the timeline.
+ */
+ Assert(carry.lsn >= cur->lsn || carry.time >= cur->time);
+
+ /*
+ * If the element is at capacity, swap the element with the carry and
+ * continue on to find an element with space to represent the new
+ * member.
+ */
+ temp = *cur;
+ *cur = carry;
+ carry = temp;
+ }
+
+ /*
+ * Time to use another element in the array -- and increase the length in
+ * the process
+ */
+ timeline->data[timeline->length] = carry;
+ timeline->length++;
+}
+
+
+/*
+ * Translate time to a LSN using the provided timeline. The timeline will not
+ * be modified.
+ */
+XLogRecPtr
+estimate_lsn_at_time(const LSNTimeline *timeline, TimestampTz time)
+{
+ TimestampTz time_elapsed;
+ XLogRecPtr lsns_elapsed;
+ double result;
+
+ LSNTime start = {.time = PgStartTime,.lsn = PgStartLSN};
+ LSNTime end = {.time = GetCurrentTimestamp(),.lsn = GetXLogInsertRecPtr()};
+
+ /*
+ * If the target time is after the current time, our best estimate of the
+ * LSN is the current insert LSN.
+ */
+ if (time >= end.time)
+ return end.lsn;
+
+ for (int i = 0; i < timeline->length; i++)
+ {
+ /* Pass times more recent than our target time */
+ if (timeline->data[i].time > time)
+ continue;
+
+ /* Found the first element before our target time */
+ start = timeline->data[i];
+
+ /*
+ * If there is only one element in the array, use the current time as
+ * the end of the range. Otherwise it is the element preceding our
+ * start.
+ */
+ if (i > 0)
+ end = timeline->data[i - 1];
+ break;
+ }
+
+ time_elapsed = end.time - start.time;
+ Assert(time_elapsed != 0);
+
+ lsns_elapsed = end.lsn - start.lsn;
+ Assert(lsns_elapsed != 0);
+
+ result = (double) (time - start.time) / time_elapsed * lsns_elapsed + start.lsn;
+ if (result < 0)
+ return InvalidXLogRecPtr;
+ return result;
+}
+
+/*
+ * Translate lsn to a time using the provided timeline. The timeline will not
+ * be modified.
+ */
+TimestampTz
+estimate_time_at_lsn(const LSNTimeline *timeline, XLogRecPtr lsn)
+{
+ TimestampTz time_elapsed;
+ XLogRecPtr lsns_elapsed;
+ TimestampTz result;
+
+ LSNTime start = {.time = PgStartTime,.lsn = PgStartLSN};
+ LSNTime end = {.time = GetCurrentTimestamp(),.lsn = GetXLogInsertRecPtr()};
+
+ /*
+ * If the target LSN is after the current insert LSN, the current time is
+ * our best estimate.
+ */
+ if (lsn >= end.lsn)
+ return end.time;
+
+ for (int i = 0; i < timeline->length; i++)
+ {
+ /* Pass LSNs more recent than our target LSN */
+ if (timeline->data[i].lsn > lsn)
+ continue;
+
+ /* Found the first element before our target LSN */
+ start = timeline->data[i];
+
+ /*
+ * If there is only one element in the array, use the current LSN and
+ * time as the end of the range. Otherwise, use the preceding element
+ * (the first element occuring before our target LSN in the timeline).
+ */
+ if (i > 0)
+ end = timeline->data[i - 1];
+ break;
+ }
+
+ time_elapsed = end.time - start.time;
+ Assert(time_elapsed != 0);
+
+ lsns_elapsed = end.lsn - start.lsn;
+ Assert(lsns_elapsed != 0);
+
+ result = (lsn - start.lsn) / lsns_elapsed * time_elapsed + start.time;
+ if (result < 0)
+ return 0;
+ return result;
+}
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index ab91b3b367..ddbe320bf3 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -11,6 +11,7 @@
#ifndef PGSTAT_H
#define PGSTAT_H
+#include "access/xlogdefs.h"
#include "datatype/timestamp.h"
#include "portability/instr_time.h"
#include "postmaster/pgarch.h" /* for MAX_XFN_CHARS */
@@ -428,6 +429,39 @@ typedef struct PgStat_StatTabEntry
PgStat_Counter autoanalyze_count;
} PgStat_StatTabEntry;
+/*
+ * The elements of an LSNTimeline. Each LSNTime represents one or more time,
+ * LSN pairs. The LSN is typically the insert LSN recorded at the time. Members
+ * is the number of logical members -- each a time, LSN pair -- represented in
+ * the LSNTime.
+ */
+typedef struct LSNTime
+{
+ TimestampTz time;
+ XLogRecPtr lsn;
+ uint64 members;
+} LSNTime;
+
+/*
+ * A timeline consists of LSNTimes from most to least recent. Each element of
+ * the array in the timeline may represent 2^array index logical members --
+ * meaning that each element's capacity is twice that of the preceding element.
+ * This gives more recent times greater precision than less recent ones. An
+ * array of size 64 should provide sufficient capacity without accounting for
+ * what to do when all elements of the array are at capacity.
+ *
+ * When LSNTimes are inserted into the timeline, they are absorbed into the
+ * first array element with spare capacity -- with the new combined element
+ * having the lesser of the two values. The timeline's length is the highest
+ * array index representing one or more logical members. Use the timeline for
+ * LSN <-> time conversion using linear interpolation.
+ */
+typedef struct LSNTimeline
+{
+ int length;
+ LSNTime data[64];
+} LSNTimeline;
+
typedef struct PgStat_WalStats
{
PgStat_Counter wal_records;
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index e37ef9aa76..3a4121b482 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -1520,6 +1520,8 @@ LogicalTapeSet
LsnReadQueue
LsnReadQueueNextFun
LsnReadQueueNextStatus
+LSNTime
+LSNTimeline
LtreeGistOptions
LtreeSignature
MAGIC
--
2.37.2
From 7b5b8e53026ecdcdccc07029a38d35e8d13985fd Mon Sep 17 00:00:00 2001
From: Melanie Plageman <melanieplage...@gmail.com>
Date: Wed, 27 Dec 2023 16:41:47 -0500
Subject: [PATCH v1 3/5] Add LSNTimeline to PgStat_WalStats
Add a globally maintained instance of the new LSNTimeline to
PgStat_WalStats and add utility functions for maintaining and accessing
it. This commit does not insert new values to the timeline or use the
helpers to access it.
---
src/backend/utils/activity/pgstat_wal.c | 48 +++++++++++++++++++++----
src/include/pgstat.h | 6 ++++
2 files changed, 48 insertions(+), 6 deletions(-)
diff --git a/src/backend/utils/activity/pgstat_wal.c b/src/backend/utils/activity/pgstat_wal.c
index ba40aad258..594185acb9 100644
--- a/src/backend/utils/activity/pgstat_wal.c
+++ b/src/backend/utils/activity/pgstat_wal.c
@@ -36,10 +36,10 @@ static WalUsage prevWalUsage;
static void lsntime_absorb(LSNTime *a, const LSNTime *b);
-void lsntime_insert(LSNTimeline *timeline, TimestampTz time, XLogRecPtr lsn);
+static void lsntime_insert(LSNTimeline *timeline, TimestampTz time, XLogRecPtr lsn);
-XLogRecPtr estimate_lsn_at_time(const LSNTimeline *timeline, TimestampTz time);
-TimestampTz estimate_time_at_lsn(const LSNTimeline *timeline, XLogRecPtr lsn);
+static XLogRecPtr estimate_lsn_at_time(const LSNTimeline *timeline, TimestampTz time);
+static TimestampTz estimate_time_at_lsn(const LSNTimeline *timeline, XLogRecPtr lsn);
/*
* Calculate how much WAL usage counters have increased and update
@@ -222,7 +222,7 @@ lsntime_absorb(LSNTime *a, const LSNTime *b)
* Insert a new LSNTime into the LSNTimeline in the first element with spare
* capacity.
*/
-void
+static void
lsntime_insert(LSNTimeline *timeline, TimestampTz time,
XLogRecPtr lsn)
{
@@ -284,7 +284,7 @@ lsntime_insert(LSNTimeline *timeline, TimestampTz time,
* Translate time to a LSN using the provided timeline. The timeline will not
* be modified.
*/
-XLogRecPtr
+static XLogRecPtr
estimate_lsn_at_time(const LSNTimeline *timeline, TimestampTz time)
{
TimestampTz time_elapsed;
@@ -336,7 +336,7 @@ estimate_lsn_at_time(const LSNTimeline *timeline, TimestampTz time)
* Translate lsn to a time using the provided timeline. The timeline will not
* be modified.
*/
-TimestampTz
+static TimestampTz
estimate_time_at_lsn(const LSNTimeline *timeline, XLogRecPtr lsn)
{
TimestampTz time_elapsed;
@@ -383,3 +383,39 @@ estimate_time_at_lsn(const LSNTimeline *timeline, XLogRecPtr lsn)
return 0;
return result;
}
+
+XLogRecPtr
+pgstat_wal_estimate_lsn_at_time(TimestampTz time)
+{
+ XLogRecPtr result;
+ PgStatShared_Wal *stats_shmem = &pgStatLocal.shmem->wal;
+
+ LWLockAcquire(&stats_shmem->lock, LW_SHARED);
+ result = estimate_lsn_at_time(&stats_shmem->stats.timeline, time);
+ LWLockRelease(&stats_shmem->lock);
+
+ return result;
+}
+
+TimestampTz
+pgstat_wal_estimate_time_at_lsn(XLogRecPtr lsn)
+{
+ TimestampTz result;
+ PgStatShared_Wal *stats_shmem = &pgStatLocal.shmem->wal;
+
+ LWLockAcquire(&stats_shmem->lock, LW_SHARED);
+ result = estimate_time_at_lsn(&stats_shmem->stats.timeline, lsn);
+ LWLockRelease(&stats_shmem->lock);
+
+ return result;
+}
+
+void
+pgstat_wal_update_lsntimeline(TimestampTz time, XLogRecPtr lsn)
+{
+ PgStatShared_Wal *stats_shmem = &pgStatLocal.shmem->wal;
+
+ LWLockAcquire(&stats_shmem->lock, LW_EXCLUSIVE);
+ lsntime_insert(&stats_shmem->stats.timeline, time, lsn);
+ LWLockRelease(&stats_shmem->lock);
+}
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index ddbe320bf3..dd914e606e 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -472,6 +472,7 @@ typedef struct PgStat_WalStats
PgStat_Counter wal_sync;
PgStat_Counter wal_write_time;
PgStat_Counter wal_sync_time;
+ LSNTimeline timeline;
TimestampTz stat_reset_timestamp;
} PgStat_WalStats;
@@ -754,6 +755,11 @@ extern void pgstat_execute_transactional_drops(int ndrops, struct xl_xact_stats_
extern void pgstat_report_wal(bool force);
extern PgStat_WalStats *pgstat_fetch_stat_wal(void);
+/* Helpers for maintaining the LSNTimeline */
+extern XLogRecPtr pgstat_wal_estimate_lsn_at_time(TimestampTz time);
+extern TimestampTz pgstat_wal_estimate_time_at_lsn(XLogRecPtr lsn);
+extern void pgstat_wal_update_lsntimeline(TimestampTz time, XLogRecPtr lsn);
+
/*
* Variables in pgstat.c
--
2.37.2
From 18d4844e8cf1a6ddc92c46c57fac682fda79ad41 Mon Sep 17 00:00:00 2001
From: Melanie Plageman <melanieplage...@gmail.com>
Date: Wed, 27 Dec 2023 16:32:40 -0500
Subject: [PATCH v1 4/5] Bgwriter maintains global LSNTimeline
Insert new LSN, time pairs to the global LSNTimeline stored in
PgStat_WalStats in the background writer's main loop. This ensures that
new values are added to the timeline in a regular manner.
---
src/backend/postmaster/bgwriter.c | 4 +++-
1 file changed, 3 insertions(+), 1 deletion(-)
diff --git a/src/backend/postmaster/bgwriter.c b/src/backend/postmaster/bgwriter.c
index d02dc17b9c..9a2ef869b5 100644
--- a/src/backend/postmaster/bgwriter.c
+++ b/src/backend/postmaster/bgwriter.c
@@ -277,6 +277,7 @@ BackgroundWriterMain(void)
{
TimestampTz timeout = 0;
TimestampTz now = GetCurrentTimestamp();
+ XLogRecPtr current_lsn = GetLastImportantRecPtr();
timeout = TimestampTzPlusMilliseconds(last_snapshot_ts,
LOG_SNAPSHOT_INTERVAL_MS);
@@ -289,10 +290,11 @@ BackgroundWriterMain(void)
* the end of the record.
*/
if (now >= timeout &&
- last_snapshot_lsn <= GetLastImportantRecPtr())
+ last_snapshot_lsn <= current_lsn)
{
last_snapshot_lsn = LogStandbySnapshot();
last_snapshot_ts = now;
+ pgstat_wal_update_lsntimeline(now, current_lsn);
}
}
--
2.37.2
From ddc0e0b9dbb5b6457e121e286737736dfaad7ef5 Mon Sep 17 00:00:00 2001
From: Melanie Plageman <melanieplage...@gmail.com>
Date: Wed, 27 Dec 2023 15:46:16 -0500
Subject: [PATCH v1 5/5] Add time <-> LSN translation functions to pageinspect
Previous commits added a global LSNTimeline, maintained by background
writer, that allows approximate translations between time and LSNs. This
can be useful for approximating the time of last modification of a page
or estimating the LSN consumption rate to moderate maintenance processes
and balance system resource utilization. This commit adds user-facing
access to the conversion capabilities of the timeline.
---
.../pageinspect/pageinspect--1.10--1.11.sql | 10 +++++
contrib/pageinspect/rawpage.c | 26 +++++++++++
doc/src/sgml/pageinspect.sgml | 45 +++++++++++++++++++
3 files changed, 81 insertions(+)
diff --git a/contrib/pageinspect/pageinspect--1.10--1.11.sql b/contrib/pageinspect/pageinspect--1.10--1.11.sql
index 8fa5e105bc..72b16d5f84 100644
--- a/contrib/pageinspect/pageinspect--1.10--1.11.sql
+++ b/contrib/pageinspect/pageinspect--1.10--1.11.sql
@@ -26,3 +26,13 @@ ALTER FUNCTION hash_bitmap_info(regclass, int8) PARALLEL RESTRICTED;
-- Likewise for gist_page_items.
ALTER FUNCTION brin_page_items(bytea, regclass) PARALLEL RESTRICTED;
ALTER FUNCTION gist_page_items(bytea, regclass) PARALLEL RESTRICTED;
+
+CREATE FUNCTION estimate_lsn_at_time(IN input_time timestamp with time zone,
+ OUT lsn pg_lsn)
+AS 'MODULE_PATHNAME', 'estimate_lsn_at_time'
+LANGUAGE C STRICT PARALLEL SAFE;
+
+CREATE FUNCTION estimate_time_at_lsn(IN lsn pg_lsn,
+ OUT result timestamp with time zone)
+AS 'MODULE_PATHNAME', 'estimate_time_at_lsn'
+LANGUAGE C STRICT PARALLEL SAFE;
diff --git a/contrib/pageinspect/rawpage.c b/contrib/pageinspect/rawpage.c
index b25a63cbd6..6d15ab542f 100644
--- a/contrib/pageinspect/rawpage.c
+++ b/contrib/pageinspect/rawpage.c
@@ -22,6 +22,7 @@
#include "funcapi.h"
#include "miscadmin.h"
#include "pageinspect.h"
+#include "pgstat.h"
#include "storage/bufmgr.h"
#include "storage/checksum.h"
#include "utils/builtins.h"
@@ -335,6 +336,9 @@ page_header(PG_FUNCTION_ARGS)
PG_FUNCTION_INFO_V1(page_checksum_1_9);
PG_FUNCTION_INFO_V1(page_checksum);
+PG_FUNCTION_INFO_V1(estimate_lsn_at_time);
+PG_FUNCTION_INFO_V1(estimate_time_at_lsn);
+
static Datum
page_checksum_internal(PG_FUNCTION_ARGS, enum pageinspect_version ext_version)
{
@@ -374,3 +378,25 @@ page_checksum(PG_FUNCTION_ARGS)
{
return page_checksum_internal(fcinfo, PAGEINSPECT_V1_8);
}
+
+Datum
+estimate_time_at_lsn(PG_FUNCTION_ARGS)
+{
+ XLogRecPtr lsn = PG_GETARG_LSN(0);
+ TimestampTz result;
+
+ result = pgstat_wal_estimate_time_at_lsn(lsn);
+
+ PG_RETURN_TIMESTAMPTZ(result);
+}
+
+Datum
+estimate_lsn_at_time(PG_FUNCTION_ARGS)
+{
+ TimestampTz time = PG_GETARG_TIMESTAMPTZ(0);
+ XLogRecPtr result;
+
+ result = pgstat_wal_estimate_lsn_at_time(time);
+
+ PG_RETURN_LSN(result);
+}
diff --git a/doc/src/sgml/pageinspect.sgml b/doc/src/sgml/pageinspect.sgml
index 27e0598f74..cfd60bfd9a 100644
--- a/doc/src/sgml/pageinspect.sgml
+++ b/doc/src/sgml/pageinspect.sgml
@@ -153,6 +153,51 @@ test=# SELECT fsm_page_contents(get_raw_page('pg_class', 'fsm', 0));
</para>
</listitem>
</varlistentry>
+
+ <varlistentry>
+ <term>
+ <function>estimate_lsn_at_time(lsn timestamptz) returns pg_lsn</function>
+ <indexterm>
+ <primary>estimate_lsn_at_time</primary>
+ </indexterm>
+ </term>
+
+ <listitem>
+ <para>
+ <function>estimate_lsn_at_time</function> estimates the LSN at the provided time.
+ </para>
+
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term>
+ <function>estimate_time_at_lsn(lsn pg_lsn) returns timestamp with timezone</function>
+ <indexterm>
+ <primary>estimate_time_at_lsn</primary>
+ </indexterm>
+ </term>
+
+ <listitem>
+ <para>
+ <function>estimate_time_at_lsn</function> estimates the time at provided LSN.
+ </para>
+
+ <para>
+ One useful application is approximating the last modification time of a
+ given page in a relation. For example, when combined with pageinspect
+ functions returning a page LSN:
+<screen>
+test=# SELECT estimate_time_at_lsn((SELECT lsn from page_header(get_raw_page('pg_class', 0))));
+ estimate_time_at_lsn
+-------------------------------
+ 2023-12-22 08:01:02.393598-05
+</screen>
+ </para>
+
+ </listitem>
+ </varlistentry>
+
</variablelist>
</sect2>
--
2.37.2
