Enhance statistics reset functions to return reset timestamp
Started by Shinya Kato5 months ago9 messages
Hi hackers,
I would like to propose a series of patches that enhance the behavior
of various statistics reset functions by making them return the reset
timestamp. This change improves usability and aligns with the behavior
introduced in commit dc9f8a798[1]https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=dc9f8a798307244d791b71f666f50de319799e7c -- Best regards, Shinya Kato NTT OSS Center, where pg_stat_statements_reset()
was updated to return the reset time.
The following functions have been modified to return a TIMESTAMP WITH
TIME ZONE value indicating when the statistics were reset:
- pg_stat_reset()
- pg_stat_reset_shared()
- pg_stat_reset_single_table_counters()
- pg_stat_reset_backend_stats()
- pg_stat_reset_single_function_counters()
- pg_stat_reset_slru()
- pg_stat_reset_replication_slot()
- pg_stat_reset_subscription_stats()
- pg_stat_clear_snapshot()
For pg_stat_reset_backend_stats() and
pg_stat_reset_replication_slot(), the functions return the reset
timestamp when a valid input is provided. If an invalid input is given
(e.g., an invalid backend PID or replication slot name), the functions
return NULL.
This allows users to easily determine whether the reset operation was
successful based on the return value.
Thoughts?
[1]: https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=dc9f8a798307244d791b71f666f50de319799e7c -- Best regards, Shinya Kato NTT OSS Center
--
Best regards,
Shinya Kato
NTT OSS Center
Attachments:
v1-0001-Make-pg_stat_reset-return-the-reset-time.patchapplication/octet-stream; name=v1-0001-Make-pg_stat_reset-return-the-reset-time.patchDownload
From 48a6422875508ec49bf89d18f987126f24ced165 Mon Sep 17 00:00:00 2001
From: Shinya Kato <shinya11.kato@gmail.com>
Date: Fri, 2 May 2025 14:44:06 +0900
Subject: [PATCH v1 1/9] Make pg_stat_reset() return the reset time
---
doc/src/sgml/monitoring.sgml | 5 +++--
src/backend/utils/activity/pgstat.c | 4 +++-
src/backend/utils/adt/pgstatfuncs.c | 4 ++--
src/include/catalog/pg_proc.dat | 2 +-
src/include/pgstat.h | 2 +-
src/test/isolation/expected/stats.out | 8 ++++----
src/test/isolation/expected/stats_1.out | 8 ++++----
src/test/isolation/specs/stats.spec | 2 +-
src/test/regress/expected/stats.out | 16 ++++++++--------
src/test/regress/sql/stats.sql | 4 ++--
10 files changed, 29 insertions(+), 26 deletions(-)
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index 3f4a27a736e..b72b840048b 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -5008,10 +5008,11 @@ description | Waiting for a newly initialized WAL file to reach durable storage
<primary>pg_stat_reset</primary>
</indexterm>
<function>pg_stat_reset</function> ()
- <returnvalue>void</returnvalue>
+ <returnvalue>timestamp with time zone</returnvalue>
</para>
<para>
- Resets all statistics counters for the current database to zero.
+ Resets all statistics counters for the current database to zero, and
+ returns the time of the reset.
</para>
<para>
This function is restricted to superusers by default, but other users
diff --git a/src/backend/utils/activity/pgstat.c b/src/backend/utils/activity/pgstat.c
index 6bc91ce0dad..707d55abdb6 100644
--- a/src/backend/utils/activity/pgstat.c
+++ b/src/backend/utils/activity/pgstat.c
@@ -830,7 +830,7 @@ match_db_entries(PgStatShared_HashEntry *entry, Datum match_data)
* Permission checking for this function is managed through the normal
* GRANT system.
*/
-void
+TimestampTz
pgstat_reset_counters(void)
{
TimestampTz ts = GetCurrentTimestamp();
@@ -838,6 +838,8 @@ pgstat_reset_counters(void)
pgstat_reset_matching_entries(match_db_entries,
ObjectIdGetDatum(MyDatabaseId),
ts);
+
+ return ts;
}
/*
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index c756c2bebaa..56c5dc4efce 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -1857,9 +1857,9 @@ pg_stat_force_next_flush(PG_FUNCTION_ARGS)
Datum
pg_stat_reset(PG_FUNCTION_ARGS)
{
- pgstat_reset_counters();
+ TimestampTz ts = pgstat_reset_counters();
- PG_RETURN_VOID();
+ PG_RETURN_TIMESTAMPTZ(ts);
}
/*
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 118d6da1ace..7f3b314875d 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -6140,7 +6140,7 @@
{ oid => '2274',
descr => 'statistics: reset collected statistics for current database',
proname => 'pg_stat_reset', proisstrict => 'f', provolatile => 'v',
- prorettype => 'void', proargtypes => '', prosrc => 'pg_stat_reset' },
+ prorettype => 'timestamptz', proargtypes => '', prosrc => 'pg_stat_reset' },
{ oid => '3775',
descr => 'statistics: reset collected statistics shared across the cluster',
proname => 'pg_stat_reset_shared', proisstrict => 'f', provolatile => 'v',
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index 202bd2d5ace..62a4db726df 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -524,7 +524,7 @@ extern void pgstat_initialize(void);
extern long pgstat_report_stat(bool force);
extern void pgstat_force_next_flush(void);
-extern void pgstat_reset_counters(void);
+extern TimestampTz pgstat_reset_counters(void);
extern void pgstat_reset(PgStat_Kind kind, Oid dboid, uint64 objid);
extern void pgstat_reset_of_kind(PgStat_Kind kind);
diff --git a/src/test/isolation/expected/stats.out b/src/test/isolation/expected/stats.out
index 8c7fe60217e..be9dc186fef 100644
--- a/src/test/isolation/expected/stats.out
+++ b/src/test/isolation/expected/stats.out
@@ -1295,10 +1295,10 @@ name |pg_stat_get_function_calls|total_above_zero|self_above_zero
test_stat_func| 2|t |t
(1 row)
-step s1_reset: SELECT pg_stat_reset();
-pg_stat_reset
--------------
-
+step s1_reset: SELECT pg_stat_reset() IS NOT NULL AS t;
+t
+-
+t
(1 row)
step s1_func_stats:
diff --git a/src/test/isolation/expected/stats_1.out b/src/test/isolation/expected/stats_1.out
index 6b965bb9553..3017c6f4619 100644
--- a/src/test/isolation/expected/stats_1.out
+++ b/src/test/isolation/expected/stats_1.out
@@ -1295,10 +1295,10 @@ name |pg_stat_get_function_calls|total_above_zero|self_above_zero
test_stat_func| 2|t |t
(1 row)
-step s1_reset: SELECT pg_stat_reset();
-pg_stat_reset
--------------
-
+step s1_reset: SELECT pg_stat_reset() IS NOT NULL AS t;
+t
+-
+t
(1 row)
step s1_func_stats:
diff --git a/src/test/isolation/specs/stats.spec b/src/test/isolation/specs/stats.spec
index 1d98ac785b8..22ef6d3206b 100644
--- a/src/test/isolation/specs/stats.spec
+++ b/src/test/isolation/specs/stats.spec
@@ -56,7 +56,7 @@ step s1_func_call { SELECT test_stat_func(); }
step s1_func_drop { DROP FUNCTION test_stat_func(); }
step s1_func_stats_reset { SELECT pg_stat_reset_single_function_counters('test_stat_func'::regproc); }
step s1_func_stats_reset_nonexistent { SELECT pg_stat_reset_single_function_counters(12000); }
-step s1_reset { SELECT pg_stat_reset(); }
+step s1_reset { SELECT pg_stat_reset() IS NOT NULL AS t; }
step s1_func_stats {
SELECT
tso.name,
diff --git a/src/test/regress/expected/stats.out b/src/test/regress/expected/stats.out
index 605f5070376..5db2d81afb1 100644
--- a/src/test/regress/expected/stats.out
+++ b/src/test/regress/expected/stats.out
@@ -1102,17 +1102,17 @@ ERROR: unrecognized reset target: "unknown"
HINT: Target must be "archiver", "bgwriter", "checkpointer", "io", "recovery_prefetch", "slru", or "wal".
-- Test that reset works for pg_stat_database
-- Since pg_stat_database stats_reset starts out as NULL, reset it once first so we have something to compare it to
-SELECT pg_stat_reset();
- pg_stat_reset
----------------
-
+SELECT pg_stat_reset() IS NOT NULL AS t;
+ t
+---
+ t
(1 row)
SELECT stats_reset AS db_reset_ts FROM pg_stat_database WHERE datname = (SELECT current_database()) \gset
-SELECT pg_stat_reset();
- pg_stat_reset
----------------
-
+SELECT pg_stat_reset() IS NOT NULL AS t;
+ t
+---
+ t
(1 row)
SELECT stats_reset > :'db_reset_ts'::timestamptz FROM pg_stat_database WHERE datname = (SELECT current_database());
diff --git a/src/test/regress/sql/stats.sql b/src/test/regress/sql/stats.sql
index 54e72866344..5c77ccc1319 100644
--- a/src/test/regress/sql/stats.sql
+++ b/src/test/regress/sql/stats.sql
@@ -512,9 +512,9 @@ SELECT pg_stat_reset_shared('unknown');
-- Test that reset works for pg_stat_database
-- Since pg_stat_database stats_reset starts out as NULL, reset it once first so we have something to compare it to
-SELECT pg_stat_reset();
+SELECT pg_stat_reset() IS NOT NULL AS t;
SELECT stats_reset AS db_reset_ts FROM pg_stat_database WHERE datname = (SELECT current_database()) \gset
-SELECT pg_stat_reset();
+SELECT pg_stat_reset() IS NOT NULL AS t;
SELECT stats_reset > :'db_reset_ts'::timestamptz FROM pg_stat_database WHERE datname = (SELECT current_database());
--
2.47.1
v1-0002-Make-pg_stat_reset_shared-return-the-reset-time.patchapplication/octet-stream; name=v1-0002-Make-pg_stat_reset_shared-return-the-reset-time.patchDownload
From a1a2cab465199c5b96a50637c70fc7b6354bb6ba Mon Sep 17 00:00:00 2001
From: Shinya Kato <shinya11.kato@gmail.com>
Date: Fri, 2 May 2025 14:58:07 +0900
Subject: [PATCH v1 2/9] Make pg_stat_reset_shared() return the reset time
---
doc/src/sgml/monitoring.sgml | 5 +-
src/backend/access/transam/xlogprefetcher.c | 6 ++-
src/backend/catalog/system_functions.sql | 2 +-
src/backend/utils/activity/pgstat.c | 5 +-
src/backend/utils/adt/pgstatfuncs.c | 21 ++++----
src/include/access/xlogprefetcher.h | 3 +-
src/include/catalog/pg_proc.dat | 2 +-
src/include/pgstat.h | 2 +-
src/test/regress/expected/stats.out | 56 ++++++++++-----------
src/test/regress/sql/stats.sql | 14 +++---
10 files changed, 61 insertions(+), 55 deletions(-)
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index b72b840048b..521ad113012 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -5026,11 +5026,12 @@ description | Waiting for a newly initialized WAL file to reach durable storage
<primary>pg_stat_reset_shared</primary>
</indexterm>
<function>pg_stat_reset_shared</function> ( [ <parameter>target</parameter> <type>text</type> <literal>DEFAULT</literal> <literal>NULL</literal> ] )
- <returnvalue>void</returnvalue>
+ <returnvalue>timestamp with time zone</returnvalue>
</para>
<para>
Resets some cluster-wide statistics counters to zero, depending on the
- argument. <parameter>target</parameter> can be:
+ argument, and returns the time of the reset.
+ <parameter>target</parameter> can be:
<itemizedlist>
<listitem>
<para>
diff --git a/src/backend/access/transam/xlogprefetcher.c b/src/backend/access/transam/xlogprefetcher.c
index ed3aacabc98..bc033ae1842 100644
--- a/src/backend/access/transam/xlogprefetcher.c
+++ b/src/backend/access/transam/xlogprefetcher.c
@@ -299,16 +299,18 @@ XLogPrefetchShmemSize(void)
/*
* Reset all counters to zero.
*/
-void
+TimestampTz
XLogPrefetchResetStats(void)
{
- pg_atomic_write_u64(&SharedStats->reset_time, GetCurrentTimestamp());
+ TimestampTz ts = GetCurrentTimestamp();
+ pg_atomic_write_u64(&SharedStats->reset_time, ts);
pg_atomic_write_u64(&SharedStats->prefetch, 0);
pg_atomic_write_u64(&SharedStats->hit, 0);
pg_atomic_write_u64(&SharedStats->skip_init, 0);
pg_atomic_write_u64(&SharedStats->skip_new, 0);
pg_atomic_write_u64(&SharedStats->skip_fpw, 0);
pg_atomic_write_u64(&SharedStats->skip_rep, 0);
+ return ts;
}
void
diff --git a/src/backend/catalog/system_functions.sql b/src/backend/catalog/system_functions.sql
index 566f308e443..0f19c2c478b 100644
--- a/src/backend/catalog/system_functions.sql
+++ b/src/backend/catalog/system_functions.sql
@@ -638,7 +638,7 @@ AS 'unicode_is_normalized';
CREATE OR REPLACE FUNCTION
pg_stat_reset_shared(target text DEFAULT NULL)
-RETURNS void
+RETURNS timestamp with time zone
LANGUAGE INTERNAL
CALLED ON NULL INPUT VOLATILE PARALLEL SAFE
AS 'pg_stat_reset_shared';
diff --git a/src/backend/utils/activity/pgstat.c b/src/backend/utils/activity/pgstat.c
index 707d55abdb6..00b1ed31cef 100644
--- a/src/backend/utils/activity/pgstat.c
+++ b/src/backend/utils/activity/pgstat.c
@@ -873,7 +873,7 @@ pgstat_reset(PgStat_Kind kind, Oid dboid, uint64 objid)
* Permission checking for this function is managed through the normal
* GRANT system.
*/
-void
+TimestampTz
pgstat_reset_of_kind(PgStat_Kind kind)
{
const PgStat_KindInfo *kind_info = pgstat_get_kind_info(kind);
@@ -883,8 +883,9 @@ pgstat_reset_of_kind(PgStat_Kind kind)
kind_info->reset_all_cb(ts);
else
pgstat_reset_entries_of_kind(kind, ts);
-}
+ return ts;
+}
/* ------------------------------------------------------------
* Fetching of stats
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index 56c5dc4efce..36404fac4c6 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -1872,6 +1872,7 @@ Datum
pg_stat_reset_shared(PG_FUNCTION_ARGS)
{
char *target = NULL;
+ TimestampTz ts;
if (PG_ARGISNULL(0))
{
@@ -1882,34 +1883,34 @@ pg_stat_reset_shared(PG_FUNCTION_ARGS)
pgstat_reset_of_kind(PGSTAT_KIND_IO);
XLogPrefetchResetStats();
pgstat_reset_of_kind(PGSTAT_KIND_SLRU);
- pgstat_reset_of_kind(PGSTAT_KIND_WAL);
+ ts = pgstat_reset_of_kind(PGSTAT_KIND_WAL);
- PG_RETURN_VOID();
+ PG_RETURN_TIMESTAMPTZ(ts);
}
target = text_to_cstring(PG_GETARG_TEXT_PP(0));
if (strcmp(target, "archiver") == 0)
- pgstat_reset_of_kind(PGSTAT_KIND_ARCHIVER);
+ ts = pgstat_reset_of_kind(PGSTAT_KIND_ARCHIVER);
else if (strcmp(target, "bgwriter") == 0)
- pgstat_reset_of_kind(PGSTAT_KIND_BGWRITER);
+ ts = pgstat_reset_of_kind(PGSTAT_KIND_BGWRITER);
else if (strcmp(target, "checkpointer") == 0)
- pgstat_reset_of_kind(PGSTAT_KIND_CHECKPOINTER);
+ ts = pgstat_reset_of_kind(PGSTAT_KIND_CHECKPOINTER);
else if (strcmp(target, "io") == 0)
- pgstat_reset_of_kind(PGSTAT_KIND_IO);
+ ts = pgstat_reset_of_kind(PGSTAT_KIND_IO);
else if (strcmp(target, "recovery_prefetch") == 0)
- XLogPrefetchResetStats();
+ ts = XLogPrefetchResetStats();
else if (strcmp(target, "slru") == 0)
- pgstat_reset_of_kind(PGSTAT_KIND_SLRU);
+ ts = pgstat_reset_of_kind(PGSTAT_KIND_SLRU);
else if (strcmp(target, "wal") == 0)
- pgstat_reset_of_kind(PGSTAT_KIND_WAL);
+ ts = pgstat_reset_of_kind(PGSTAT_KIND_WAL);
else
ereport(ERROR,
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
errmsg("unrecognized reset target: \"%s\"", target),
errhint("Target must be \"archiver\", \"bgwriter\", \"checkpointer\", \"io\", \"recovery_prefetch\", \"slru\", or \"wal\".")));
- PG_RETURN_VOID();
+ PG_RETURN_TIMESTAMPTZ(ts);
}
/*
diff --git a/src/include/access/xlogprefetcher.h b/src/include/access/xlogprefetcher.h
index 50b39c1fb0d..6cc95b70c6e 100644
--- a/src/include/access/xlogprefetcher.h
+++ b/src/include/access/xlogprefetcher.h
@@ -16,6 +16,7 @@
#include "access/xlogdefs.h"
#include "access/xlogreader.h"
#include "access/xlogrecord.h"
+#include "utils/timestamp.h"
/* GUCs */
extern PGDLLIMPORT int recovery_prefetch;
@@ -37,7 +38,7 @@ extern void XLogPrefetchReconfigure(void);
extern size_t XLogPrefetchShmemSize(void);
extern void XLogPrefetchShmemInit(void);
-extern void XLogPrefetchResetStats(void);
+extern TimestampTz XLogPrefetchResetStats(void);
extern XLogPrefetcher *XLogPrefetcherAllocate(XLogReaderState *reader);
extern void XLogPrefetcherFree(XLogPrefetcher *prefetcher);
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 7f3b314875d..9f86ad80ade 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -6144,7 +6144,7 @@
{ oid => '3775',
descr => 'statistics: reset collected statistics shared across the cluster',
proname => 'pg_stat_reset_shared', proisstrict => 'f', provolatile => 'v',
- prorettype => 'void', proargtypes => 'text',
+ prorettype => 'timestamptz', proargtypes => 'text',
prosrc => 'pg_stat_reset_shared' },
{ oid => '3776',
descr => 'statistics: reset collected statistics for a single table or index in the current database or shared across all databases in the cluster',
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index 62a4db726df..d89175927ee 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -526,7 +526,7 @@ extern void pgstat_force_next_flush(void);
extern TimestampTz pgstat_reset_counters(void);
extern void pgstat_reset(PgStat_Kind kind, Oid dboid, uint64 objid);
-extern void pgstat_reset_of_kind(PgStat_Kind kind);
+extern TimestampTz pgstat_reset_of_kind(PgStat_Kind kind);
/* stats accessors */
extern void pgstat_clear_snapshot(void);
diff --git a/src/test/regress/expected/stats.out b/src/test/regress/expected/stats.out
index 5db2d81afb1..34a2351e29b 100644
--- a/src/test/regress/expected/stats.out
+++ b/src/test/regress/expected/stats.out
@@ -1014,10 +1014,10 @@ SELECT stats_reset > :'slru_notify_reset_ts'::timestamptz FROM pg_stat_slru WHER
-- Test that reset_shared with archiver specified as the stats type works
SELECT stats_reset AS archiver_reset_ts FROM pg_stat_archiver \gset
-SELECT pg_stat_reset_shared('archiver');
- pg_stat_reset_shared
-----------------------
-
+SELECT pg_stat_reset_shared('archiver') IS NOT NULL AS t;
+ t
+---
+ t
(1 row)
SELECT stats_reset > :'archiver_reset_ts'::timestamptz FROM pg_stat_archiver;
@@ -1028,10 +1028,10 @@ SELECT stats_reset > :'archiver_reset_ts'::timestamptz FROM pg_stat_archiver;
-- Test that reset_shared with bgwriter specified as the stats type works
SELECT stats_reset AS bgwriter_reset_ts FROM pg_stat_bgwriter \gset
-SELECT pg_stat_reset_shared('bgwriter');
- pg_stat_reset_shared
-----------------------
-
+SELECT pg_stat_reset_shared('bgwriter') IS NOT NULL AS t;
+ t
+---
+ t
(1 row)
SELECT stats_reset > :'bgwriter_reset_ts'::timestamptz FROM pg_stat_bgwriter;
@@ -1042,10 +1042,10 @@ SELECT stats_reset > :'bgwriter_reset_ts'::timestamptz FROM pg_stat_bgwriter;
-- Test that reset_shared with checkpointer specified as the stats type works
SELECT stats_reset AS checkpointer_reset_ts FROM pg_stat_checkpointer \gset
-SELECT pg_stat_reset_shared('checkpointer');
- pg_stat_reset_shared
-----------------------
-
+SELECT pg_stat_reset_shared('checkpointer') IS NOT NULL AS t;
+ t
+---
+ t
(1 row)
SELECT stats_reset > :'checkpointer_reset_ts'::timestamptz FROM pg_stat_checkpointer;
@@ -1056,10 +1056,10 @@ SELECT stats_reset > :'checkpointer_reset_ts'::timestamptz FROM pg_stat_checkpoi
-- Test that reset_shared with recovery_prefetch specified as the stats type works
SELECT stats_reset AS recovery_prefetch_reset_ts FROM pg_stat_recovery_prefetch \gset
-SELECT pg_stat_reset_shared('recovery_prefetch');
- pg_stat_reset_shared
-----------------------
-
+SELECT pg_stat_reset_shared('recovery_prefetch') IS NOT NULL AS t;
+ t
+---
+ t
(1 row)
SELECT stats_reset > :'recovery_prefetch_reset_ts'::timestamptz FROM pg_stat_recovery_prefetch;
@@ -1070,10 +1070,10 @@ SELECT stats_reset > :'recovery_prefetch_reset_ts'::timestamptz FROM pg_stat_rec
-- Test that reset_shared with slru specified as the stats type works
SELECT max(stats_reset) AS slru_reset_ts FROM pg_stat_slru \gset
-SELECT pg_stat_reset_shared('slru');
- pg_stat_reset_shared
-----------------------
-
+SELECT pg_stat_reset_shared('slru') IS NOT NULL AS t;
+ t
+---
+ t
(1 row)
SELECT max(stats_reset) > :'slru_reset_ts'::timestamptz FROM pg_stat_slru;
@@ -1084,10 +1084,10 @@ SELECT max(stats_reset) > :'slru_reset_ts'::timestamptz FROM pg_stat_slru;
-- Test that reset_shared with wal specified as the stats type works
SELECT stats_reset AS wal_reset_ts FROM pg_stat_wal \gset
-SELECT pg_stat_reset_shared('wal');
- pg_stat_reset_shared
-----------------------
-
+SELECT pg_stat_reset_shared('wal') IS NOT NULL AS t;
+ t
+---
+ t
(1 row)
SELECT stats_reset > :'wal_reset_ts'::timestamptz FROM pg_stat_wal;
@@ -1696,10 +1696,10 @@ SELECT sum(evictions) + sum(reuses) + sum(extends) + sum(fsyncs) + sum(reads) +
FROM pg_stat_io \gset
SELECT sum(evictions) + sum(reuses) + sum(extends) + sum(fsyncs) + sum(reads) + sum(writes) + sum(writebacks) + sum(hits) AS my_io_stats_pre_reset
FROM pg_stat_get_backend_io(pg_backend_pid()) \gset
-SELECT pg_stat_reset_shared('io');
- pg_stat_reset_shared
-----------------------
-
+SELECT pg_stat_reset_shared('io') IS NOT NULL AS t;
+ t
+---
+ t
(1 row)
SELECT sum(evictions) + sum(reuses) + sum(extends) + sum(fsyncs) + sum(reads) + sum(writes) + sum(writebacks) + sum(hits) AS io_stats_post_reset
diff --git a/src/test/regress/sql/stats.sql b/src/test/regress/sql/stats.sql
index 5c77ccc1319..8249c2d4d89 100644
--- a/src/test/regress/sql/stats.sql
+++ b/src/test/regress/sql/stats.sql
@@ -478,32 +478,32 @@ SELECT stats_reset > :'slru_notify_reset_ts'::timestamptz FROM pg_stat_slru WHER
-- Test that reset_shared with archiver specified as the stats type works
SELECT stats_reset AS archiver_reset_ts FROM pg_stat_archiver \gset
-SELECT pg_stat_reset_shared('archiver');
+SELECT pg_stat_reset_shared('archiver') IS NOT NULL AS t;
SELECT stats_reset > :'archiver_reset_ts'::timestamptz FROM pg_stat_archiver;
-- Test that reset_shared with bgwriter specified as the stats type works
SELECT stats_reset AS bgwriter_reset_ts FROM pg_stat_bgwriter \gset
-SELECT pg_stat_reset_shared('bgwriter');
+SELECT pg_stat_reset_shared('bgwriter') IS NOT NULL AS t;
SELECT stats_reset > :'bgwriter_reset_ts'::timestamptz FROM pg_stat_bgwriter;
-- Test that reset_shared with checkpointer specified as the stats type works
SELECT stats_reset AS checkpointer_reset_ts FROM pg_stat_checkpointer \gset
-SELECT pg_stat_reset_shared('checkpointer');
+SELECT pg_stat_reset_shared('checkpointer') IS NOT NULL AS t;
SELECT stats_reset > :'checkpointer_reset_ts'::timestamptz FROM pg_stat_checkpointer;
-- Test that reset_shared with recovery_prefetch specified as the stats type works
SELECT stats_reset AS recovery_prefetch_reset_ts FROM pg_stat_recovery_prefetch \gset
-SELECT pg_stat_reset_shared('recovery_prefetch');
+SELECT pg_stat_reset_shared('recovery_prefetch') IS NOT NULL AS t;
SELECT stats_reset > :'recovery_prefetch_reset_ts'::timestamptz FROM pg_stat_recovery_prefetch;
-- Test that reset_shared with slru specified as the stats type works
SELECT max(stats_reset) AS slru_reset_ts FROM pg_stat_slru \gset
-SELECT pg_stat_reset_shared('slru');
+SELECT pg_stat_reset_shared('slru') IS NOT NULL AS t;
SELECT max(stats_reset) > :'slru_reset_ts'::timestamptz FROM pg_stat_slru;
-- Test that reset_shared with wal specified as the stats type works
SELECT stats_reset AS wal_reset_ts FROM pg_stat_wal \gset
-SELECT pg_stat_reset_shared('wal');
+SELECT pg_stat_reset_shared('wal') IS NOT NULL AS t;
SELECT stats_reset > :'wal_reset_ts'::timestamptz FROM pg_stat_wal;
-- Test error case for reset_shared with unknown stats type
@@ -813,7 +813,7 @@ SELECT sum(evictions) + sum(reuses) + sum(extends) + sum(fsyncs) + sum(reads) +
FROM pg_stat_io \gset
SELECT sum(evictions) + sum(reuses) + sum(extends) + sum(fsyncs) + sum(reads) + sum(writes) + sum(writebacks) + sum(hits) AS my_io_stats_pre_reset
FROM pg_stat_get_backend_io(pg_backend_pid()) \gset
-SELECT pg_stat_reset_shared('io');
+SELECT pg_stat_reset_shared('io') IS NOT NULL AS t;
SELECT sum(evictions) + sum(reuses) + sum(extends) + sum(fsyncs) + sum(reads) + sum(writes) + sum(writebacks) + sum(hits) AS io_stats_post_reset
FROM pg_stat_io \gset
SELECT :io_stats_post_reset < :io_stats_pre_reset;
--
2.47.1
v1-0003-Make-pg_stat_reset_single_table_counters-return-t.patchapplication/octet-stream; name=v1-0003-Make-pg_stat_reset_single_table_counters-return-t.patchDownload
From 7449c99e186d487da3e812ab093a52531bb64f5b Mon Sep 17 00:00:00 2001
From: Shinya Kato <shinya11.kato@gmail.com>
Date: Fri, 2 May 2025 15:30:39 +0900
Subject: [PATCH v1 3/9] Make pg_stat_reset_single_table_counters() return the
reset time
---
doc/src/sgml/monitoring.sgml | 5 +++--
src/backend/utils/activity/pgstat.c | 4 +++-
src/backend/utils/adt/pgstatfuncs.c | 5 +++--
src/include/catalog/pg_proc.dat | 2 +-
src/include/pgstat.h | 2 +-
src/test/regress/expected/stats.out | 16 ++++++++--------
src/test/regress/sql/stats.sql | 4 ++--
7 files changed, 21 insertions(+), 17 deletions(-)
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index 521ad113012..99fdc8aabac 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -5095,11 +5095,12 @@ description | Waiting for a newly initialized WAL file to reach durable storage
<primary>pg_stat_reset_single_table_counters</primary>
</indexterm>
<function>pg_stat_reset_single_table_counters</function> ( <type>oid</type> )
- <returnvalue>void</returnvalue>
+ <returnvalue>timestamp with time zone</returnvalue>
</para>
<para>
Resets statistics for a single table or index in the current database
- or shared across all databases in the cluster to zero.
+ or shared across all databases in the cluster to zero, and returns the
+ time of the reset.
</para>
<para>
This function is restricted to superusers by default, but other users
diff --git a/src/backend/utils/activity/pgstat.c b/src/backend/utils/activity/pgstat.c
index 00b1ed31cef..aecf9fbdcd1 100644
--- a/src/backend/utils/activity/pgstat.c
+++ b/src/backend/utils/activity/pgstat.c
@@ -851,7 +851,7 @@ pgstat_reset_counters(void)
* Permission checking for this function is managed through the normal
* GRANT system.
*/
-void
+TimestampTz
pgstat_reset(PgStat_Kind kind, Oid dboid, uint64 objid)
{
const PgStat_KindInfo *kind_info = pgstat_get_kind_info(kind);
@@ -865,6 +865,8 @@ pgstat_reset(PgStat_Kind kind, Oid dboid, uint64 objid)
if (!kind_info->accessed_across_databases)
pgstat_reset_database_timestamp(dboid, ts);
+
+ return ts;
}
/*
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index 36404fac4c6..bc985f6eaad 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -1922,10 +1922,11 @@ pg_stat_reset_single_table_counters(PG_FUNCTION_ARGS)
{
Oid taboid = PG_GETARG_OID(0);
Oid dboid = (IsSharedRelation(taboid) ? InvalidOid : MyDatabaseId);
+ TimestampTz ts;
- pgstat_reset(PGSTAT_KIND_RELATION, dboid, taboid);
+ ts = pgstat_reset(PGSTAT_KIND_RELATION, dboid, taboid);
- PG_RETURN_VOID();
+ PG_RETURN_TIMESTAMPTZ(ts);
}
Datum
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 9f86ad80ade..9077c313bfd 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -6149,7 +6149,7 @@
{ oid => '3776',
descr => 'statistics: reset collected statistics for a single table or index in the current database or shared across all databases in the cluster',
proname => 'pg_stat_reset_single_table_counters', provolatile => 'v',
- prorettype => 'void', proargtypes => 'oid',
+ prorettype => 'timestamptz', proargtypes => 'oid',
prosrc => 'pg_stat_reset_single_table_counters' },
{ oid => '3777',
descr => 'statistics: reset collected statistics for a single function in the current database',
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index d89175927ee..ead16d7dad2 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -525,7 +525,7 @@ extern long pgstat_report_stat(bool force);
extern void pgstat_force_next_flush(void);
extern TimestampTz pgstat_reset_counters(void);
-extern void pgstat_reset(PgStat_Kind kind, Oid dboid, uint64 objid);
+extern TimestampTz pgstat_reset(PgStat_Kind kind, Oid dboid, uint64 objid);
extern TimestampTz pgstat_reset_of_kind(PgStat_Kind kind);
/* stats accessors */
diff --git a/src/test/regress/expected/stats.out b/src/test/regress/expected/stats.out
index 34a2351e29b..983930e5009 100644
--- a/src/test/regress/expected/stats.out
+++ b/src/test/regress/expected/stats.out
@@ -666,10 +666,10 @@ SELECT last_seq_scan, last_idx_scan FROM pg_stat_all_tables WHERE relid = 'test_
(1 row)
COMMIT;
-SELECT pg_stat_reset_single_table_counters('test_last_scan'::regclass);
- pg_stat_reset_single_table_counters
--------------------------------------
-
+SELECT pg_stat_reset_single_table_counters('test_last_scan'::regclass) IS NOT NULL AS t;
+ t
+---
+ t
(1 row)
SELECT seq_scan, idx_scan FROM pg_stat_all_tables WHERE relid = 'test_last_scan'::regclass;
@@ -877,10 +877,10 @@ SELECT (n_tup_ins + n_tup_upd) > 0 AS has_data FROM pg_stat_all_tables
t
(1 row)
-SELECT pg_stat_reset_single_table_counters('pg_shdescription'::regclass);
- pg_stat_reset_single_table_counters
--------------------------------------
-
+SELECT pg_stat_reset_single_table_counters('pg_shdescription'::regclass) IS NOT NULL AS t;
+ t
+---
+ t
(1 row)
SELECT (n_tup_ins + n_tup_upd) > 0 AS has_data FROM pg_stat_all_tables
diff --git a/src/test/regress/sql/stats.sql b/src/test/regress/sql/stats.sql
index 8249c2d4d89..1801b0fec9a 100644
--- a/src/test/regress/sql/stats.sql
+++ b/src/test/regress/sql/stats.sql
@@ -312,7 +312,7 @@ SELECT pg_stat_force_next_flush();
SELECT last_seq_scan, last_idx_scan FROM pg_stat_all_tables WHERE relid = 'test_last_scan'::regclass;
COMMIT;
-SELECT pg_stat_reset_single_table_counters('test_last_scan'::regclass);
+SELECT pg_stat_reset_single_table_counters('test_last_scan'::regclass) IS NOT NULL AS t;
SELECT seq_scan, idx_scan FROM pg_stat_all_tables WHERE relid = 'test_last_scan'::regclass;
-- ensure we start out with exactly one index and sequential scan
@@ -402,7 +402,7 @@ COMMIT;
-- check that the stats are reset.
SELECT (n_tup_ins + n_tup_upd) > 0 AS has_data FROM pg_stat_all_tables
WHERE relid = 'pg_shdescription'::regclass;
-SELECT pg_stat_reset_single_table_counters('pg_shdescription'::regclass);
+SELECT pg_stat_reset_single_table_counters('pg_shdescription'::regclass) IS NOT NULL AS t;
SELECT (n_tup_ins + n_tup_upd) > 0 AS has_data FROM pg_stat_all_tables
WHERE relid = 'pg_shdescription'::regclass;
--
2.47.1
v1-0004-Make-pg_stat_reset_backend_stats-return-the-reset.patchapplication/octet-stream; name=v1-0004-Make-pg_stat_reset_backend_stats-return-the-reset.patchDownload
From 8b7676f1dffcc71bc3a42b9011143470ffe12c3e Mon Sep 17 00:00:00 2001
From: Shinya Kato <shinya11.kato@gmail.com>
Date: Fri, 2 May 2025 15:41:00 +0900
Subject: [PATCH v1 4/9] Make pg_stat_reset_backend_stats() return the reset
time
---
doc/src/sgml/monitoring.sgml | 5 +++--
src/backend/utils/adt/pgstatfuncs.c | 11 ++++++-----
src/include/catalog/pg_proc.dat | 2 +-
src/test/regress/expected/stats.out | 9 ++++++++-
src/test/regress/sql/stats.sql | 4 +++-
5 files changed, 21 insertions(+), 10 deletions(-)
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index 99fdc8aabac..f48aefd054c 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -5114,11 +5114,12 @@ description | Waiting for a newly initialized WAL file to reach durable storage
<primary>pg_stat_reset_backend_stats</primary>
</indexterm>
<function>pg_stat_reset_backend_stats</function> ( <type>integer</type> )
- <returnvalue>void</returnvalue>
+ <returnvalue>timestamp with time zone</returnvalue>
</para>
<para>
Resets statistics for a single backend with the specified process ID
- to zero.
+ to zero, and returns the time of the reset or <literal>NULL</literal>
+ if an invalid backend process ID is specified.
</para>
<para>
This function is restricted to superusers by default, but other users
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index bc985f6eaad..8350233dcaf 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -1948,6 +1948,7 @@ pg_stat_reset_backend_stats(PG_FUNCTION_ARGS)
PGPROC *proc;
PgBackendStatus *beentry;
ProcNumber procNumber;
+ TimestampTz ts;
int backend_pid = PG_GETARG_INT32(0);
proc = BackendPidGetProc(backend_pid);
@@ -1957,21 +1958,21 @@ pg_stat_reset_backend_stats(PG_FUNCTION_ARGS)
proc = AuxiliaryPidGetProc(backend_pid);
if (!proc)
- PG_RETURN_VOID();
+ PG_RETURN_NULL();
procNumber = GetNumberFromPGProc(proc);
beentry = pgstat_get_beentry_by_proc_number(procNumber);
if (!beentry)
- PG_RETURN_VOID();
+ PG_RETURN_NULL();
/* Check if the backend type tracks statistics */
if (!pgstat_tracks_backend_bktype(beentry->st_backendType))
- PG_RETURN_VOID();
+ PG_RETURN_NULL();
- pgstat_reset(PGSTAT_KIND_BACKEND, InvalidOid, procNumber);
+ ts = pgstat_reset(PGSTAT_KIND_BACKEND, InvalidOid, procNumber);
- PG_RETURN_VOID();
+ PG_RETURN_TIMESTAMPTZ(ts);
}
/* Reset SLRU counters (a specific one or all of them). */
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 9077c313bfd..40e868b9714 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -6158,7 +6158,7 @@
prosrc => 'pg_stat_reset_single_function_counters' },
{ oid => '6387', descr => 'statistics: reset statistics for a single backend',
proname => 'pg_stat_reset_backend_stats', provolatile => 'v',
- prorettype => 'void', proargtypes => 'int4',
+ prorettype => 'timestamptz', proargtypes => 'int4',
prosrc => 'pg_stat_reset_backend_stats' },
{ oid => '2307',
descr => 'statistics: reset collected statistics for a single SLRU',
diff --git a/src/test/regress/expected/stats.out b/src/test/regress/expected/stats.out
index 983930e5009..91b3be1a8af 100644
--- a/src/test/regress/expected/stats.out
+++ b/src/test/regress/expected/stats.out
@@ -1720,7 +1720,14 @@ SELECT :my_io_stats_pre_reset <= :my_io_stats_post_reset;
(1 row)
-- but pg_stat_reset_backend_stats() does
-SELECT pg_stat_reset_backend_stats(pg_backend_pid());
+SELECT pg_stat_reset_backend_stats(pg_backend_pid()) IS NOT NULL AS t;
+ t
+---
+ t
+(1 row)
+
+-- Invalid backend process ID is specified
+SELECT pg_stat_reset_backend_stats(0);
pg_stat_reset_backend_stats
-----------------------------
diff --git a/src/test/regress/sql/stats.sql b/src/test/regress/sql/stats.sql
index 1801b0fec9a..77d3e1a845d 100644
--- a/src/test/regress/sql/stats.sql
+++ b/src/test/regress/sql/stats.sql
@@ -822,7 +822,9 @@ SELECT sum(evictions) + sum(reuses) + sum(extends) + sum(fsyncs) + sum(reads) +
-- pg_stat_reset_shared() did not reset backend IO stats
SELECT :my_io_stats_pre_reset <= :my_io_stats_post_reset;
-- but pg_stat_reset_backend_stats() does
-SELECT pg_stat_reset_backend_stats(pg_backend_pid());
+SELECT pg_stat_reset_backend_stats(pg_backend_pid()) IS NOT NULL AS t;
+-- Invalid backend process ID is specified
+SELECT pg_stat_reset_backend_stats(0);
SELECT sum(evictions) + sum(reuses) + sum(extends) + sum(fsyncs) + sum(reads) + sum(writes) + sum(writebacks) + sum(hits) AS my_io_stats_post_backend_reset
FROM pg_stat_get_backend_io(pg_backend_pid()) \gset
SELECT :my_io_stats_pre_reset > :my_io_stats_post_backend_reset;
--
2.47.1
v1-0005-Make-pg_stat_reset_single_function_counters-retur.patchapplication/octet-stream; name=v1-0005-Make-pg_stat_reset_single_function_counters-retur.patchDownload
From 131880187b0435cf309f64afa13c4219836d286c Mon Sep 17 00:00:00 2001
From: Shinya Kato <shinya11.kato@gmail.com>
Date: Fri, 2 May 2025 16:42:00 +0900
Subject: [PATCH v1 5/9] Make pg_stat_reset_single_function_counters() return
the reset time
---
doc/src/sgml/monitoring.sgml | 4 ++--
src/backend/utils/adt/pgstatfuncs.c | 5 +++--
src/include/catalog/pg_proc.dat | 2 +-
src/test/isolation/expected/stats.out | 16 ++++++++--------
src/test/isolation/expected/stats_1.out | 16 ++++++++--------
src/test/isolation/specs/stats.spec | 4 ++--
6 files changed, 24 insertions(+), 23 deletions(-)
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index f48aefd054c..2aeb835a7ab 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -5133,11 +5133,11 @@ description | Waiting for a newly initialized WAL file to reach durable storage
<primary>pg_stat_reset_single_function_counters</primary>
</indexterm>
<function>pg_stat_reset_single_function_counters</function> ( <type>oid</type> )
- <returnvalue>void</returnvalue>
+ <returnvalue>timestamp with time zone</returnvalue>
</para>
<para>
Resets statistics for a single function in the current database to
- zero.
+ zero, and returns the time of the reset.
</para>
<para>
This function is restricted to superusers by default, but other users
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index 8350233dcaf..f987d91d040 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -1933,10 +1933,11 @@ Datum
pg_stat_reset_single_function_counters(PG_FUNCTION_ARGS)
{
Oid funcoid = PG_GETARG_OID(0);
+ TimestampTz ts;
- pgstat_reset(PGSTAT_KIND_FUNCTION, MyDatabaseId, funcoid);
+ ts = pgstat_reset(PGSTAT_KIND_FUNCTION, MyDatabaseId, funcoid);
- PG_RETURN_VOID();
+ PG_RETURN_TIMESTAMPTZ(ts);
}
/*
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 40e868b9714..0a2079fd842 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -6154,7 +6154,7 @@
{ oid => '3777',
descr => 'statistics: reset collected statistics for a single function in the current database',
proname => 'pg_stat_reset_single_function_counters', provolatile => 'v',
- prorettype => 'void', proargtypes => 'oid',
+ prorettype => 'timestamptz', proargtypes => 'oid',
prosrc => 'pg_stat_reset_single_function_counters' },
{ oid => '6387', descr => 'statistics: reset statistics for a single backend',
proname => 'pg_stat_reset_backend_stats', provolatile => 'v',
diff --git a/src/test/isolation/expected/stats.out b/src/test/isolation/expected/stats.out
index be9dc186fef..132f8e55fc3 100644
--- a/src/test/isolation/expected/stats.out
+++ b/src/test/isolation/expected/stats.out
@@ -1137,10 +1137,10 @@ name |pg_stat_get_function_calls|total_above_zero|self_above_zero
test_stat_func| 3|t |t
(1 row)
-step s1_func_stats_reset: SELECT pg_stat_reset_single_function_counters('test_stat_func'::regproc);
-pg_stat_reset_single_function_counters
---------------------------------------
-
+step s1_func_stats_reset: SELECT pg_stat_reset_single_function_counters('test_stat_func'::regproc) IS NOT NULL AS t;
+t
+-
+t
(1 row)
step s1_func_stats:
@@ -1200,10 +1200,10 @@ pg_stat_get_function_calls
(1 row)
-step s1_func_stats_reset_nonexistent: SELECT pg_stat_reset_single_function_counters(12000);
-pg_stat_reset_single_function_counters
---------------------------------------
-
+step s1_func_stats_reset_nonexistent: SELECT pg_stat_reset_single_function_counters(12000) IS NOT NULL AS t;
+t
+-
+t
(1 row)
step s1_func_stats_nonexistent:
diff --git a/src/test/isolation/expected/stats_1.out b/src/test/isolation/expected/stats_1.out
index 3017c6f4619..2b16808fbb5 100644
--- a/src/test/isolation/expected/stats_1.out
+++ b/src/test/isolation/expected/stats_1.out
@@ -1137,10 +1137,10 @@ name |pg_stat_get_function_calls|total_above_zero|self_above_zero
test_stat_func| 3|t |t
(1 row)
-step s1_func_stats_reset: SELECT pg_stat_reset_single_function_counters('test_stat_func'::regproc);
-pg_stat_reset_single_function_counters
---------------------------------------
-
+step s1_func_stats_reset: SELECT pg_stat_reset_single_function_counters('test_stat_func'::regproc) IS NOT NULL AS t;
+t
+-
+t
(1 row)
step s1_func_stats:
@@ -1200,10 +1200,10 @@ pg_stat_get_function_calls
(1 row)
-step s1_func_stats_reset_nonexistent: SELECT pg_stat_reset_single_function_counters(12000);
-pg_stat_reset_single_function_counters
---------------------------------------
-
+step s1_func_stats_reset_nonexistent: SELECT pg_stat_reset_single_function_counters(12000) IS NOT NULL AS t;
+t
+-
+t
(1 row)
step s1_func_stats_nonexistent:
diff --git a/src/test/isolation/specs/stats.spec b/src/test/isolation/specs/stats.spec
index 22ef6d3206b..3893d3983f0 100644
--- a/src/test/isolation/specs/stats.spec
+++ b/src/test/isolation/specs/stats.spec
@@ -54,8 +54,8 @@ step s1_track_funcs_all { SET track_functions = 'all'; }
step s1_track_funcs_none { SET track_functions = 'none'; }
step s1_func_call { SELECT test_stat_func(); }
step s1_func_drop { DROP FUNCTION test_stat_func(); }
-step s1_func_stats_reset { SELECT pg_stat_reset_single_function_counters('test_stat_func'::regproc); }
-step s1_func_stats_reset_nonexistent { SELECT pg_stat_reset_single_function_counters(12000); }
+step s1_func_stats_reset { SELECT pg_stat_reset_single_function_counters('test_stat_func'::regproc) IS NOT NULL AS t; }
+step s1_func_stats_reset_nonexistent { SELECT pg_stat_reset_single_function_counters(12000) IS NOT NULL AS t; }
step s1_reset { SELECT pg_stat_reset() IS NOT NULL AS t; }
step s1_func_stats {
SELECT
--
2.47.1
v1-0006-Make-pg_stat_reset_slru-return-the-reset-time.patchapplication/octet-stream; name=v1-0006-Make-pg_stat_reset_slru-return-the-reset-time.patchDownload
From f11083f2f6e714f87dfb5adb0621394cb4891c9d Mon Sep 17 00:00:00 2001
From: Shinya Kato <shinya11.kato@gmail.com>
Date: Fri, 2 May 2025 16:57:23 +0900
Subject: [PATCH v1 6/9] Make pg_stat_reset_slru() return the reset time
---
doc/src/sgml/monitoring.sgml | 4 ++--
src/backend/catalog/system_functions.sql | 2 +-
src/backend/utils/activity/pgstat_slru.c | 4 +++-
src/backend/utils/adt/pgstatfuncs.c | 7 ++++---
src/include/catalog/pg_proc.dat | 2 +-
src/include/pgstat.h | 2 +-
src/test/regress/expected/stats.out | 16 ++++++++--------
src/test/regress/sql/stats.sql | 4 ++--
8 files changed, 22 insertions(+), 19 deletions(-)
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index 2aeb835a7ab..df90f4668c4 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -5151,11 +5151,11 @@ description | Waiting for a newly initialized WAL file to reach durable storage
<primary>pg_stat_reset_slru</primary>
</indexterm>
<function>pg_stat_reset_slru</function> ( [ <parameter>target</parameter> <type>text</type> <literal>DEFAULT</literal> <literal>NULL</literal> ] )
- <returnvalue>void</returnvalue>
+ <returnvalue>timestamp with time zone</returnvalue>
</para>
<para>
Resets statistics to zero for a single SLRU cache, or for all SLRUs in
- the cluster. If <parameter>target</parameter> is
+ the cluster, and returns the time of the reset. If <parameter>target</parameter> is
<literal>NULL</literal> or is not specified, all the counters shown in
the <structname>pg_stat_slru</structname> view for all SLRU caches are
reset. The argument can be one of
diff --git a/src/backend/catalog/system_functions.sql b/src/backend/catalog/system_functions.sql
index 0f19c2c478b..4aa93250d06 100644
--- a/src/backend/catalog/system_functions.sql
+++ b/src/backend/catalog/system_functions.sql
@@ -645,7 +645,7 @@ AS 'pg_stat_reset_shared';
CREATE OR REPLACE FUNCTION
pg_stat_reset_slru(target text DEFAULT NULL)
-RETURNS void
+RETURNS timestamp with time zone
LANGUAGE INTERNAL
CALLED ON NULL INPUT VOLATILE PARALLEL SAFE
AS 'pg_stat_reset_slru';
diff --git a/src/backend/utils/activity/pgstat_slru.c b/src/backend/utils/activity/pgstat_slru.c
index 7bd8744accb..b37a0e7391d 100644
--- a/src/backend/utils/activity/pgstat_slru.c
+++ b/src/backend/utils/activity/pgstat_slru.c
@@ -41,7 +41,7 @@ static bool have_slrustats = false;
* Permission checking for this function is managed through the normal
* GRANT system.
*/
-void
+TimestampTz
pgstat_reset_slru(const char *name)
{
TimestampTz ts = GetCurrentTimestamp();
@@ -49,6 +49,8 @@ pgstat_reset_slru(const char *name)
Assert(name != NULL);
pgstat_reset_slru_counter_internal(pgstat_get_slru_index(name), ts);
+
+ return ts;
}
/*
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index f987d91d040..b28ceab91d9 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -1981,16 +1981,17 @@ Datum
pg_stat_reset_slru(PG_FUNCTION_ARGS)
{
char *target = NULL;
+ TimestampTz ts;
if (PG_ARGISNULL(0))
- pgstat_reset_of_kind(PGSTAT_KIND_SLRU);
+ ts = pgstat_reset_of_kind(PGSTAT_KIND_SLRU);
else
{
target = text_to_cstring(PG_GETARG_TEXT_PP(0));
- pgstat_reset_slru(target);
+ ts = pgstat_reset_slru(target);
}
- PG_RETURN_VOID();
+ PG_RETURN_TIMESTAMPTZ(ts);
}
/* Reset replication slots stats (a specific one or all of them). */
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 0a2079fd842..6edc8ae67e4 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -6163,7 +6163,7 @@
{ oid => '2307',
descr => 'statistics: reset collected statistics for a single SLRU',
proname => 'pg_stat_reset_slru', proisstrict => 'f', provolatile => 'v',
- prorettype => 'void', proargtypes => 'text', proargnames => '{target}',
+ prorettype => 'timestamptz', proargtypes => 'text', proargnames => '{target}',
prosrc => 'pg_stat_reset_slru' },
{ oid => '6170',
descr => 'statistics: reset collected statistics for a single replication slot',
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index ead16d7dad2..809c00dec6f 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -746,7 +746,7 @@ extern PgStat_StatReplSlotEntry *pgstat_fetch_replslot(NameData slotname);
* Functions in pgstat_slru.c
*/
-extern void pgstat_reset_slru(const char *);
+extern TimestampTz pgstat_reset_slru(const char *);
extern void pgstat_count_slru_page_zeroed(int slru_idx);
extern void pgstat_count_slru_page_hit(int slru_idx);
extern void pgstat_count_slru_page_read(int slru_idx);
diff --git a/src/test/regress/expected/stats.out b/src/test/regress/expected/stats.out
index 91b3be1a8af..4c5612ab1ba 100644
--- a/src/test/regress/expected/stats.out
+++ b/src/test/regress/expected/stats.out
@@ -980,10 +980,10 @@ WHERE pg_stat_get_backend_pid(beid) = pg_backend_pid();
-- Test that reset_slru with a specified SLRU works.
SELECT stats_reset AS slru_commit_ts_reset_ts FROM pg_stat_slru WHERE name = 'commit_timestamp' \gset
SELECT stats_reset AS slru_notify_reset_ts FROM pg_stat_slru WHERE name = 'notify' \gset
-SELECT pg_stat_reset_slru('commit_timestamp');
- pg_stat_reset_slru
---------------------
-
+SELECT pg_stat_reset_slru('commit_timestamp') IS NOT NULL AS t;
+ t
+---
+ t
(1 row)
SELECT stats_reset > :'slru_commit_ts_reset_ts'::timestamptz FROM pg_stat_slru WHERE name = 'commit_timestamp';
@@ -994,10 +994,10 @@ SELECT stats_reset > :'slru_commit_ts_reset_ts'::timestamptz FROM pg_stat_slru W
SELECT stats_reset AS slru_commit_ts_reset_ts FROM pg_stat_slru WHERE name = 'commit_timestamp' \gset
-- Test that multiple SLRUs are reset when no specific SLRU provided to reset function
-SELECT pg_stat_reset_slru();
- pg_stat_reset_slru
---------------------
-
+SELECT pg_stat_reset_slru() IS NOT NULL AS t;
+ t
+---
+ t
(1 row)
SELECT stats_reset > :'slru_commit_ts_reset_ts'::timestamptz FROM pg_stat_slru WHERE name = 'commit_timestamp';
diff --git a/src/test/regress/sql/stats.sql b/src/test/regress/sql/stats.sql
index 77d3e1a845d..62a8424d4c0 100644
--- a/src/test/regress/sql/stats.sql
+++ b/src/test/regress/sql/stats.sql
@@ -467,12 +467,12 @@ WHERE pg_stat_get_backend_pid(beid) = pg_backend_pid();
-- Test that reset_slru with a specified SLRU works.
SELECT stats_reset AS slru_commit_ts_reset_ts FROM pg_stat_slru WHERE name = 'commit_timestamp' \gset
SELECT stats_reset AS slru_notify_reset_ts FROM pg_stat_slru WHERE name = 'notify' \gset
-SELECT pg_stat_reset_slru('commit_timestamp');
+SELECT pg_stat_reset_slru('commit_timestamp') IS NOT NULL AS t;
SELECT stats_reset > :'slru_commit_ts_reset_ts'::timestamptz FROM pg_stat_slru WHERE name = 'commit_timestamp';
SELECT stats_reset AS slru_commit_ts_reset_ts FROM pg_stat_slru WHERE name = 'commit_timestamp' \gset
-- Test that multiple SLRUs are reset when no specific SLRU provided to reset function
-SELECT pg_stat_reset_slru();
+SELECT pg_stat_reset_slru() IS NOT NULL AS t;
SELECT stats_reset > :'slru_commit_ts_reset_ts'::timestamptz FROM pg_stat_slru WHERE name = 'commit_timestamp';
SELECT stats_reset > :'slru_notify_reset_ts'::timestamptz FROM pg_stat_slru WHERE name = 'notify';
--
2.47.1
v1-0007-Make-pg_stat_reset_replication_slot-return-the-re.patchapplication/octet-stream; name=v1-0007-Make-pg_stat_reset_replication_slot-return-the-re.patchDownload
From 2018607f26f9a532f23a059bc8819b9fedf8ff20 Mon Sep 17 00:00:00 2001
From: Shinya Kato <shinya11.kato@gmail.com>
Date: Fri, 2 May 2025 18:13:45 +0900
Subject: [PATCH v1 7/9] Make pg_stat_reset_replication_slot() return the reset
time
---
contrib/test_decoding/expected/stats.out | 35 +++++++++++++++-----
contrib/test_decoding/sql/stats.sql | 9 +++--
doc/src/sgml/monitoring.sgml | 9 ++---
src/backend/utils/activity/pgstat_replslot.c | 9 +++--
src/backend/utils/adt/pgstatfuncs.c | 10 ++++--
src/include/catalog/pg_proc.dat | 2 +-
src/include/pgstat.h | 2 +-
7 files changed, 55 insertions(+), 21 deletions(-)
diff --git a/contrib/test_decoding/expected/stats.out b/contrib/test_decoding/expected/stats.out
index de6dc416130..874a52eb28e 100644
--- a/contrib/test_decoding/expected/stats.out
+++ b/contrib/test_decoding/expected/stats.out
@@ -47,10 +47,10 @@ SELECT slot_name, spill_txns = 0 AS spill_txns, spill_count = 0 AS spill_count,
RESET logical_decoding_work_mem;
-- reset stats for one slot, others should be unaffected
-SELECT pg_stat_reset_replication_slot('regression_slot_stats1');
- pg_stat_reset_replication_slot
---------------------------------
-
+SELECT pg_stat_reset_replication_slot('regression_slot_stats1') IS NOT NULL AS t;
+ t
+---
+ t
(1 row)
SELECT slot_name, spill_txns = 0 AS spill_txns, spill_count = 0 AS spill_count, total_txns > 0 AS total_txns, total_bytes > 0 AS total_bytes FROM pg_stat_replication_slots ORDER BY slot_name;
@@ -62,10 +62,10 @@ SELECT slot_name, spill_txns = 0 AS spill_txns, spill_count = 0 AS spill_count,
(3 rows)
-- reset stats for all slots
-SELECT pg_stat_reset_replication_slot(NULL);
- pg_stat_reset_replication_slot
---------------------------------
-
+SELECT pg_stat_reset_replication_slot(NULL) IS NOT NULL AS t;
+ t
+---
+ t
(1 row)
SELECT slot_name, spill_txns = 0 AS spill_txns, spill_count = 0 AS spill_count, total_txns > 0 AS total_txns, total_bytes > 0 AS total_bytes FROM pg_stat_replication_slots ORDER BY slot_name;
@@ -91,6 +91,25 @@ SELECT * FROM pg_stat_get_replication_slot('do-not-exist');
do-not-exist | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
(1 row)
+-- verify physical replication slot stats are not reset
+SELECT 'init' FROM pg_create_physical_replication_slot('regression_slot_stats_physical') ps;
+ ?column?
+----------
+ init
+(1 row)
+
+SELECT pg_stat_reset_replication_slot('regression_slot_stats_physical');
+ pg_stat_reset_replication_slot
+--------------------------------
+
+(1 row)
+
+SELECT pg_drop_replication_slot('regression_slot_stats_physical');
+ pg_drop_replication_slot
+--------------------------
+
+(1 row)
+
-- spilling the xact
BEGIN;
INSERT INTO stats_test SELECT 'serialize-topbig--1:'||g.i FROM generate_series(1, 5000) g(i);
diff --git a/contrib/test_decoding/sql/stats.sql b/contrib/test_decoding/sql/stats.sql
index a022fe1bf07..8ca60f788e3 100644
--- a/contrib/test_decoding/sql/stats.sql
+++ b/contrib/test_decoding/sql/stats.sql
@@ -19,11 +19,11 @@ SELECT slot_name, spill_txns = 0 AS spill_txns, spill_count = 0 AS spill_count,
RESET logical_decoding_work_mem;
-- reset stats for one slot, others should be unaffected
-SELECT pg_stat_reset_replication_slot('regression_slot_stats1');
+SELECT pg_stat_reset_replication_slot('regression_slot_stats1') IS NOT NULL AS t;
SELECT slot_name, spill_txns = 0 AS spill_txns, spill_count = 0 AS spill_count, total_txns > 0 AS total_txns, total_bytes > 0 AS total_bytes FROM pg_stat_replication_slots ORDER BY slot_name;
-- reset stats for all slots
-SELECT pg_stat_reset_replication_slot(NULL);
+SELECT pg_stat_reset_replication_slot(NULL) IS NOT NULL AS t;
SELECT slot_name, spill_txns = 0 AS spill_txns, spill_count = 0 AS spill_count, total_txns > 0 AS total_txns, total_bytes > 0 AS total_bytes FROM pg_stat_replication_slots ORDER BY slot_name;
-- verify accessing/resetting stats for non-existent slot does something reasonable
@@ -31,6 +31,11 @@ SELECT * FROM pg_stat_get_replication_slot('do-not-exist');
SELECT pg_stat_reset_replication_slot('do-not-exist');
SELECT * FROM pg_stat_get_replication_slot('do-not-exist');
+-- verify physical replication slot stats are not reset
+SELECT 'init' FROM pg_create_physical_replication_slot('regression_slot_stats_physical') ps;
+SELECT pg_stat_reset_replication_slot('regression_slot_stats_physical');
+SELECT pg_drop_replication_slot('regression_slot_stats_physical');
+
-- spilling the xact
BEGIN;
INSERT INTO stats_test SELECT 'serialize-topbig--1:'||g.i FROM generate_series(1, 5000) g(i);
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index df90f4668c4..27f22e6fd76 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -5183,12 +5183,13 @@ description | Waiting for a newly initialized WAL file to reach durable storage
<primary>pg_stat_reset_replication_slot</primary>
</indexterm>
<function>pg_stat_reset_replication_slot</function> ( <type>text</type> )
- <returnvalue>void</returnvalue>
+ <returnvalue>timestamp with time zone</returnvalue>
</para>
<para>
- Resets statistics of the replication slot defined by the argument. If
- the argument is <literal>NULL</literal>, resets statistics for all
- the replication slots.
+ Resets statistics of the replication slot defined by the argument, and
+ returns the time of the reset or <literal>NULL</literal> if the replication
+ slot defined by the argument is a physical slot. If the argument is
+ <literal>NULL</literal>, resets statistics for all the replication slots.
</para>
<para>
This function is restricted to superusers by default, but other users
diff --git a/src/backend/utils/activity/pgstat_replslot.c b/src/backend/utils/activity/pgstat_replslot.c
index ccfb11c49bf..67f5c53989e 100644
--- a/src/backend/utils/activity/pgstat_replslot.c
+++ b/src/backend/utils/activity/pgstat_replslot.c
@@ -37,11 +37,14 @@ static int get_replslot_index(const char *name, bool need_lock);
*
* Permission checking for this function is managed through the normal
* GRANT system.
+ *
+ * NB: Returns the time of the reset or 0 if it is a physical slot.
*/
-void
+TimestampTz
pgstat_reset_replslot(const char *name)
{
ReplicationSlot *slot;
+ TimestampTz ts = 0;
Assert(name != NULL);
@@ -61,10 +64,12 @@ pgstat_reset_replslot(const char *name)
* as we collect stats only for logical slots.
*/
if (SlotIsLogical(slot))
- pgstat_reset(PGSTAT_KIND_REPLSLOT, InvalidOid,
+ ts = pgstat_reset(PGSTAT_KIND_REPLSLOT, InvalidOid,
ReplicationSlotIndex(slot));
LWLockRelease(ReplicationSlotControlLock);
+
+ return ts;
}
/*
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index b28ceab91d9..7d9df708266 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -1999,16 +1999,20 @@ Datum
pg_stat_reset_replication_slot(PG_FUNCTION_ARGS)
{
char *target = NULL;
+ TimestampTz ts;
if (PG_ARGISNULL(0))
- pgstat_reset_of_kind(PGSTAT_KIND_REPLSLOT);
+ ts = pgstat_reset_of_kind(PGSTAT_KIND_REPLSLOT);
else
{
target = text_to_cstring(PG_GETARG_TEXT_PP(0));
- pgstat_reset_replslot(target);
+ ts = pgstat_reset_replslot(target);
}
- PG_RETURN_VOID();
+ if (ts == 0)
+ PG_RETURN_NULL();
+ else
+ PG_RETURN_TIMESTAMPTZ(ts);
}
/* Reset subscription stats (a specific one or all of them) */
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 6edc8ae67e4..fa1a26d3f03 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -6168,7 +6168,7 @@
{ oid => '6170',
descr => 'statistics: reset collected statistics for a single replication slot',
proname => 'pg_stat_reset_replication_slot', proisstrict => 'f',
- provolatile => 'v', prorettype => 'void', proargtypes => 'text',
+ provolatile => 'v', prorettype => 'timestamptz', proargtypes => 'text',
prosrc => 'pg_stat_reset_replication_slot' },
{ oid => '6232',
descr => 'statistics: reset collected statistics for a single subscription',
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index 809c00dec6f..96cfbaab798 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -733,7 +733,7 @@ extern PgStat_TableStatus *find_tabstat_entry(Oid rel_id);
* Functions in pgstat_replslot.c
*/
-extern void pgstat_reset_replslot(const char *name);
+extern TimestampTz pgstat_reset_replslot(const char *name);
struct ReplicationSlot;
extern void pgstat_report_replslot(struct ReplicationSlot *slot, const PgStat_StatReplSlotEntry *repSlotStat);
extern void pgstat_create_replslot(struct ReplicationSlot *slot);
--
2.47.1
v1-0008-Make-pg_stat_reset_subscription_stats-return-the-.patchapplication/octet-stream; name=v1-0008-Make-pg_stat_reset_subscription_stats-return-the-.patchDownload
From 428f06d17024678953e8fc61ccaf05257cb5aeb7 Mon Sep 17 00:00:00 2001
From: Shinya Kato <shinya11.kato@gmail.com>
Date: Fri, 2 May 2025 18:48:28 +0900
Subject: [PATCH v1 8/9] Make pg_stat_reset_subscription_stats() return the
reset time
---
doc/src/sgml/monitoring.sgml | 8 ++++----
src/backend/utils/adt/pgstatfuncs.c | 7 ++++---
src/include/catalog/pg_proc.dat | 2 +-
src/test/regress/expected/subscription.out | 16 ++++++++--------
src/test/regress/sql/subscription.sql | 4 ++--
5 files changed, 19 insertions(+), 18 deletions(-)
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index 27f22e6fd76..e7ba7932b1a 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -5203,13 +5203,13 @@ description | Waiting for a newly initialized WAL file to reach durable storage
<primary>pg_stat_reset_subscription_stats</primary>
</indexterm>
<function>pg_stat_reset_subscription_stats</function> ( <type>oid</type> )
- <returnvalue>void</returnvalue>
+ <returnvalue>timestamp with time zone</returnvalue>
</para>
<para>
Resets statistics for a single subscription shown in the
- <structname>pg_stat_subscription_stats</structname> view to zero. If
- the argument is <literal>NULL</literal>, reset statistics for all
- subscriptions.
+ <structname>pg_stat_subscription_stats</structname> view to zero, and
+ returns the time of the reset. If the argument is
+ <literal>NULL</literal>, reset statistics for all subscriptions.
</para>
<para>
This function is restricted to superusers by default, but other users
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index 7d9df708266..7b23fce1712 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -2020,11 +2020,12 @@ Datum
pg_stat_reset_subscription_stats(PG_FUNCTION_ARGS)
{
Oid subid;
+ TimestampTz ts;
if (PG_ARGISNULL(0))
{
/* Clear all subscription stats */
- pgstat_reset_of_kind(PGSTAT_KIND_SUBSCRIPTION);
+ ts = pgstat_reset_of_kind(PGSTAT_KIND_SUBSCRIPTION);
}
else
{
@@ -2034,10 +2035,10 @@ pg_stat_reset_subscription_stats(PG_FUNCTION_ARGS)
ereport(ERROR,
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
errmsg("invalid subscription OID %u", subid)));
- pgstat_reset(PGSTAT_KIND_SUBSCRIPTION, InvalidOid, subid);
+ ts = pgstat_reset(PGSTAT_KIND_SUBSCRIPTION, InvalidOid, subid);
}
- PG_RETURN_VOID();
+ PG_RETURN_TIMESTAMPTZ(ts);
}
Datum
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index fa1a26d3f03..48d6f03afeb 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -6173,7 +6173,7 @@
{ oid => '6232',
descr => 'statistics: reset collected statistics for a single subscription',
proname => 'pg_stat_reset_subscription_stats', proisstrict => 'f',
- provolatile => 'v', prorettype => 'void', proargtypes => 'oid',
+ provolatile => 'v', prorettype => 'timestamptz', proargtypes => 'oid',
prosrc => 'pg_stat_reset_subscription_stats' },
{ oid => '3163', descr => 'current trigger depth',
diff --git a/src/test/regress/expected/subscription.out b/src/test/regress/expected/subscription.out
index a98c97f7616..e41d90c95a4 100644
--- a/src/test/regress/expected/subscription.out
+++ b/src/test/regress/expected/subscription.out
@@ -47,10 +47,10 @@ SELECT subname, stats_reset IS NULL stats_reset_is_null FROM pg_stat_subscriptio
regress_testsub | t
(1 row)
-SELECT pg_stat_reset_subscription_stats(oid) FROM pg_subscription WHERE subname = 'regress_testsub';
- pg_stat_reset_subscription_stats
-----------------------------------
-
+SELECT pg_stat_reset_subscription_stats(oid) IS NOT NULL AS t FROM pg_subscription WHERE subname = 'regress_testsub';
+ t
+---
+ t
(1 row)
SELECT subname, stats_reset IS NULL stats_reset_is_null FROM pg_stat_subscription_stats WHERE subname = 'regress_testsub';
@@ -61,10 +61,10 @@ SELECT subname, stats_reset IS NULL stats_reset_is_null FROM pg_stat_subscriptio
-- Reset the stats again and check if the new reset_stats is updated.
SELECT stats_reset as prev_stats_reset FROM pg_stat_subscription_stats WHERE subname = 'regress_testsub' \gset
-SELECT pg_stat_reset_subscription_stats(oid) FROM pg_subscription WHERE subname = 'regress_testsub';
- pg_stat_reset_subscription_stats
-----------------------------------
-
+SELECT pg_stat_reset_subscription_stats(oid) IS NOT NULL AS t FROM pg_subscription WHERE subname = 'regress_testsub';
+ t
+---
+ t
(1 row)
SELECT :'prev_stats_reset' < stats_reset FROM pg_stat_subscription_stats WHERE subname = 'regress_testsub';
diff --git a/src/test/regress/sql/subscription.sql b/src/test/regress/sql/subscription.sql
index f0f714fe747..13b1e2ed797 100644
--- a/src/test/regress/sql/subscription.sql
+++ b/src/test/regress/sql/subscription.sql
@@ -34,12 +34,12 @@ SELECT obj_description(s.oid, 'pg_subscription') FROM pg_subscription s;
-- Check if the subscription stats are created and stats_reset is updated
-- by pg_stat_reset_subscription_stats().
SELECT subname, stats_reset IS NULL stats_reset_is_null FROM pg_stat_subscription_stats WHERE subname = 'regress_testsub';
-SELECT pg_stat_reset_subscription_stats(oid) FROM pg_subscription WHERE subname = 'regress_testsub';
+SELECT pg_stat_reset_subscription_stats(oid) IS NOT NULL AS t FROM pg_subscription WHERE subname = 'regress_testsub';
SELECT subname, stats_reset IS NULL stats_reset_is_null FROM pg_stat_subscription_stats WHERE subname = 'regress_testsub';
-- Reset the stats again and check if the new reset_stats is updated.
SELECT stats_reset as prev_stats_reset FROM pg_stat_subscription_stats WHERE subname = 'regress_testsub' \gset
-SELECT pg_stat_reset_subscription_stats(oid) FROM pg_subscription WHERE subname = 'regress_testsub';
+SELECT pg_stat_reset_subscription_stats(oid) IS NOT NULL AS t FROM pg_subscription WHERE subname = 'regress_testsub';
SELECT :'prev_stats_reset' < stats_reset FROM pg_stat_subscription_stats WHERE subname = 'regress_testsub';
-- fail - name already exists
--
2.47.1
v1-0009-Make-pg_stat_clear_snapshot-return-the-reset-time.patchapplication/octet-stream; name=v1-0009-Make-pg_stat_clear_snapshot-return-the-reset-time.patchDownload
From 090214a2f227ed832fec1d7e0d06cfdb0d7be0bd Mon Sep 17 00:00:00 2001
From: Shinya Kato <shinya11.kato@gmail.com>
Date: Fri, 2 May 2025 19:08:45 +0900
Subject: [PATCH v1 9/9] Make pg_stat_clear_snapshot() return the reset time
---
doc/src/sgml/monitoring.sgml | 5 ++--
src/backend/utils/activity/pgstat.c | 4 +++-
src/backend/utils/adt/pgstatfuncs.c | 6 +++--
src/include/catalog/pg_proc.dat | 2 +-
src/include/pgstat.h | 2 +-
src/test/isolation/expected/stats.out | 32 ++++++++++++-------------
src/test/isolation/expected/stats_1.out | 32 ++++++++++++-------------
src/test/isolation/specs/stats.spec | 2 +-
src/test/regress/expected/stats.out | 8 +++----
src/test/regress/sql/stats.sql | 2 +-
10 files changed, 50 insertions(+), 45 deletions(-)
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index e7ba7932b1a..8a1632eae0c 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -4995,10 +4995,11 @@ description | Waiting for a newly initialized WAL file to reach durable storage
<primary>pg_stat_clear_snapshot</primary>
</indexterm>
<function>pg_stat_clear_snapshot</function> ()
- <returnvalue>void</returnvalue>
+ <returnvalue>timestamp with time zone</returnvalue>
</para>
<para>
- Discards the current statistics snapshot or cached information.
+ Discards the current statistics snapshot or cached information, and
+ returns the time of the discard.
</para></entry>
</row>
diff --git a/src/backend/utils/activity/pgstat.c b/src/backend/utils/activity/pgstat.c
index aecf9fbdcd1..d0702aa499d 100644
--- a/src/backend/utils/activity/pgstat.c
+++ b/src/backend/utils/activity/pgstat.c
@@ -902,7 +902,7 @@ pgstat_reset_of_kind(PgStat_Kind kind)
* the no-longer-wanted snapshot. Updates of stats_fetch_consistency can
* cause this routine to be called.
*/
-void
+TimestampTz
pgstat_clear_snapshot(void)
{
pgstat_assert_is_up();
@@ -932,6 +932,8 @@ pgstat_clear_snapshot(void)
/* Reset this flag, as it may be possible that a cleanup was forced. */
force_stats_snapshot_clear = false;
+
+ return GetCurrentTimestamp();
}
void *
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index 7b23fce1712..422944e75f2 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -1837,9 +1837,11 @@ pg_stat_get_snapshot_timestamp(PG_FUNCTION_ARGS)
Datum
pg_stat_clear_snapshot(PG_FUNCTION_ARGS)
{
- pgstat_clear_snapshot();
+ TimestampTz ts;
- PG_RETURN_VOID();
+ ts = pgstat_clear_snapshot();
+
+ PG_RETURN_TIMESTAMPTZ(ts);
}
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 48d6f03afeb..52d140d24aa 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -6130,7 +6130,7 @@
{ oid => '2230',
descr => 'statistics: discard current transaction\'s statistics snapshot',
proname => 'pg_stat_clear_snapshot', proisstrict => 'f', provolatile => 'v',
- proparallel => 'r', prorettype => 'void', proargtypes => '',
+ proparallel => 'r', prorettype => 'timestamptz', proargtypes => '',
prosrc => 'pg_stat_clear_snapshot' },
{ oid => '2137',
descr => 'statistics: force stats to be flushed after the next commit',
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index 96cfbaab798..6aeeb561435 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -529,7 +529,7 @@ extern TimestampTz pgstat_reset(PgStat_Kind kind, Oid dboid, uint64 objid);
extern TimestampTz pgstat_reset_of_kind(PgStat_Kind kind);
/* stats accessors */
-extern void pgstat_clear_snapshot(void);
+extern TimestampTz pgstat_clear_snapshot(void);
extern TimestampTz pgstat_get_stat_snapshot_timestamp(bool *have_snapshot);
/* helpers */
diff --git a/src/test/isolation/expected/stats.out b/src/test/isolation/expected/stats.out
index 132f8e55fc3..43cfc1880c6 100644
--- a/src/test/isolation/expected/stats.out
+++ b/src/test/isolation/expected/stats.out
@@ -3422,10 +3422,10 @@ step s1_slru_check_stats:
t
(1 row)
-step s1_clear_snapshot: SELECT pg_stat_clear_snapshot();
-pg_stat_clear_snapshot
-----------------------
-
+step s1_clear_snapshot: SELECT pg_stat_clear_snapshot() IS NOT NULL AS t;
+t
+-
+t
(1 row)
step s1_slru_check_stats:
@@ -3495,10 +3495,10 @@ step s1_slru_check_stats:
f
(1 row)
-step s1_clear_snapshot: SELECT pg_stat_clear_snapshot();
-pg_stat_clear_snapshot
-----------------------
-
+step s1_clear_snapshot: SELECT pg_stat_clear_snapshot() IS NOT NULL AS t;
+t
+-
+t
(1 row)
step s1_slru_check_stats:
@@ -3568,10 +3568,10 @@ step s1_slru_check_stats:
f
(1 row)
-step s1_clear_snapshot: SELECT pg_stat_clear_snapshot();
-pg_stat_clear_snapshot
-----------------------
-
+step s1_clear_snapshot: SELECT pg_stat_clear_snapshot() IS NOT NULL AS t;
+t
+-
+t
(1 row)
step s1_slru_check_stats:
@@ -3712,10 +3712,10 @@ name |pg_stat_get_function_calls|total_above_zero|self_above_zero
test_stat_func| | |
(1 row)
-step s1_clear_snapshot: SELECT pg_stat_clear_snapshot();
-pg_stat_clear_snapshot
-----------------------
-
+step s1_clear_snapshot: SELECT pg_stat_clear_snapshot() IS NOT NULL AS t;
+t
+-
+t
(1 row)
step s1_func_stats:
diff --git a/src/test/isolation/expected/stats_1.out b/src/test/isolation/expected/stats_1.out
index 2b16808fbb5..f83420dcd7d 100644
--- a/src/test/isolation/expected/stats_1.out
+++ b/src/test/isolation/expected/stats_1.out
@@ -3446,10 +3446,10 @@ step s1_slru_check_stats:
t
(1 row)
-step s1_clear_snapshot: SELECT pg_stat_clear_snapshot();
-pg_stat_clear_snapshot
-----------------------
-
+step s1_clear_snapshot: SELECT pg_stat_clear_snapshot() IS NOT NULL AS t;
+t
+-
+t
(1 row)
step s1_slru_check_stats:
@@ -3519,10 +3519,10 @@ step s1_slru_check_stats:
f
(1 row)
-step s1_clear_snapshot: SELECT pg_stat_clear_snapshot();
-pg_stat_clear_snapshot
-----------------------
-
+step s1_clear_snapshot: SELECT pg_stat_clear_snapshot() IS NOT NULL AS t;
+t
+-
+t
(1 row)
step s1_slru_check_stats:
@@ -3592,10 +3592,10 @@ step s1_slru_check_stats:
f
(1 row)
-step s1_clear_snapshot: SELECT pg_stat_clear_snapshot();
-pg_stat_clear_snapshot
-----------------------
-
+step s1_clear_snapshot: SELECT pg_stat_clear_snapshot() IS NOT NULL AS t;
+t
+-
+t
(1 row)
step s1_slru_check_stats:
@@ -3736,10 +3736,10 @@ name |pg_stat_get_function_calls|total_above_zero|self_above_zero
test_stat_func| | |
(1 row)
-step s1_clear_snapshot: SELECT pg_stat_clear_snapshot();
-pg_stat_clear_snapshot
-----------------------
-
+step s1_clear_snapshot: SELECT pg_stat_clear_snapshot() IS NOT NULL AS t;
+t
+-
+t
(1 row)
step s1_func_stats:
diff --git a/src/test/isolation/specs/stats.spec b/src/test/isolation/specs/stats.spec
index 3893d3983f0..7e211d3fa83 100644
--- a/src/test/isolation/specs/stats.spec
+++ b/src/test/isolation/specs/stats.spec
@@ -40,7 +40,7 @@ setup { SET stats_fetch_consistency = 'none'; }
step s1_fetch_consistency_none { SET stats_fetch_consistency = 'none'; }
step s1_fetch_consistency_cache { SET stats_fetch_consistency = 'cache'; }
step s1_fetch_consistency_snapshot { SET stats_fetch_consistency = 'snapshot'; }
-step s1_clear_snapshot { SELECT pg_stat_clear_snapshot(); }
+step s1_clear_snapshot { SELECT pg_stat_clear_snapshot() IS NOT NULL AS t; }
step s1_begin { BEGIN; }
step s1_commit { COMMIT; }
step s1_rollback { ROLLBACK; }
diff --git a/src/test/regress/expected/stats.out b/src/test/regress/expected/stats.out
index 4c5612ab1ba..7d13fca9e51 100644
--- a/src/test/regress/expected/stats.out
+++ b/src/test/regress/expected/stats.out
@@ -1147,10 +1147,10 @@ SELECT pg_stat_get_snapshot_timestamp() >= NOW();
(1 row)
-- shows NULL again after clearing
-SELECT pg_stat_clear_snapshot();
- pg_stat_clear_snapshot
-------------------------
-
+SELECT pg_stat_clear_snapshot() IS NOT NULL AS t;
+ t
+---
+ t
(1 row)
SELECT pg_stat_get_snapshot_timestamp();
diff --git a/src/test/regress/sql/stats.sql b/src/test/regress/sql/stats.sql
index 62a8424d4c0..aab46b91ad0 100644
--- a/src/test/regress/sql/stats.sql
+++ b/src/test/regress/sql/stats.sql
@@ -529,7 +529,7 @@ SELECT pg_stat_get_snapshot_timestamp();
SELECT pg_stat_get_function_calls(0);
SELECT pg_stat_get_snapshot_timestamp() >= NOW();
-- shows NULL again after clearing
-SELECT pg_stat_clear_snapshot();
+SELECT pg_stat_clear_snapshot() IS NOT NULL AS t;
SELECT pg_stat_get_snapshot_timestamp();
COMMIT;
--
2.47.1
Re: Enhance statistics reset functions to return reset timestamp
On Fri, Aug 08, 2025 at 01:18:39PM +0900, Shinya Kato wrote:
The following functions have been modified to return a TIMESTAMP WITH
TIME ZONE value indicating when the statistics were reset:
- pg_stat_reset()
- pg_stat_reset_shared()
- pg_stat_reset_single_table_counters()
- pg_stat_reset_backend_stats()
- pg_stat_reset_single_function_counters()
- pg_stat_reset_slru()
- pg_stat_reset_replication_slot()
- pg_stat_reset_subscription_stats()
- pg_stat_clear_snapshot()For pg_stat_reset_backend_stats() and
pg_stat_reset_replication_slot(), the functions return the reset
timestamp when a valid input is provided. If an invalid input is given
(e.g., an invalid backend PID or replication slot name), the functions
return NULL.
This allows users to easily determine whether the reset operation was
successful based on the return value.
I am not sure that this is a good idea overall, but I'm OK if I finish
outvoted.
At the exception of pg_stat_reset_backend_stats(), all the functions
you are listing above are written so as they never fail, so claiming
that returning a timestamp value for this reason sounds a bit strange
to me. And we already know this information based on what the stats
tables already hold for the reset timestamps set when their stats
kinds callbacks are called.
I can get behind a change for pg_stat_reset_backend_stats() to make it
return a status, though, as the information depends on some procnum
lookups which is triggered depending on what the user provides in
input.
--
Michael
Re: Enhance statistics reset functions to return reset timestamp
On Fri, Aug 8, 2025 at 5:06 PM Michael Paquier <michael@paquier.xyz> wrote:
On Fri, Aug 08, 2025 at 01:18:39PM +0900, Shinya Kato wrote:
The following functions have been modified to return a TIMESTAMP WITH
TIME ZONE value indicating when the statistics were reset:
- pg_stat_reset()
- pg_stat_reset_shared()
- pg_stat_reset_single_table_counters()
- pg_stat_reset_backend_stats()
- pg_stat_reset_single_function_counters()
- pg_stat_reset_slru()
- pg_stat_reset_replication_slot()
- pg_stat_reset_subscription_stats()
- pg_stat_clear_snapshot()For pg_stat_reset_backend_stats() and
pg_stat_reset_replication_slot(), the functions return the reset
timestamp when a valid input is provided. If an invalid input is given
(e.g., an invalid backend PID or replication slot name), the functions
return NULL.
This allows users to easily determine whether the reset operation was
successful based on the return value.I am not sure that this is a good idea overall, but I'm OK if I finish
outvoted.
Thanks for the review!
At the exception of pg_stat_reset_backend_stats(), all the functions
you are listing above are written so as they never fail, so claiming
that returning a timestamp value for this reason sounds a bit strange
to me. And we already know this information based on what the stats
tables already hold for the reset timestamps set when their stats
kinds callbacks are called.
You are correct that these functions don't fail, so the lack of a
return value is not an issue. However, it would be more convenient,
for instance, when running these functions before and after a test
script, to get an audit trail of the reset time just by checking the
return value, without having to check the pg_stats_* views.
I can get behind a change for pg_stat_reset_backend_stats() to make it
return a status, though, as the information depends on some procnum
lookups which is triggered depending on what the user provides in
input.
The same applies to pg_stat_reset_replication_slot(). While specifying
a physical replication slot does not raise an error, the operation has
no effect. I think it would be better if this outcome could be
confirmed via the return value.
--
Best regards,
Shinya Kato
NTT OSS Center
Re: Enhance statistics reset functions to return reset timestamp
Hi,
On 2025-08-08 13:18:39 +0900, Shinya Kato wrote:
I would like to propose a series of patches that enhance the behavior
of various statistics reset functions by making them return the reset
timestamp. This change improves usability and aligns with the behavior
introduced in commit dc9f8a798[1], where pg_stat_statements_reset()
was updated to return the reset time.The following functions have been modified to return a TIMESTAMP WITH
TIME ZONE value indicating when the statistics were reset:
- pg_stat_reset()
- pg_stat_reset_shared()
- pg_stat_reset_single_table_counters()
- pg_stat_reset_backend_stats()
- pg_stat_reset_single_function_counters()
- pg_stat_reset_slru()
- pg_stat_reset_replication_slot()
- pg_stat_reset_subscription_stats()
- pg_stat_clear_snapshot()
-1 - I think it was a mistake to introduce support for granular resets, we
shouldn't bury ourselves deeper. If anything we should rip out everything
other than 1) a global reset b) a per-database reset.
Leaving that aside, I just don't see a convincing use case for returning the
timestamp here.
Greetings,
Andres Freund
Re: Enhance statistics reset functions to return reset timestamp
On Sat, Aug 9, 2025 at 7:37 AM Andres Freund <andres@anarazel.de> wrote:
Hi,
On 2025-08-08 13:18:39 +0900, Shinya Kato wrote:
I would like to propose a series of patches that enhance the behavior
of various statistics reset functions by making them return the reset
timestamp. This change improves usability and aligns with the behavior
introduced in commit dc9f8a798[1], where pg_stat_statements_reset()
was updated to return the reset time.The following functions have been modified to return a TIMESTAMP WITH
TIME ZONE value indicating when the statistics were reset:
- pg_stat_reset()
- pg_stat_reset_shared()
- pg_stat_reset_single_table_counters()
- pg_stat_reset_backend_stats()
- pg_stat_reset_single_function_counters()
- pg_stat_reset_slru()
- pg_stat_reset_replication_slot()
- pg_stat_reset_subscription_stats()
- pg_stat_clear_snapshot()-1 - I think it was a mistake to introduce support for granular resets, we
shouldn't bury ourselves deeper. If anything we should rip out everything
other than 1) a global reset b) a per-database reset.Leaving that aside, I just don't see a convincing use case for returning the
timestamp here.
As I mentioned earlier, the use case is to obtain an audit trail of
the statistics reset time from the function’s return value.
Also, how should we think about consistency with
pg_stat_statements_reset()? The reset timestamp for pg_stat_statements
can be obtained from the pg_stat_statements_info view, which returns a
TIMESTAMPTZ. Of course, pg_stat_statements is a contrib module, so we
don’t necessarily have to match its behavior. However, when running a
script that resets various statistics before tests, if only
pg_stat_statements_reset() returns a TIMESTAMPTZ, it might look as
though the other statistics reset functions failed.
What these patches do is simply return the TIMESTAMPTZ that is already
computed for the pg_stat_* views, which seems to me a reasonable
change.
--
Best regards,
Shinya Kato
NTT OSS Center
Re: Enhance statistics reset functions to return reset timestamp
Hi,
On Wed, Aug 13, 2025 at 4:06 PM Shinya Kato <shinya11.kato@gmail.com> wrote:
On Sat, Aug 9, 2025 at 7:37 AM Andres Freund <andres@anarazel.de> wrote:
Hi,
On 2025-08-08 13:18:39 +0900, Shinya Kato wrote:
I would like to propose a series of patches that enhance the behavior
of various statistics reset functions by making them return the reset
timestamp. This change improves usability and aligns with the behavior
introduced in commit dc9f8a798[1], where pg_stat_statements_reset()
was updated to return the reset time.The following functions have been modified to return a TIMESTAMP WITH
TIME ZONE value indicating when the statistics were reset:
- pg_stat_reset()
- pg_stat_reset_shared()
- pg_stat_reset_single_table_counters()
- pg_stat_reset_backend_stats()
- pg_stat_reset_single_function_counters()
- pg_stat_reset_slru()
- pg_stat_reset_replication_slot()
- pg_stat_reset_subscription_stats()
- pg_stat_clear_snapshot()-1 - I think it was a mistake to introduce support for granular resets, we
shouldn't bury ourselves deeper. If anything we should rip out everything
other than 1) a global reset b) a per-database reset.Leaving that aside, I just don't see a convincing use case for returning the
timestamp here.As I mentioned earlier, the use case is to obtain an audit trail of
the statistics reset time from the function’s return value.Also, how should we think about consistency with
pg_stat_statements_reset()? The reset timestamp for pg_stat_statements
can be obtained from the pg_stat_statements_info view, which returns a
TIMESTAMPTZ. Of course, pg_stat_statements is a contrib module, so we
don’t necessarily have to match its behavior. However, when running a
script that resets various statistics before tests, if only
pg_stat_statements_reset() returns a TIMESTAMPTZ, it might look as
though the other statistics reset functions failed.What these patches do is simply return the TIMESTAMPTZ that is already
computed for the pg_stat_* views, which seems to me a reasonable
change.
Rebased the patches.
--
Best regards,
Shinya Kato
NTT OSS Center
Attachments:
v2-0001-Make-pg_stat_reset-return-the-reset-time.patchapplication/octet-stream; name=v2-0001-Make-pg_stat_reset-return-the-reset-time.patchDownload
From 94b13265fb2f11cef56a3188495b2875c381f95d Mon Sep 17 00:00:00 2001
From: Shinya Kato <shinya11.kato@gmail.com>
Date: Fri, 2 May 2025 14:44:06 +0900
Subject: [PATCH v2 1/9] Make pg_stat_reset() return the reset time
---
doc/src/sgml/monitoring.sgml | 5 +++--
src/backend/utils/activity/pgstat.c | 4 +++-
src/backend/utils/adt/pgstatfuncs.c | 4 ++--
src/include/catalog/pg_proc.dat | 2 +-
src/include/pgstat.h | 2 +-
src/test/isolation/expected/stats.out | 8 ++++----
src/test/isolation/expected/stats_1.out | 8 ++++----
src/test/isolation/specs/stats.spec | 2 +-
src/test/regress/expected/stats.out | 16 ++++++++--------
src/test/regress/sql/stats.sql | 4 ++--
10 files changed, 29 insertions(+), 26 deletions(-)
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index 3f4a27a736e..b72b840048b 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -5008,10 +5008,11 @@ description | Waiting for a newly initialized WAL file to reach durable storage
<primary>pg_stat_reset</primary>
</indexterm>
<function>pg_stat_reset</function> ()
- <returnvalue>void</returnvalue>
+ <returnvalue>timestamp with time zone</returnvalue>
</para>
<para>
- Resets all statistics counters for the current database to zero.
+ Resets all statistics counters for the current database to zero, and
+ returns the time of the reset.
</para>
<para>
This function is restricted to superusers by default, but other users
diff --git a/src/backend/utils/activity/pgstat.c b/src/backend/utils/activity/pgstat.c
index f8e91484e36..258c01618d6 100644
--- a/src/backend/utils/activity/pgstat.c
+++ b/src/backend/utils/activity/pgstat.c
@@ -830,7 +830,7 @@ match_db_entries(PgStatShared_HashEntry *entry, Datum match_data)
* Permission checking for this function is managed through the normal
* GRANT system.
*/
-void
+TimestampTz
pgstat_reset_counters(void)
{
TimestampTz ts = GetCurrentTimestamp();
@@ -838,6 +838,8 @@ pgstat_reset_counters(void)
pgstat_reset_matching_entries(match_db_entries,
ObjectIdGetDatum(MyDatabaseId),
ts);
+
+ return ts;
}
/*
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index c756c2bebaa..56c5dc4efce 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -1857,9 +1857,9 @@ pg_stat_force_next_flush(PG_FUNCTION_ARGS)
Datum
pg_stat_reset(PG_FUNCTION_ARGS)
{
- pgstat_reset_counters();
+ TimestampTz ts = pgstat_reset_counters();
- PG_RETURN_VOID();
+ PG_RETURN_TIMESTAMPTZ(ts);
}
/*
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 03e82d28c87..6ae064492e5 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -6152,7 +6152,7 @@
{ oid => '2274',
descr => 'statistics: reset collected statistics for current database',
proname => 'pg_stat_reset', proisstrict => 'f', provolatile => 'v',
- prorettype => 'void', proargtypes => '', prosrc => 'pg_stat_reset' },
+ prorettype => 'timestamptz', proargtypes => '', prosrc => 'pg_stat_reset' },
{ oid => '3775',
descr => 'statistics: reset collected statistics shared across the cluster',
proname => 'pg_stat_reset_shared', proisstrict => 'f', provolatile => 'v',
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index f402b17295c..1573b4fe4fa 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -524,7 +524,7 @@ extern void pgstat_initialize(void);
extern long pgstat_report_stat(bool force);
extern void pgstat_force_next_flush(void);
-extern void pgstat_reset_counters(void);
+extern TimestampTz pgstat_reset_counters(void);
extern void pgstat_reset(PgStat_Kind kind, Oid dboid, uint64 objid);
extern void pgstat_reset_of_kind(PgStat_Kind kind);
diff --git a/src/test/isolation/expected/stats.out b/src/test/isolation/expected/stats.out
index 8c7fe60217e..be9dc186fef 100644
--- a/src/test/isolation/expected/stats.out
+++ b/src/test/isolation/expected/stats.out
@@ -1295,10 +1295,10 @@ name |pg_stat_get_function_calls|total_above_zero|self_above_zero
test_stat_func| 2|t |t
(1 row)
-step s1_reset: SELECT pg_stat_reset();
-pg_stat_reset
--------------
-
+step s1_reset: SELECT pg_stat_reset() IS NOT NULL AS t;
+t
+-
+t
(1 row)
step s1_func_stats:
diff --git a/src/test/isolation/expected/stats_1.out b/src/test/isolation/expected/stats_1.out
index 6b965bb9553..3017c6f4619 100644
--- a/src/test/isolation/expected/stats_1.out
+++ b/src/test/isolation/expected/stats_1.out
@@ -1295,10 +1295,10 @@ name |pg_stat_get_function_calls|total_above_zero|self_above_zero
test_stat_func| 2|t |t
(1 row)
-step s1_reset: SELECT pg_stat_reset();
-pg_stat_reset
--------------
-
+step s1_reset: SELECT pg_stat_reset() IS NOT NULL AS t;
+t
+-
+t
(1 row)
step s1_func_stats:
diff --git a/src/test/isolation/specs/stats.spec b/src/test/isolation/specs/stats.spec
index 1d98ac785b8..22ef6d3206b 100644
--- a/src/test/isolation/specs/stats.spec
+++ b/src/test/isolation/specs/stats.spec
@@ -56,7 +56,7 @@ step s1_func_call { SELECT test_stat_func(); }
step s1_func_drop { DROP FUNCTION test_stat_func(); }
step s1_func_stats_reset { SELECT pg_stat_reset_single_function_counters('test_stat_func'::regproc); }
step s1_func_stats_reset_nonexistent { SELECT pg_stat_reset_single_function_counters(12000); }
-step s1_reset { SELECT pg_stat_reset(); }
+step s1_reset { SELECT pg_stat_reset() IS NOT NULL AS t; }
step s1_func_stats {
SELECT
tso.name,
diff --git a/src/test/regress/expected/stats.out b/src/test/regress/expected/stats.out
index 605f5070376..5db2d81afb1 100644
--- a/src/test/regress/expected/stats.out
+++ b/src/test/regress/expected/stats.out
@@ -1102,17 +1102,17 @@ ERROR: unrecognized reset target: "unknown"
HINT: Target must be "archiver", "bgwriter", "checkpointer", "io", "recovery_prefetch", "slru", or "wal".
-- Test that reset works for pg_stat_database
-- Since pg_stat_database stats_reset starts out as NULL, reset it once first so we have something to compare it to
-SELECT pg_stat_reset();
- pg_stat_reset
----------------
-
+SELECT pg_stat_reset() IS NOT NULL AS t;
+ t
+---
+ t
(1 row)
SELECT stats_reset AS db_reset_ts FROM pg_stat_database WHERE datname = (SELECT current_database()) \gset
-SELECT pg_stat_reset();
- pg_stat_reset
----------------
-
+SELECT pg_stat_reset() IS NOT NULL AS t;
+ t
+---
+ t
(1 row)
SELECT stats_reset > :'db_reset_ts'::timestamptz FROM pg_stat_database WHERE datname = (SELECT current_database());
diff --git a/src/test/regress/sql/stats.sql b/src/test/regress/sql/stats.sql
index 54e72866344..5c77ccc1319 100644
--- a/src/test/regress/sql/stats.sql
+++ b/src/test/regress/sql/stats.sql
@@ -512,9 +512,9 @@ SELECT pg_stat_reset_shared('unknown');
-- Test that reset works for pg_stat_database
-- Since pg_stat_database stats_reset starts out as NULL, reset it once first so we have something to compare it to
-SELECT pg_stat_reset();
+SELECT pg_stat_reset() IS NOT NULL AS t;
SELECT stats_reset AS db_reset_ts FROM pg_stat_database WHERE datname = (SELECT current_database()) \gset
-SELECT pg_stat_reset();
+SELECT pg_stat_reset() IS NOT NULL AS t;
SELECT stats_reset > :'db_reset_ts'::timestamptz FROM pg_stat_database WHERE datname = (SELECT current_database());
--
2.47.3
v2-0002-Make-pg_stat_reset_shared-return-the-reset-time.patchapplication/octet-stream; name=v2-0002-Make-pg_stat_reset_shared-return-the-reset-time.patchDownload
From 8076a582a9dd035a928c8d9c79eced610c674521 Mon Sep 17 00:00:00 2001
From: Shinya Kato <shinya11.kato@gmail.com>
Date: Fri, 2 May 2025 14:58:07 +0900
Subject: [PATCH v2 2/9] Make pg_stat_reset_shared() return the reset time
---
doc/src/sgml/monitoring.sgml | 5 +-
src/backend/access/transam/xlogprefetcher.c | 6 ++-
src/backend/catalog/system_functions.sql | 2 +-
src/backend/utils/activity/pgstat.c | 5 +-
src/backend/utils/adt/pgstatfuncs.c | 21 ++++----
src/include/access/xlogprefetcher.h | 3 +-
src/include/catalog/pg_proc.dat | 2 +-
src/include/pgstat.h | 2 +-
src/test/regress/expected/stats.out | 56 ++++++++++-----------
src/test/regress/sql/stats.sql | 14 +++---
10 files changed, 61 insertions(+), 55 deletions(-)
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index b72b840048b..521ad113012 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -5026,11 +5026,12 @@ description | Waiting for a newly initialized WAL file to reach durable storage
<primary>pg_stat_reset_shared</primary>
</indexterm>
<function>pg_stat_reset_shared</function> ( [ <parameter>target</parameter> <type>text</type> <literal>DEFAULT</literal> <literal>NULL</literal> ] )
- <returnvalue>void</returnvalue>
+ <returnvalue>timestamp with time zone</returnvalue>
</para>
<para>
Resets some cluster-wide statistics counters to zero, depending on the
- argument. <parameter>target</parameter> can be:
+ argument, and returns the time of the reset.
+ <parameter>target</parameter> can be:
<itemizedlist>
<listitem>
<para>
diff --git a/src/backend/access/transam/xlogprefetcher.c b/src/backend/access/transam/xlogprefetcher.c
index ed3aacabc98..bc033ae1842 100644
--- a/src/backend/access/transam/xlogprefetcher.c
+++ b/src/backend/access/transam/xlogprefetcher.c
@@ -299,16 +299,18 @@ XLogPrefetchShmemSize(void)
/*
* Reset all counters to zero.
*/
-void
+TimestampTz
XLogPrefetchResetStats(void)
{
- pg_atomic_write_u64(&SharedStats->reset_time, GetCurrentTimestamp());
+ TimestampTz ts = GetCurrentTimestamp();
+ pg_atomic_write_u64(&SharedStats->reset_time, ts);
pg_atomic_write_u64(&SharedStats->prefetch, 0);
pg_atomic_write_u64(&SharedStats->hit, 0);
pg_atomic_write_u64(&SharedStats->skip_init, 0);
pg_atomic_write_u64(&SharedStats->skip_new, 0);
pg_atomic_write_u64(&SharedStats->skip_fpw, 0);
pg_atomic_write_u64(&SharedStats->skip_rep, 0);
+ return ts;
}
void
diff --git a/src/backend/catalog/system_functions.sql b/src/backend/catalog/system_functions.sql
index 566f308e443..0f19c2c478b 100644
--- a/src/backend/catalog/system_functions.sql
+++ b/src/backend/catalog/system_functions.sql
@@ -638,7 +638,7 @@ AS 'unicode_is_normalized';
CREATE OR REPLACE FUNCTION
pg_stat_reset_shared(target text DEFAULT NULL)
-RETURNS void
+RETURNS timestamp with time zone
LANGUAGE INTERNAL
CALLED ON NULL INPUT VOLATILE PARALLEL SAFE
AS 'pg_stat_reset_shared';
diff --git a/src/backend/utils/activity/pgstat.c b/src/backend/utils/activity/pgstat.c
index 258c01618d6..62d92ab3c09 100644
--- a/src/backend/utils/activity/pgstat.c
+++ b/src/backend/utils/activity/pgstat.c
@@ -873,7 +873,7 @@ pgstat_reset(PgStat_Kind kind, Oid dboid, uint64 objid)
* Permission checking for this function is managed through the normal
* GRANT system.
*/
-void
+TimestampTz
pgstat_reset_of_kind(PgStat_Kind kind)
{
const PgStat_KindInfo *kind_info = pgstat_get_kind_info(kind);
@@ -883,8 +883,9 @@ pgstat_reset_of_kind(PgStat_Kind kind)
kind_info->reset_all_cb(ts);
else
pgstat_reset_entries_of_kind(kind, ts);
-}
+ return ts;
+}
/* ------------------------------------------------------------
* Fetching of stats
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index 56c5dc4efce..36404fac4c6 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -1872,6 +1872,7 @@ Datum
pg_stat_reset_shared(PG_FUNCTION_ARGS)
{
char *target = NULL;
+ TimestampTz ts;
if (PG_ARGISNULL(0))
{
@@ -1882,34 +1883,34 @@ pg_stat_reset_shared(PG_FUNCTION_ARGS)
pgstat_reset_of_kind(PGSTAT_KIND_IO);
XLogPrefetchResetStats();
pgstat_reset_of_kind(PGSTAT_KIND_SLRU);
- pgstat_reset_of_kind(PGSTAT_KIND_WAL);
+ ts = pgstat_reset_of_kind(PGSTAT_KIND_WAL);
- PG_RETURN_VOID();
+ PG_RETURN_TIMESTAMPTZ(ts);
}
target = text_to_cstring(PG_GETARG_TEXT_PP(0));
if (strcmp(target, "archiver") == 0)
- pgstat_reset_of_kind(PGSTAT_KIND_ARCHIVER);
+ ts = pgstat_reset_of_kind(PGSTAT_KIND_ARCHIVER);
else if (strcmp(target, "bgwriter") == 0)
- pgstat_reset_of_kind(PGSTAT_KIND_BGWRITER);
+ ts = pgstat_reset_of_kind(PGSTAT_KIND_BGWRITER);
else if (strcmp(target, "checkpointer") == 0)
- pgstat_reset_of_kind(PGSTAT_KIND_CHECKPOINTER);
+ ts = pgstat_reset_of_kind(PGSTAT_KIND_CHECKPOINTER);
else if (strcmp(target, "io") == 0)
- pgstat_reset_of_kind(PGSTAT_KIND_IO);
+ ts = pgstat_reset_of_kind(PGSTAT_KIND_IO);
else if (strcmp(target, "recovery_prefetch") == 0)
- XLogPrefetchResetStats();
+ ts = XLogPrefetchResetStats();
else if (strcmp(target, "slru") == 0)
- pgstat_reset_of_kind(PGSTAT_KIND_SLRU);
+ ts = pgstat_reset_of_kind(PGSTAT_KIND_SLRU);
else if (strcmp(target, "wal") == 0)
- pgstat_reset_of_kind(PGSTAT_KIND_WAL);
+ ts = pgstat_reset_of_kind(PGSTAT_KIND_WAL);
else
ereport(ERROR,
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
errmsg("unrecognized reset target: \"%s\"", target),
errhint("Target must be \"archiver\", \"bgwriter\", \"checkpointer\", \"io\", \"recovery_prefetch\", \"slru\", or \"wal\".")));
- PG_RETURN_VOID();
+ PG_RETURN_TIMESTAMPTZ(ts);
}
/*
diff --git a/src/include/access/xlogprefetcher.h b/src/include/access/xlogprefetcher.h
index 50b39c1fb0d..6cc95b70c6e 100644
--- a/src/include/access/xlogprefetcher.h
+++ b/src/include/access/xlogprefetcher.h
@@ -16,6 +16,7 @@
#include "access/xlogdefs.h"
#include "access/xlogreader.h"
#include "access/xlogrecord.h"
+#include "utils/timestamp.h"
/* GUCs */
extern PGDLLIMPORT int recovery_prefetch;
@@ -37,7 +38,7 @@ extern void XLogPrefetchReconfigure(void);
extern size_t XLogPrefetchShmemSize(void);
extern void XLogPrefetchShmemInit(void);
-extern void XLogPrefetchResetStats(void);
+extern TimestampTz XLogPrefetchResetStats(void);
extern XLogPrefetcher *XLogPrefetcherAllocate(XLogReaderState *reader);
extern void XLogPrefetcherFree(XLogPrefetcher *prefetcher);
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 6ae064492e5..5dffc8ccb4b 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -6156,7 +6156,7 @@
{ oid => '3775',
descr => 'statistics: reset collected statistics shared across the cluster',
proname => 'pg_stat_reset_shared', proisstrict => 'f', provolatile => 'v',
- prorettype => 'void', proargtypes => 'text',
+ prorettype => 'timestamptz', proargtypes => 'text',
prosrc => 'pg_stat_reset_shared' },
{ oid => '3776',
descr => 'statistics: reset collected statistics for a single table or index in the current database or shared across all databases in the cluster',
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index 1573b4fe4fa..a7a1ef2167a 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -526,7 +526,7 @@ extern void pgstat_force_next_flush(void);
extern TimestampTz pgstat_reset_counters(void);
extern void pgstat_reset(PgStat_Kind kind, Oid dboid, uint64 objid);
-extern void pgstat_reset_of_kind(PgStat_Kind kind);
+extern TimestampTz pgstat_reset_of_kind(PgStat_Kind kind);
/* stats accessors */
extern void pgstat_clear_snapshot(void);
diff --git a/src/test/regress/expected/stats.out b/src/test/regress/expected/stats.out
index 5db2d81afb1..34a2351e29b 100644
--- a/src/test/regress/expected/stats.out
+++ b/src/test/regress/expected/stats.out
@@ -1014,10 +1014,10 @@ SELECT stats_reset > :'slru_notify_reset_ts'::timestamptz FROM pg_stat_slru WHER
-- Test that reset_shared with archiver specified as the stats type works
SELECT stats_reset AS archiver_reset_ts FROM pg_stat_archiver \gset
-SELECT pg_stat_reset_shared('archiver');
- pg_stat_reset_shared
-----------------------
-
+SELECT pg_stat_reset_shared('archiver') IS NOT NULL AS t;
+ t
+---
+ t
(1 row)
SELECT stats_reset > :'archiver_reset_ts'::timestamptz FROM pg_stat_archiver;
@@ -1028,10 +1028,10 @@ SELECT stats_reset > :'archiver_reset_ts'::timestamptz FROM pg_stat_archiver;
-- Test that reset_shared with bgwriter specified as the stats type works
SELECT stats_reset AS bgwriter_reset_ts FROM pg_stat_bgwriter \gset
-SELECT pg_stat_reset_shared('bgwriter');
- pg_stat_reset_shared
-----------------------
-
+SELECT pg_stat_reset_shared('bgwriter') IS NOT NULL AS t;
+ t
+---
+ t
(1 row)
SELECT stats_reset > :'bgwriter_reset_ts'::timestamptz FROM pg_stat_bgwriter;
@@ -1042,10 +1042,10 @@ SELECT stats_reset > :'bgwriter_reset_ts'::timestamptz FROM pg_stat_bgwriter;
-- Test that reset_shared with checkpointer specified as the stats type works
SELECT stats_reset AS checkpointer_reset_ts FROM pg_stat_checkpointer \gset
-SELECT pg_stat_reset_shared('checkpointer');
- pg_stat_reset_shared
-----------------------
-
+SELECT pg_stat_reset_shared('checkpointer') IS NOT NULL AS t;
+ t
+---
+ t
(1 row)
SELECT stats_reset > :'checkpointer_reset_ts'::timestamptz FROM pg_stat_checkpointer;
@@ -1056,10 +1056,10 @@ SELECT stats_reset > :'checkpointer_reset_ts'::timestamptz FROM pg_stat_checkpoi
-- Test that reset_shared with recovery_prefetch specified as the stats type works
SELECT stats_reset AS recovery_prefetch_reset_ts FROM pg_stat_recovery_prefetch \gset
-SELECT pg_stat_reset_shared('recovery_prefetch');
- pg_stat_reset_shared
-----------------------
-
+SELECT pg_stat_reset_shared('recovery_prefetch') IS NOT NULL AS t;
+ t
+---
+ t
(1 row)
SELECT stats_reset > :'recovery_prefetch_reset_ts'::timestamptz FROM pg_stat_recovery_prefetch;
@@ -1070,10 +1070,10 @@ SELECT stats_reset > :'recovery_prefetch_reset_ts'::timestamptz FROM pg_stat_rec
-- Test that reset_shared with slru specified as the stats type works
SELECT max(stats_reset) AS slru_reset_ts FROM pg_stat_slru \gset
-SELECT pg_stat_reset_shared('slru');
- pg_stat_reset_shared
-----------------------
-
+SELECT pg_stat_reset_shared('slru') IS NOT NULL AS t;
+ t
+---
+ t
(1 row)
SELECT max(stats_reset) > :'slru_reset_ts'::timestamptz FROM pg_stat_slru;
@@ -1084,10 +1084,10 @@ SELECT max(stats_reset) > :'slru_reset_ts'::timestamptz FROM pg_stat_slru;
-- Test that reset_shared with wal specified as the stats type works
SELECT stats_reset AS wal_reset_ts FROM pg_stat_wal \gset
-SELECT pg_stat_reset_shared('wal');
- pg_stat_reset_shared
-----------------------
-
+SELECT pg_stat_reset_shared('wal') IS NOT NULL AS t;
+ t
+---
+ t
(1 row)
SELECT stats_reset > :'wal_reset_ts'::timestamptz FROM pg_stat_wal;
@@ -1696,10 +1696,10 @@ SELECT sum(evictions) + sum(reuses) + sum(extends) + sum(fsyncs) + sum(reads) +
FROM pg_stat_io \gset
SELECT sum(evictions) + sum(reuses) + sum(extends) + sum(fsyncs) + sum(reads) + sum(writes) + sum(writebacks) + sum(hits) AS my_io_stats_pre_reset
FROM pg_stat_get_backend_io(pg_backend_pid()) \gset
-SELECT pg_stat_reset_shared('io');
- pg_stat_reset_shared
-----------------------
-
+SELECT pg_stat_reset_shared('io') IS NOT NULL AS t;
+ t
+---
+ t
(1 row)
SELECT sum(evictions) + sum(reuses) + sum(extends) + sum(fsyncs) + sum(reads) + sum(writes) + sum(writebacks) + sum(hits) AS io_stats_post_reset
diff --git a/src/test/regress/sql/stats.sql b/src/test/regress/sql/stats.sql
index 5c77ccc1319..8249c2d4d89 100644
--- a/src/test/regress/sql/stats.sql
+++ b/src/test/regress/sql/stats.sql
@@ -478,32 +478,32 @@ SELECT stats_reset > :'slru_notify_reset_ts'::timestamptz FROM pg_stat_slru WHER
-- Test that reset_shared with archiver specified as the stats type works
SELECT stats_reset AS archiver_reset_ts FROM pg_stat_archiver \gset
-SELECT pg_stat_reset_shared('archiver');
+SELECT pg_stat_reset_shared('archiver') IS NOT NULL AS t;
SELECT stats_reset > :'archiver_reset_ts'::timestamptz FROM pg_stat_archiver;
-- Test that reset_shared with bgwriter specified as the stats type works
SELECT stats_reset AS bgwriter_reset_ts FROM pg_stat_bgwriter \gset
-SELECT pg_stat_reset_shared('bgwriter');
+SELECT pg_stat_reset_shared('bgwriter') IS NOT NULL AS t;
SELECT stats_reset > :'bgwriter_reset_ts'::timestamptz FROM pg_stat_bgwriter;
-- Test that reset_shared with checkpointer specified as the stats type works
SELECT stats_reset AS checkpointer_reset_ts FROM pg_stat_checkpointer \gset
-SELECT pg_stat_reset_shared('checkpointer');
+SELECT pg_stat_reset_shared('checkpointer') IS NOT NULL AS t;
SELECT stats_reset > :'checkpointer_reset_ts'::timestamptz FROM pg_stat_checkpointer;
-- Test that reset_shared with recovery_prefetch specified as the stats type works
SELECT stats_reset AS recovery_prefetch_reset_ts FROM pg_stat_recovery_prefetch \gset
-SELECT pg_stat_reset_shared('recovery_prefetch');
+SELECT pg_stat_reset_shared('recovery_prefetch') IS NOT NULL AS t;
SELECT stats_reset > :'recovery_prefetch_reset_ts'::timestamptz FROM pg_stat_recovery_prefetch;
-- Test that reset_shared with slru specified as the stats type works
SELECT max(stats_reset) AS slru_reset_ts FROM pg_stat_slru \gset
-SELECT pg_stat_reset_shared('slru');
+SELECT pg_stat_reset_shared('slru') IS NOT NULL AS t;
SELECT max(stats_reset) > :'slru_reset_ts'::timestamptz FROM pg_stat_slru;
-- Test that reset_shared with wal specified as the stats type works
SELECT stats_reset AS wal_reset_ts FROM pg_stat_wal \gset
-SELECT pg_stat_reset_shared('wal');
+SELECT pg_stat_reset_shared('wal') IS NOT NULL AS t;
SELECT stats_reset > :'wal_reset_ts'::timestamptz FROM pg_stat_wal;
-- Test error case for reset_shared with unknown stats type
@@ -813,7 +813,7 @@ SELECT sum(evictions) + sum(reuses) + sum(extends) + sum(fsyncs) + sum(reads) +
FROM pg_stat_io \gset
SELECT sum(evictions) + sum(reuses) + sum(extends) + sum(fsyncs) + sum(reads) + sum(writes) + sum(writebacks) + sum(hits) AS my_io_stats_pre_reset
FROM pg_stat_get_backend_io(pg_backend_pid()) \gset
-SELECT pg_stat_reset_shared('io');
+SELECT pg_stat_reset_shared('io') IS NOT NULL AS t;
SELECT sum(evictions) + sum(reuses) + sum(extends) + sum(fsyncs) + sum(reads) + sum(writes) + sum(writebacks) + sum(hits) AS io_stats_post_reset
FROM pg_stat_io \gset
SELECT :io_stats_post_reset < :io_stats_pre_reset;
--
2.47.3
v2-0003-Make-pg_stat_reset_single_table_counters-return-t.patchapplication/octet-stream; name=v2-0003-Make-pg_stat_reset_single_table_counters-return-t.patchDownload
From ad8de34433441b7ce405c9ed51849082cf2e9632 Mon Sep 17 00:00:00 2001
From: Shinya Kato <shinya11.kato@gmail.com>
Date: Fri, 2 May 2025 15:30:39 +0900
Subject: [PATCH v2 3/9] Make pg_stat_reset_single_table_counters() return the
reset time
---
doc/src/sgml/monitoring.sgml | 5 +++--
src/backend/utils/activity/pgstat.c | 4 +++-
src/backend/utils/adt/pgstatfuncs.c | 5 +++--
src/include/catalog/pg_proc.dat | 2 +-
src/include/pgstat.h | 2 +-
src/test/regress/expected/stats.out | 16 ++++++++--------
src/test/regress/sql/stats.sql | 4 ++--
7 files changed, 21 insertions(+), 17 deletions(-)
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index 521ad113012..99fdc8aabac 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -5095,11 +5095,12 @@ description | Waiting for a newly initialized WAL file to reach durable storage
<primary>pg_stat_reset_single_table_counters</primary>
</indexterm>
<function>pg_stat_reset_single_table_counters</function> ( <type>oid</type> )
- <returnvalue>void</returnvalue>
+ <returnvalue>timestamp with time zone</returnvalue>
</para>
<para>
Resets statistics for a single table or index in the current database
- or shared across all databases in the cluster to zero.
+ or shared across all databases in the cluster to zero, and returns the
+ time of the reset.
</para>
<para>
This function is restricted to superusers by default, but other users
diff --git a/src/backend/utils/activity/pgstat.c b/src/backend/utils/activity/pgstat.c
index 62d92ab3c09..a9e1e7bd7ce 100644
--- a/src/backend/utils/activity/pgstat.c
+++ b/src/backend/utils/activity/pgstat.c
@@ -851,7 +851,7 @@ pgstat_reset_counters(void)
* Permission checking for this function is managed through the normal
* GRANT system.
*/
-void
+TimestampTz
pgstat_reset(PgStat_Kind kind, Oid dboid, uint64 objid)
{
const PgStat_KindInfo *kind_info = pgstat_get_kind_info(kind);
@@ -865,6 +865,8 @@ pgstat_reset(PgStat_Kind kind, Oid dboid, uint64 objid)
if (!kind_info->accessed_across_databases)
pgstat_reset_database_timestamp(dboid, ts);
+
+ return ts;
}
/*
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index 36404fac4c6..bc985f6eaad 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -1922,10 +1922,11 @@ pg_stat_reset_single_table_counters(PG_FUNCTION_ARGS)
{
Oid taboid = PG_GETARG_OID(0);
Oid dboid = (IsSharedRelation(taboid) ? InvalidOid : MyDatabaseId);
+ TimestampTz ts;
- pgstat_reset(PGSTAT_KIND_RELATION, dboid, taboid);
+ ts = pgstat_reset(PGSTAT_KIND_RELATION, dboid, taboid);
- PG_RETURN_VOID();
+ PG_RETURN_TIMESTAMPTZ(ts);
}
Datum
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 5dffc8ccb4b..64f0a3d2d05 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -6161,7 +6161,7 @@
{ oid => '3776',
descr => 'statistics: reset collected statistics for a single table or index in the current database or shared across all databases in the cluster',
proname => 'pg_stat_reset_single_table_counters', provolatile => 'v',
- prorettype => 'void', proargtypes => 'oid',
+ prorettype => 'timestamptz', proargtypes => 'oid',
prosrc => 'pg_stat_reset_single_table_counters' },
{ oid => '3777',
descr => 'statistics: reset collected statistics for a single function in the current database',
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index a7a1ef2167a..03bcbb923cf 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -525,7 +525,7 @@ extern long pgstat_report_stat(bool force);
extern void pgstat_force_next_flush(void);
extern TimestampTz pgstat_reset_counters(void);
-extern void pgstat_reset(PgStat_Kind kind, Oid dboid, uint64 objid);
+extern TimestampTz pgstat_reset(PgStat_Kind kind, Oid dboid, uint64 objid);
extern TimestampTz pgstat_reset_of_kind(PgStat_Kind kind);
/* stats accessors */
diff --git a/src/test/regress/expected/stats.out b/src/test/regress/expected/stats.out
index 34a2351e29b..983930e5009 100644
--- a/src/test/regress/expected/stats.out
+++ b/src/test/regress/expected/stats.out
@@ -666,10 +666,10 @@ SELECT last_seq_scan, last_idx_scan FROM pg_stat_all_tables WHERE relid = 'test_
(1 row)
COMMIT;
-SELECT pg_stat_reset_single_table_counters('test_last_scan'::regclass);
- pg_stat_reset_single_table_counters
--------------------------------------
-
+SELECT pg_stat_reset_single_table_counters('test_last_scan'::regclass) IS NOT NULL AS t;
+ t
+---
+ t
(1 row)
SELECT seq_scan, idx_scan FROM pg_stat_all_tables WHERE relid = 'test_last_scan'::regclass;
@@ -877,10 +877,10 @@ SELECT (n_tup_ins + n_tup_upd) > 0 AS has_data FROM pg_stat_all_tables
t
(1 row)
-SELECT pg_stat_reset_single_table_counters('pg_shdescription'::regclass);
- pg_stat_reset_single_table_counters
--------------------------------------
-
+SELECT pg_stat_reset_single_table_counters('pg_shdescription'::regclass) IS NOT NULL AS t;
+ t
+---
+ t
(1 row)
SELECT (n_tup_ins + n_tup_upd) > 0 AS has_data FROM pg_stat_all_tables
diff --git a/src/test/regress/sql/stats.sql b/src/test/regress/sql/stats.sql
index 8249c2d4d89..1801b0fec9a 100644
--- a/src/test/regress/sql/stats.sql
+++ b/src/test/regress/sql/stats.sql
@@ -312,7 +312,7 @@ SELECT pg_stat_force_next_flush();
SELECT last_seq_scan, last_idx_scan FROM pg_stat_all_tables WHERE relid = 'test_last_scan'::regclass;
COMMIT;
-SELECT pg_stat_reset_single_table_counters('test_last_scan'::regclass);
+SELECT pg_stat_reset_single_table_counters('test_last_scan'::regclass) IS NOT NULL AS t;
SELECT seq_scan, idx_scan FROM pg_stat_all_tables WHERE relid = 'test_last_scan'::regclass;
-- ensure we start out with exactly one index and sequential scan
@@ -402,7 +402,7 @@ COMMIT;
-- check that the stats are reset.
SELECT (n_tup_ins + n_tup_upd) > 0 AS has_data FROM pg_stat_all_tables
WHERE relid = 'pg_shdescription'::regclass;
-SELECT pg_stat_reset_single_table_counters('pg_shdescription'::regclass);
+SELECT pg_stat_reset_single_table_counters('pg_shdescription'::regclass) IS NOT NULL AS t;
SELECT (n_tup_ins + n_tup_upd) > 0 AS has_data FROM pg_stat_all_tables
WHERE relid = 'pg_shdescription'::regclass;
--
2.47.3
v2-0004-Make-pg_stat_reset_backend_stats-return-the-reset.patchapplication/octet-stream; name=v2-0004-Make-pg_stat_reset_backend_stats-return-the-reset.patchDownload
From 1d83c84ff849afbec324f5d21820b185b1104166 Mon Sep 17 00:00:00 2001
From: Shinya Kato <shinya11.kato@gmail.com>
Date: Fri, 2 May 2025 15:41:00 +0900
Subject: [PATCH v2 4/9] Make pg_stat_reset_backend_stats() return the reset
time
---
doc/src/sgml/monitoring.sgml | 5 +++--
src/backend/utils/adt/pgstatfuncs.c | 11 ++++++-----
src/include/catalog/pg_proc.dat | 2 +-
src/test/regress/expected/stats.out | 9 ++++++++-
src/test/regress/sql/stats.sql | 4 +++-
5 files changed, 21 insertions(+), 10 deletions(-)
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index 99fdc8aabac..f48aefd054c 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -5114,11 +5114,12 @@ description | Waiting for a newly initialized WAL file to reach durable storage
<primary>pg_stat_reset_backend_stats</primary>
</indexterm>
<function>pg_stat_reset_backend_stats</function> ( <type>integer</type> )
- <returnvalue>void</returnvalue>
+ <returnvalue>timestamp with time zone</returnvalue>
</para>
<para>
Resets statistics for a single backend with the specified process ID
- to zero.
+ to zero, and returns the time of the reset or <literal>NULL</literal>
+ if an invalid backend process ID is specified.
</para>
<para>
This function is restricted to superusers by default, but other users
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index bc985f6eaad..8350233dcaf 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -1948,6 +1948,7 @@ pg_stat_reset_backend_stats(PG_FUNCTION_ARGS)
PGPROC *proc;
PgBackendStatus *beentry;
ProcNumber procNumber;
+ TimestampTz ts;
int backend_pid = PG_GETARG_INT32(0);
proc = BackendPidGetProc(backend_pid);
@@ -1957,21 +1958,21 @@ pg_stat_reset_backend_stats(PG_FUNCTION_ARGS)
proc = AuxiliaryPidGetProc(backend_pid);
if (!proc)
- PG_RETURN_VOID();
+ PG_RETURN_NULL();
procNumber = GetNumberFromPGProc(proc);
beentry = pgstat_get_beentry_by_proc_number(procNumber);
if (!beentry)
- PG_RETURN_VOID();
+ PG_RETURN_NULL();
/* Check if the backend type tracks statistics */
if (!pgstat_tracks_backend_bktype(beentry->st_backendType))
- PG_RETURN_VOID();
+ PG_RETURN_NULL();
- pgstat_reset(PGSTAT_KIND_BACKEND, InvalidOid, procNumber);
+ ts = pgstat_reset(PGSTAT_KIND_BACKEND, InvalidOid, procNumber);
- PG_RETURN_VOID();
+ PG_RETURN_TIMESTAMPTZ(ts);
}
/* Reset SLRU counters (a specific one or all of them). */
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 64f0a3d2d05..f4fe2626ef3 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -6170,7 +6170,7 @@
prosrc => 'pg_stat_reset_single_function_counters' },
{ oid => '6387', descr => 'statistics: reset statistics for a single backend',
proname => 'pg_stat_reset_backend_stats', provolatile => 'v',
- prorettype => 'void', proargtypes => 'int4',
+ prorettype => 'timestamptz', proargtypes => 'int4',
prosrc => 'pg_stat_reset_backend_stats' },
{ oid => '2307',
descr => 'statistics: reset collected statistics for a single SLRU',
diff --git a/src/test/regress/expected/stats.out b/src/test/regress/expected/stats.out
index 983930e5009..91b3be1a8af 100644
--- a/src/test/regress/expected/stats.out
+++ b/src/test/regress/expected/stats.out
@@ -1720,7 +1720,14 @@ SELECT :my_io_stats_pre_reset <= :my_io_stats_post_reset;
(1 row)
-- but pg_stat_reset_backend_stats() does
-SELECT pg_stat_reset_backend_stats(pg_backend_pid());
+SELECT pg_stat_reset_backend_stats(pg_backend_pid()) IS NOT NULL AS t;
+ t
+---
+ t
+(1 row)
+
+-- Invalid backend process ID is specified
+SELECT pg_stat_reset_backend_stats(0);
pg_stat_reset_backend_stats
-----------------------------
diff --git a/src/test/regress/sql/stats.sql b/src/test/regress/sql/stats.sql
index 1801b0fec9a..77d3e1a845d 100644
--- a/src/test/regress/sql/stats.sql
+++ b/src/test/regress/sql/stats.sql
@@ -822,7 +822,9 @@ SELECT sum(evictions) + sum(reuses) + sum(extends) + sum(fsyncs) + sum(reads) +
-- pg_stat_reset_shared() did not reset backend IO stats
SELECT :my_io_stats_pre_reset <= :my_io_stats_post_reset;
-- but pg_stat_reset_backend_stats() does
-SELECT pg_stat_reset_backend_stats(pg_backend_pid());
+SELECT pg_stat_reset_backend_stats(pg_backend_pid()) IS NOT NULL AS t;
+-- Invalid backend process ID is specified
+SELECT pg_stat_reset_backend_stats(0);
SELECT sum(evictions) + sum(reuses) + sum(extends) + sum(fsyncs) + sum(reads) + sum(writes) + sum(writebacks) + sum(hits) AS my_io_stats_post_backend_reset
FROM pg_stat_get_backend_io(pg_backend_pid()) \gset
SELECT :my_io_stats_pre_reset > :my_io_stats_post_backend_reset;
--
2.47.3
v2-0005-Make-pg_stat_reset_single_function_counters-retur.patchapplication/octet-stream; name=v2-0005-Make-pg_stat_reset_single_function_counters-retur.patchDownload
From 380c722ed5cbb8261f103a7f9a9c457711472e49 Mon Sep 17 00:00:00 2001
From: Shinya Kato <shinya11.kato@gmail.com>
Date: Fri, 2 May 2025 16:42:00 +0900
Subject: [PATCH v2 5/9] Make pg_stat_reset_single_function_counters() return
the reset time
---
doc/src/sgml/monitoring.sgml | 4 ++--
src/backend/utils/adt/pgstatfuncs.c | 5 +++--
src/include/catalog/pg_proc.dat | 2 +-
src/test/isolation/expected/stats.out | 16 ++++++++--------
src/test/isolation/expected/stats_1.out | 16 ++++++++--------
src/test/isolation/specs/stats.spec | 4 ++--
6 files changed, 24 insertions(+), 23 deletions(-)
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index f48aefd054c..2aeb835a7ab 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -5133,11 +5133,11 @@ description | Waiting for a newly initialized WAL file to reach durable storage
<primary>pg_stat_reset_single_function_counters</primary>
</indexterm>
<function>pg_stat_reset_single_function_counters</function> ( <type>oid</type> )
- <returnvalue>void</returnvalue>
+ <returnvalue>timestamp with time zone</returnvalue>
</para>
<para>
Resets statistics for a single function in the current database to
- zero.
+ zero, and returns the time of the reset.
</para>
<para>
This function is restricted to superusers by default, but other users
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index 8350233dcaf..f987d91d040 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -1933,10 +1933,11 @@ Datum
pg_stat_reset_single_function_counters(PG_FUNCTION_ARGS)
{
Oid funcoid = PG_GETARG_OID(0);
+ TimestampTz ts;
- pgstat_reset(PGSTAT_KIND_FUNCTION, MyDatabaseId, funcoid);
+ ts = pgstat_reset(PGSTAT_KIND_FUNCTION, MyDatabaseId, funcoid);
- PG_RETURN_VOID();
+ PG_RETURN_TIMESTAMPTZ(ts);
}
/*
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index f4fe2626ef3..c08ebb72b25 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -6166,7 +6166,7 @@
{ oid => '3777',
descr => 'statistics: reset collected statistics for a single function in the current database',
proname => 'pg_stat_reset_single_function_counters', provolatile => 'v',
- prorettype => 'void', proargtypes => 'oid',
+ prorettype => 'timestamptz', proargtypes => 'oid',
prosrc => 'pg_stat_reset_single_function_counters' },
{ oid => '6387', descr => 'statistics: reset statistics for a single backend',
proname => 'pg_stat_reset_backend_stats', provolatile => 'v',
diff --git a/src/test/isolation/expected/stats.out b/src/test/isolation/expected/stats.out
index be9dc186fef..132f8e55fc3 100644
--- a/src/test/isolation/expected/stats.out
+++ b/src/test/isolation/expected/stats.out
@@ -1137,10 +1137,10 @@ name |pg_stat_get_function_calls|total_above_zero|self_above_zero
test_stat_func| 3|t |t
(1 row)
-step s1_func_stats_reset: SELECT pg_stat_reset_single_function_counters('test_stat_func'::regproc);
-pg_stat_reset_single_function_counters
---------------------------------------
-
+step s1_func_stats_reset: SELECT pg_stat_reset_single_function_counters('test_stat_func'::regproc) IS NOT NULL AS t;
+t
+-
+t
(1 row)
step s1_func_stats:
@@ -1200,10 +1200,10 @@ pg_stat_get_function_calls
(1 row)
-step s1_func_stats_reset_nonexistent: SELECT pg_stat_reset_single_function_counters(12000);
-pg_stat_reset_single_function_counters
---------------------------------------
-
+step s1_func_stats_reset_nonexistent: SELECT pg_stat_reset_single_function_counters(12000) IS NOT NULL AS t;
+t
+-
+t
(1 row)
step s1_func_stats_nonexistent:
diff --git a/src/test/isolation/expected/stats_1.out b/src/test/isolation/expected/stats_1.out
index 3017c6f4619..2b16808fbb5 100644
--- a/src/test/isolation/expected/stats_1.out
+++ b/src/test/isolation/expected/stats_1.out
@@ -1137,10 +1137,10 @@ name |pg_stat_get_function_calls|total_above_zero|self_above_zero
test_stat_func| 3|t |t
(1 row)
-step s1_func_stats_reset: SELECT pg_stat_reset_single_function_counters('test_stat_func'::regproc);
-pg_stat_reset_single_function_counters
---------------------------------------
-
+step s1_func_stats_reset: SELECT pg_stat_reset_single_function_counters('test_stat_func'::regproc) IS NOT NULL AS t;
+t
+-
+t
(1 row)
step s1_func_stats:
@@ -1200,10 +1200,10 @@ pg_stat_get_function_calls
(1 row)
-step s1_func_stats_reset_nonexistent: SELECT pg_stat_reset_single_function_counters(12000);
-pg_stat_reset_single_function_counters
---------------------------------------
-
+step s1_func_stats_reset_nonexistent: SELECT pg_stat_reset_single_function_counters(12000) IS NOT NULL AS t;
+t
+-
+t
(1 row)
step s1_func_stats_nonexistent:
diff --git a/src/test/isolation/specs/stats.spec b/src/test/isolation/specs/stats.spec
index 22ef6d3206b..3893d3983f0 100644
--- a/src/test/isolation/specs/stats.spec
+++ b/src/test/isolation/specs/stats.spec
@@ -54,8 +54,8 @@ step s1_track_funcs_all { SET track_functions = 'all'; }
step s1_track_funcs_none { SET track_functions = 'none'; }
step s1_func_call { SELECT test_stat_func(); }
step s1_func_drop { DROP FUNCTION test_stat_func(); }
-step s1_func_stats_reset { SELECT pg_stat_reset_single_function_counters('test_stat_func'::regproc); }
-step s1_func_stats_reset_nonexistent { SELECT pg_stat_reset_single_function_counters(12000); }
+step s1_func_stats_reset { SELECT pg_stat_reset_single_function_counters('test_stat_func'::regproc) IS NOT NULL AS t; }
+step s1_func_stats_reset_nonexistent { SELECT pg_stat_reset_single_function_counters(12000) IS NOT NULL AS t; }
step s1_reset { SELECT pg_stat_reset() IS NOT NULL AS t; }
step s1_func_stats {
SELECT
--
2.47.3
v2-0006-Make-pg_stat_reset_slru-return-the-reset-time.patchapplication/octet-stream; name=v2-0006-Make-pg_stat_reset_slru-return-the-reset-time.patchDownload
From 33dabccd704d105a89f19d3f39f2db6c8498aa35 Mon Sep 17 00:00:00 2001
From: Shinya Kato <shinya11.kato@gmail.com>
Date: Fri, 2 May 2025 16:57:23 +0900
Subject: [PATCH v2 6/9] Make pg_stat_reset_slru() return the reset time
---
doc/src/sgml/monitoring.sgml | 4 ++--
src/backend/catalog/system_functions.sql | 2 +-
src/backend/utils/activity/pgstat_slru.c | 4 +++-
src/backend/utils/adt/pgstatfuncs.c | 7 ++++---
src/include/catalog/pg_proc.dat | 2 +-
src/include/pgstat.h | 2 +-
src/test/regress/expected/stats.out | 16 ++++++++--------
src/test/regress/sql/stats.sql | 4 ++--
8 files changed, 22 insertions(+), 19 deletions(-)
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index 2aeb835a7ab..df90f4668c4 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -5151,11 +5151,11 @@ description | Waiting for a newly initialized WAL file to reach durable storage
<primary>pg_stat_reset_slru</primary>
</indexterm>
<function>pg_stat_reset_slru</function> ( [ <parameter>target</parameter> <type>text</type> <literal>DEFAULT</literal> <literal>NULL</literal> ] )
- <returnvalue>void</returnvalue>
+ <returnvalue>timestamp with time zone</returnvalue>
</para>
<para>
Resets statistics to zero for a single SLRU cache, or for all SLRUs in
- the cluster. If <parameter>target</parameter> is
+ the cluster, and returns the time of the reset. If <parameter>target</parameter> is
<literal>NULL</literal> or is not specified, all the counters shown in
the <structname>pg_stat_slru</structname> view for all SLRU caches are
reset. The argument can be one of
diff --git a/src/backend/catalog/system_functions.sql b/src/backend/catalog/system_functions.sql
index 0f19c2c478b..4aa93250d06 100644
--- a/src/backend/catalog/system_functions.sql
+++ b/src/backend/catalog/system_functions.sql
@@ -645,7 +645,7 @@ AS 'pg_stat_reset_shared';
CREATE OR REPLACE FUNCTION
pg_stat_reset_slru(target text DEFAULT NULL)
-RETURNS void
+RETURNS timestamp with time zone
LANGUAGE INTERNAL
CALLED ON NULL INPUT VOLATILE PARALLEL SAFE
AS 'pg_stat_reset_slru';
diff --git a/src/backend/utils/activity/pgstat_slru.c b/src/backend/utils/activity/pgstat_slru.c
index da50f8a0457..89a534048fb 100644
--- a/src/backend/utils/activity/pgstat_slru.c
+++ b/src/backend/utils/activity/pgstat_slru.c
@@ -41,7 +41,7 @@ static bool have_slrustats = false;
* Permission checking for this function is managed through the normal
* GRANT system.
*/
-void
+TimestampTz
pgstat_reset_slru(const char *name)
{
TimestampTz ts = GetCurrentTimestamp();
@@ -49,6 +49,8 @@ pgstat_reset_slru(const char *name)
Assert(name != NULL);
pgstat_reset_slru_counter_internal(pgstat_get_slru_index(name), ts);
+
+ return ts;
}
/*
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index f987d91d040..b28ceab91d9 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -1981,16 +1981,17 @@ Datum
pg_stat_reset_slru(PG_FUNCTION_ARGS)
{
char *target = NULL;
+ TimestampTz ts;
if (PG_ARGISNULL(0))
- pgstat_reset_of_kind(PGSTAT_KIND_SLRU);
+ ts = pgstat_reset_of_kind(PGSTAT_KIND_SLRU);
else
{
target = text_to_cstring(PG_GETARG_TEXT_PP(0));
- pgstat_reset_slru(target);
+ ts = pgstat_reset_slru(target);
}
- PG_RETURN_VOID();
+ PG_RETURN_TIMESTAMPTZ(ts);
}
/* Reset replication slots stats (a specific one or all of them). */
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index c08ebb72b25..87b14848c22 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -6175,7 +6175,7 @@
{ oid => '2307',
descr => 'statistics: reset collected statistics for a single SLRU',
proname => 'pg_stat_reset_slru', proisstrict => 'f', provolatile => 'v',
- prorettype => 'void', proargtypes => 'text', proargnames => '{target}',
+ prorettype => 'timestamptz', proargtypes => 'text', proargnames => '{target}',
prosrc => 'pg_stat_reset_slru' },
{ oid => '6170',
descr => 'statistics: reset collected statistics for a single replication slot',
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index 03bcbb923cf..2d741980ef5 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -746,7 +746,7 @@ extern PgStat_StatReplSlotEntry *pgstat_fetch_replslot(NameData slotname);
* Functions in pgstat_slru.c
*/
-extern void pgstat_reset_slru(const char *);
+extern TimestampTz pgstat_reset_slru(const char *);
extern void pgstat_count_slru_blocks_zeroed(int slru_idx);
extern void pgstat_count_slru_blocks_hit(int slru_idx);
extern void pgstat_count_slru_blocks_read(int slru_idx);
diff --git a/src/test/regress/expected/stats.out b/src/test/regress/expected/stats.out
index 91b3be1a8af..4c5612ab1ba 100644
--- a/src/test/regress/expected/stats.out
+++ b/src/test/regress/expected/stats.out
@@ -980,10 +980,10 @@ WHERE pg_stat_get_backend_pid(beid) = pg_backend_pid();
-- Test that reset_slru with a specified SLRU works.
SELECT stats_reset AS slru_commit_ts_reset_ts FROM pg_stat_slru WHERE name = 'commit_timestamp' \gset
SELECT stats_reset AS slru_notify_reset_ts FROM pg_stat_slru WHERE name = 'notify' \gset
-SELECT pg_stat_reset_slru('commit_timestamp');
- pg_stat_reset_slru
---------------------
-
+SELECT pg_stat_reset_slru('commit_timestamp') IS NOT NULL AS t;
+ t
+---
+ t
(1 row)
SELECT stats_reset > :'slru_commit_ts_reset_ts'::timestamptz FROM pg_stat_slru WHERE name = 'commit_timestamp';
@@ -994,10 +994,10 @@ SELECT stats_reset > :'slru_commit_ts_reset_ts'::timestamptz FROM pg_stat_slru W
SELECT stats_reset AS slru_commit_ts_reset_ts FROM pg_stat_slru WHERE name = 'commit_timestamp' \gset
-- Test that multiple SLRUs are reset when no specific SLRU provided to reset function
-SELECT pg_stat_reset_slru();
- pg_stat_reset_slru
---------------------
-
+SELECT pg_stat_reset_slru() IS NOT NULL AS t;
+ t
+---
+ t
(1 row)
SELECT stats_reset > :'slru_commit_ts_reset_ts'::timestamptz FROM pg_stat_slru WHERE name = 'commit_timestamp';
diff --git a/src/test/regress/sql/stats.sql b/src/test/regress/sql/stats.sql
index 77d3e1a845d..62a8424d4c0 100644
--- a/src/test/regress/sql/stats.sql
+++ b/src/test/regress/sql/stats.sql
@@ -467,12 +467,12 @@ WHERE pg_stat_get_backend_pid(beid) = pg_backend_pid();
-- Test that reset_slru with a specified SLRU works.
SELECT stats_reset AS slru_commit_ts_reset_ts FROM pg_stat_slru WHERE name = 'commit_timestamp' \gset
SELECT stats_reset AS slru_notify_reset_ts FROM pg_stat_slru WHERE name = 'notify' \gset
-SELECT pg_stat_reset_slru('commit_timestamp');
+SELECT pg_stat_reset_slru('commit_timestamp') IS NOT NULL AS t;
SELECT stats_reset > :'slru_commit_ts_reset_ts'::timestamptz FROM pg_stat_slru WHERE name = 'commit_timestamp';
SELECT stats_reset AS slru_commit_ts_reset_ts FROM pg_stat_slru WHERE name = 'commit_timestamp' \gset
-- Test that multiple SLRUs are reset when no specific SLRU provided to reset function
-SELECT pg_stat_reset_slru();
+SELECT pg_stat_reset_slru() IS NOT NULL AS t;
SELECT stats_reset > :'slru_commit_ts_reset_ts'::timestamptz FROM pg_stat_slru WHERE name = 'commit_timestamp';
SELECT stats_reset > :'slru_notify_reset_ts'::timestamptz FROM pg_stat_slru WHERE name = 'notify';
--
2.47.3
v2-0007-Make-pg_stat_reset_replication_slot-return-the-re.patchapplication/octet-stream; name=v2-0007-Make-pg_stat_reset_replication_slot-return-the-re.patchDownload
From 1ed547640b7fe97f27032d7da6f50d2ba28db9c3 Mon Sep 17 00:00:00 2001
From: Shinya Kato <shinya11.kato@gmail.com>
Date: Fri, 2 May 2025 18:13:45 +0900
Subject: [PATCH v2 7/9] Make pg_stat_reset_replication_slot() return the reset
time
---
contrib/test_decoding/expected/stats.out | 35 +++++++++++++++-----
contrib/test_decoding/sql/stats.sql | 9 +++--
doc/src/sgml/monitoring.sgml | 9 ++---
src/backend/utils/activity/pgstat_replslot.c | 9 +++--
src/backend/utils/adt/pgstatfuncs.c | 10 ++++--
src/include/catalog/pg_proc.dat | 2 +-
src/include/pgstat.h | 2 +-
7 files changed, 55 insertions(+), 21 deletions(-)
diff --git a/contrib/test_decoding/expected/stats.out b/contrib/test_decoding/expected/stats.out
index de6dc416130..874a52eb28e 100644
--- a/contrib/test_decoding/expected/stats.out
+++ b/contrib/test_decoding/expected/stats.out
@@ -47,10 +47,10 @@ SELECT slot_name, spill_txns = 0 AS spill_txns, spill_count = 0 AS spill_count,
RESET logical_decoding_work_mem;
-- reset stats for one slot, others should be unaffected
-SELECT pg_stat_reset_replication_slot('regression_slot_stats1');
- pg_stat_reset_replication_slot
---------------------------------
-
+SELECT pg_stat_reset_replication_slot('regression_slot_stats1') IS NOT NULL AS t;
+ t
+---
+ t
(1 row)
SELECT slot_name, spill_txns = 0 AS spill_txns, spill_count = 0 AS spill_count, total_txns > 0 AS total_txns, total_bytes > 0 AS total_bytes FROM pg_stat_replication_slots ORDER BY slot_name;
@@ -62,10 +62,10 @@ SELECT slot_name, spill_txns = 0 AS spill_txns, spill_count = 0 AS spill_count,
(3 rows)
-- reset stats for all slots
-SELECT pg_stat_reset_replication_slot(NULL);
- pg_stat_reset_replication_slot
---------------------------------
-
+SELECT pg_stat_reset_replication_slot(NULL) IS NOT NULL AS t;
+ t
+---
+ t
(1 row)
SELECT slot_name, spill_txns = 0 AS spill_txns, spill_count = 0 AS spill_count, total_txns > 0 AS total_txns, total_bytes > 0 AS total_bytes FROM pg_stat_replication_slots ORDER BY slot_name;
@@ -91,6 +91,25 @@ SELECT * FROM pg_stat_get_replication_slot('do-not-exist');
do-not-exist | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
(1 row)
+-- verify physical replication slot stats are not reset
+SELECT 'init' FROM pg_create_physical_replication_slot('regression_slot_stats_physical') ps;
+ ?column?
+----------
+ init
+(1 row)
+
+SELECT pg_stat_reset_replication_slot('regression_slot_stats_physical');
+ pg_stat_reset_replication_slot
+--------------------------------
+
+(1 row)
+
+SELECT pg_drop_replication_slot('regression_slot_stats_physical');
+ pg_drop_replication_slot
+--------------------------
+
+(1 row)
+
-- spilling the xact
BEGIN;
INSERT INTO stats_test SELECT 'serialize-topbig--1:'||g.i FROM generate_series(1, 5000) g(i);
diff --git a/contrib/test_decoding/sql/stats.sql b/contrib/test_decoding/sql/stats.sql
index a022fe1bf07..8ca60f788e3 100644
--- a/contrib/test_decoding/sql/stats.sql
+++ b/contrib/test_decoding/sql/stats.sql
@@ -19,11 +19,11 @@ SELECT slot_name, spill_txns = 0 AS spill_txns, spill_count = 0 AS spill_count,
RESET logical_decoding_work_mem;
-- reset stats for one slot, others should be unaffected
-SELECT pg_stat_reset_replication_slot('regression_slot_stats1');
+SELECT pg_stat_reset_replication_slot('regression_slot_stats1') IS NOT NULL AS t;
SELECT slot_name, spill_txns = 0 AS spill_txns, spill_count = 0 AS spill_count, total_txns > 0 AS total_txns, total_bytes > 0 AS total_bytes FROM pg_stat_replication_slots ORDER BY slot_name;
-- reset stats for all slots
-SELECT pg_stat_reset_replication_slot(NULL);
+SELECT pg_stat_reset_replication_slot(NULL) IS NOT NULL AS t;
SELECT slot_name, spill_txns = 0 AS spill_txns, spill_count = 0 AS spill_count, total_txns > 0 AS total_txns, total_bytes > 0 AS total_bytes FROM pg_stat_replication_slots ORDER BY slot_name;
-- verify accessing/resetting stats for non-existent slot does something reasonable
@@ -31,6 +31,11 @@ SELECT * FROM pg_stat_get_replication_slot('do-not-exist');
SELECT pg_stat_reset_replication_slot('do-not-exist');
SELECT * FROM pg_stat_get_replication_slot('do-not-exist');
+-- verify physical replication slot stats are not reset
+SELECT 'init' FROM pg_create_physical_replication_slot('regression_slot_stats_physical') ps;
+SELECT pg_stat_reset_replication_slot('regression_slot_stats_physical');
+SELECT pg_drop_replication_slot('regression_slot_stats_physical');
+
-- spilling the xact
BEGIN;
INSERT INTO stats_test SELECT 'serialize-topbig--1:'||g.i FROM generate_series(1, 5000) g(i);
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index df90f4668c4..27f22e6fd76 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -5183,12 +5183,13 @@ description | Waiting for a newly initialized WAL file to reach durable storage
<primary>pg_stat_reset_replication_slot</primary>
</indexterm>
<function>pg_stat_reset_replication_slot</function> ( <type>text</type> )
- <returnvalue>void</returnvalue>
+ <returnvalue>timestamp with time zone</returnvalue>
</para>
<para>
- Resets statistics of the replication slot defined by the argument. If
- the argument is <literal>NULL</literal>, resets statistics for all
- the replication slots.
+ Resets statistics of the replication slot defined by the argument, and
+ returns the time of the reset or <literal>NULL</literal> if the replication
+ slot defined by the argument is a physical slot. If the argument is
+ <literal>NULL</literal>, resets statistics for all the replication slots.
</para>
<para>
This function is restricted to superusers by default, but other users
diff --git a/src/backend/utils/activity/pgstat_replslot.c b/src/backend/utils/activity/pgstat_replslot.c
index ccfb11c49bf..67f5c53989e 100644
--- a/src/backend/utils/activity/pgstat_replslot.c
+++ b/src/backend/utils/activity/pgstat_replslot.c
@@ -37,11 +37,14 @@ static int get_replslot_index(const char *name, bool need_lock);
*
* Permission checking for this function is managed through the normal
* GRANT system.
+ *
+ * NB: Returns the time of the reset or 0 if it is a physical slot.
*/
-void
+TimestampTz
pgstat_reset_replslot(const char *name)
{
ReplicationSlot *slot;
+ TimestampTz ts = 0;
Assert(name != NULL);
@@ -61,10 +64,12 @@ pgstat_reset_replslot(const char *name)
* as we collect stats only for logical slots.
*/
if (SlotIsLogical(slot))
- pgstat_reset(PGSTAT_KIND_REPLSLOT, InvalidOid,
+ ts = pgstat_reset(PGSTAT_KIND_REPLSLOT, InvalidOid,
ReplicationSlotIndex(slot));
LWLockRelease(ReplicationSlotControlLock);
+
+ return ts;
}
/*
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index b28ceab91d9..7d9df708266 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -1999,16 +1999,20 @@ Datum
pg_stat_reset_replication_slot(PG_FUNCTION_ARGS)
{
char *target = NULL;
+ TimestampTz ts;
if (PG_ARGISNULL(0))
- pgstat_reset_of_kind(PGSTAT_KIND_REPLSLOT);
+ ts = pgstat_reset_of_kind(PGSTAT_KIND_REPLSLOT);
else
{
target = text_to_cstring(PG_GETARG_TEXT_PP(0));
- pgstat_reset_replslot(target);
+ ts = pgstat_reset_replslot(target);
}
- PG_RETURN_VOID();
+ if (ts == 0)
+ PG_RETURN_NULL();
+ else
+ PG_RETURN_TIMESTAMPTZ(ts);
}
/* Reset subscription stats (a specific one or all of them) */
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 87b14848c22..757c4711a85 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -6180,7 +6180,7 @@
{ oid => '6170',
descr => 'statistics: reset collected statistics for a single replication slot',
proname => 'pg_stat_reset_replication_slot', proisstrict => 'f',
- provolatile => 'v', prorettype => 'void', proargtypes => 'text',
+ provolatile => 'v', prorettype => 'timestamptz', proargtypes => 'text',
prosrc => 'pg_stat_reset_replication_slot' },
{ oid => '6232',
descr => 'statistics: reset collected statistics for a single subscription',
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index 2d741980ef5..d2d9479fc0f 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -733,7 +733,7 @@ extern PgStat_TableStatus *find_tabstat_entry(Oid rel_id);
* Functions in pgstat_replslot.c
*/
-extern void pgstat_reset_replslot(const char *name);
+extern TimestampTz pgstat_reset_replslot(const char *name);
struct ReplicationSlot;
extern void pgstat_report_replslot(struct ReplicationSlot *slot, const PgStat_StatReplSlotEntry *repSlotStat);
extern void pgstat_create_replslot(struct ReplicationSlot *slot);
--
2.47.3
v2-0008-Make-pg_stat_reset_subscription_stats-return-the-.patchapplication/octet-stream; name=v2-0008-Make-pg_stat_reset_subscription_stats-return-the-.patchDownload
From 33b41c31f5c63b755015589a94f1dd5d8807893a Mon Sep 17 00:00:00 2001
From: Shinya Kato <shinya11.kato@gmail.com>
Date: Fri, 2 May 2025 18:48:28 +0900
Subject: [PATCH v2 8/9] Make pg_stat_reset_subscription_stats() return the
reset time
---
doc/src/sgml/monitoring.sgml | 8 ++++----
src/backend/utils/adt/pgstatfuncs.c | 7 ++++---
src/include/catalog/pg_proc.dat | 2 +-
src/test/regress/expected/subscription.out | 16 ++++++++--------
src/test/regress/sql/subscription.sql | 4 ++--
5 files changed, 19 insertions(+), 18 deletions(-)
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index 27f22e6fd76..e7ba7932b1a 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -5203,13 +5203,13 @@ description | Waiting for a newly initialized WAL file to reach durable storage
<primary>pg_stat_reset_subscription_stats</primary>
</indexterm>
<function>pg_stat_reset_subscription_stats</function> ( <type>oid</type> )
- <returnvalue>void</returnvalue>
+ <returnvalue>timestamp with time zone</returnvalue>
</para>
<para>
Resets statistics for a single subscription shown in the
- <structname>pg_stat_subscription_stats</structname> view to zero. If
- the argument is <literal>NULL</literal>, reset statistics for all
- subscriptions.
+ <structname>pg_stat_subscription_stats</structname> view to zero, and
+ returns the time of the reset. If the argument is
+ <literal>NULL</literal>, reset statistics for all subscriptions.
</para>
<para>
This function is restricted to superusers by default, but other users
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index 7d9df708266..7b23fce1712 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -2020,11 +2020,12 @@ Datum
pg_stat_reset_subscription_stats(PG_FUNCTION_ARGS)
{
Oid subid;
+ TimestampTz ts;
if (PG_ARGISNULL(0))
{
/* Clear all subscription stats */
- pgstat_reset_of_kind(PGSTAT_KIND_SUBSCRIPTION);
+ ts = pgstat_reset_of_kind(PGSTAT_KIND_SUBSCRIPTION);
}
else
{
@@ -2034,10 +2035,10 @@ pg_stat_reset_subscription_stats(PG_FUNCTION_ARGS)
ereport(ERROR,
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
errmsg("invalid subscription OID %u", subid)));
- pgstat_reset(PGSTAT_KIND_SUBSCRIPTION, InvalidOid, subid);
+ ts = pgstat_reset(PGSTAT_KIND_SUBSCRIPTION, InvalidOid, subid);
}
- PG_RETURN_VOID();
+ PG_RETURN_TIMESTAMPTZ(ts);
}
Datum
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 757c4711a85..1f7f5f95d01 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -6185,7 +6185,7 @@
{ oid => '6232',
descr => 'statistics: reset collected statistics for a single subscription',
proname => 'pg_stat_reset_subscription_stats', proisstrict => 'f',
- provolatile => 'v', prorettype => 'void', proargtypes => 'oid',
+ provolatile => 'v', prorettype => 'timestamptz', proargtypes => 'oid',
prosrc => 'pg_stat_reset_subscription_stats' },
{ oid => '3163', descr => 'current trigger depth',
diff --git a/src/test/regress/expected/subscription.out b/src/test/regress/expected/subscription.out
index c7f1266fc2f..4b3e6c496d9 100644
--- a/src/test/regress/expected/subscription.out
+++ b/src/test/regress/expected/subscription.out
@@ -47,10 +47,10 @@ SELECT subname, stats_reset IS NULL stats_reset_is_null FROM pg_stat_subscriptio
regress_testsub | t
(1 row)
-SELECT pg_stat_reset_subscription_stats(oid) FROM pg_subscription WHERE subname = 'regress_testsub';
- pg_stat_reset_subscription_stats
-----------------------------------
-
+SELECT pg_stat_reset_subscription_stats(oid) IS NOT NULL AS t FROM pg_subscription WHERE subname = 'regress_testsub';
+ t
+---
+ t
(1 row)
SELECT subname, stats_reset IS NULL stats_reset_is_null FROM pg_stat_subscription_stats WHERE subname = 'regress_testsub';
@@ -61,10 +61,10 @@ SELECT subname, stats_reset IS NULL stats_reset_is_null FROM pg_stat_subscriptio
-- Reset the stats again and check if the new reset_stats is updated.
SELECT stats_reset as prev_stats_reset FROM pg_stat_subscription_stats WHERE subname = 'regress_testsub' \gset
-SELECT pg_stat_reset_subscription_stats(oid) FROM pg_subscription WHERE subname = 'regress_testsub';
- pg_stat_reset_subscription_stats
-----------------------------------
-
+SELECT pg_stat_reset_subscription_stats(oid) IS NOT NULL AS t FROM pg_subscription WHERE subname = 'regress_testsub';
+ t
+---
+ t
(1 row)
SELECT :'prev_stats_reset' < stats_reset FROM pg_stat_subscription_stats WHERE subname = 'regress_testsub';
diff --git a/src/test/regress/sql/subscription.sql b/src/test/regress/sql/subscription.sql
index ef0c298d2df..8f4aa760fe2 100644
--- a/src/test/regress/sql/subscription.sql
+++ b/src/test/regress/sql/subscription.sql
@@ -34,12 +34,12 @@ SELECT obj_description(s.oid, 'pg_subscription') FROM pg_subscription s;
-- Check if the subscription stats are created and stats_reset is updated
-- by pg_stat_reset_subscription_stats().
SELECT subname, stats_reset IS NULL stats_reset_is_null FROM pg_stat_subscription_stats WHERE subname = 'regress_testsub';
-SELECT pg_stat_reset_subscription_stats(oid) FROM pg_subscription WHERE subname = 'regress_testsub';
+SELECT pg_stat_reset_subscription_stats(oid) IS NOT NULL AS t FROM pg_subscription WHERE subname = 'regress_testsub';
SELECT subname, stats_reset IS NULL stats_reset_is_null FROM pg_stat_subscription_stats WHERE subname = 'regress_testsub';
-- Reset the stats again and check if the new reset_stats is updated.
SELECT stats_reset as prev_stats_reset FROM pg_stat_subscription_stats WHERE subname = 'regress_testsub' \gset
-SELECT pg_stat_reset_subscription_stats(oid) FROM pg_subscription WHERE subname = 'regress_testsub';
+SELECT pg_stat_reset_subscription_stats(oid) IS NOT NULL AS t FROM pg_subscription WHERE subname = 'regress_testsub';
SELECT :'prev_stats_reset' < stats_reset FROM pg_stat_subscription_stats WHERE subname = 'regress_testsub';
-- fail - name already exists
--
2.47.3
v2-0009-Make-pg_stat_clear_snapshot-return-the-reset-time.patchapplication/octet-stream; name=v2-0009-Make-pg_stat_clear_snapshot-return-the-reset-time.patchDownload
From e71f3aa6f83195f034b7a3006536998d42bf79c7 Mon Sep 17 00:00:00 2001
From: Shinya Kato <shinya11.kato@gmail.com>
Date: Fri, 2 May 2025 19:08:45 +0900
Subject: [PATCH v2 9/9] Make pg_stat_clear_snapshot() return the reset time
---
doc/src/sgml/monitoring.sgml | 5 ++--
src/backend/utils/activity/pgstat.c | 4 +++-
src/backend/utils/adt/pgstatfuncs.c | 6 +++--
src/include/catalog/pg_proc.dat | 2 +-
src/include/pgstat.h | 2 +-
src/test/isolation/expected/stats.out | 32 ++++++++++++-------------
src/test/isolation/expected/stats_1.out | 32 ++++++++++++-------------
src/test/isolation/specs/stats.spec | 2 +-
src/test/regress/expected/stats.out | 8 +++----
src/test/regress/sql/stats.sql | 2 +-
10 files changed, 50 insertions(+), 45 deletions(-)
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index e7ba7932b1a..8a1632eae0c 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -4995,10 +4995,11 @@ description | Waiting for a newly initialized WAL file to reach durable storage
<primary>pg_stat_clear_snapshot</primary>
</indexterm>
<function>pg_stat_clear_snapshot</function> ()
- <returnvalue>void</returnvalue>
+ <returnvalue>timestamp with time zone</returnvalue>
</para>
<para>
- Discards the current statistics snapshot or cached information.
+ Discards the current statistics snapshot or cached information, and
+ returns the time of the discard.
</para></entry>
</row>
diff --git a/src/backend/utils/activity/pgstat.c b/src/backend/utils/activity/pgstat.c
index a9e1e7bd7ce..f53dbfe6a51 100644
--- a/src/backend/utils/activity/pgstat.c
+++ b/src/backend/utils/activity/pgstat.c
@@ -902,7 +902,7 @@ pgstat_reset_of_kind(PgStat_Kind kind)
* the no-longer-wanted snapshot. Updates of stats_fetch_consistency can
* cause this routine to be called.
*/
-void
+TimestampTz
pgstat_clear_snapshot(void)
{
pgstat_assert_is_up();
@@ -932,6 +932,8 @@ pgstat_clear_snapshot(void)
/* Reset this flag, as it may be possible that a cleanup was forced. */
force_stats_snapshot_clear = false;
+
+ return GetCurrentTimestamp();
}
void *
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index 7b23fce1712..422944e75f2 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -1837,9 +1837,11 @@ pg_stat_get_snapshot_timestamp(PG_FUNCTION_ARGS)
Datum
pg_stat_clear_snapshot(PG_FUNCTION_ARGS)
{
- pgstat_clear_snapshot();
+ TimestampTz ts;
- PG_RETURN_VOID();
+ ts = pgstat_clear_snapshot();
+
+ PG_RETURN_TIMESTAMPTZ(ts);
}
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 1f7f5f95d01..a02bb054114 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -6142,7 +6142,7 @@
{ oid => '2230',
descr => 'statistics: discard current transaction\'s statistics snapshot',
proname => 'pg_stat_clear_snapshot', proisstrict => 'f', provolatile => 'v',
- proparallel => 'r', prorettype => 'void', proargtypes => '',
+ proparallel => 'r', prorettype => 'timestamptz', proargtypes => '',
prosrc => 'pg_stat_clear_snapshot' },
{ oid => '2137',
descr => 'statistics: force stats to be flushed after the next commit',
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index d2d9479fc0f..b5eb00cbc9e 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -529,7 +529,7 @@ extern TimestampTz pgstat_reset(PgStat_Kind kind, Oid dboid, uint64 objid);
extern TimestampTz pgstat_reset_of_kind(PgStat_Kind kind);
/* stats accessors */
-extern void pgstat_clear_snapshot(void);
+extern TimestampTz pgstat_clear_snapshot(void);
extern TimestampTz pgstat_get_stat_snapshot_timestamp(bool *have_snapshot);
/* helpers */
diff --git a/src/test/isolation/expected/stats.out b/src/test/isolation/expected/stats.out
index 132f8e55fc3..43cfc1880c6 100644
--- a/src/test/isolation/expected/stats.out
+++ b/src/test/isolation/expected/stats.out
@@ -3422,10 +3422,10 @@ step s1_slru_check_stats:
t
(1 row)
-step s1_clear_snapshot: SELECT pg_stat_clear_snapshot();
-pg_stat_clear_snapshot
-----------------------
-
+step s1_clear_snapshot: SELECT pg_stat_clear_snapshot() IS NOT NULL AS t;
+t
+-
+t
(1 row)
step s1_slru_check_stats:
@@ -3495,10 +3495,10 @@ step s1_slru_check_stats:
f
(1 row)
-step s1_clear_snapshot: SELECT pg_stat_clear_snapshot();
-pg_stat_clear_snapshot
-----------------------
-
+step s1_clear_snapshot: SELECT pg_stat_clear_snapshot() IS NOT NULL AS t;
+t
+-
+t
(1 row)
step s1_slru_check_stats:
@@ -3568,10 +3568,10 @@ step s1_slru_check_stats:
f
(1 row)
-step s1_clear_snapshot: SELECT pg_stat_clear_snapshot();
-pg_stat_clear_snapshot
-----------------------
-
+step s1_clear_snapshot: SELECT pg_stat_clear_snapshot() IS NOT NULL AS t;
+t
+-
+t
(1 row)
step s1_slru_check_stats:
@@ -3712,10 +3712,10 @@ name |pg_stat_get_function_calls|total_above_zero|self_above_zero
test_stat_func| | |
(1 row)
-step s1_clear_snapshot: SELECT pg_stat_clear_snapshot();
-pg_stat_clear_snapshot
-----------------------
-
+step s1_clear_snapshot: SELECT pg_stat_clear_snapshot() IS NOT NULL AS t;
+t
+-
+t
(1 row)
step s1_func_stats:
diff --git a/src/test/isolation/expected/stats_1.out b/src/test/isolation/expected/stats_1.out
index 2b16808fbb5..f83420dcd7d 100644
--- a/src/test/isolation/expected/stats_1.out
+++ b/src/test/isolation/expected/stats_1.out
@@ -3446,10 +3446,10 @@ step s1_slru_check_stats:
t
(1 row)
-step s1_clear_snapshot: SELECT pg_stat_clear_snapshot();
-pg_stat_clear_snapshot
-----------------------
-
+step s1_clear_snapshot: SELECT pg_stat_clear_snapshot() IS NOT NULL AS t;
+t
+-
+t
(1 row)
step s1_slru_check_stats:
@@ -3519,10 +3519,10 @@ step s1_slru_check_stats:
f
(1 row)
-step s1_clear_snapshot: SELECT pg_stat_clear_snapshot();
-pg_stat_clear_snapshot
-----------------------
-
+step s1_clear_snapshot: SELECT pg_stat_clear_snapshot() IS NOT NULL AS t;
+t
+-
+t
(1 row)
step s1_slru_check_stats:
@@ -3592,10 +3592,10 @@ step s1_slru_check_stats:
f
(1 row)
-step s1_clear_snapshot: SELECT pg_stat_clear_snapshot();
-pg_stat_clear_snapshot
-----------------------
-
+step s1_clear_snapshot: SELECT pg_stat_clear_snapshot() IS NOT NULL AS t;
+t
+-
+t
(1 row)
step s1_slru_check_stats:
@@ -3736,10 +3736,10 @@ name |pg_stat_get_function_calls|total_above_zero|self_above_zero
test_stat_func| | |
(1 row)
-step s1_clear_snapshot: SELECT pg_stat_clear_snapshot();
-pg_stat_clear_snapshot
-----------------------
-
+step s1_clear_snapshot: SELECT pg_stat_clear_snapshot() IS NOT NULL AS t;
+t
+-
+t
(1 row)
step s1_func_stats:
diff --git a/src/test/isolation/specs/stats.spec b/src/test/isolation/specs/stats.spec
index 3893d3983f0..7e211d3fa83 100644
--- a/src/test/isolation/specs/stats.spec
+++ b/src/test/isolation/specs/stats.spec
@@ -40,7 +40,7 @@ setup { SET stats_fetch_consistency = 'none'; }
step s1_fetch_consistency_none { SET stats_fetch_consistency = 'none'; }
step s1_fetch_consistency_cache { SET stats_fetch_consistency = 'cache'; }
step s1_fetch_consistency_snapshot { SET stats_fetch_consistency = 'snapshot'; }
-step s1_clear_snapshot { SELECT pg_stat_clear_snapshot(); }
+step s1_clear_snapshot { SELECT pg_stat_clear_snapshot() IS NOT NULL AS t; }
step s1_begin { BEGIN; }
step s1_commit { COMMIT; }
step s1_rollback { ROLLBACK; }
diff --git a/src/test/regress/expected/stats.out b/src/test/regress/expected/stats.out
index 4c5612ab1ba..7d13fca9e51 100644
--- a/src/test/regress/expected/stats.out
+++ b/src/test/regress/expected/stats.out
@@ -1147,10 +1147,10 @@ SELECT pg_stat_get_snapshot_timestamp() >= NOW();
(1 row)
-- shows NULL again after clearing
-SELECT pg_stat_clear_snapshot();
- pg_stat_clear_snapshot
-------------------------
-
+SELECT pg_stat_clear_snapshot() IS NOT NULL AS t;
+ t
+---
+ t
(1 row)
SELECT pg_stat_get_snapshot_timestamp();
diff --git a/src/test/regress/sql/stats.sql b/src/test/regress/sql/stats.sql
index 62a8424d4c0..aab46b91ad0 100644
--- a/src/test/regress/sql/stats.sql
+++ b/src/test/regress/sql/stats.sql
@@ -529,7 +529,7 @@ SELECT pg_stat_get_snapshot_timestamp();
SELECT pg_stat_get_function_calls(0);
SELECT pg_stat_get_snapshot_timestamp() >= NOW();
-- shows NULL again after clearing
-SELECT pg_stat_clear_snapshot();
+SELECT pg_stat_clear_snapshot() IS NOT NULL AS t;
SELECT pg_stat_get_snapshot_timestamp();
COMMIT;
--
2.47.3
Re: Enhance statistics reset functions to return reset timestamp
On Fri, Sep 12, 2025 at 3:31 PM Shinya Kato <shinya11.kato@gmail.com> wrote:
Hi,
On Wed, Aug 13, 2025 at 4:06 PM Shinya Kato <shinya11.kato@gmail.com> wrote:
On Sat, Aug 9, 2025 at 7:37 AM Andres Freund <andres@anarazel.de> wrote:
Hi,
On 2025-08-08 13:18:39 +0900, Shinya Kato wrote:
I would like to propose a series of patches that enhance the behavior
of various statistics reset functions by making them return the reset
timestamp. This change improves usability and aligns with the behavior
introduced in commit dc9f8a798[1], where pg_stat_statements_reset()
was updated to return the reset time.The following functions have been modified to return a TIMESTAMP WITH
TIME ZONE value indicating when the statistics were reset:
- pg_stat_reset()
- pg_stat_reset_shared()
- pg_stat_reset_single_table_counters()
- pg_stat_reset_backend_stats()
- pg_stat_reset_single_function_counters()
- pg_stat_reset_slru()
- pg_stat_reset_replication_slot()
- pg_stat_reset_subscription_stats()
- pg_stat_clear_snapshot()-1 - I think it was a mistake to introduce support for granular resets, we
shouldn't bury ourselves deeper. If anything we should rip out everything
other than 1) a global reset b) a per-database reset.Leaving that aside, I just don't see a convincing use case for returning the
timestamp here.As I mentioned earlier, the use case is to obtain an audit trail of
the statistics reset time from the function’s return value.Also, how should we think about consistency with
pg_stat_statements_reset()? The reset timestamp for pg_stat_statements
can be obtained from the pg_stat_statements_info view, which returns a
TIMESTAMPTZ. Of course, pg_stat_statements is a contrib module, so we
don’t necessarily have to match its behavior. However, when running a
script that resets various statistics before tests, if only
pg_stat_statements_reset() returns a TIMESTAMPTZ, it might look as
though the other statistics reset functions failed.What these patches do is simply return the TIMESTAMPTZ that is already
computed for the pg_stat_* views, which seems to me a reasonable
change.Rebased the patches.
Rebased the patches.
--
Best regards,
Shinya Kato
NTT OSS Center
Attachments:
v3-0001-Make-pg_stat_reset-return-the-reset-time.patchapplication/octet-stream; name=v3-0001-Make-pg_stat_reset-return-the-reset-time.patchDownload
From c0806b07be8ca9a389ce3fca53e59f61a04611be Mon Sep 17 00:00:00 2001
From: Shinya Kato <shinya11.kato@gmail.com>
Date: Fri, 2 May 2025 14:44:06 +0900
Subject: [PATCH v3 1/9] Make pg_stat_reset() return the reset time
---
doc/src/sgml/monitoring.sgml | 5 +++--
src/backend/utils/activity/pgstat.c | 4 +++-
src/backend/utils/adt/pgstatfuncs.c | 4 ++--
src/include/catalog/pg_proc.dat | 2 +-
src/include/pgstat.h | 2 +-
src/test/isolation/expected/stats.out | 8 ++++----
src/test/isolation/expected/stats_1.out | 8 ++++----
src/test/isolation/specs/stats.spec | 2 +-
src/test/regress/expected/stats.out | 16 ++++++++--------
src/test/regress/sql/stats.sql | 4 ++--
10 files changed, 29 insertions(+), 26 deletions(-)
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index d5f0fb7ba7c..6ebb205bb3f 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -5068,10 +5068,11 @@ description | Waiting for a newly initialized WAL file to reach durable storage
<primary>pg_stat_reset</primary>
</indexterm>
<function>pg_stat_reset</function> ()
- <returnvalue>void</returnvalue>
+ <returnvalue>timestamp with time zone</returnvalue>
</para>
<para>
- Resets all statistics counters for the current database to zero.
+ Resets all statistics counters for the current database to zero, and
+ returns the time of the reset.
</para>
<para>
This function is restricted to superusers by default, but other users
diff --git a/src/backend/utils/activity/pgstat.c b/src/backend/utils/activity/pgstat.c
index 7ef06150df7..510e6cb11b6 100644
--- a/src/backend/utils/activity/pgstat.c
+++ b/src/backend/utils/activity/pgstat.c
@@ -832,7 +832,7 @@ match_db_entries(PgStatShared_HashEntry *entry, Datum match_data)
* Permission checking for this function is managed through the normal
* GRANT system.
*/
-void
+TimestampTz
pgstat_reset_counters(void)
{
TimestampTz ts = GetCurrentTimestamp();
@@ -840,6 +840,8 @@ pgstat_reset_counters(void)
pgstat_reset_matching_entries(match_db_entries,
ObjectIdGetDatum(MyDatabaseId),
ts);
+
+ return ts;
}
/*
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index 1fe33df2756..393958be632 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -1878,9 +1878,9 @@ pg_stat_force_next_flush(PG_FUNCTION_ARGS)
Datum
pg_stat_reset(PG_FUNCTION_ARGS)
{
- pgstat_reset_counters();
+ TimestampTz ts = pgstat_reset_counters();
- PG_RETURN_VOID();
+ PG_RETURN_TIMESTAMPTZ(ts);
}
/*
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index eecb43ec6f0..1d1edf67ac5 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -6160,7 +6160,7 @@
{ oid => '2274',
descr => 'statistics: reset collected statistics for current database',
proname => 'pg_stat_reset', proisstrict => 'f', provolatile => 'v',
- prorettype => 'void', proargtypes => '', prosrc => 'pg_stat_reset' },
+ prorettype => 'timestamptz', proargtypes => '', prosrc => 'pg_stat_reset' },
{ oid => '3775',
descr => 'statistics: reset collected statistics shared across the cluster',
proname => 'pg_stat_reset_shared', proisstrict => 'f', provolatile => 'v',
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index bc8077cbae6..33b3bd242a8 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -529,7 +529,7 @@ extern void pgstat_initialize(void);
extern long pgstat_report_stat(bool force);
extern void pgstat_force_next_flush(void);
-extern void pgstat_reset_counters(void);
+extern TimestampTz pgstat_reset_counters(void);
extern void pgstat_reset(PgStat_Kind kind, Oid dboid, uint64 objid);
extern void pgstat_reset_of_kind(PgStat_Kind kind);
diff --git a/src/test/isolation/expected/stats.out b/src/test/isolation/expected/stats.out
index cfad309ccf3..89dcca903eb 100644
--- a/src/test/isolation/expected/stats.out
+++ b/src/test/isolation/expected/stats.out
@@ -1313,10 +1313,10 @@ name |pg_stat_get_function_calls|total_above_zero|self_above_zero
test_stat_func| 2|t |t
(1 row)
-step s1_reset: SELECT pg_stat_reset();
-pg_stat_reset
--------------
-
+step s1_reset: SELECT pg_stat_reset() IS NOT NULL AS t;
+t
+-
+t
(1 row)
step s1_func_stats:
diff --git a/src/test/isolation/expected/stats_1.out b/src/test/isolation/expected/stats_1.out
index e1d937784cb..ea6f4aebbc0 100644
--- a/src/test/isolation/expected/stats_1.out
+++ b/src/test/isolation/expected/stats_1.out
@@ -1313,10 +1313,10 @@ name |pg_stat_get_function_calls|total_above_zero|self_above_zero
test_stat_func| 2|t |t
(1 row)
-step s1_reset: SELECT pg_stat_reset();
-pg_stat_reset
--------------
-
+step s1_reset: SELECT pg_stat_reset() IS NOT NULL AS t;
+t
+-
+t
(1 row)
step s1_func_stats:
diff --git a/src/test/isolation/specs/stats.spec b/src/test/isolation/specs/stats.spec
index da16710da0f..a8705926fa6 100644
--- a/src/test/isolation/specs/stats.spec
+++ b/src/test/isolation/specs/stats.spec
@@ -63,7 +63,7 @@ step s1_func_stats_reset_check {
IS NOT NULL AS has_stats_reset;
}
step s1_func_stats_reset_nonexistent { SELECT pg_stat_reset_single_function_counters(12000); }
-step s1_reset { SELECT pg_stat_reset(); }
+step s1_reset { SELECT pg_stat_reset() IS NOT NULL AS t; }
step s1_func_stats {
SELECT
tso.name,
diff --git a/src/test/regress/expected/stats.out b/src/test/regress/expected/stats.out
index 67e1860e984..c150229da52 100644
--- a/src/test/regress/expected/stats.out
+++ b/src/test/regress/expected/stats.out
@@ -1133,17 +1133,17 @@ ERROR: unrecognized reset target: "unknown"
HINT: Target must be "archiver", "bgwriter", "checkpointer", "io", "recovery_prefetch", "slru", or "wal".
-- Test that reset works for pg_stat_database
-- Since pg_stat_database stats_reset starts out as NULL, reset it once first so we have something to compare it to
-SELECT pg_stat_reset();
- pg_stat_reset
----------------
-
+SELECT pg_stat_reset() IS NOT NULL AS t;
+ t
+---
+ t
(1 row)
SELECT stats_reset AS db_reset_ts FROM pg_stat_database WHERE datname = (SELECT current_database()) \gset
-SELECT pg_stat_reset();
- pg_stat_reset
----------------
-
+SELECT pg_stat_reset() IS NOT NULL AS t;
+ t
+---
+ t
(1 row)
SELECT stats_reset > :'db_reset_ts'::timestamptz FROM pg_stat_database WHERE datname = (SELECT current_database());
diff --git a/src/test/regress/sql/stats.sql b/src/test/regress/sql/stats.sql
index 8768e0f27fd..86690e41b69 100644
--- a/src/test/regress/sql/stats.sql
+++ b/src/test/regress/sql/stats.sql
@@ -526,9 +526,9 @@ SELECT pg_stat_reset_shared('unknown');
-- Test that reset works for pg_stat_database
-- Since pg_stat_database stats_reset starts out as NULL, reset it once first so we have something to compare it to
-SELECT pg_stat_reset();
+SELECT pg_stat_reset() IS NOT NULL AS t;
SELECT stats_reset AS db_reset_ts FROM pg_stat_database WHERE datname = (SELECT current_database()) \gset
-SELECT pg_stat_reset();
+SELECT pg_stat_reset() IS NOT NULL AS t;
SELECT stats_reset > :'db_reset_ts'::timestamptz FROM pg_stat_database WHERE datname = (SELECT current_database());
--
2.47.3
v3-0002-Make-pg_stat_reset_shared-return-the-reset-time.patchapplication/octet-stream; name=v3-0002-Make-pg_stat_reset_shared-return-the-reset-time.patchDownload
From 9b4727e20cd7df7d2acde17610c8612d07aac789 Mon Sep 17 00:00:00 2001
From: Shinya Kato <shinya11.kato@gmail.com>
Date: Fri, 2 May 2025 14:58:07 +0900
Subject: [PATCH v3 2/9] Make pg_stat_reset_shared() return the reset time
---
doc/src/sgml/monitoring.sgml | 5 +-
src/backend/access/transam/xlogprefetcher.c | 6 ++-
src/backend/catalog/system_functions.sql | 2 +-
src/backend/utils/activity/pgstat.c | 5 +-
src/backend/utils/adt/pgstatfuncs.c | 21 ++++----
src/include/access/xlogprefetcher.h | 3 +-
src/include/catalog/pg_proc.dat | 2 +-
src/include/pgstat.h | 2 +-
src/test/regress/expected/stats.out | 56 ++++++++++-----------
src/test/regress/sql/stats.sql | 14 +++---
10 files changed, 61 insertions(+), 55 deletions(-)
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index 6ebb205bb3f..e5edee97e94 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -5086,11 +5086,12 @@ description | Waiting for a newly initialized WAL file to reach durable storage
<primary>pg_stat_reset_shared</primary>
</indexterm>
<function>pg_stat_reset_shared</function> ( [ <parameter>target</parameter> <type>text</type> <literal>DEFAULT</literal> <literal>NULL</literal> ] )
- <returnvalue>void</returnvalue>
+ <returnvalue>timestamp with time zone</returnvalue>
</para>
<para>
Resets some cluster-wide statistics counters to zero, depending on the
- argument. <parameter>target</parameter> can be:
+ argument, and returns the time of the reset.
+ <parameter>target</parameter> can be:
<itemizedlist>
<listitem>
<para>
diff --git a/src/backend/access/transam/xlogprefetcher.c b/src/backend/access/transam/xlogprefetcher.c
index ed3aacabc98..bc033ae1842 100644
--- a/src/backend/access/transam/xlogprefetcher.c
+++ b/src/backend/access/transam/xlogprefetcher.c
@@ -299,16 +299,18 @@ XLogPrefetchShmemSize(void)
/*
* Reset all counters to zero.
*/
-void
+TimestampTz
XLogPrefetchResetStats(void)
{
- pg_atomic_write_u64(&SharedStats->reset_time, GetCurrentTimestamp());
+ TimestampTz ts = GetCurrentTimestamp();
+ pg_atomic_write_u64(&SharedStats->reset_time, ts);
pg_atomic_write_u64(&SharedStats->prefetch, 0);
pg_atomic_write_u64(&SharedStats->hit, 0);
pg_atomic_write_u64(&SharedStats->skip_init, 0);
pg_atomic_write_u64(&SharedStats->skip_new, 0);
pg_atomic_write_u64(&SharedStats->skip_fpw, 0);
pg_atomic_write_u64(&SharedStats->skip_rep, 0);
+ return ts;
}
void
diff --git a/src/backend/catalog/system_functions.sql b/src/backend/catalog/system_functions.sql
index 2d946d6d9e9..0d679e6a8d4 100644
--- a/src/backend/catalog/system_functions.sql
+++ b/src/backend/catalog/system_functions.sql
@@ -638,7 +638,7 @@ AS 'unicode_is_normalized';
CREATE OR REPLACE FUNCTION
pg_stat_reset_shared(target text DEFAULT NULL)
-RETURNS void
+RETURNS timestamp with time zone
LANGUAGE INTERNAL
CALLED ON NULL INPUT VOLATILE PARALLEL SAFE
AS 'pg_stat_reset_shared';
diff --git a/src/backend/utils/activity/pgstat.c b/src/backend/utils/activity/pgstat.c
index 510e6cb11b6..1fcd140a589 100644
--- a/src/backend/utils/activity/pgstat.c
+++ b/src/backend/utils/activity/pgstat.c
@@ -875,7 +875,7 @@ pgstat_reset(PgStat_Kind kind, Oid dboid, uint64 objid)
* Permission checking for this function is managed through the normal
* GRANT system.
*/
-void
+TimestampTz
pgstat_reset_of_kind(PgStat_Kind kind)
{
const PgStat_KindInfo *kind_info = pgstat_get_kind_info(kind);
@@ -885,8 +885,9 @@ pgstat_reset_of_kind(PgStat_Kind kind)
kind_info->reset_all_cb(ts);
else
pgstat_reset_entries_of_kind(kind, ts);
-}
+ return ts;
+}
/* ------------------------------------------------------------
* Fetching of stats
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index 393958be632..4a84575e7a7 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -1893,6 +1893,7 @@ Datum
pg_stat_reset_shared(PG_FUNCTION_ARGS)
{
char *target = NULL;
+ TimestampTz ts;
if (PG_ARGISNULL(0))
{
@@ -1903,34 +1904,34 @@ pg_stat_reset_shared(PG_FUNCTION_ARGS)
pgstat_reset_of_kind(PGSTAT_KIND_IO);
XLogPrefetchResetStats();
pgstat_reset_of_kind(PGSTAT_KIND_SLRU);
- pgstat_reset_of_kind(PGSTAT_KIND_WAL);
+ ts = pgstat_reset_of_kind(PGSTAT_KIND_WAL);
- PG_RETURN_VOID();
+ PG_RETURN_TIMESTAMPTZ(ts);
}
target = text_to_cstring(PG_GETARG_TEXT_PP(0));
if (strcmp(target, "archiver") == 0)
- pgstat_reset_of_kind(PGSTAT_KIND_ARCHIVER);
+ ts = pgstat_reset_of_kind(PGSTAT_KIND_ARCHIVER);
else if (strcmp(target, "bgwriter") == 0)
- pgstat_reset_of_kind(PGSTAT_KIND_BGWRITER);
+ ts = pgstat_reset_of_kind(PGSTAT_KIND_BGWRITER);
else if (strcmp(target, "checkpointer") == 0)
- pgstat_reset_of_kind(PGSTAT_KIND_CHECKPOINTER);
+ ts = pgstat_reset_of_kind(PGSTAT_KIND_CHECKPOINTER);
else if (strcmp(target, "io") == 0)
- pgstat_reset_of_kind(PGSTAT_KIND_IO);
+ ts = pgstat_reset_of_kind(PGSTAT_KIND_IO);
else if (strcmp(target, "recovery_prefetch") == 0)
- XLogPrefetchResetStats();
+ ts = XLogPrefetchResetStats();
else if (strcmp(target, "slru") == 0)
- pgstat_reset_of_kind(PGSTAT_KIND_SLRU);
+ ts = pgstat_reset_of_kind(PGSTAT_KIND_SLRU);
else if (strcmp(target, "wal") == 0)
- pgstat_reset_of_kind(PGSTAT_KIND_WAL);
+ ts = pgstat_reset_of_kind(PGSTAT_KIND_WAL);
else
ereport(ERROR,
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
errmsg("unrecognized reset target: \"%s\"", target),
errhint("Target must be \"archiver\", \"bgwriter\", \"checkpointer\", \"io\", \"recovery_prefetch\", \"slru\", or \"wal\".")));
- PG_RETURN_VOID();
+ PG_RETURN_TIMESTAMPTZ(ts);
}
/*
diff --git a/src/include/access/xlogprefetcher.h b/src/include/access/xlogprefetcher.h
index 50b39c1fb0d..6cc95b70c6e 100644
--- a/src/include/access/xlogprefetcher.h
+++ b/src/include/access/xlogprefetcher.h
@@ -16,6 +16,7 @@
#include "access/xlogdefs.h"
#include "access/xlogreader.h"
#include "access/xlogrecord.h"
+#include "utils/timestamp.h"
/* GUCs */
extern PGDLLIMPORT int recovery_prefetch;
@@ -37,7 +38,7 @@ extern void XLogPrefetchReconfigure(void);
extern size_t XLogPrefetchShmemSize(void);
extern void XLogPrefetchShmemInit(void);
-extern void XLogPrefetchResetStats(void);
+extern TimestampTz XLogPrefetchResetStats(void);
extern XLogPrefetcher *XLogPrefetcherAllocate(XLogReaderState *reader);
extern void XLogPrefetcherFree(XLogPrefetcher *prefetcher);
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 1d1edf67ac5..df291d21ef7 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -6164,7 +6164,7 @@
{ oid => '3775',
descr => 'statistics: reset collected statistics shared across the cluster',
proname => 'pg_stat_reset_shared', proisstrict => 'f', provolatile => 'v',
- prorettype => 'void', proargtypes => 'text',
+ prorettype => 'timestamptz', proargtypes => 'text',
prosrc => 'pg_stat_reset_shared' },
{ oid => '3776',
descr => 'statistics: reset collected statistics for a single table or index in the current database or shared across all databases in the cluster',
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index 33b3bd242a8..a91984f086f 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -531,7 +531,7 @@ extern void pgstat_force_next_flush(void);
extern TimestampTz pgstat_reset_counters(void);
extern void pgstat_reset(PgStat_Kind kind, Oid dboid, uint64 objid);
-extern void pgstat_reset_of_kind(PgStat_Kind kind);
+extern TimestampTz pgstat_reset_of_kind(PgStat_Kind kind);
/* stats accessors */
extern void pgstat_clear_snapshot(void);
diff --git a/src/test/regress/expected/stats.out b/src/test/regress/expected/stats.out
index c150229da52..9b93d257992 100644
--- a/src/test/regress/expected/stats.out
+++ b/src/test/regress/expected/stats.out
@@ -1045,10 +1045,10 @@ SELECT stats_reset > :'slru_notify_reset_ts'::timestamptz FROM pg_stat_slru WHER
-- Test that reset_shared with archiver specified as the stats type works
SELECT stats_reset AS archiver_reset_ts FROM pg_stat_archiver \gset
-SELECT pg_stat_reset_shared('archiver');
- pg_stat_reset_shared
-----------------------
-
+SELECT pg_stat_reset_shared('archiver') IS NOT NULL AS t;
+ t
+---
+ t
(1 row)
SELECT stats_reset > :'archiver_reset_ts'::timestamptz FROM pg_stat_archiver;
@@ -1059,10 +1059,10 @@ SELECT stats_reset > :'archiver_reset_ts'::timestamptz FROM pg_stat_archiver;
-- Test that reset_shared with bgwriter specified as the stats type works
SELECT stats_reset AS bgwriter_reset_ts FROM pg_stat_bgwriter \gset
-SELECT pg_stat_reset_shared('bgwriter');
- pg_stat_reset_shared
-----------------------
-
+SELECT pg_stat_reset_shared('bgwriter') IS NOT NULL AS t;
+ t
+---
+ t
(1 row)
SELECT stats_reset > :'bgwriter_reset_ts'::timestamptz FROM pg_stat_bgwriter;
@@ -1073,10 +1073,10 @@ SELECT stats_reset > :'bgwriter_reset_ts'::timestamptz FROM pg_stat_bgwriter;
-- Test that reset_shared with checkpointer specified as the stats type works
SELECT stats_reset AS checkpointer_reset_ts FROM pg_stat_checkpointer \gset
-SELECT pg_stat_reset_shared('checkpointer');
- pg_stat_reset_shared
-----------------------
-
+SELECT pg_stat_reset_shared('checkpointer') IS NOT NULL AS t;
+ t
+---
+ t
(1 row)
SELECT stats_reset > :'checkpointer_reset_ts'::timestamptz FROM pg_stat_checkpointer;
@@ -1087,10 +1087,10 @@ SELECT stats_reset > :'checkpointer_reset_ts'::timestamptz FROM pg_stat_checkpoi
-- Test that reset_shared with recovery_prefetch specified as the stats type works
SELECT stats_reset AS recovery_prefetch_reset_ts FROM pg_stat_recovery_prefetch \gset
-SELECT pg_stat_reset_shared('recovery_prefetch');
- pg_stat_reset_shared
-----------------------
-
+SELECT pg_stat_reset_shared('recovery_prefetch') IS NOT NULL AS t;
+ t
+---
+ t
(1 row)
SELECT stats_reset > :'recovery_prefetch_reset_ts'::timestamptz FROM pg_stat_recovery_prefetch;
@@ -1101,10 +1101,10 @@ SELECT stats_reset > :'recovery_prefetch_reset_ts'::timestamptz FROM pg_stat_rec
-- Test that reset_shared with slru specified as the stats type works
SELECT max(stats_reset) AS slru_reset_ts FROM pg_stat_slru \gset
-SELECT pg_stat_reset_shared('slru');
- pg_stat_reset_shared
-----------------------
-
+SELECT pg_stat_reset_shared('slru') IS NOT NULL AS t;
+ t
+---
+ t
(1 row)
SELECT max(stats_reset) > :'slru_reset_ts'::timestamptz FROM pg_stat_slru;
@@ -1115,10 +1115,10 @@ SELECT max(stats_reset) > :'slru_reset_ts'::timestamptz FROM pg_stat_slru;
-- Test that reset_shared with wal specified as the stats type works
SELECT stats_reset AS wal_reset_ts FROM pg_stat_wal \gset
-SELECT pg_stat_reset_shared('wal');
- pg_stat_reset_shared
-----------------------
-
+SELECT pg_stat_reset_shared('wal') IS NOT NULL AS t;
+ t
+---
+ t
(1 row)
SELECT stats_reset > :'wal_reset_ts'::timestamptz FROM pg_stat_wal;
@@ -1727,10 +1727,10 @@ SELECT sum(evictions) + sum(reuses) + sum(extends) + sum(fsyncs) + sum(reads) +
FROM pg_stat_io \gset
SELECT sum(evictions) + sum(reuses) + sum(extends) + sum(fsyncs) + sum(reads) + sum(writes) + sum(writebacks) + sum(hits) AS my_io_stats_pre_reset
FROM pg_stat_get_backend_io(pg_backend_pid()) \gset
-SELECT pg_stat_reset_shared('io');
- pg_stat_reset_shared
-----------------------
-
+SELECT pg_stat_reset_shared('io') IS NOT NULL AS t;
+ t
+---
+ t
(1 row)
SELECT sum(evictions) + sum(reuses) + sum(extends) + sum(fsyncs) + sum(reads) + sum(writes) + sum(writebacks) + sum(hits) AS io_stats_post_reset
diff --git a/src/test/regress/sql/stats.sql b/src/test/regress/sql/stats.sql
index 86690e41b69..07fcff8215a 100644
--- a/src/test/regress/sql/stats.sql
+++ b/src/test/regress/sql/stats.sql
@@ -492,32 +492,32 @@ SELECT stats_reset > :'slru_notify_reset_ts'::timestamptz FROM pg_stat_slru WHER
-- Test that reset_shared with archiver specified as the stats type works
SELECT stats_reset AS archiver_reset_ts FROM pg_stat_archiver \gset
-SELECT pg_stat_reset_shared('archiver');
+SELECT pg_stat_reset_shared('archiver') IS NOT NULL AS t;
SELECT stats_reset > :'archiver_reset_ts'::timestamptz FROM pg_stat_archiver;
-- Test that reset_shared with bgwriter specified as the stats type works
SELECT stats_reset AS bgwriter_reset_ts FROM pg_stat_bgwriter \gset
-SELECT pg_stat_reset_shared('bgwriter');
+SELECT pg_stat_reset_shared('bgwriter') IS NOT NULL AS t;
SELECT stats_reset > :'bgwriter_reset_ts'::timestamptz FROM pg_stat_bgwriter;
-- Test that reset_shared with checkpointer specified as the stats type works
SELECT stats_reset AS checkpointer_reset_ts FROM pg_stat_checkpointer \gset
-SELECT pg_stat_reset_shared('checkpointer');
+SELECT pg_stat_reset_shared('checkpointer') IS NOT NULL AS t;
SELECT stats_reset > :'checkpointer_reset_ts'::timestamptz FROM pg_stat_checkpointer;
-- Test that reset_shared with recovery_prefetch specified as the stats type works
SELECT stats_reset AS recovery_prefetch_reset_ts FROM pg_stat_recovery_prefetch \gset
-SELECT pg_stat_reset_shared('recovery_prefetch');
+SELECT pg_stat_reset_shared('recovery_prefetch') IS NOT NULL AS t;
SELECT stats_reset > :'recovery_prefetch_reset_ts'::timestamptz FROM pg_stat_recovery_prefetch;
-- Test that reset_shared with slru specified as the stats type works
SELECT max(stats_reset) AS slru_reset_ts FROM pg_stat_slru \gset
-SELECT pg_stat_reset_shared('slru');
+SELECT pg_stat_reset_shared('slru') IS NOT NULL AS t;
SELECT max(stats_reset) > :'slru_reset_ts'::timestamptz FROM pg_stat_slru;
-- Test that reset_shared with wal specified as the stats type works
SELECT stats_reset AS wal_reset_ts FROM pg_stat_wal \gset
-SELECT pg_stat_reset_shared('wal');
+SELECT pg_stat_reset_shared('wal') IS NOT NULL AS t;
SELECT stats_reset > :'wal_reset_ts'::timestamptz FROM pg_stat_wal;
-- Test error case for reset_shared with unknown stats type
@@ -827,7 +827,7 @@ SELECT sum(evictions) + sum(reuses) + sum(extends) + sum(fsyncs) + sum(reads) +
FROM pg_stat_io \gset
SELECT sum(evictions) + sum(reuses) + sum(extends) + sum(fsyncs) + sum(reads) + sum(writes) + sum(writebacks) + sum(hits) AS my_io_stats_pre_reset
FROM pg_stat_get_backend_io(pg_backend_pid()) \gset
-SELECT pg_stat_reset_shared('io');
+SELECT pg_stat_reset_shared('io') IS NOT NULL AS t;
SELECT sum(evictions) + sum(reuses) + sum(extends) + sum(fsyncs) + sum(reads) + sum(writes) + sum(writebacks) + sum(hits) AS io_stats_post_reset
FROM pg_stat_io \gset
SELECT :io_stats_post_reset < :io_stats_pre_reset;
--
2.47.3
v3-0003-Make-pg_stat_reset_single_table_counters-return-t.patchapplication/octet-stream; name=v3-0003-Make-pg_stat_reset_single_table_counters-return-t.patchDownload
From f0cfbb67614b83848077cfc7c7c24a2b25f3cd7c Mon Sep 17 00:00:00 2001
From: Shinya Kato <shinya11.kato@gmail.com>
Date: Fri, 2 May 2025 15:30:39 +0900
Subject: [PATCH v3 3/9] Make pg_stat_reset_single_table_counters() return the
reset time
---
doc/src/sgml/monitoring.sgml | 5 +++--
src/backend/utils/activity/pgstat.c | 4 +++-
src/backend/utils/adt/pgstatfuncs.c | 5 +++--
src/include/catalog/pg_proc.dat | 2 +-
src/include/pgstat.h | 2 +-
src/test/regress/expected/stats.out | 24 ++++++++++++------------
src/test/regress/sql/stats.sql | 6 +++---
7 files changed, 26 insertions(+), 22 deletions(-)
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index e5edee97e94..f70ddc13f33 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -5155,11 +5155,12 @@ description | Waiting for a newly initialized WAL file to reach durable storage
<primary>pg_stat_reset_single_table_counters</primary>
</indexterm>
<function>pg_stat_reset_single_table_counters</function> ( <type>oid</type> )
- <returnvalue>void</returnvalue>
+ <returnvalue>timestamp with time zone</returnvalue>
</para>
<para>
Resets statistics for a single table or index in the current database
- or shared across all databases in the cluster to zero.
+ or shared across all databases in the cluster to zero, and returns the
+ time of the reset.
</para>
<para>
This function is restricted to superusers by default, but other users
diff --git a/src/backend/utils/activity/pgstat.c b/src/backend/utils/activity/pgstat.c
index 1fcd140a589..5b7166ee554 100644
--- a/src/backend/utils/activity/pgstat.c
+++ b/src/backend/utils/activity/pgstat.c
@@ -853,7 +853,7 @@ pgstat_reset_counters(void)
* Permission checking for this function is managed through the normal
* GRANT system.
*/
-void
+TimestampTz
pgstat_reset(PgStat_Kind kind, Oid dboid, uint64 objid)
{
const PgStat_KindInfo *kind_info = pgstat_get_kind_info(kind);
@@ -867,6 +867,8 @@ pgstat_reset(PgStat_Kind kind, Oid dboid, uint64 objid)
if (!kind_info->accessed_across_databases)
pgstat_reset_database_timestamp(dboid, ts);
+
+ return ts;
}
/*
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index 4a84575e7a7..b87f99dee19 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -1943,10 +1943,11 @@ pg_stat_reset_single_table_counters(PG_FUNCTION_ARGS)
{
Oid taboid = PG_GETARG_OID(0);
Oid dboid = (IsSharedRelation(taboid) ? InvalidOid : MyDatabaseId);
+ TimestampTz ts;
- pgstat_reset(PGSTAT_KIND_RELATION, dboid, taboid);
+ ts = pgstat_reset(PGSTAT_KIND_RELATION, dboid, taboid);
- PG_RETURN_VOID();
+ PG_RETURN_TIMESTAMPTZ(ts);
}
Datum
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index df291d21ef7..5fea29d7b27 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -6169,7 +6169,7 @@
{ oid => '3776',
descr => 'statistics: reset collected statistics for a single table or index in the current database or shared across all databases in the cluster',
proname => 'pg_stat_reset_single_table_counters', provolatile => 'v',
- prorettype => 'void', proargtypes => 'oid',
+ prorettype => 'timestamptz', proargtypes => 'oid',
prosrc => 'pg_stat_reset_single_table_counters' },
{ oid => '3777',
descr => 'statistics: reset collected statistics for a single function in the current database',
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index a91984f086f..cac2cef060d 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -530,7 +530,7 @@ extern long pgstat_report_stat(bool force);
extern void pgstat_force_next_flush(void);
extern TimestampTz pgstat_reset_counters(void);
-extern void pgstat_reset(PgStat_Kind kind, Oid dboid, uint64 objid);
+extern TimestampTz pgstat_reset(PgStat_Kind kind, Oid dboid, uint64 objid);
extern TimestampTz pgstat_reset_of_kind(PgStat_Kind kind);
/* stats accessors */
diff --git a/src/test/regress/expected/stats.out b/src/test/regress/expected/stats.out
index 9b93d257992..a3f9e04c555 100644
--- a/src/test/regress/expected/stats.out
+++ b/src/test/regress/expected/stats.out
@@ -673,10 +673,10 @@ SELECT stats_reset IS NOT NULL AS has_stats_reset
f
(1 row)
-SELECT pg_stat_reset_single_table_counters('test_last_scan'::regclass);
- pg_stat_reset_single_table_counters
--------------------------------------
-
+SELECT pg_stat_reset_single_table_counters('test_last_scan'::regclass) IS NOT NULL AS t;
+ t
+---
+ t
(1 row)
SELECT seq_scan, idx_scan, stats_reset IS NOT NULL AS has_stats_reset
@@ -868,10 +868,10 @@ SELECT idx_scan, :'test_last_idx' < last_idx_scan AS idx_ok,
(1 row)
-- check that the stats in pg_stat_all_indexes are reset
-SELECT pg_stat_reset_single_table_counters('test_last_scan_pkey'::regclass);
- pg_stat_reset_single_table_counters
--------------------------------------
-
+SELECT pg_stat_reset_single_table_counters('test_last_scan_pkey'::regclass) IS NOT NULL AS t;
+ t
+---
+ t
(1 row)
SELECT idx_scan, stats_reset IS NOT NULL AS has_stats_reset
@@ -908,10 +908,10 @@ SELECT (n_tup_ins + n_tup_upd) > 0 AS has_data FROM pg_stat_all_tables
t
(1 row)
-SELECT pg_stat_reset_single_table_counters('pg_shdescription'::regclass);
- pg_stat_reset_single_table_counters
--------------------------------------
-
+SELECT pg_stat_reset_single_table_counters('pg_shdescription'::regclass) IS NOT NULL AS t;
+ t
+---
+ t
(1 row)
SELECT (n_tup_ins + n_tup_upd) > 0 AS has_data FROM pg_stat_all_tables
diff --git a/src/test/regress/sql/stats.sql b/src/test/regress/sql/stats.sql
index 07fcff8215a..50ba7354b7e 100644
--- a/src/test/regress/sql/stats.sql
+++ b/src/test/regress/sql/stats.sql
@@ -314,7 +314,7 @@ COMMIT;
SELECT stats_reset IS NOT NULL AS has_stats_reset
FROM pg_stat_all_tables WHERE relid = 'test_last_scan'::regclass;
-SELECT pg_stat_reset_single_table_counters('test_last_scan'::regclass);
+SELECT pg_stat_reset_single_table_counters('test_last_scan'::regclass) IS NOT NULL AS t;
SELECT seq_scan, idx_scan, stats_reset IS NOT NULL AS has_stats_reset
FROM pg_stat_all_tables WHERE relid = 'test_last_scan'::regclass;
@@ -391,7 +391,7 @@ SELECT idx_scan, :'test_last_idx' < last_idx_scan AS idx_ok,
FROM pg_stat_all_indexes WHERE indexrelid = 'test_last_scan_pkey'::regclass;
-- check that the stats in pg_stat_all_indexes are reset
-SELECT pg_stat_reset_single_table_counters('test_last_scan_pkey'::regclass);
+SELECT pg_stat_reset_single_table_counters('test_last_scan_pkey'::regclass) IS NOT NULL AS t;
SELECT idx_scan, stats_reset IS NOT NULL AS has_stats_reset
FROM pg_stat_all_indexes WHERE indexrelid = 'test_last_scan_pkey'::regclass;
@@ -416,7 +416,7 @@ COMMIT;
-- check that the stats are reset.
SELECT (n_tup_ins + n_tup_upd) > 0 AS has_data FROM pg_stat_all_tables
WHERE relid = 'pg_shdescription'::regclass;
-SELECT pg_stat_reset_single_table_counters('pg_shdescription'::regclass);
+SELECT pg_stat_reset_single_table_counters('pg_shdescription'::regclass) IS NOT NULL AS t;
SELECT (n_tup_ins + n_tup_upd) > 0 AS has_data FROM pg_stat_all_tables
WHERE relid = 'pg_shdescription'::regclass;
--
2.47.3
v3-0004-Make-pg_stat_reset_backend_stats-return-the-reset.patchapplication/octet-stream; name=v3-0004-Make-pg_stat_reset_backend_stats-return-the-reset.patchDownload
From 40e6318a18f4a2a2f34553cbbec0a396beb99ffb Mon Sep 17 00:00:00 2001
From: Shinya Kato <shinya11.kato@gmail.com>
Date: Fri, 2 May 2025 15:41:00 +0900
Subject: [PATCH v3 4/9] Make pg_stat_reset_backend_stats() return the reset
time
---
doc/src/sgml/monitoring.sgml | 5 +++--
src/backend/utils/adt/pgstatfuncs.c | 11 ++++++-----
src/include/catalog/pg_proc.dat | 2 +-
src/test/regress/expected/stats.out | 9 ++++++++-
src/test/regress/sql/stats.sql | 4 +++-
5 files changed, 21 insertions(+), 10 deletions(-)
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index f70ddc13f33..bfe6145c1d6 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -5174,11 +5174,12 @@ description | Waiting for a newly initialized WAL file to reach durable storage
<primary>pg_stat_reset_backend_stats</primary>
</indexterm>
<function>pg_stat_reset_backend_stats</function> ( <type>integer</type> )
- <returnvalue>void</returnvalue>
+ <returnvalue>timestamp with time zone</returnvalue>
</para>
<para>
Resets statistics for a single backend with the specified process ID
- to zero.
+ to zero, and returns the time of the reset or <literal>NULL</literal>
+ if an invalid backend process ID is specified.
</para>
<para>
This function is restricted to superusers by default, but other users
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index b87f99dee19..18c8286dc7f 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -1969,6 +1969,7 @@ pg_stat_reset_backend_stats(PG_FUNCTION_ARGS)
PGPROC *proc;
PgBackendStatus *beentry;
ProcNumber procNumber;
+ TimestampTz ts;
int backend_pid = PG_GETARG_INT32(0);
proc = BackendPidGetProc(backend_pid);
@@ -1978,21 +1979,21 @@ pg_stat_reset_backend_stats(PG_FUNCTION_ARGS)
proc = AuxiliaryPidGetProc(backend_pid);
if (!proc)
- PG_RETURN_VOID();
+ PG_RETURN_NULL();
procNumber = GetNumberFromPGProc(proc);
beentry = pgstat_get_beentry_by_proc_number(procNumber);
if (!beentry)
- PG_RETURN_VOID();
+ PG_RETURN_NULL();
/* Check if the backend type tracks statistics */
if (!pgstat_tracks_backend_bktype(beentry->st_backendType))
- PG_RETURN_VOID();
+ PG_RETURN_NULL();
- pgstat_reset(PGSTAT_KIND_BACKEND, InvalidOid, procNumber);
+ ts = pgstat_reset(PGSTAT_KIND_BACKEND, InvalidOid, procNumber);
- PG_RETURN_VOID();
+ PG_RETURN_TIMESTAMPTZ(ts);
}
/* Reset SLRU counters (a specific one or all of them). */
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 5fea29d7b27..6548bc89a27 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -6178,7 +6178,7 @@
prosrc => 'pg_stat_reset_single_function_counters' },
{ oid => '6387', descr => 'statistics: reset statistics for a single backend',
proname => 'pg_stat_reset_backend_stats', provolatile => 'v',
- prorettype => 'void', proargtypes => 'int4',
+ prorettype => 'timestamptz', proargtypes => 'int4',
prosrc => 'pg_stat_reset_backend_stats' },
{ oid => '2307',
descr => 'statistics: reset collected statistics for a single SLRU',
diff --git a/src/test/regress/expected/stats.out b/src/test/regress/expected/stats.out
index a3f9e04c555..f1b6e21533f 100644
--- a/src/test/regress/expected/stats.out
+++ b/src/test/regress/expected/stats.out
@@ -1751,7 +1751,14 @@ SELECT :my_io_stats_pre_reset <= :my_io_stats_post_reset;
(1 row)
-- but pg_stat_reset_backend_stats() does
-SELECT pg_stat_reset_backend_stats(pg_backend_pid());
+SELECT pg_stat_reset_backend_stats(pg_backend_pid()) IS NOT NULL AS t;
+ t
+---
+ t
+(1 row)
+
+-- Invalid backend process ID is specified
+SELECT pg_stat_reset_backend_stats(0);
pg_stat_reset_backend_stats
-----------------------------
diff --git a/src/test/regress/sql/stats.sql b/src/test/regress/sql/stats.sql
index 50ba7354b7e..a1a9dad0a6a 100644
--- a/src/test/regress/sql/stats.sql
+++ b/src/test/regress/sql/stats.sql
@@ -836,7 +836,9 @@ SELECT sum(evictions) + sum(reuses) + sum(extends) + sum(fsyncs) + sum(reads) +
-- pg_stat_reset_shared() did not reset backend IO stats
SELECT :my_io_stats_pre_reset <= :my_io_stats_post_reset;
-- but pg_stat_reset_backend_stats() does
-SELECT pg_stat_reset_backend_stats(pg_backend_pid());
+SELECT pg_stat_reset_backend_stats(pg_backend_pid()) IS NOT NULL AS t;
+-- Invalid backend process ID is specified
+SELECT pg_stat_reset_backend_stats(0);
SELECT sum(evictions) + sum(reuses) + sum(extends) + sum(fsyncs) + sum(reads) + sum(writes) + sum(writebacks) + sum(hits) AS my_io_stats_post_backend_reset
FROM pg_stat_get_backend_io(pg_backend_pid()) \gset
SELECT :my_io_stats_pre_reset > :my_io_stats_post_backend_reset;
--
2.47.3
v3-0005-Make-pg_stat_reset_single_function_counters-retur.patchapplication/octet-stream; name=v3-0005-Make-pg_stat_reset_single_function_counters-retur.patchDownload
From 4264f11d67d25c6d4a2398961c3f5a879810c33e Mon Sep 17 00:00:00 2001
From: Shinya Kato <shinya11.kato@gmail.com>
Date: Fri, 2 May 2025 16:42:00 +0900
Subject: [PATCH v3 5/9] Make pg_stat_reset_single_function_counters() return
the reset time
---
doc/src/sgml/monitoring.sgml | 4 ++--
src/backend/utils/adt/pgstatfuncs.c | 5 +++--
src/include/catalog/pg_proc.dat | 2 +-
src/test/isolation/expected/stats.out | 16 ++++++++--------
src/test/isolation/expected/stats_1.out | 16 ++++++++--------
src/test/isolation/specs/stats.spec | 4 ++--
6 files changed, 24 insertions(+), 23 deletions(-)
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index bfe6145c1d6..60ece8b02a8 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -5193,11 +5193,11 @@ description | Waiting for a newly initialized WAL file to reach durable storage
<primary>pg_stat_reset_single_function_counters</primary>
</indexterm>
<function>pg_stat_reset_single_function_counters</function> ( <type>oid</type> )
- <returnvalue>void</returnvalue>
+ <returnvalue>timestamp with time zone</returnvalue>
</para>
<para>
Resets statistics for a single function in the current database to
- zero.
+ zero, and returns the time of the reset.
</para>
<para>
This function is restricted to superusers by default, but other users
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index 18c8286dc7f..b3d3d7f027d 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -1954,10 +1954,11 @@ Datum
pg_stat_reset_single_function_counters(PG_FUNCTION_ARGS)
{
Oid funcoid = PG_GETARG_OID(0);
+ TimestampTz ts;
- pgstat_reset(PGSTAT_KIND_FUNCTION, MyDatabaseId, funcoid);
+ ts = pgstat_reset(PGSTAT_KIND_FUNCTION, MyDatabaseId, funcoid);
- PG_RETURN_VOID();
+ PG_RETURN_TIMESTAMPTZ(ts);
}
/*
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 6548bc89a27..b05e96c5994 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -6174,7 +6174,7 @@
{ oid => '3777',
descr => 'statistics: reset collected statistics for a single function in the current database',
proname => 'pg_stat_reset_single_function_counters', provolatile => 'v',
- prorettype => 'void', proargtypes => 'oid',
+ prorettype => 'timestamptz', proargtypes => 'oid',
prosrc => 'pg_stat_reset_single_function_counters' },
{ oid => '6387', descr => 'statistics: reset statistics for a single backend',
proname => 'pg_stat_reset_backend_stats', provolatile => 'v',
diff --git a/src/test/isolation/expected/stats.out b/src/test/isolation/expected/stats.out
index 89dcca903eb..3b50447179d 100644
--- a/src/test/isolation/expected/stats.out
+++ b/src/test/isolation/expected/stats.out
@@ -1146,10 +1146,10 @@ has_stats_reset
f
(1 row)
-step s1_func_stats_reset: SELECT pg_stat_reset_single_function_counters('test_stat_func'::regproc);
-pg_stat_reset_single_function_counters
---------------------------------------
-
+step s1_func_stats_reset: SELECT pg_stat_reset_single_function_counters('test_stat_func'::regproc) IS NOT NULL AS t;
+t
+-
+t
(1 row)
step s1_func_stats:
@@ -1218,10 +1218,10 @@ pg_stat_get_function_calls
(1 row)
-step s1_func_stats_reset_nonexistent: SELECT pg_stat_reset_single_function_counters(12000);
-pg_stat_reset_single_function_counters
---------------------------------------
-
+step s1_func_stats_reset_nonexistent: SELECT pg_stat_reset_single_function_counters(12000) IS NOT NULL AS t;
+t
+-
+t
(1 row)
step s1_func_stats_nonexistent:
diff --git a/src/test/isolation/expected/stats_1.out b/src/test/isolation/expected/stats_1.out
index ea6f4aebbc0..6960faa2930 100644
--- a/src/test/isolation/expected/stats_1.out
+++ b/src/test/isolation/expected/stats_1.out
@@ -1146,10 +1146,10 @@ has_stats_reset
f
(1 row)
-step s1_func_stats_reset: SELECT pg_stat_reset_single_function_counters('test_stat_func'::regproc);
-pg_stat_reset_single_function_counters
---------------------------------------
-
+step s1_func_stats_reset: SELECT pg_stat_reset_single_function_counters('test_stat_func'::regproc) IS NOT NULL AS t;
+t
+-
+t
(1 row)
step s1_func_stats:
@@ -1218,10 +1218,10 @@ pg_stat_get_function_calls
(1 row)
-step s1_func_stats_reset_nonexistent: SELECT pg_stat_reset_single_function_counters(12000);
-pg_stat_reset_single_function_counters
---------------------------------------
-
+step s1_func_stats_reset_nonexistent: SELECT pg_stat_reset_single_function_counters(12000) IS NOT NULL AS t;
+t
+-
+t
(1 row)
step s1_func_stats_nonexistent:
diff --git a/src/test/isolation/specs/stats.spec b/src/test/isolation/specs/stats.spec
index a8705926fa6..a9f4e741d37 100644
--- a/src/test/isolation/specs/stats.spec
+++ b/src/test/isolation/specs/stats.spec
@@ -57,12 +57,12 @@ step s1_track_funcs_all { SET track_functions = 'all'; }
step s1_track_funcs_none { SET track_functions = 'none'; }
step s1_func_call { SELECT test_stat_func(); }
step s1_func_drop { DROP FUNCTION test_stat_func(); }
-step s1_func_stats_reset { SELECT pg_stat_reset_single_function_counters('test_stat_func'::regproc); }
+step s1_func_stats_reset { SELECT pg_stat_reset_single_function_counters('test_stat_func'::regproc) IS NOT NULL AS t; }
step s1_func_stats_reset_check {
SELECT pg_stat_get_function_stat_reset_time('test_stat_func'::regproc)
IS NOT NULL AS has_stats_reset;
}
-step s1_func_stats_reset_nonexistent { SELECT pg_stat_reset_single_function_counters(12000); }
+step s1_func_stats_reset_nonexistent { SELECT pg_stat_reset_single_function_counters(12000) IS NOT NULL AS t; }
step s1_reset { SELECT pg_stat_reset() IS NOT NULL AS t; }
step s1_func_stats {
SELECT
--
2.47.3
v3-0006-Make-pg_stat_reset_slru-return-the-reset-time.patchapplication/octet-stream; name=v3-0006-Make-pg_stat_reset_slru-return-the-reset-time.patchDownload
From a43ed4295aaafca9ace507a1b36e1bdc13de8659 Mon Sep 17 00:00:00 2001
From: Shinya Kato <shinya11.kato@gmail.com>
Date: Fri, 2 May 2025 16:57:23 +0900
Subject: [PATCH v3 6/9] Make pg_stat_reset_slru() return the reset time
---
doc/src/sgml/monitoring.sgml | 4 ++--
src/backend/catalog/system_functions.sql | 2 +-
src/backend/utils/activity/pgstat_slru.c | 4 +++-
src/backend/utils/adt/pgstatfuncs.c | 7 ++++---
src/include/catalog/pg_proc.dat | 2 +-
src/include/pgstat.h | 2 +-
src/test/regress/expected/stats.out | 16 ++++++++--------
src/test/regress/sql/stats.sql | 4 ++--
8 files changed, 22 insertions(+), 19 deletions(-)
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index 60ece8b02a8..07b7b160987 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -5211,11 +5211,11 @@ description | Waiting for a newly initialized WAL file to reach durable storage
<primary>pg_stat_reset_slru</primary>
</indexterm>
<function>pg_stat_reset_slru</function> ( [ <parameter>target</parameter> <type>text</type> <literal>DEFAULT</literal> <literal>NULL</literal> ] )
- <returnvalue>void</returnvalue>
+ <returnvalue>timestamp with time zone</returnvalue>
</para>
<para>
Resets statistics to zero for a single SLRU cache, or for all SLRUs in
- the cluster. If <parameter>target</parameter> is
+ the cluster, and returns the time of the reset. If <parameter>target</parameter> is
<literal>NULL</literal> or is not specified, all the counters shown in
the <structname>pg_stat_slru</structname> view for all SLRU caches are
reset. The argument can be one of
diff --git a/src/backend/catalog/system_functions.sql b/src/backend/catalog/system_functions.sql
index 0d679e6a8d4..4039fe5abc8 100644
--- a/src/backend/catalog/system_functions.sql
+++ b/src/backend/catalog/system_functions.sql
@@ -645,7 +645,7 @@ AS 'pg_stat_reset_shared';
CREATE OR REPLACE FUNCTION
pg_stat_reset_slru(target text DEFAULT NULL)
-RETURNS void
+RETURNS timestamp with time zone
LANGUAGE INTERNAL
CALLED ON NULL INPUT VOLATILE PARALLEL SAFE
AS 'pg_stat_reset_slru';
diff --git a/src/backend/utils/activity/pgstat_slru.c b/src/backend/utils/activity/pgstat_slru.c
index da50f8a0457..89a534048fb 100644
--- a/src/backend/utils/activity/pgstat_slru.c
+++ b/src/backend/utils/activity/pgstat_slru.c
@@ -41,7 +41,7 @@ static bool have_slrustats = false;
* Permission checking for this function is managed through the normal
* GRANT system.
*/
-void
+TimestampTz
pgstat_reset_slru(const char *name)
{
TimestampTz ts = GetCurrentTimestamp();
@@ -49,6 +49,8 @@ pgstat_reset_slru(const char *name)
Assert(name != NULL);
pgstat_reset_slru_counter_internal(pgstat_get_slru_index(name), ts);
+
+ return ts;
}
/*
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index b3d3d7f027d..cabdbd8303a 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -2002,16 +2002,17 @@ Datum
pg_stat_reset_slru(PG_FUNCTION_ARGS)
{
char *target = NULL;
+ TimestampTz ts;
if (PG_ARGISNULL(0))
- pgstat_reset_of_kind(PGSTAT_KIND_SLRU);
+ ts = pgstat_reset_of_kind(PGSTAT_KIND_SLRU);
else
{
target = text_to_cstring(PG_GETARG_TEXT_PP(0));
- pgstat_reset_slru(target);
+ ts = pgstat_reset_slru(target);
}
- PG_RETURN_VOID();
+ PG_RETURN_TIMESTAMPTZ(ts);
}
/* Reset replication slots stats (a specific one or all of them). */
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index b05e96c5994..749c8c3ac1c 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -6183,7 +6183,7 @@
{ oid => '2307',
descr => 'statistics: reset collected statistics for a single SLRU',
proname => 'pg_stat_reset_slru', proisstrict => 'f', provolatile => 'v',
- prorettype => 'void', proargtypes => 'text', proargnames => '{target}',
+ prorettype => 'timestamptz', proargtypes => 'text', proargnames => '{target}',
prosrc => 'pg_stat_reset_slru' },
{ oid => '6170',
descr => 'statistics: reset collected statistics for a single replication slot',
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index cac2cef060d..11efea568db 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -751,7 +751,7 @@ extern PgStat_StatReplSlotEntry *pgstat_fetch_replslot(NameData slotname);
* Functions in pgstat_slru.c
*/
-extern void pgstat_reset_slru(const char *);
+extern TimestampTz pgstat_reset_slru(const char *);
extern void pgstat_count_slru_blocks_zeroed(int slru_idx);
extern void pgstat_count_slru_blocks_hit(int slru_idx);
extern void pgstat_count_slru_blocks_read(int slru_idx);
diff --git a/src/test/regress/expected/stats.out b/src/test/regress/expected/stats.out
index f1b6e21533f..c62d8ad26ff 100644
--- a/src/test/regress/expected/stats.out
+++ b/src/test/regress/expected/stats.out
@@ -1011,10 +1011,10 @@ WHERE pg_stat_get_backend_pid(beid) = pg_backend_pid();
-- Test that reset_slru with a specified SLRU works.
SELECT stats_reset AS slru_commit_ts_reset_ts FROM pg_stat_slru WHERE name = 'commit_timestamp' \gset
SELECT stats_reset AS slru_notify_reset_ts FROM pg_stat_slru WHERE name = 'notify' \gset
-SELECT pg_stat_reset_slru('commit_timestamp');
- pg_stat_reset_slru
---------------------
-
+SELECT pg_stat_reset_slru('commit_timestamp') IS NOT NULL AS t;
+ t
+---
+ t
(1 row)
SELECT stats_reset > :'slru_commit_ts_reset_ts'::timestamptz FROM pg_stat_slru WHERE name = 'commit_timestamp';
@@ -1025,10 +1025,10 @@ SELECT stats_reset > :'slru_commit_ts_reset_ts'::timestamptz FROM pg_stat_slru W
SELECT stats_reset AS slru_commit_ts_reset_ts FROM pg_stat_slru WHERE name = 'commit_timestamp' \gset
-- Test that multiple SLRUs are reset when no specific SLRU provided to reset function
-SELECT pg_stat_reset_slru();
- pg_stat_reset_slru
---------------------
-
+SELECT pg_stat_reset_slru() IS NOT NULL AS t;
+ t
+---
+ t
(1 row)
SELECT stats_reset > :'slru_commit_ts_reset_ts'::timestamptz FROM pg_stat_slru WHERE name = 'commit_timestamp';
diff --git a/src/test/regress/sql/stats.sql b/src/test/regress/sql/stats.sql
index a1a9dad0a6a..9e7f1a5fd2f 100644
--- a/src/test/regress/sql/stats.sql
+++ b/src/test/regress/sql/stats.sql
@@ -481,12 +481,12 @@ WHERE pg_stat_get_backend_pid(beid) = pg_backend_pid();
-- Test that reset_slru with a specified SLRU works.
SELECT stats_reset AS slru_commit_ts_reset_ts FROM pg_stat_slru WHERE name = 'commit_timestamp' \gset
SELECT stats_reset AS slru_notify_reset_ts FROM pg_stat_slru WHERE name = 'notify' \gset
-SELECT pg_stat_reset_slru('commit_timestamp');
+SELECT pg_stat_reset_slru('commit_timestamp') IS NOT NULL AS t;
SELECT stats_reset > :'slru_commit_ts_reset_ts'::timestamptz FROM pg_stat_slru WHERE name = 'commit_timestamp';
SELECT stats_reset AS slru_commit_ts_reset_ts FROM pg_stat_slru WHERE name = 'commit_timestamp' \gset
-- Test that multiple SLRUs are reset when no specific SLRU provided to reset function
-SELECT pg_stat_reset_slru();
+SELECT pg_stat_reset_slru() IS NOT NULL AS t;
SELECT stats_reset > :'slru_commit_ts_reset_ts'::timestamptz FROM pg_stat_slru WHERE name = 'commit_timestamp';
SELECT stats_reset > :'slru_notify_reset_ts'::timestamptz FROM pg_stat_slru WHERE name = 'notify';
--
2.47.3
v3-0007-Make-pg_stat_reset_replication_slot-return-the-re.patchapplication/octet-stream; name=v3-0007-Make-pg_stat_reset_replication_slot-return-the-re.patchDownload
From 5759fd15c53dc8c57cb368e227f81a10c75f06a2 Mon Sep 17 00:00:00 2001
From: Shinya Kato <shinya11.kato@gmail.com>
Date: Fri, 2 May 2025 18:13:45 +0900
Subject: [PATCH v3 7/9] Make pg_stat_reset_replication_slot() return the reset
time
---
contrib/test_decoding/expected/stats.out | 35 +++++++++++++++-----
contrib/test_decoding/sql/stats.sql | 9 +++--
doc/src/sgml/monitoring.sgml | 9 ++---
src/backend/utils/activity/pgstat_replslot.c | 9 +++--
src/backend/utils/adt/pgstatfuncs.c | 10 ++++--
src/include/catalog/pg_proc.dat | 2 +-
src/include/pgstat.h | 2 +-
7 files changed, 55 insertions(+), 21 deletions(-)
diff --git a/contrib/test_decoding/expected/stats.out b/contrib/test_decoding/expected/stats.out
index 28da9123cc8..51c77f8bf3f 100644
--- a/contrib/test_decoding/expected/stats.out
+++ b/contrib/test_decoding/expected/stats.out
@@ -47,10 +47,10 @@ SELECT slot_name, spill_txns = 0 AS spill_txns, spill_count = 0 AS spill_count,
RESET logical_decoding_work_mem;
-- reset stats for one slot, others should be unaffected
-SELECT pg_stat_reset_replication_slot('regression_slot_stats1');
- pg_stat_reset_replication_slot
---------------------------------
-
+SELECT pg_stat_reset_replication_slot('regression_slot_stats1') IS NOT NULL AS t;
+ t
+---
+ t
(1 row)
SELECT slot_name, spill_txns = 0 AS spill_txns, spill_count = 0 AS spill_count, total_txns > 0 AS total_txns, total_bytes > 0 AS total_bytes, mem_exceeded_count = 0 AS mem_exceeded_count FROM pg_stat_replication_slots ORDER BY slot_name;
@@ -62,10 +62,10 @@ SELECT slot_name, spill_txns = 0 AS spill_txns, spill_count = 0 AS spill_count,
(3 rows)
-- reset stats for all slots
-SELECT pg_stat_reset_replication_slot(NULL);
- pg_stat_reset_replication_slot
---------------------------------
-
+SELECT pg_stat_reset_replication_slot(NULL) IS NOT NULL AS t;
+ t
+---
+ t
(1 row)
SELECT slot_name, spill_txns = 0 AS spill_txns, spill_count = 0 AS spill_count, total_txns > 0 AS total_txns, total_bytes > 0 AS total_bytes, mem_exceeded_count = 0 AS mem_exceeded_count FROM pg_stat_replication_slots ORDER BY slot_name;
@@ -91,6 +91,25 @@ SELECT * FROM pg_stat_get_replication_slot('do-not-exist');
do-not-exist | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
(1 row)
+-- verify physical replication slot stats are not reset
+SELECT 'init' FROM pg_create_physical_replication_slot('regression_slot_stats_physical') ps;
+ ?column?
+----------
+ init
+(1 row)
+
+SELECT pg_stat_reset_replication_slot('regression_slot_stats_physical');
+ pg_stat_reset_replication_slot
+--------------------------------
+
+(1 row)
+
+SELECT pg_drop_replication_slot('regression_slot_stats_physical');
+ pg_drop_replication_slot
+--------------------------
+
+(1 row)
+
-- spilling the xact
BEGIN;
INSERT INTO stats_test SELECT 'serialize-topbig--1:'||g.i FROM generate_series(1, 5000) g(i);
diff --git a/contrib/test_decoding/sql/stats.sql b/contrib/test_decoding/sql/stats.sql
index 6661dbcb85c..391c3321c5a 100644
--- a/contrib/test_decoding/sql/stats.sql
+++ b/contrib/test_decoding/sql/stats.sql
@@ -19,11 +19,11 @@ SELECT slot_name, spill_txns = 0 AS spill_txns, spill_count = 0 AS spill_count,
RESET logical_decoding_work_mem;
-- reset stats for one slot, others should be unaffected
-SELECT pg_stat_reset_replication_slot('regression_slot_stats1');
+SELECT pg_stat_reset_replication_slot('regression_slot_stats1') IS NOT NULL AS t;
SELECT slot_name, spill_txns = 0 AS spill_txns, spill_count = 0 AS spill_count, total_txns > 0 AS total_txns, total_bytes > 0 AS total_bytes, mem_exceeded_count = 0 AS mem_exceeded_count FROM pg_stat_replication_slots ORDER BY slot_name;
-- reset stats for all slots
-SELECT pg_stat_reset_replication_slot(NULL);
+SELECT pg_stat_reset_replication_slot(NULL) IS NOT NULL AS t;
SELECT slot_name, spill_txns = 0 AS spill_txns, spill_count = 0 AS spill_count, total_txns > 0 AS total_txns, total_bytes > 0 AS total_bytes, mem_exceeded_count = 0 AS mem_exceeded_count FROM pg_stat_replication_slots ORDER BY slot_name;
-- verify accessing/resetting stats for non-existent slot does something reasonable
@@ -31,6 +31,11 @@ SELECT * FROM pg_stat_get_replication_slot('do-not-exist');
SELECT pg_stat_reset_replication_slot('do-not-exist');
SELECT * FROM pg_stat_get_replication_slot('do-not-exist');
+-- verify physical replication slot stats are not reset
+SELECT 'init' FROM pg_create_physical_replication_slot('regression_slot_stats_physical') ps;
+SELECT pg_stat_reset_replication_slot('regression_slot_stats_physical');
+SELECT pg_drop_replication_slot('regression_slot_stats_physical');
+
-- spilling the xact
BEGIN;
INSERT INTO stats_test SELECT 'serialize-topbig--1:'||g.i FROM generate_series(1, 5000) g(i);
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index 07b7b160987..38838755eed 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -5243,12 +5243,13 @@ description | Waiting for a newly initialized WAL file to reach durable storage
<primary>pg_stat_reset_replication_slot</primary>
</indexterm>
<function>pg_stat_reset_replication_slot</function> ( <type>text</type> )
- <returnvalue>void</returnvalue>
+ <returnvalue>timestamp with time zone</returnvalue>
</para>
<para>
- Resets statistics of the replication slot defined by the argument. If
- the argument is <literal>NULL</literal>, resets statistics for all
- the replication slots.
+ Resets statistics of the replication slot defined by the argument, and
+ returns the time of the reset or <literal>NULL</literal> if the replication
+ slot defined by the argument is a physical slot. If the argument is
+ <literal>NULL</literal>, resets statistics for all the replication slots.
</para>
<para>
This function is restricted to superusers by default, but other users
diff --git a/src/backend/utils/activity/pgstat_replslot.c b/src/backend/utils/activity/pgstat_replslot.c
index d210c261ac6..efcc83f8224 100644
--- a/src/backend/utils/activity/pgstat_replslot.c
+++ b/src/backend/utils/activity/pgstat_replslot.c
@@ -37,11 +37,14 @@ static int get_replslot_index(const char *name, bool need_lock);
*
* Permission checking for this function is managed through the normal
* GRANT system.
+ *
+ * NB: Returns the time of the reset or 0 if it is a physical slot.
*/
-void
+TimestampTz
pgstat_reset_replslot(const char *name)
{
ReplicationSlot *slot;
+ TimestampTz ts = 0;
Assert(name != NULL);
@@ -61,10 +64,12 @@ pgstat_reset_replslot(const char *name)
* as we collect stats only for logical slots.
*/
if (SlotIsLogical(slot))
- pgstat_reset(PGSTAT_KIND_REPLSLOT, InvalidOid,
+ ts = pgstat_reset(PGSTAT_KIND_REPLSLOT, InvalidOid,
ReplicationSlotIndex(slot));
LWLockRelease(ReplicationSlotControlLock);
+
+ return ts;
}
/*
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index cabdbd8303a..ce6630f5eac 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -2020,16 +2020,20 @@ Datum
pg_stat_reset_replication_slot(PG_FUNCTION_ARGS)
{
char *target = NULL;
+ TimestampTz ts;
if (PG_ARGISNULL(0))
- pgstat_reset_of_kind(PGSTAT_KIND_REPLSLOT);
+ ts = pgstat_reset_of_kind(PGSTAT_KIND_REPLSLOT);
else
{
target = text_to_cstring(PG_GETARG_TEXT_PP(0));
- pgstat_reset_replslot(target);
+ ts = pgstat_reset_replslot(target);
}
- PG_RETURN_VOID();
+ if (ts == 0)
+ PG_RETURN_NULL();
+ else
+ PG_RETURN_TIMESTAMPTZ(ts);
}
/* Reset subscription stats (a specific one or all of them) */
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 749c8c3ac1c..941cb8f9cf6 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -6188,7 +6188,7 @@
{ oid => '6170',
descr => 'statistics: reset collected statistics for a single replication slot',
proname => 'pg_stat_reset_replication_slot', proisstrict => 'f',
- provolatile => 'v', prorettype => 'void', proargtypes => 'text',
+ provolatile => 'v', prorettype => 'timestamptz', proargtypes => 'text',
prosrc => 'pg_stat_reset_replication_slot' },
{ oid => '6232',
descr => 'statistics: reset collected statistics for a single subscription',
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index 11efea568db..dca57e27599 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -738,7 +738,7 @@ extern PgStat_TableStatus *find_tabstat_entry(Oid rel_id);
* Functions in pgstat_replslot.c
*/
-extern void pgstat_reset_replslot(const char *name);
+extern TimestampTz pgstat_reset_replslot(const char *name);
struct ReplicationSlot;
extern void pgstat_report_replslot(struct ReplicationSlot *slot, const PgStat_StatReplSlotEntry *repSlotStat);
extern void pgstat_create_replslot(struct ReplicationSlot *slot);
--
2.47.3
v3-0008-Make-pg_stat_reset_subscription_stats-return-the-.patchapplication/octet-stream; name=v3-0008-Make-pg_stat_reset_subscription_stats-return-the-.patchDownload
From 7de8f575533c9751894916183e74f37111d73a5f Mon Sep 17 00:00:00 2001
From: Shinya Kato <shinya11.kato@gmail.com>
Date: Fri, 2 May 2025 18:48:28 +0900
Subject: [PATCH v3 8/9] Make pg_stat_reset_subscription_stats() return the
reset time
---
doc/src/sgml/monitoring.sgml | 8 ++++----
src/backend/utils/adt/pgstatfuncs.c | 7 ++++---
src/include/catalog/pg_proc.dat | 2 +-
src/test/regress/expected/subscription.out | 16 ++++++++--------
src/test/regress/sql/subscription.sql | 4 ++--
5 files changed, 19 insertions(+), 18 deletions(-)
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index 38838755eed..fe561c01da1 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -5263,13 +5263,13 @@ description | Waiting for a newly initialized WAL file to reach durable storage
<primary>pg_stat_reset_subscription_stats</primary>
</indexterm>
<function>pg_stat_reset_subscription_stats</function> ( <type>oid</type> )
- <returnvalue>void</returnvalue>
+ <returnvalue>timestamp with time zone</returnvalue>
</para>
<para>
Resets statistics for a single subscription shown in the
- <structname>pg_stat_subscription_stats</structname> view to zero. If
- the argument is <literal>NULL</literal>, reset statistics for all
- subscriptions.
+ <structname>pg_stat_subscription_stats</structname> view to zero, and
+ returns the time of the reset. If the argument is
+ <literal>NULL</literal>, reset statistics for all subscriptions.
</para>
<para>
This function is restricted to superusers by default, but other users
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index ce6630f5eac..b4ffc401626 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -2041,11 +2041,12 @@ Datum
pg_stat_reset_subscription_stats(PG_FUNCTION_ARGS)
{
Oid subid;
+ TimestampTz ts;
if (PG_ARGISNULL(0))
{
/* Clear all subscription stats */
- pgstat_reset_of_kind(PGSTAT_KIND_SUBSCRIPTION);
+ ts = pgstat_reset_of_kind(PGSTAT_KIND_SUBSCRIPTION);
}
else
{
@@ -2055,10 +2056,10 @@ pg_stat_reset_subscription_stats(PG_FUNCTION_ARGS)
ereport(ERROR,
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
errmsg("invalid subscription OID %u", subid)));
- pgstat_reset(PGSTAT_KIND_SUBSCRIPTION, InvalidOid, subid);
+ ts = pgstat_reset(PGSTAT_KIND_SUBSCRIPTION, InvalidOid, subid);
}
- PG_RETURN_VOID();
+ PG_RETURN_TIMESTAMPTZ(ts);
}
Datum
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 941cb8f9cf6..e9b56688032 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -6193,7 +6193,7 @@
{ oid => '6232',
descr => 'statistics: reset collected statistics for a single subscription',
proname => 'pg_stat_reset_subscription_stats', proisstrict => 'f',
- provolatile => 'v', prorettype => 'void', proargtypes => 'oid',
+ provolatile => 'v', prorettype => 'timestamptz', proargtypes => 'oid',
prosrc => 'pg_stat_reset_subscription_stats' },
{ oid => '3163', descr => 'current trigger depth',
diff --git a/src/test/regress/expected/subscription.out b/src/test/regress/expected/subscription.out
index 327d1e7731f..dca7fa66767 100644
--- a/src/test/regress/expected/subscription.out
+++ b/src/test/regress/expected/subscription.out
@@ -47,10 +47,10 @@ SELECT subname, stats_reset IS NULL stats_reset_is_null FROM pg_stat_subscriptio
regress_testsub | t
(1 row)
-SELECT pg_stat_reset_subscription_stats(oid) FROM pg_subscription WHERE subname = 'regress_testsub';
- pg_stat_reset_subscription_stats
-----------------------------------
-
+SELECT pg_stat_reset_subscription_stats(oid) IS NOT NULL AS t FROM pg_subscription WHERE subname = 'regress_testsub';
+ t
+---
+ t
(1 row)
SELECT subname, stats_reset IS NULL stats_reset_is_null FROM pg_stat_subscription_stats WHERE subname = 'regress_testsub';
@@ -61,10 +61,10 @@ SELECT subname, stats_reset IS NULL stats_reset_is_null FROM pg_stat_subscriptio
-- Reset the stats again and check if the new reset_stats is updated.
SELECT stats_reset as prev_stats_reset FROM pg_stat_subscription_stats WHERE subname = 'regress_testsub' \gset
-SELECT pg_stat_reset_subscription_stats(oid) FROM pg_subscription WHERE subname = 'regress_testsub';
- pg_stat_reset_subscription_stats
-----------------------------------
-
+SELECT pg_stat_reset_subscription_stats(oid) IS NOT NULL AS t FROM pg_subscription WHERE subname = 'regress_testsub';
+ t
+---
+ t
(1 row)
SELECT :'prev_stats_reset' < stats_reset FROM pg_stat_subscription_stats WHERE subname = 'regress_testsub';
diff --git a/src/test/regress/sql/subscription.sql b/src/test/regress/sql/subscription.sql
index ef0c298d2df..8f4aa760fe2 100644
--- a/src/test/regress/sql/subscription.sql
+++ b/src/test/regress/sql/subscription.sql
@@ -34,12 +34,12 @@ SELECT obj_description(s.oid, 'pg_subscription') FROM pg_subscription s;
-- Check if the subscription stats are created and stats_reset is updated
-- by pg_stat_reset_subscription_stats().
SELECT subname, stats_reset IS NULL stats_reset_is_null FROM pg_stat_subscription_stats WHERE subname = 'regress_testsub';
-SELECT pg_stat_reset_subscription_stats(oid) FROM pg_subscription WHERE subname = 'regress_testsub';
+SELECT pg_stat_reset_subscription_stats(oid) IS NOT NULL AS t FROM pg_subscription WHERE subname = 'regress_testsub';
SELECT subname, stats_reset IS NULL stats_reset_is_null FROM pg_stat_subscription_stats WHERE subname = 'regress_testsub';
-- Reset the stats again and check if the new reset_stats is updated.
SELECT stats_reset as prev_stats_reset FROM pg_stat_subscription_stats WHERE subname = 'regress_testsub' \gset
-SELECT pg_stat_reset_subscription_stats(oid) FROM pg_subscription WHERE subname = 'regress_testsub';
+SELECT pg_stat_reset_subscription_stats(oid) IS NOT NULL AS t FROM pg_subscription WHERE subname = 'regress_testsub';
SELECT :'prev_stats_reset' < stats_reset FROM pg_stat_subscription_stats WHERE subname = 'regress_testsub';
-- fail - name already exists
--
2.47.3
v3-0009-Make-pg_stat_clear_snapshot-return-the-reset-time.patchapplication/octet-stream; name=v3-0009-Make-pg_stat_clear_snapshot-return-the-reset-time.patchDownload
From ec071144e88e26e893f1d67f9170c9ac706da55e Mon Sep 17 00:00:00 2001
From: Shinya Kato <shinya11.kato@gmail.com>
Date: Fri, 2 May 2025 19:08:45 +0900
Subject: [PATCH v3 9/9] Make pg_stat_clear_snapshot() return the reset time
---
doc/src/sgml/monitoring.sgml | 5 ++--
src/backend/utils/activity/pgstat.c | 4 +++-
src/backend/utils/adt/pgstatfuncs.c | 6 +++--
src/include/catalog/pg_proc.dat | 2 +-
src/include/pgstat.h | 2 +-
src/test/isolation/expected/stats.out | 32 ++++++++++++-------------
src/test/isolation/expected/stats_1.out | 32 ++++++++++++-------------
src/test/isolation/specs/stats.spec | 2 +-
src/test/regress/expected/stats.out | 8 +++----
src/test/regress/sql/stats.sql | 2 +-
10 files changed, 50 insertions(+), 45 deletions(-)
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index fe561c01da1..08e402eb5db 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -5055,10 +5055,11 @@ description | Waiting for a newly initialized WAL file to reach durable storage
<primary>pg_stat_clear_snapshot</primary>
</indexterm>
<function>pg_stat_clear_snapshot</function> ()
- <returnvalue>void</returnvalue>
+ <returnvalue>timestamp with time zone</returnvalue>
</para>
<para>
- Discards the current statistics snapshot or cached information.
+ Discards the current statistics snapshot or cached information, and
+ returns the time of the discard.
</para></entry>
</row>
diff --git a/src/backend/utils/activity/pgstat.c b/src/backend/utils/activity/pgstat.c
index 5b7166ee554..88e49bea703 100644
--- a/src/backend/utils/activity/pgstat.c
+++ b/src/backend/utils/activity/pgstat.c
@@ -904,7 +904,7 @@ pgstat_reset_of_kind(PgStat_Kind kind)
* the no-longer-wanted snapshot. Updates of stats_fetch_consistency can
* cause this routine to be called.
*/
-void
+TimestampTz
pgstat_clear_snapshot(void)
{
pgstat_assert_is_up();
@@ -934,6 +934,8 @@ pgstat_clear_snapshot(void)
/* Reset this flag, as it may be possible that a cleanup was forced. */
force_stats_snapshot_clear = false;
+
+ return GetCurrentTimestamp();
}
void *
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index b4ffc401626..89a941a94c4 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -1858,9 +1858,11 @@ pg_stat_get_snapshot_timestamp(PG_FUNCTION_ARGS)
Datum
pg_stat_clear_snapshot(PG_FUNCTION_ARGS)
{
- pgstat_clear_snapshot();
+ TimestampTz ts;
- PG_RETURN_VOID();
+ ts = pgstat_clear_snapshot();
+
+ PG_RETURN_TIMESTAMPTZ(ts);
}
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index e9b56688032..0a89cc02df9 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -6150,7 +6150,7 @@
{ oid => '2230',
descr => 'statistics: discard current transaction\'s statistics snapshot',
proname => 'pg_stat_clear_snapshot', proisstrict => 'f', provolatile => 'v',
- proparallel => 'r', prorettype => 'void', proargtypes => '',
+ proparallel => 'r', prorettype => 'timestamptz', proargtypes => '',
prosrc => 'pg_stat_clear_snapshot' },
{ oid => '2137',
descr => 'statistics: force stats to be flushed after the next commit',
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index dca57e27599..80d7842822d 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -534,7 +534,7 @@ extern TimestampTz pgstat_reset(PgStat_Kind kind, Oid dboid, uint64 objid);
extern TimestampTz pgstat_reset_of_kind(PgStat_Kind kind);
/* stats accessors */
-extern void pgstat_clear_snapshot(void);
+extern TimestampTz pgstat_clear_snapshot(void);
extern TimestampTz pgstat_get_stat_snapshot_timestamp(bool *have_snapshot);
/* helpers */
diff --git a/src/test/isolation/expected/stats.out b/src/test/isolation/expected/stats.out
index 3b50447179d..8fe02a660d6 100644
--- a/src/test/isolation/expected/stats.out
+++ b/src/test/isolation/expected/stats.out
@@ -3440,10 +3440,10 @@ step s1_slru_check_stats:
t
(1 row)
-step s1_clear_snapshot: SELECT pg_stat_clear_snapshot();
-pg_stat_clear_snapshot
-----------------------
-
+step s1_clear_snapshot: SELECT pg_stat_clear_snapshot() IS NOT NULL AS t;
+t
+-
+t
(1 row)
step s1_slru_check_stats:
@@ -3513,10 +3513,10 @@ step s1_slru_check_stats:
f
(1 row)
-step s1_clear_snapshot: SELECT pg_stat_clear_snapshot();
-pg_stat_clear_snapshot
-----------------------
-
+step s1_clear_snapshot: SELECT pg_stat_clear_snapshot() IS NOT NULL AS t;
+t
+-
+t
(1 row)
step s1_slru_check_stats:
@@ -3586,10 +3586,10 @@ step s1_slru_check_stats:
f
(1 row)
-step s1_clear_snapshot: SELECT pg_stat_clear_snapshot();
-pg_stat_clear_snapshot
-----------------------
-
+step s1_clear_snapshot: SELECT pg_stat_clear_snapshot() IS NOT NULL AS t;
+t
+-
+t
(1 row)
step s1_slru_check_stats:
@@ -3730,10 +3730,10 @@ name |pg_stat_get_function_calls|total_above_zero|self_above_zero
test_stat_func| | |
(1 row)
-step s1_clear_snapshot: SELECT pg_stat_clear_snapshot();
-pg_stat_clear_snapshot
-----------------------
-
+step s1_clear_snapshot: SELECT pg_stat_clear_snapshot() IS NOT NULL AS t;
+t
+-
+t
(1 row)
step s1_func_stats:
diff --git a/src/test/isolation/expected/stats_1.out b/src/test/isolation/expected/stats_1.out
index 6960faa2930..0e3bf074abb 100644
--- a/src/test/isolation/expected/stats_1.out
+++ b/src/test/isolation/expected/stats_1.out
@@ -3464,10 +3464,10 @@ step s1_slru_check_stats:
t
(1 row)
-step s1_clear_snapshot: SELECT pg_stat_clear_snapshot();
-pg_stat_clear_snapshot
-----------------------
-
+step s1_clear_snapshot: SELECT pg_stat_clear_snapshot() IS NOT NULL AS t;
+t
+-
+t
(1 row)
step s1_slru_check_stats:
@@ -3537,10 +3537,10 @@ step s1_slru_check_stats:
f
(1 row)
-step s1_clear_snapshot: SELECT pg_stat_clear_snapshot();
-pg_stat_clear_snapshot
-----------------------
-
+step s1_clear_snapshot: SELECT pg_stat_clear_snapshot() IS NOT NULL AS t;
+t
+-
+t
(1 row)
step s1_slru_check_stats:
@@ -3610,10 +3610,10 @@ step s1_slru_check_stats:
f
(1 row)
-step s1_clear_snapshot: SELECT pg_stat_clear_snapshot();
-pg_stat_clear_snapshot
-----------------------
-
+step s1_clear_snapshot: SELECT pg_stat_clear_snapshot() IS NOT NULL AS t;
+t
+-
+t
(1 row)
step s1_slru_check_stats:
@@ -3754,10 +3754,10 @@ name |pg_stat_get_function_calls|total_above_zero|self_above_zero
test_stat_func| | |
(1 row)
-step s1_clear_snapshot: SELECT pg_stat_clear_snapshot();
-pg_stat_clear_snapshot
-----------------------
-
+step s1_clear_snapshot: SELECT pg_stat_clear_snapshot() IS NOT NULL AS t;
+t
+-
+t
(1 row)
step s1_func_stats:
diff --git a/src/test/isolation/specs/stats.spec b/src/test/isolation/specs/stats.spec
index a9f4e741d37..677e381ab12 100644
--- a/src/test/isolation/specs/stats.spec
+++ b/src/test/isolation/specs/stats.spec
@@ -43,7 +43,7 @@ setup { SET stats_fetch_consistency = 'none'; }
step s1_fetch_consistency_none { SET stats_fetch_consistency = 'none'; }
step s1_fetch_consistency_cache { SET stats_fetch_consistency = 'cache'; }
step s1_fetch_consistency_snapshot { SET stats_fetch_consistency = 'snapshot'; }
-step s1_clear_snapshot { SELECT pg_stat_clear_snapshot(); }
+step s1_clear_snapshot { SELECT pg_stat_clear_snapshot() IS NOT NULL AS t; }
step s1_begin { BEGIN; }
step s1_commit { COMMIT; }
step s1_rollback { ROLLBACK; }
diff --git a/src/test/regress/expected/stats.out b/src/test/regress/expected/stats.out
index c62d8ad26ff..5e4b637792e 100644
--- a/src/test/regress/expected/stats.out
+++ b/src/test/regress/expected/stats.out
@@ -1178,10 +1178,10 @@ SELECT pg_stat_get_snapshot_timestamp() >= NOW();
(1 row)
-- shows NULL again after clearing
-SELECT pg_stat_clear_snapshot();
- pg_stat_clear_snapshot
-------------------------
-
+SELECT pg_stat_clear_snapshot() IS NOT NULL AS t;
+ t
+---
+ t
(1 row)
SELECT pg_stat_get_snapshot_timestamp();
diff --git a/src/test/regress/sql/stats.sql b/src/test/regress/sql/stats.sql
index 9e7f1a5fd2f..7e9a5a9c343 100644
--- a/src/test/regress/sql/stats.sql
+++ b/src/test/regress/sql/stats.sql
@@ -543,7 +543,7 @@ SELECT pg_stat_get_snapshot_timestamp();
SELECT pg_stat_get_function_calls(0);
SELECT pg_stat_get_snapshot_timestamp() >= NOW();
-- shows NULL again after clearing
-SELECT pg_stat_clear_snapshot();
+SELECT pg_stat_clear_snapshot() IS NOT NULL AS t;
SELECT pg_stat_get_snapshot_timestamp();
COMMIT;
--
2.47.3
Re: Enhance statistics reset functions to return reset timestamp
On Sat, 9 Aug 2025 at 10:38, Andres Freund <andres@anarazel.de> wrote:
On 2025-08-08 13:18:39 +0900, Shinya Kato wrote:
I would like to propose a series of patches that enhance the behavior
of various statistics reset functions by making them return the reset
timestamp. This change improves usability and aligns with the behavior
introduced in commit dc9f8a798[1], where pg_stat_statements_reset()
was updated to return the reset time.The following functions have been modified to return a TIMESTAMP WITH
TIME ZONE value indicating when the statistics were reset:
-1 - I think it was a mistake to introduce support for granular resets, we
shouldn't bury ourselves deeper. If anything we should rip out everything
other than 1) a global reset b) a per-database reset.Leaving that aside, I just don't see a convincing use case for returning the
timestamp here.
I agree with Andres here. Resetting the statistics for the purpose of
tracking what's happened since last reset is just a mistake and it can
even be dangerous when you consider autovacuum is driven from the
PgStat_StatTabEntry stats. The race-condition free way of doing this
is to log the values and subtract the previous value from the current
one. That's pretty easy to do in Postgres with the LAG() window
function. You don't need this feature to do that, so -1 from me.
David.
Re: Enhance statistics reset functions to return reset timestamp
On Mon, Oct 27, 2025 at 12:50 PM David Rowley <dgrowleyml@gmail.com> wrote:
On Sat, 9 Aug 2025 at 10:38, Andres Freund <andres@anarazel.de> wrote:
On 2025-08-08 13:18:39 +0900, Shinya Kato wrote:
I would like to propose a series of patches that enhance the behavior
of various statistics reset functions by making them return the reset
timestamp. This change improves usability and aligns with the behavior
introduced in commit dc9f8a798[1], where pg_stat_statements_reset()
was updated to return the reset time.The following functions have been modified to return a TIMESTAMP WITH
TIME ZONE value indicating when the statistics were reset:-1 - I think it was a mistake to introduce support for granular resets, we
shouldn't bury ourselves deeper. If anything we should rip out everything
other than 1) a global reset b) a per-database reset.Leaving that aside, I just don't see a convincing use case for returning the
timestamp here.I agree with Andres here. Resetting the statistics for the purpose of
tracking what's happened since last reset is just a mistake and it can
even be dangerous when you consider autovacuum is driven from the
PgStat_StatTabEntry stats. The race-condition free way of doing this
is to log the values and subtract the previous value from the current
one. That's pretty easy to do in Postgres with the LAG() window
function. You don't need this feature to do that, so -1 from me.
Okay, since there are some objections, I will withdraw these patches.
Thank you all for your comments.
--
Best regards,
Shinya Kato
NTT OSS Center