From 768829e8e64d7296a86ce55467c5e2adf1e2b3f7 Mon Sep 17 00:00:00 2001
From: Melanie Plageman <melanieplageman@gmail.com>
Date: Wed, 27 Dec 2023 15:46:16 -0500
Subject: [PATCH v2 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.

ci-os-only:
---
 .../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 8fa5e105bc4..72b16d5f84d 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 2800ebd62f5..514d8092838 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 27e0598f74c..cfd60bfd9aa 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