From cf9e6f507bc9781bf79e8c39766c8e84209d2ada Mon Sep 17 00:00:00 2001
From: Melanie Plageman <melanieplageman@gmail.com>
Date: Wed, 21 Feb 2024 20:06:29 -0500
Subject: [PATCH v3 5/5] Add time <-> LSN translation functions

Previous commits added a global LSNTimeline, maintained by background
writer, that allows approximate translations between time and LSNs.

Add SQL-callable functions to convert from LSN to time and back and a
SQL-callable function returning the entire LSNTimeline.

This could be useful in combination with SQL-callable functions
accessing a page LSN to approximate the time of last modification of a
page or estimating the LSN consumption rate to moderate maintenance
processes and balance system resource utilization.
---
 src/backend/utils/activity/pgstat_wal.c | 58 +++++++++++++++++++++++++
 src/include/catalog/pg_proc.dat         | 22 ++++++++++
 2 files changed, 80 insertions(+)

diff --git a/src/backend/utils/activity/pgstat_wal.c b/src/backend/utils/activity/pgstat_wal.c
index df4c91ee3cf..27f2b23cd88 100644
--- a/src/backend/utils/activity/pgstat_wal.c
+++ b/src/backend/utils/activity/pgstat_wal.c
@@ -18,6 +18,7 @@
 #include "postgres.h"
 
 #include "access/xlog.h"
+#include "funcapi.h"
 #include "utils/pgstat_internal.h"
 #include "executor/instrument.h"
 #include "utils/builtins.h"
@@ -418,3 +419,60 @@ pgstat_wal_update_lsntimeline(TimestampTz time, XLogRecPtr lsn)
 	lsntime_insert(&stats_shmem->stats.timeline, time, lsn);
 	LWLockRelease(&stats_shmem->lock);
 }
+
+PG_FUNCTION_INFO_V1(pg_estimate_lsn_at_time);
+PG_FUNCTION_INFO_V1(pg_estimate_time_at_lsn);
+PG_FUNCTION_INFO_V1(pg_lsntimeline);
+
+Datum
+pg_estimate_time_at_lsn(PG_FUNCTION_ARGS)
+{
+	XLogRecPtr	lsn = PG_GETARG_LSN(0);
+	PgStatShared_Wal *stats_shmem = &pgStatLocal.shmem->wal;
+	TimestampTz result;
+
+	LWLockAcquire(&stats_shmem->lock, LW_SHARED);
+	result = estimate_time_at_lsn(&stats_shmem->stats.timeline, lsn);
+	LWLockRelease(&stats_shmem->lock);
+
+	PG_RETURN_TIMESTAMPTZ(result);
+}
+
+Datum
+pg_estimate_lsn_at_time(PG_FUNCTION_ARGS)
+{
+	PgStatShared_Wal *stats_shmem = &pgStatLocal.shmem->wal;
+	TimestampTz time = PG_GETARG_TIMESTAMPTZ(0);
+	XLogRecPtr	result;
+
+	LWLockAcquire(&stats_shmem->lock, LW_SHARED);
+	result = estimate_lsn_at_time(&stats_shmem->stats.timeline, time);
+	LWLockRelease(&stats_shmem->lock);
+
+	PG_RETURN_LSN(result);
+}
+
+Datum
+pg_lsntimeline(PG_FUNCTION_ARGS)
+{
+	ReturnSetInfo *rsinfo;
+	LSNTimeline *timeline;
+	PgStatShared_Wal *stats_shmem = &pgStatLocal.shmem->wal;
+
+	InitMaterializedSRF(fcinfo, 0);
+	rsinfo = (ReturnSetInfo *) fcinfo->resultinfo;
+	LWLockAcquire(&stats_shmem->lock, LW_SHARED);
+	timeline = &stats_shmem->stats.timeline;
+	for (int i = LSNTIMELINE_VOLUME - timeline->length; i < LSNTIMELINE_VOLUME; i++)
+	{
+		Datum		values[2] = {0};
+		bool		nulls[2] = {0};
+
+		values[0] = timeline->data[i].time;
+		values[1] = timeline->data[i].lsn;
+		tuplestore_putvalues(rsinfo->setResult, rsinfo->setDesc,
+							 values, nulls);
+	}
+	LWLockRelease(&stats_shmem->lock);
+	return (Datum) 0;
+}
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 9c120fc2b7f..e69cf9c2437 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -6326,6 +6326,28 @@
   prorettype => 'timestamptz', proargtypes => 'xid',
   prosrc => 'pg_xact_commit_timestamp' },
 
+{ oid => '9997',
+  descr => 'get approximate LSN at a particular point in time',
+  proname => 'pg_estimate_lsn_at_time', provolatile => 'v',
+  prorettype => 'pg_lsn', proargtypes => 'timestamptz',
+  prosrc => 'pg_estimate_lsn_at_time' },
+
+{ oid => '9996',
+  descr => 'get approximate time at a particular LSN',
+  proname => 'pg_estimate_time_at_lsn', provolatile => 'v',
+  prorettype => 'timestamptz', proargtypes => 'pg_lsn',
+  prosrc => 'pg_estimate_time_at_lsn' },
+
+{ oid => '9994',
+  descr => 'print the LSN timeline',
+  proname => 'pg_lsntimeline', prorows => '64',
+  proretset => 't', provolatile => 'v', proparallel => 's',
+  prorettype => 'record', proargtypes => '',
+  proallargtypes => '{timestamptz,pg_lsn}',
+  proargmodes => '{o,o}',
+  proargnames => '{time, lsn}',
+  prosrc => 'pg_lsntimeline' },
+
 { oid => '6168',
   descr => 'get commit timestamp and replication origin of a transaction',
   proname => 'pg_xact_commit_timestamp_origin', provolatile => 'v',
-- 
2.37.2