From eeb936816924b91a194ed03a4296b4f669e72071 Mon Sep 17 00:00:00 2001 From: Melanie Plageman Date: Wed, 7 Aug 2024 10:57:45 -0400 Subject: [PATCH v7 3/4] Add time <-> LSN translation range functions Previous commits added a global LSNTimeStream, maintained by background writer and functions to return a range of LSNs covering a time or time covering an LSN. Add SQL-callable functions to produce these ranges 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 | 75 +++++++++++++++++++ src/backend/utils/adt/pgstatfuncs.c | 107 ++++++++++++++++++++++++++++ src/include/catalog/pg_proc.dat | 27 +++++++ src/test/regress/expected/stats.out | 43 +++++++++++ src/test/regress/sql/stats.sql | 28 ++++++++ 5 files changed, 280 insertions(+) diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml index 55417a6fa9d..9b63659900b 100644 --- a/doc/src/sgml/monitoring.sgml +++ b/doc/src/sgml/monitoring.sgml @@ -3195,6 +3195,81 @@ 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_stat_lsn_bounds_for_time + + pg_stat_lsn_bounds_for_time + ( timestamp with time zone ) + record + (lsn pg_lsn, + lsn pg_lsn) + + + Returns the upper and lower bound of the LSN range on the global + LSNTimeLine in which the time falls. + + + + + + + pg_stat_time_bounds_for_lsn + + pg_stat_time_bounds_for_lsn + ( pg_lsn ) + record + ( time timestamp with time zone, + time timestamp with time zone) + + + Returns the upper and lower bound of the time range on the global + LSNTimeLine in which the LSN falls. + + + + + + + pg_stat_lsntime_stream + + pg_stat_lsntime_stream () + setof record + ( lsn pg_lsn, + time timestamp with time zone) + + + Returns all of the LSN-time pairs currently in the global LSN time + stream. + + + + +
+ + + diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c index 32211371237..ac862fb679a 100644 --- a/src/backend/utils/adt/pgstatfuncs.c +++ b/src/backend/utils/adt/pgstatfuncs.c @@ -30,6 +30,7 @@ #include "storage/procarray.h" #include "utils/acl.h" #include "utils/builtins.h" +#include "utils/pg_lsn.h" #include "utils/timestamp.h" #define UINT32_ACCESS_ONCE(var) ((uint32)(*((volatile uint32 *)&(var)))) @@ -1526,6 +1527,112 @@ pg_stat_get_wal(PG_FUNCTION_ARGS) PG_RETURN_DATUM(HeapTupleGetDatum(heap_form_tuple(tupdesc, values, nulls))); } +/* + * Returns the LSN, time pairs making up the global LSNTimeStream maintained + * in WAL statistics. + */ +Datum +pg_stat_lsntime_stream(PG_FUNCTION_ARGS) +{ + ReturnSetInfo *rsinfo; + PgStat_WalStats *stats; + LSNTimeStream *stream; + + InitMaterializedSRF(fcinfo, 0); + rsinfo = (ReturnSetInfo *) fcinfo->resultinfo; + + stats = pgstat_fetch_stat_wal(); + stream = &stats->stream; + + for (size_t i = 0; i < stream->length; i++) + { + Datum values[2] = {0}; + bool nulls[2] = {0}; + + values[0] = stream->data[i].lsn; + values[1] = stream->data[i].time; + tuplestore_putvalues(rsinfo->setResult, rsinfo->setDesc, + values, nulls); + } + + return (Datum) 0; +} + +/* + * Returns the upper and lower bounds of an LSN range covering the passed-in + * time. If the passed-in time is far enough in the past that we don't have + * data, the lower bound will be InvalidXLogRecPtr. If it is in the future, + * the upper bound will be FFFFFFFF/FFFFFFFF. + */ +Datum +pg_stat_lsn_bounds_for_time(PG_FUNCTION_ARGS) +{ + PgStat_WalStats *wal_stats; + TimestampTz target_time; + LSNTime lower, + upper; + TupleDesc tupdesc; + Datum values[2] = {0}; + bool nulls[2] = {0}; + + target_time = PG_GETARG_TIMESTAMPTZ(0); + + tupdesc = CreateTemplateTupleDesc(2); + TupleDescInitEntry(tupdesc, (AttrNumber) 1, "lower", + PG_LSNOID, -1, 0); + TupleDescInitEntry(tupdesc, (AttrNumber) 2, "upper", + PG_LSNOID, -1, 0); + BlessTupleDesc(tupdesc); + + wal_stats = pgstat_fetch_stat_wal(); + lsn_bounds_for_time(&wal_stats->stream, target_time, &lower, &upper); + + values[0] = lower.lsn; + values[1] = upper.lsn; + + PG_RETURN_DATUM(HeapTupleGetDatum(heap_form_tuple(tupdesc, + values, + nulls))); +} + + +/* + * Returns the upper and lower bounds of a TimestampTz range covering the + * passed-in LSN. If the passed-in LSN is far enough in the past that we don't + * have data, the lower bound will be -infinity. If the passed-in LSN is in + * the future, the upper bound will be infinity. + */ +Datum +pg_stat_time_bounds_for_lsn(PG_FUNCTION_ARGS) +{ + PgStat_WalStats *wal_stats; + XLogRecPtr target_lsn; + LSNTime lower, + upper; + TupleDesc tupdesc; + Datum values[2] = {0}; + bool nulls[2] = {0}; + + target_lsn = PG_GETARG_LSN(0); + + tupdesc = CreateTemplateTupleDesc(2); + TupleDescInitEntry(tupdesc, (AttrNumber) 1, "lower", + TIMESTAMPTZOID, -1, 0); + TupleDescInitEntry(tupdesc, (AttrNumber) 2, "upper", + TIMESTAMPTZOID, -1, 0); + BlessTupleDesc(tupdesc); + + wal_stats = pgstat_fetch_stat_wal(); + time_bounds_for_lsn(&wal_stats->stream, target_lsn, &lower, &upper); + + values[0] = lower.time; + values[1] = upper.time; + + PG_RETURN_DATUM(HeapTupleGetDatum(heap_form_tuple(tupdesc, + values, + nulls))); +} + /* * Returns statistics of SLRU caches. */ diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index d36f6001bb1..c59f42bc974 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -6375,6 +6375,33 @@ prorettype => 'timestamptz', proargtypes => 'xid', prosrc => 'pg_xact_commit_timestamp' }, +{ oid => '9997', descr => 'get upper and lower time bounds for LSN', + proname => 'pg_stat_time_bounds_for_lsn', provolatile => 'v', + proisstrict => 't', proparallel => 'u', + prorettype => 'record', proargtypes => 'pg_lsn', + proallargtypes => '{pg_lsn,timestamptz,timestamptz}', + proargmodes => '{i,o,o}', + proargnames => '{target_lsn, lower, upper}', + prosrc => 'pg_stat_time_bounds_for_lsn' }, + +{ oid => '9996', descr => 'get upper and lower LSN bounds for time', + proname => 'pg_stat_lsn_bounds_for_time', provolatile => 'v', + proisstrict => 't', proparallel => 'u', + prorettype => 'record', proargtypes => 'timestamptz', + proallargtypes => '{timestamptz,pg_lsn,pg_lsn}', + proargmodes => '{i,o,o}', + proargnames => '{target_time, lower, upper}', + prosrc => 'pg_stat_lsn_bounds_for_time' }, + +{ oid => '9994', + descr => 'print the LSN Time Stream', + proname => 'pg_stat_lsntime_stream', prorows => '64', + provolatile => 'v', proparallel => 'u', + proretset => 't', prorettype => 'record', + proargtypes => '', proallargtypes => '{pg_lsn,timestamptz}', + proargmodes => '{o,o}', proargnames => '{lsn,time}', + prosrc => 'pg_stat_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..5f32e3bd9e0 100644 --- a/src/test/regress/expected/stats.out +++ b/src/test/regress/expected/stats.out @@ -813,6 +813,49 @@ 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 ----- +-- Test the functions querying the global LSNTimeStream stored in WAL stats. +-- An LSN range covering a time 100 years in the past should be from 0 to a +-- non-zero LSN (either the oldest LSN in the stream or the current insert +-- LSN). +SELECT lower = pg_lsn(0), + upper > pg_lsn(0) + FROM pg_stat_lsn_bounds_for_time(now() - make_interval(years=> 100)); + ?column? | ?column? +----------+---------- + t | t +(1 row) + +-- An LSN range covering a time 100 years in the future should be from roughly +-- the current time to FFFFFFFF/FFFFFFFF (UINT64_MAX). +SELECT lower > pg_lsn(0), + upper = pg_lsn('FFFFFFFF/FFFFFFFF') + FROM pg_stat_lsn_bounds_for_time(now() + make_interval(years=> 100)); + ?column? | ?column? +----------+---------- + t | t +(1 row) + +-- A TimestampTz range covering LSN 0 should be from -infinity to a positive +-- time (either the oldest time in the stream or the current time). +SELECT lower = timestamptz('-infinity'), + upper::time > 'allballs'::time + FROM pg_stat_time_bounds_for_lsn(pg_lsn(0)); + ?column? | ?column? +----------+---------- + t | t +(1 row) + +-- A TimestampTz range covering an LSN 1 GB in the future should be from +-- roughly the current time to infinity. +SELECT lower::time > 'allballs'::time, + upper = timestamptz('infinity') + FROM pg_stat_time_bounds_for_lsn( + pg_current_wal_insert_lsn() + 1000000000); + ?column? | ?column? +----------+---------- + t | 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..0260779141c 100644 --- a/src/test/regress/sql/stats.sql +++ b/src/test/regress/sql/stats.sql @@ -411,6 +411,34 @@ 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 ----- +-- Test the functions querying the global LSNTimeStream stored in WAL stats. + +-- An LSN range covering a time 100 years in the past should be from 0 to a +-- non-zero LSN (either the oldest LSN in the stream or the current insert +-- LSN). +SELECT lower = pg_lsn(0), + upper > pg_lsn(0) + FROM pg_stat_lsn_bounds_for_time(now() - make_interval(years=> 100)); + +-- An LSN range covering a time 100 years in the future should be from roughly +-- the current time to FFFFFFFF/FFFFFFFF (UINT64_MAX). +SELECT lower > pg_lsn(0), + upper = pg_lsn('FFFFFFFF/FFFFFFFF') + FROM pg_stat_lsn_bounds_for_time(now() + make_interval(years=> 100)); + +-- A TimestampTz range covering LSN 0 should be from -infinity to a positive +-- time (either the oldest time in the stream or the current time). +SELECT lower = timestamptz('-infinity'), + upper::time > 'allballs'::time + FROM pg_stat_time_bounds_for_lsn(pg_lsn(0)); + +-- A TimestampTz range covering an LSN 1 GB in the future should be from +-- roughly the current time to infinity. +SELECT lower::time > 'allballs'::time, + upper = timestamptz('infinity') + FROM pg_stat_time_bounds_for_lsn( + pg_current_wal_insert_lsn() + 1000000000); + -- 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