From 49f767bc859356df8b4a6ea03d490b6b1aa1d48d Mon Sep 17 00:00:00 2001 From: Melanie Plageman Date: Wed, 21 Feb 2024 20:06:29 -0500 Subject: [PATCH v6 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 | 52 +++++++++++++++++++ src/include/catalog/pg_proc.dat | 22 +++++++++ src/test/regress/expected/stats.out | 13 +++++ src/test/regress/sql/stats.sql | 5 ++ 5 files changed, 158 insertions(+) diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml index 55417a6fa9d..f86e77955d6 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 + + In addition to these WAL stats, a stream of LSN-time pairs is accessible + via the functions shown in . + + + + LSN Time Stream Information Functions + + + + + Function + + + Description + + + + + + + + + pg_estimate_lsn_at_time + + pg_estimate_lsn_at_time ( timestamp with time zone ) + pg_lsn + + + Returns the estimated lsn at the provided time. + + + + + + + pg_estimate_lsn_at_time + + pg_estimate_lsn_at_time ( pg_lsn ) + timestamp with time zone + + + Returns the estimated time at the provided lsn. + + + + + + + pg_lsntime_stream + + pg_lsntime_stream () + record + ( time timestamp with time zone, + lsn pg_lsnwith time zone) + + + Returns all of the LSN-time pairs in the current LSN time stream. + + + + +
+ + + diff --git a/src/backend/utils/activity/pgstat_wal.c b/src/backend/utils/activity/pgstat_wal.c index c1c3da22b2f..7552a964b80 100644 --- a/src/backend/utils/activity/pgstat_wal.c +++ b/src/backend/utils/activity/pgstat_wal.c @@ -19,8 +19,10 @@ #include "access/xlog.h" #include "executor/instrument.h" +#include "funcapi.h" #include "math.h" #include "utils/builtins.h" +#include "utils/pg_lsn.h" #include "utils/pgstat_internal.h" #include "utils/timestamp.h" @@ -525,3 +527,53 @@ pgstat_wal_update_lsntime_stream(TimestampTz time, XLogRecPtr lsn) lsntime_insert(&stats_shmem->stats.stream, time, lsn); LWLockRelease(&stats_shmem->lock); } + +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 = 0; i < stream->length; 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 54b50ee5d61..b5d8d0d3673 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -6375,6 +6375,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', diff --git a/src/test/regress/expected/stats.out b/src/test/regress/expected/stats.out index 6e08898b183..b02b74e5872 100644 --- a/src/test/regress/expected/stats.out +++ b/src/test/regress/expected/stats.out @@ -813,6 +813,19 @@ SELECT (n_tup_ins + n_tup_upd) > 0 AS has_data FROM pg_stat_all_tables ----- -- Test that various stats views are being properly populated ----- +SELECT pg_estimate_time_at_lsn(pg_current_wal_insert_lsn()) > + now() - make_interval(years=> 100); + ?column? +---------- + t +(1 row) + +SELECT pg_estimate_lsn_at_time(now()) - '0/0' > 0; + ?column? +---------- + t +(1 row) + -- Test that sessions is incremented when a new session is started in pg_stat_database SELECT sessions AS db_stat_sessions FROM pg_stat_database WHERE datname = (SELECT current_database()) \gset \c diff --git a/src/test/regress/sql/stats.sql b/src/test/regress/sql/stats.sql index d8ac0d06f48..8562bdb45e8 100644 --- a/src/test/regress/sql/stats.sql +++ b/src/test/regress/sql/stats.sql @@ -411,6 +411,11 @@ SELECT (n_tup_ins + n_tup_upd) > 0 AS has_data FROM pg_stat_all_tables -- Test that various stats views are being properly populated ----- +SELECT pg_estimate_time_at_lsn(pg_current_wal_insert_lsn()) > + now() - make_interval(years=> 100); + +SELECT pg_estimate_lsn_at_time(now()) - '0/0' > 0; + -- Test that sessions is incremented when a new session is started in pg_stat_database SELECT sessions AS db_stat_sessions FROM pg_stat_database WHERE datname = (SELECT current_database()) \gset \c -- 2.34.1