public inbox for [email protected]
help / color / mirror / Atom feedFrom: shihao zhong <[email protected]>
To: Sami Imseih <[email protected]>
Cc: Kirill Reshke <[email protected]>
Cc: Fujii Masao <[email protected]>
Cc: PostgreSQL-development <[email protected]>
Subject: Re: Add missing stats_reset column to pg_stat_database_conflicts view
Date: Wed, 11 Mar 2026 10:10:32 -0400
Message-ID: <CAGRkXqRVDuhXNCvQ=j-+5KCu+Gcar_h5OeHTaMaVKF-FLV2YvQ@mail.gmail.com> (raw)
In-Reply-To: <CAA5RZ0ta3y==2AStFyo-SYyJ2ztd3ZiurN1HQR9gzDTKVtgUDg@mail.gmail.com>
References: <CAGRkXqS98OebEWjax99_LVAECsxCB8i=BfsdAL34i-5QHfwyOQ@mail.gmail.com>
<CAA5RZ0vSjCEAuKXhxacnp0ftrYkf9QRcyPK-AvTi_otFVDWphA@mail.gmail.com>
<CAHGQGwEeGUQtj7TOznM2_O2uV-g6iOa1UBBTBfyugJ-nSoS89Q@mail.gmail.com>
<CALdSSPi4apueVqgnJEZqpy6O0HSEPUiUzf=vTLPPijsQLob2Eg@mail.gmail.com>
<CAGRkXqQ0qBHTcD=UZ7_GTLsgn+W-=1c8suMJ3yWZb2eP4m0fBg@mail.gmail.com>
<CAA5RZ0ta3y==2AStFyo-SYyJ2ztd3ZiurN1HQR9gzDTKVtgUDg@mail.gmail.com>
On Tue, Mar 10, 2026 at 3:27 PM Sami Imseih <[email protected]> wrote:
>
> Hi,
>
> Please don't top-post. it makes following the thread difficult.
>
> > > I also noticed that pg_statio_all_sequences does not have a reset
> > > column. We should fix this one also. What do you think?
> >
> > Right now the pg_statio_all_tables, pg_statio_all_indexes,
> > pg_statio_all_sequences, pg_stat_user_functions all do not have
> > reset_stat supported. I am actively working on tadd a reset_stat
> > support for these view. For now, let's quickly address the db conflict
> > first.
>
> It looks like stats_reset for pg_stat_user_functions was added in
> b71bae41a0cd and for the others you mention, expected for
> pg_statio_all_sequences, was added in a5b543258aa2.
> These are already targeted for 19, and you can also see
> that in the devel docs [1].
>
> The changes you attached in v2 look good to me, but I think
> we should also add a test in stats.sql as well.
>
> FWIW, I find using "git format-patch" better for the threads. It
> forces you to write a commit message and properly formats
> the patch name [2]? It's the most common way I see patches
> being submitted.
>
> [1] [https://www.postgresql.org/docs/devel/monitoring-stats.html]
> [2] [https://wiki.postgresql.org/wiki/Submitting_a_Patch]
>
> --
> Sami Imseih
> Amazon Web Services (AWS).
Thanks for pointing that out. I've added new tests and used git
format-patch to generate a new patch.
Attachments:
[application/octet-stream] pg_stat_database_conflicts_v3.patch (4.4K, 2-pg_stat_database_conflicts_v3.patch)
download | inline diff:
From 50deb9ed6d65706595073e0c5982fe594925c53d Mon Sep 17 00:00:00 2001
From: shihao zhong <[email protected]>
Date: Wed, 11 Mar 2026 14:05:57 +0000
Subject: [PATCH] Add stats_reset column to pg_stat_database_conflicts view
This commit adds the stats_reset column to pg_stat_database_conflicts,
allowing users to see when the reset was last performed. This brings
consistency with pg_stat_database and other statistics views. Includes: -
system_views.sql: View definition update - monitoring.sgml: Documentation
update - catversion.h: Mandatory catalog version bump - Regression tests:
Updated rules.out and added stats.sql test case
---
doc/src/sgml/monitoring.sgml | 8 ++++++++
src/backend/catalog/system_views.sql | 3 ++-
src/test/regress/expected/rules.out | 3 ++-
src/test/regress/expected/stats.out | 14 ++++++++++++++
src/test/regress/sql/stats.sql | 5 +++++
5 files changed, 31 insertions(+), 2 deletions(-)
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index cc014564c97..763f6731dee 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -4036,6 +4036,14 @@ description | Waiting for a newly initialized WAL file to reach durable storage
on the primary
</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..3361030d34c 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -1163,7 +1163,8 @@ CREATE VIEW pg_stat_database_conflicts AS
pg_stat_get_db_conflict_snapshot(D.oid) AS confl_snapshot,
pg_stat_get_db_conflict_bufferpin(D.oid) AS confl_bufferpin,
pg_stat_get_db_conflict_startup_deadlock(D.oid) AS confl_deadlock,
- pg_stat_get_db_conflict_logicalslot(D.oid) AS confl_active_logicalslot
+ pg_stat_get_db_conflict_logicalslot(D.oid) AS confl_active_logicalslot,
+ pg_stat_get_db_stat_reset_time(D.oid) AS stats_reset
FROM pg_database D;
CREATE VIEW pg_stat_user_functions AS
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index f373ad704b6..71d7262049e 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -1921,7 +1921,8 @@ pg_stat_database_conflicts| SELECT oid AS datid,
pg_stat_get_db_conflict_snapshot(oid) AS confl_snapshot,
pg_stat_get_db_conflict_bufferpin(oid) AS confl_bufferpin,
pg_stat_get_db_conflict_startup_deadlock(oid) AS confl_deadlock,
- pg_stat_get_db_conflict_logicalslot(oid) AS confl_active_logicalslot
+ pg_stat_get_db_conflict_logicalslot(oid) AS confl_active_logicalslot,
+ pg_stat_get_db_stat_reset_time(oid) AS stats_reset
FROM pg_database d;
pg_stat_gssapi| SELECT pid,
gss_auth AS gss_authenticated,
diff --git a/src/test/regress/expected/stats.out b/src/test/regress/expected/stats.out
index cd00f35bf7a..0d634c3af42 100644
--- a/src/test/regress/expected/stats.out
+++ b/src/test/regress/expected/stats.out
@@ -1910,4 +1910,18 @@ SELECT * FROM check_estimated_rows('SELECT * FROM table_fillfactor');
(1 row)
DROP TABLE table_fillfactor;
+-- Test that the stats_reset column in pg_stat_database_conflicts is correctly maintained
+SELECT pg_stat_reset();
+ pg_stat_reset
+---------------
+
+(1 row)
+
+SELECT stats_reset IS NOT NULL AS has_stats_reset
+ FROM pg_stat_database_conflicts WHERE datname = current_database();
+ has_stats_reset
+-----------------
+ t
+(1 row)
+
-- End of Stats Test
diff --git a/src/test/regress/sql/stats.sql b/src/test/regress/sql/stats.sql
index 8768e0f27fd..e5fb90cf0c3 100644
--- a/src/test/regress/sql/stats.sql
+++ b/src/test/regress/sql/stats.sql
@@ -944,4 +944,9 @@ SELECT * FROM check_estimated_rows('SELECT * FROM table_fillfactor');
DROP TABLE table_fillfactor;
+-- Test that the stats_reset column in pg_stat_database_conflicts is correctly maintained
+SELECT pg_stat_reset();
+SELECT stats_reset IS NOT NULL AS has_stats_reset
+ FROM pg_stat_database_conflicts WHERE datname = current_database();
+
-- End of Stats Test
--
2.53.0.473.g4a7958ca14-goog
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], [email protected], [email protected]
Subject: Re: Add missing stats_reset column to pg_stat_database_conflicts view
In-Reply-To: <CAGRkXqRVDuhXNCvQ=j-+5KCu+Gcar_h5OeHTaMaVKF-FLV2YvQ@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