On Wed, Jun 26, 2024 at 10:04 PM Melanie Plageman
<melanieplage...@gmail.com> wrote:
>
> I've implemented these review points in the attached v4.
I realized the docs had a compilation error. Attached v5 fixes that as
well as three bugs I found while using this patch set more intensely
today.
I see Michael has been working on some crash safety for stats here
[1]. I wonder if that would be sufficient for the LSNTimeStream. I
haven't examined his patch functionality yet, though.
I also had an off-list conversation with Robert where he suggested I
could perhaps change the user-facing functions for estimating an
LSN/time conversion to instead return a floor and a ceiling -- instead
of linearly interpolating a guess. This would be a way to keep users
from misunderstanding the accuracy of the functions to translate LSN
<-> time. I'm interested in what others think of this.
I like this idea a lot because it allows me to worry less about how I
decide to compress the data and whether or not it will be accurate for
use cases different than my own (the opportunistic freezing
heuristic). If I can provide a floor and a ceiling that are definitely
accurate, I don't have to worry about misleading people.
- Melanie
[1] https://www.postgresql.org/message-id/ZnEiqAITL-VgZDoY%40paquier.xyz
From f492af31c1b9917aa27ba3ad76560e59f3fd5c9b Mon Sep 17 00:00:00 2001
From: Melanie Plageman <melanieplage...@gmail.com>
Date: Wed, 27 Dec 2023 16:40:27 -0500
Subject: [PATCH v5 2/6] Add LSNTimeStream for converting LSN <-> time
Add a new structure, LSNTimeStream, consisting of LSNTimes -- each an
LSN, time pair. The LSNTimeStream is fixed size, so when a new LSNTime
is inserted to a full LSNTimeStream, an LSNTime is dropped and the new
LSNTime is inserted. We drop the LSNTime whose absence would cause the
least error when interpolating between its adjoining points.
LSN <-> time conversions can be done using linear interpolation with two
LSNTimes on the LSNTimeStream.
This commit does not add a global instance of LSNTimeStream. It adds the
structures and functions needed to maintain and access such a stream.
---
src/backend/utils/activity/pgstat_wal.c | 233 ++++++++++++++++++++++++
src/include/pgstat.h | 32 ++++
src/tools/pgindent/typedefs.list | 2 +
3 files changed, 267 insertions(+)
diff --git a/src/backend/utils/activity/pgstat_wal.c b/src/backend/utils/activity/pgstat_wal.c
index 0e374f133a9..cef9429994c 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 "executor/instrument.h"
+#include "utils/builtins.h"
#include "utils/pgstat_internal.h"
+#include "utils/timestamp.h"
PgStat_PendingWalStats PendingWalStats = {0};
@@ -32,6 +35,11 @@ PgStat_PendingWalStats PendingWalStats = {0};
static WalUsage prevWalUsage;
+static void lsntime_insert(LSNTimeStream *stream, TimestampTz time, XLogRecPtr lsn);
+
+XLogRecPtr estimate_lsn_at_time(const LSNTimeStream *stream, TimestampTz time);
+TimestampTz estimate_time_at_lsn(const LSNTimeStream *stream, XLogRecPtr lsn);
+
/*
* Calculate how much WAL usage counters have increased and update
* shared WAL and IO statistics.
@@ -184,3 +192,228 @@ pgstat_wal_snapshot_cb(void)
sizeof(pgStatLocal.snapshot.wal));
LWLockRelease(&stats_shmem->lock);
}
+
+/*
+ * Given three LSNTimes, calculate the area of the triangle they form were they
+ * plotted with time on the X axis and LSN on the Y axis.
+ */
+static int
+lsn_ts_calculate_error_area(LSNTime *left, LSNTime *mid, LSNTime *right)
+{
+ int rectangle_all = (right->time - left->time) * (right->lsn - left->lsn);
+ int triangle1 = rectangle_all / 2;
+ int triangle2 = (mid->lsn - left->lsn) * (mid->time - left->time) / 2;
+ int triangle3 = (right->lsn - mid->lsn) * (right->time - mid->time) / 2;
+ int rectangle_part = (right->lsn - mid->lsn) * (mid->time - left->time);
+
+ return rectangle_all - triangle1 - triangle2 - triangle3 - rectangle_part;
+}
+
+/*
+ * Determine which LSNTime to drop from a full LSNTimeStream. Once the LSNTime
+ * is dropped, points between it and either of its adjacent LSNTimes will be
+ * interpolated between those two LSNTimes instead. To keep the LSNTimeStream
+ * as accurate as possible, drop the LSNTime whose absence would have the least
+ * impact on future interpolations.
+ *
+ * We determine the error that would be introduced by dropping a point on the
+ * stream by calculating the area of the triangle formed by the LSNTime and its
+ * adjacent LSNTimes. We do this for each LSNTime in the stream (except for the
+ * first and last LSNTimes) and choose the LSNTime with the smallest error
+ * (area). We avoid extrapolation by never dropping the first or last points.
+ */
+static int
+lsntime_to_drop(LSNTimeStream *stream)
+{
+ int min_area = INT_MAX;
+ int target_point = stream->length - 1;
+
+ /* Don't drop points if free space available */
+ Assert(stream->length == LSNTIMESTREAM_VOLUME);
+
+ for (int i = stream->length - 1; i-- > 0;)
+ {
+ LSNTime *left = &stream->data[i - 1];
+ LSNTime *mid = &stream->data[i];
+ LSNTime *right = &stream->data[i + 1];
+ int area = lsn_ts_calculate_error_area(left, mid, right);
+
+ if (abs(area) < abs(min_area))
+ {
+ min_area = area;
+ target_point = i;
+ }
+ }
+
+ return target_point;
+}
+
+/*
+ * Insert a new LSNTime into the LSNTimeStream in the first available element,
+ * or, if there are no empty elements, drop an LSNTime from the stream, move
+ * all LSNTimes down and insert the new LSNTime into the element at index 0.
+ */
+void
+lsntime_insert(LSNTimeStream *stream, TimestampTz time,
+ XLogRecPtr lsn)
+{
+ int drop;
+ LSNTime entrant = {.lsn = lsn,.time = time};
+
+ if (stream->length < LSNTIMESTREAM_VOLUME)
+ {
+ /*
+ * The new entry should exceed the most recent entry to ensure time
+ * moves forward on the stream.
+ */
+ Assert(stream->length == 0 ||
+ (lsn >= stream->data[LSNTIMESTREAM_VOLUME - stream->length].lsn &&
+ time >= stream->data[LSNTIMESTREAM_VOLUME - stream->length].time));
+
+ /*
+ * If there are unfilled elements in the stream, insert the passed-in
+ * LSNTime into the tail of the array.
+ */
+ stream->length++;
+ stream->data[LSNTIMESTREAM_VOLUME - stream->length] = entrant;
+ return;
+ }
+
+ drop = lsntime_to_drop(stream);
+ if (drop < 0 || drop >= stream->length)
+ {
+ elog(WARNING, "Unable to insert LSNTime to LSNTimeStream. Drop failed.");
+ return;
+ }
+
+ /*
+ * Drop the LSNTime at index drop by copying the array from drop - 1 to
+ * drop
+ */
+ memmove(&stream->data[1], &stream->data[0], sizeof(LSNTime) * drop);
+ stream->data[0] = entrant;
+}
+
+/*
+ * Translate time to a LSN using the provided stream. The stream will not
+ * be modified.
+ */
+XLogRecPtr
+estimate_lsn_at_time(const LSNTimeStream *stream, TimestampTz time)
+{
+ XLogRecPtr result;
+ int64 time_elapsed,
+ lsns_elapsed;
+ LSNTime start = {.time = PgStartTime,.lsn = PgStartLSN};
+ LSNTime end = {.time = GetCurrentTimestamp(),.lsn = GetXLogInsertRecPtr()};
+
+ /*
+ * If the provided time is before DB startup, the best we can do is return
+ * the start LSN.
+ */
+ if (time < start.time)
+ return start.lsn;
+
+ /*
+ * If the provided time is after now, the current LSN is our best
+ * estimate.
+ */
+ if (time >= end.time)
+ return end.lsn;
+
+ /*
+ * Loop through the stream. Stop at the first LSNTime earlier than our
+ * target time. This LSNTime will be our interpolation start point. If
+ * there's an LSNTime later than that, then that will be our interpolation
+ * end point.
+ */
+ for (int i = LSNTIMESTREAM_VOLUME - stream->length; i < LSNTIMESTREAM_VOLUME; i++)
+ {
+ if (stream->data[i].time > time)
+ continue;
+
+ start = stream->data[i];
+ if (i > LSNTIMESTREAM_VOLUME)
+ end = stream->data[i - 1];
+ goto stop;
+ }
+
+ /*
+ * If we exhausted the stream, then use its earliest LSNTime as our
+ * interpolation end point.
+ */
+ if (stream->length > 0)
+ end = stream->data[LSNTIMESTREAM_VOLUME - 1];
+
+stop:
+ Assert(end.time > start.time);
+ Assert(end.lsn > start.lsn);
+ 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;
+ return Max(result, 0);
+}
+
+/*
+ * Translate lsn to a time using the provided stream. The stream will not
+ * be modified.
+ */
+TimestampTz
+estimate_time_at_lsn(const LSNTimeStream *stream, XLogRecPtr lsn)
+{
+ int64 time_elapsed,
+ lsns_elapsed;
+ TimestampTz result;
+ LSNTime start = {.time = PgStartTime,.lsn = PgStartLSN};
+ LSNTime end = {.time = GetCurrentTimestamp(),.lsn = GetXLogInsertRecPtr()};
+
+ /*
+ * If the LSN is before DB startup, the best we can do is return that
+ * time.
+ */
+ if (lsn <= start.lsn)
+ return start.time;
+
+ /*
+ * If the target LSN is after the current insert LSN, the current time is
+ * our best estimate.
+ */
+ if (lsn >= end.lsn)
+ return end.time;
+
+ /*
+ * Loop through the stream. Stop at the first LSNTime earlier than our
+ * target LSN. This LSNTime will be our interpolation start point. If
+ * there's an LSNTime later than that, then that will be our interpolation
+ * end point.
+ */
+ for (int i = LSNTIMESTREAM_VOLUME - stream->length; i < LSNTIMESTREAM_VOLUME; i++)
+ {
+ if (stream->data[i].lsn > lsn)
+ continue;
+
+ start = stream->data[i];
+ if (i > LSNTIMESTREAM_VOLUME - stream->length)
+ end = stream->data[i - 1];
+ goto stop;
+ }
+
+ /*
+ * If we exhausted the stream, then use its earliest LSNTime as our
+ * interpolation end point.
+ */
+ if (stream->length > 0)
+ end = stream->data[LSNTIMESTREAM_VOLUME - 1];
+
+stop:
+ Assert(end.time > start.time);
+ Assert(end.lsn > start.lsn);
+ time_elapsed = end.time - start.time;
+ Assert(time_elapsed != 0);
+ lsns_elapsed = end.lsn - start.lsn;
+ Assert(lsns_elapsed != 0);
+ result = (double) (lsn - start.lsn) / lsns_elapsed * time_elapsed + start.time;
+ return Max(result, 0);
+}
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index 2136239710e..af348be839c 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,37 @@ typedef struct PgStat_StatTabEntry
PgStat_Counter autoanalyze_count;
} PgStat_StatTabEntry;
+/*
+ * The elements of an LSNTimeStream. Each LSNTime represents one or more time,
+ * LSN pairs. The LSN is typically the insert LSN recorded at the time.
+ */
+typedef struct LSNTime
+{
+ TimestampTz time;
+ XLogRecPtr lsn;
+} LSNTime;
+
+#define LSNTIMESTREAM_VOLUME 64
+
+/*
+ * An LSN time stream is an array consisting of LSNTimes from most to least
+ * recent. The array is filled from end to start before the contents of any
+ * elements are merged. Once the LSNTimeStream length == volume (the array is
+ * full), an LSNTime is dropped, the new LSNTime is added at index 0, and the
+ * intervening LSNTimes are moved down by one.
+ *
+ * When dropping an LSNTime, we attempt to pick the member which would
+ * introduce the least error into the stream. See lsntime_to_drop() for more
+ * details.
+ *
+ * Use the stream for LSN <-> time conversion using linear interpolation.
+ */
+typedef struct LSNTimeStream
+{
+ int length;
+ LSNTime data[LSNTIMESTREAM_VOLUME];
+} LSNTimeStream;
+
typedef struct PgStat_WalStats
{
PgStat_Counter wal_records;
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index 82b3b411fb5..a5851d44b16 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -1584,6 +1584,8 @@ LogicalTapeSet
LsnReadQueue
LsnReadQueueNextFun
LsnReadQueueNextStatus
+LSNTime
+LSNTimeStream
LtreeGistOptions
LtreeSignature
MAGIC
--
2.34.1
From af14cc7652649e1e651ab73c2041b5bf434a57c3 Mon Sep 17 00:00:00 2001
From: Melanie Plageman <melanieplage...@gmail.com>
Date: Wed, 27 Dec 2023 16:32:40 -0500
Subject: [PATCH v5 4/6] Bgwriter maintains global LSNTimeStream
Insert new LSN, time pairs to the global LSNTimeStream stored in
PgStat_WalStats in the background writer's main loop. This ensures that
new values are added to the stream in a regular manner.
---
src/backend/postmaster/bgwriter.c | 13 +++++++++----
1 file changed, 9 insertions(+), 4 deletions(-)
diff --git a/src/backend/postmaster/bgwriter.c b/src/backend/postmaster/bgwriter.c
index 0f75548759a..02b039cfacf 100644
--- a/src/backend/postmaster/bgwriter.c
+++ b/src/backend/postmaster/bgwriter.c
@@ -273,6 +273,7 @@ BackgroundWriterMain(char *startup_data, size_t startup_data_len)
{
TimestampTz timeout = 0;
TimestampTz now = GetCurrentTimestamp();
+ XLogRecPtr current_lsn;
timeout = TimestampTzPlusMilliseconds(last_snapshot_ts,
LOG_SNAPSHOT_INTERVAL_MS);
@@ -284,11 +285,15 @@ BackgroundWriterMain(char *startup_data, size_t startup_data_len)
* start of a record, whereas last_snapshot_lsn points just past
* the end of the record.
*/
- if (now >= timeout &&
- last_snapshot_lsn <= GetLastImportantRecPtr())
+ if (now >= timeout)
{
- last_snapshot_lsn = LogStandbySnapshot();
- last_snapshot_ts = now;
+ current_lsn = GetLastImportantRecPtr();
+ if (last_snapshot_lsn <= current_lsn)
+ {
+ last_snapshot_lsn = LogStandbySnapshot();
+ last_snapshot_ts = now;
+ pgstat_wal_update_lsntime_stream(now, current_lsn);
+ }
}
}
--
2.34.1
From 693cd6d15b0e60c16f8e7977e1631676f4ca7c5d Mon Sep 17 00:00:00 2001
From: Melanie Plageman <melanieplage...@gmail.com>
Date: Tue, 5 Dec 2023 07:29:39 -0500
Subject: [PATCH v5 1/6] 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 | 2 ++
src/include/utils/builtins.h | 3 +++
3 files changed, 7 insertions(+)
diff --git a/src/backend/access/transam/xlog.c b/src/backend/access/transam/xlog.c
index d36272ab4ff..5be3361582e 100644
--- a/src/backend/access/transam/xlog.c
+++ b/src/backend/access/transam/xlog.c
@@ -142,6 +142,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 bf0241aed0c..f1b60fe6cee 100644
--- a/src/backend/postmaster/postmaster.c
+++ b/src/backend/postmaster/postmaster.c
@@ -117,6 +117,7 @@
#include "storage/proc.h"
#include "tcop/backend_startup.h"
#include "tcop/tcopprot.h"
+#include "utils/builtins.h"
#include "utils/datetime.h"
#include "utils/memutils.h"
#include "utils/pidfile.h"
@@ -1345,6 +1346,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 359c570f23e..16a7a058bc7 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
@@ -85,6 +86,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.34.1
From f384a26ef0264b688b7be1d1e0a18b17313a3ffc Mon Sep 17 00:00:00 2001
From: Melanie Plageman <melanieplage...@gmail.com>
Date: Wed, 21 Feb 2024 20:06:29 -0500
Subject: [PATCH v5 5/6] Add time <-> LSN translation functions
Previous commits added a global LSNTimeStream, 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 LSNTimeStream.
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.
---
doc/src/sgml/monitoring.sgml | 66 +++++++++++++++++++++++++
src/backend/utils/activity/pgstat_wal.c | 56 +++++++++++++++++++++
src/include/catalog/pg_proc.dat | 22 +++++++++
3 files changed, 144 insertions(+)
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index 991f6299075..979c193a721 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -3195,6 +3195,72 @@ description | Waiting for a newly initialized WAL file to reach durable storage
</tgroup>
</table>
+ <para>
+ In addition to these WAL stats, a stream of LSN-time pairs is accessible
+ via the functions shown in <xref linkend="functions-lsn-time-stream"/>.
+ </para>
+
+ <table id="functions-lsn-time-stream">
+ <title>LSN Time Stream Information Functions</title>
+ <tgroup cols="1">
+ <thead>
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ Function
+ </para>
+ <para>
+ Description
+ </para></entry>
+ </row>
+ </thead>
+
+ <tbody>
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <indexterm>
+ <primary>pg_estimate_lsn_at_time</primary>
+ </indexterm>
+ <function>pg_estimate_lsn_at_time</function> ( <type>timestamp with time zone</type> )
+ <returnvalue>pg_lsn</returnvalue>
+ </para>
+ <para>
+ Returns the estimated lsn at the provided time.
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <indexterm>
+ <primary>pg_estimate_lsn_at_time</primary>
+ </indexterm>
+ <function>pg_estimate_lsn_at_time</function> ( <type>pg_lsn</type> )
+ <returnvalue>timestamp with time zone</returnvalue>
+ </para>
+ <para>
+ Returns the estimated time at the provided lsn.
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <indexterm>
+ <primary>pg_lsntime_stream</primary>
+ </indexterm>
+ <function>pg_lsntime_stream</function> ()
+ <returnvalue>record</returnvalue>
+ ( <parameter>time</parameter> <type>timestamp with time zone</type>,
+ <parameter>lsn</parameter> <type>pg_lsnwith time zone</type>)
+ </para>
+ <para>
+ Returns all of the LSN-time pairs in the current LSN time stream.
+ </para></entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+
+
+
</sect2>
<sect2 id="monitoring-pg-stat-database-view">
diff --git a/src/backend/utils/activity/pgstat_wal.c b/src/backend/utils/activity/pgstat_wal.c
index 68bc5b4e9af..2e05eb1a4f3 100644
--- a/src/backend/utils/activity/pgstat_wal.c
+++ b/src/backend/utils/activity/pgstat_wal.c
@@ -19,7 +19,9 @@
#include "access/xlog.h"
#include "executor/instrument.h"
+#include "funcapi.h"
#include "utils/builtins.h"
+#include "utils/pg_lsn.h"
#include "utils/pgstat_internal.h"
#include "utils/timestamp.h"
@@ -427,3 +429,57 @@ pgstat_wal_update_lsntime_stream(TimestampTz time, XLogRecPtr lsn)
lsntime_insert(&stats_shmem->stats.stream, 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_lsntime_stream);
+
+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.stream, 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.stream, time);
+ LWLockRelease(&stats_shmem->lock);
+
+ PG_RETURN_LSN(result);
+}
+
+Datum
+pg_lsntime_stream(PG_FUNCTION_ARGS)
+{
+ ReturnSetInfo *rsinfo;
+ PgStat_WalStats *stats = pgstat_fetch_stat_wal();
+ LSNTimeStream *stream = &stats->stream;
+
+ InitMaterializedSRF(fcinfo, 0);
+ rsinfo = (ReturnSetInfo *) fcinfo->resultinfo;
+ for (int i = LSNTIMESTREAM_VOLUME - stream->length; i < LSNTIMESTREAM_VOLUME; i++)
+ {
+ Datum values[2] = {0};
+ bool nulls[2] = {0};
+
+ values[0] = stream->data[i].time;
+ values[1] = stream->data[i].lsn;
+ tuplestore_putvalues(rsinfo->setResult, rsinfo->setDesc,
+ values, nulls);
+ }
+ return (Datum) 0;
+}
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 6a5476d3c4c..8ab14b49b2a 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -6342,6 +6342,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 Time Stream',
+ proname => 'pg_lsntime_stream', prorows => '64',
+ proretset => 't', provolatile => 'v', proparallel => 's',
+ prorettype => 'record', proargtypes => '',
+ proallargtypes => '{timestamptz,pg_lsn}',
+ proargmodes => '{o,o}',
+ proargnames => '{time, lsn}',
+ prosrc => 'pg_lsntime_stream' },
+
{ oid => '6168',
descr => 'get commit timestamp and replication origin of a transaction',
proname => 'pg_xact_commit_timestamp_origin', provolatile => 'v',
--
2.34.1
From f78ea2aee794358a8ba1dabece1debd52d48a10c Mon Sep 17 00:00:00 2001
From: Melanie Plageman <melanieplage...@gmail.com>
Date: Wed, 21 Feb 2024 20:28:27 -0500
Subject: [PATCH v5 3/6] Add LSNTimeStream to PgStat_WalStats
Add a globally maintained instance of an LSNTimeStream to
PgStat_WalStats and a utility function to insert new values.
---
src/backend/utils/activity/pgstat_wal.c | 10 ++++++++++
src/include/pgstat.h | 4 ++++
2 files changed, 14 insertions(+)
diff --git a/src/backend/utils/activity/pgstat_wal.c b/src/backend/utils/activity/pgstat_wal.c
index cef9429994c..68bc5b4e9af 100644
--- a/src/backend/utils/activity/pgstat_wal.c
+++ b/src/backend/utils/activity/pgstat_wal.c
@@ -417,3 +417,13 @@ stop:
result = (double) (lsn - start.lsn) / lsns_elapsed * time_elapsed + start.time;
return Max(result, 0);
}
+
+void
+pgstat_wal_update_lsntime_stream(TimestampTz time, XLogRecPtr lsn)
+{
+ PgStatShared_Wal *stats_shmem = &pgStatLocal.shmem->wal;
+
+ LWLockAcquire(&stats_shmem->lock, LW_EXCLUSIVE);
+ lsntime_insert(&stats_shmem->stats.stream, time, lsn);
+ LWLockRelease(&stats_shmem->lock);
+}
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index af348be839c..773e3cd5003 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -470,6 +470,7 @@ typedef struct PgStat_WalStats
PgStat_Counter wal_sync;
PgStat_Counter wal_write_time;
PgStat_Counter wal_sync_time;
+ LSNTimeStream stream;
TimestampTz stat_reset_timestamp;
} PgStat_WalStats;
@@ -752,6 +753,9 @@ 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 LSNTimeStream */
+extern void pgstat_wal_update_lsntime_stream(TimestampTz time, XLogRecPtr lsn);
+
/*
* Variables in pgstat.c
--
2.34.1
