public inbox for [email protected]
help / color / mirror / Atom feedFrom: Sami Imseih <[email protected]>
To: pgsql-hackers <[email protected]>
Cc: Fujii Masao <[email protected]>
Subject: Add missing stats_reset column to pg_statio_all_sequences view
Date: Thu, 12 Mar 2026 17:06:13 -0500
Message-ID: <CAA5RZ0v0OPGyDpwxkX81CtTt9xsj9-TNxhm=8JdOvEKPsVVFNg@mail.gmail.com> (raw)
Hi,
When reviewing [1], it was also discovered pg_statio_all_sequences is missing
the stats_reset column.
The attached fixes this.
CC'ing Fujii-San, who is also planning to commit [1]
[1] [https://www.postgresql.org/message-id/CAHGQGwEeGUQtj7TOznM2_O2uV-g6iOa1UBBTBfyugJ-nSoS89Q%40mail.gma...]
--
Sami Imseih
Amazon Web Services (AWS)
Attachments:
[application/octet-stream] v1-0001-Add-stats_reset-column-to-pg_statio_all_sequences.patch (6.1K, 2-v1-0001-Add-stats_reset-column-to-pg_statio_all_sequences.patch)
download | inline diff:
From b5331fa3aad5fbacf475ac1c4c1007e844cc289f Mon Sep 17 00:00:00 2001
From: "Sami Imseih (AWS)"
<[email protected]>
Date: Thu, 12 Mar 2026 21:47:56 +0000
Subject: [PATCH v1 1/1] Add stats_reset column to pg_statio_all_sequences
The view was missing the stats_reset column, unlike the other
pg_statio_* views which already expose it. Add the column and
document it.
Also add a regression test to verify pg_stat_reset() properly
updates the stats_reset timestamp for sequences.
Catalog version bumped.
---
doc/src/sgml/monitoring.sgml | 9 +++++++++
src/backend/catalog/system_views.sql | 3 ++-
src/include/catalog/catversion.h | 2 +-
src/test/regress/expected/rules.out | 9 ++++++---
src/test/regress/expected/stats.out | 30 ++++++++++++++++++++++++++++
src/test/regress/sql/stats.sql | 12 +++++++++++
6 files changed, 60 insertions(+), 5 deletions(-)
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index 3bf6d6d54df..df4425fe8ba 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -4900,6 +4900,15 @@ description | Waiting for a newly initialized WAL file to reach durable storage
Number of buffer hits in this sequence
</para></entry>
</row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>stats_reset</structfield> <type>timestamp with time zone</type>
+ </para>
+ <para>
+ Time at which these statistics were last reset
+ </para></entry>
+ </row>
</tbody>
</tgroup>
</table>
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 339c016e510..ab31ff6dcc0 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -896,7 +896,8 @@ CREATE VIEW pg_statio_all_sequences AS
C.relname AS relname,
pg_stat_get_blocks_fetched(C.oid) -
pg_stat_get_blocks_hit(C.oid) AS blks_read,
- pg_stat_get_blocks_hit(C.oid) AS blks_hit
+ pg_stat_get_blocks_hit(C.oid) AS blks_hit,
+ pg_stat_get_stat_reset_time(C.oid) AS stats_reset
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE C.relkind = 'S';
diff --git a/src/include/catalog/catversion.h b/src/include/catalog/catversion.h
index 90f46b03502..e7d6f70ecfa 100644
--- a/src/include/catalog/catversion.h
+++ b/src/include/catalog/catversion.h
@@ -57,6 +57,6 @@
*/
/* yyyymmddN */
-#define CATALOG_VERSION_NO 202603101
+#define CATALOG_VERSION_NO 202603121
#endif
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index f373ad704b6..315e77234c0 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -2450,7 +2450,8 @@ pg_statio_all_sequences| SELECT c.oid AS relid,
n.nspname AS schemaname,
c.relname,
(pg_stat_get_blocks_fetched(c.oid) - pg_stat_get_blocks_hit(c.oid)) AS blks_read,
- pg_stat_get_blocks_hit(c.oid) AS blks_hit
+ pg_stat_get_blocks_hit(c.oid) AS blks_hit,
+ pg_stat_get_stat_reset_time(c.oid) AS stats_reset
FROM (pg_class c
LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace)))
WHERE (c.relkind = 'S'::"char");
@@ -2492,7 +2493,8 @@ pg_statio_sys_sequences| SELECT relid,
schemaname,
relname,
blks_read,
- blks_hit
+ blks_hit,
+ stats_reset
FROM pg_statio_all_sequences
WHERE ((schemaname = ANY (ARRAY['pg_catalog'::name, 'information_schema'::name])) OR (schemaname ~ '^pg_toast'::text));
pg_statio_sys_tables| SELECT relid,
@@ -2523,7 +2525,8 @@ pg_statio_user_sequences| SELECT relid,
schemaname,
relname,
blks_read,
- blks_hit
+ blks_hit,
+ stats_reset
FROM pg_statio_all_sequences
WHERE ((schemaname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])) AND (schemaname !~ '^pg_toast'::text));
pg_statio_user_tables| SELECT relid,
diff --git a/src/test/regress/expected/stats.out b/src/test/regress/expected/stats.out
index cd00f35bf7a..c150c309c1c 100644
--- a/src/test/regress/expected/stats.out
+++ b/src/test/regress/expected/stats.out
@@ -1152,6 +1152,36 @@ SELECT stats_reset > :'db_reset_ts'::timestamptz FROM pg_stat_database WHERE dat
t
(1 row)
+-- Test that reset works for pg_statio_all_sequences
+-- Same as the previous test, reset it once first so we have a baseline stats_reset to compare to
+-- The sequence should also accumulate some stats for reset to matter
+CREATE SEQUENCE test_seq1;
+SELECT nextval('test_seq1');
+ nextval
+---------
+ 1
+(1 row)
+
+SELECT pg_stat_reset();
+ pg_stat_reset
+---------------
+
+(1 row)
+
+SELECT stats_reset AS db_reset_ts FROM pg_statio_all_sequences WHERE relname ='test_seq1' \gset
+SELECT pg_stat_reset();
+ pg_stat_reset
+---------------
+
+(1 row)
+
+SELECT stats_reset > :'db_reset_ts'::timestamptz FROM pg_statio_all_sequences WHERE relname ='test_seq1';
+ ?column?
+----------
+ t
+(1 row)
+
+DROP SEQUENCE test_seq1;
----
-- pg_stat_get_snapshot_timestamp behavior
----
diff --git a/src/test/regress/sql/stats.sql b/src/test/regress/sql/stats.sql
index 8768e0f27fd..6c40000e6ac 100644
--- a/src/test/regress/sql/stats.sql
+++ b/src/test/regress/sql/stats.sql
@@ -531,6 +531,18 @@ SELECT stats_reset AS db_reset_ts FROM pg_stat_database WHERE datname = (SELECT
SELECT pg_stat_reset();
SELECT stats_reset > :'db_reset_ts'::timestamptz FROM pg_stat_database WHERE datname = (SELECT current_database());
+-- Test that reset works for pg_statio_all_sequences
+
+-- Same as the previous test, reset it once first so we have a baseline stats_reset to compare to
+-- The sequence should also accumulate some stats for reset to matter
+CREATE SEQUENCE test_seq1;
+SELECT nextval('test_seq1');
+SELECT pg_stat_reset();
+SELECT stats_reset AS db_reset_ts FROM pg_statio_all_sequences WHERE relname ='test_seq1' \gset
+SELECT pg_stat_reset();
+SELECT stats_reset > :'db_reset_ts'::timestamptz FROM pg_statio_all_sequences WHERE relname ='test_seq1';
+DROP SEQUENCE test_seq1;
+
----
-- pg_stat_get_snapshot_timestamp behavior
--
2.47.3
view thread (24+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected]
Subject: Re: Add missing stats_reset column to pg_statio_all_sequences view
In-Reply-To: <CAA5RZ0v0OPGyDpwxkX81CtTt9xsj9-TNxhm=8JdOvEKPsVVFNg@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox