public inbox for [email protected]
help / color / mirror / Atom feedFrom: Fujii Masao <[email protected]>
To: shihao zhong <[email protected]>
Cc: Sami Imseih <[email protected]>
Cc: Kirill Reshke <[email protected]>
Cc: PostgreSQL-development <[email protected]>
Subject: Re: Add missing stats_reset column to pg_stat_database_conflicts view
Date: Thu, 12 Mar 2026 13:50:28 +0900
Message-ID: <CAHGQGwHaHnRrz9hqpDdn984mqToJDdkjMRVvEXXgFwCnXJb89Q@mail.gmail.com> (raw)
In-Reply-To: <CAGRkXqRVDuhXNCvQ=j-+5KCu+Gcar_h5OeHTaMaVKF-FLV2YvQ@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>
<CAGRkXqRVDuhXNCvQ=j-+5KCu+Gcar_h5OeHTaMaVKF-FLV2YvQ@mail.gmail.com>
On Wed, Mar 11, 2026 at 11:10 PM shihao zhong <[email protected]> wrote:
> Thanks for pointing that out. I've added new tests and used git
> format-patch to generate a new patch.
Thanks for updating the patch!
+-- 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();
Since stats.sql already includes tests verifying that reset works for
pg_stat_database, it might be better to add the test for
pg_stat_database_conflicts alongside those, rather than at the end of
stats.sql. Thought? The attached updated patch does that.
I also fixed some indentation issues in the docs in the patch.
Regards,
--
Fujii Masao
Attachments:
[application/octet-stream] v4-0001-Add-stats_reset-column-to-pg_stat_database_confli.patch (6.6K, 2-v4-0001-Add-stats_reset-column-to-pg_stat_database_confli.patch)
download | inline diff:
From 9aabc976bad3e2b131d27fcb9b8770b9d54642a8 Mon Sep 17 00:00:00 2001
From: Fujii Masao <[email protected]>
Date: Thu, 12 Mar 2026 12:15:32 +0900
Subject: [PATCH v4] Add stats_reset column to pg_stat_database_conflicts.
This commit adds a stats_reset column to pg_stat_database_conflicts,
allowing users to see when the statistics in this view were last reset.
This makes the view consistent with pg_stat_database and other statistics
views.
Catalog version bumped.
Author: Shihao Zhong <[email protected]>
Reviewed-by: Sami Imseih <[email protected]>
Reviewed-by: Kirill Reshke <[email protected]>
Discussion: https://postgr.es/m/CAGRkXqS98OebEWjax99_LVAECsxCB8i=BfsdAL34i-5QHfwyOQ@mail.gmail.com
---
doc/src/sgml/monitoring.sgml | 9 +++++++++
src/backend/catalog/system_views.sql | 3 ++-
src/test/regress/expected/rules.out | 3 ++-
src/test/regress/expected/stats.out | 21 ++++++++++++++-------
src/test/regress/sql/stats.sql | 15 +++++++++++----
5 files changed, 38 insertions(+), 13 deletions(-)
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index cc014564c97..7ef9fbba874 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -4036,6 +4036,15 @@ 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..90d48bc9c80 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..ba23db66eb6 100644
--- a/src/test/regress/expected/stats.out
+++ b/src/test/regress/expected/stats.out
@@ -1131,25 +1131,32 @@ SELECT stats_reset > :'wal_reset_ts'::timestamptz FROM pg_stat_wal;
SELECT pg_stat_reset_shared('unknown');
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
+-- Test that reset works for pg_stat_database and pg_stat_database_conflicts
+-- Since stats_reset in pg_stat_database and pg_stat_database_conflicts starts
+-- out as NULL, reset it once first so we have something to compare it to
SELECT pg_stat_reset();
pg_stat_reset
---------------
(1 row)
-SELECT stats_reset AS db_reset_ts FROM pg_stat_database WHERE datname = (SELECT current_database()) \gset
+SELECT D.stats_reset AS db_reset_ts, DC.stats_reset AS dbc_reset_ts
+FROM pg_stat_database D, pg_stat_database_conflicts DC
+WHERE D.datname = (SELECT current_database()) AND D.datname = DC.datname \gset
SELECT pg_stat_reset();
pg_stat_reset
---------------
(1 row)
-SELECT stats_reset > :'db_reset_ts'::timestamptz FROM pg_stat_database WHERE datname = (SELECT current_database());
- ?column?
-----------
- t
+SELECT D.stats_reset > :'db_reset_ts'::timestamptz,
+ DC.stats_reset > :'dbc_reset_ts'::timestamptz,
+ D.stats_reset = DC.stats_reset
+FROM pg_stat_database D, pg_stat_database_conflicts DC
+WHERE D.datname = (SELECT current_database()) AND D.datname = DC.datname;
+ ?column? | ?column? | ?column?
+----------+----------+----------
+ t | t | t
(1 row)
----
diff --git a/src/test/regress/sql/stats.sql b/src/test/regress/sql/stats.sql
index 8768e0f27fd..c04b898d861 100644
--- a/src/test/regress/sql/stats.sql
+++ b/src/test/regress/sql/stats.sql
@@ -523,13 +523,20 @@ SELECT stats_reset > :'wal_reset_ts'::timestamptz FROM pg_stat_wal;
-- Test error case for reset_shared with unknown stats type
SELECT pg_stat_reset_shared('unknown');
--- Test that reset works for pg_stat_database
+-- Test that reset works for pg_stat_database and pg_stat_database_conflicts
--- Since pg_stat_database stats_reset starts out as NULL, reset it once first so we have something to compare it to
+-- Since stats_reset in pg_stat_database and pg_stat_database_conflicts starts
+-- out as NULL, reset it once first so we have something to compare it to
SELECT pg_stat_reset();
-SELECT stats_reset AS db_reset_ts FROM pg_stat_database WHERE datname = (SELECT current_database()) \gset
+SELECT D.stats_reset AS db_reset_ts, DC.stats_reset AS dbc_reset_ts
+FROM pg_stat_database D, pg_stat_database_conflicts DC
+WHERE D.datname = (SELECT current_database()) AND D.datname = DC.datname \gset
SELECT pg_stat_reset();
-SELECT stats_reset > :'db_reset_ts'::timestamptz FROM pg_stat_database WHERE datname = (SELECT current_database());
+SELECT D.stats_reset > :'db_reset_ts'::timestamptz,
+ DC.stats_reset > :'dbc_reset_ts'::timestamptz,
+ D.stats_reset = DC.stats_reset
+FROM pg_stat_database D, pg_stat_database_conflicts DC
+WHERE D.datname = (SELECT current_database()) AND D.datname = DC.datname;
----
--
2.51.2
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: <CAHGQGwHaHnRrz9hqpDdn984mqToJDdkjMRVvEXXgFwCnXJb89Q@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