public inbox for [email protected]
help / color / mirror / Atom feedFrom: Sami Imseih <[email protected]>
To: Chao Li <[email protected]>
Cc: Fujii Masao <[email protected]>
Cc: shihao zhong <[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 11:21:18 -0500
Message-ID: <CAA5RZ0uStx0cWUCNPDPhBprAVmcdOX-uuKd9B_W9qLa6Tsp46w@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
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>
<CAHGQGwHaHnRrz9hqpDdn984mqToJDdkjMRVvEXXgFwCnXJb89Q@mail.gmail.com>
<[email protected]>
Thanks for the updated patch!
--- 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;
I think the changes to the test are overcomplicated unnecessarly. We should not
have to join pg_stat_database and pg_stat_database_conflicts. We can just
query each fo the stats_reset separately. see v5, I kept the check to
ensure that
pg_stat_database and pg_stat_database_conflicts have the same reset
time. I think
this is good to have as well. I also updated the existing comment for
more clarity.
--
Sami Imseih
Amazon Web Services (AWS)
Attachments:
[application/octet-stream] v5-0001-Add-stats_reset-column-to-pg_stat_database_confli.patch (6.4K, 2-v5-0001-Add-stats_reset-column-to-pg_stat_database_confli.patch)
download | inline diff:
From 9dd37a685ce4f83ee6b65fa7e48e5bf4867b09b8 Mon Sep 17 00:00:00 2001
From: Fujii Masao <[email protected]>
Date: Thu, 12 Mar 2026 12:15:32 +0900
Subject: [PATCH v5 1/1] 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/include/catalog/catversion.h | 2 +-
src/test/regress/expected/rules.out | 3 ++-
src/test/regress/expected/stats.out | 17 ++++++++++++++++-
src/test/regress/sql/stats.sql | 7 ++++++-
6 files changed, 36 insertions(+), 5 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/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..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..1b3d9f2fb35 100644
--- a/src/test/regress/expected/stats.out
+++ b/src/test/regress/expected/stats.out
@@ -1132,7 +1132,9 @@ 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
+-- Since pg_stat_database stats_reset starts out as NULL, reset it once first so that we
+-- have a baseline for comparison. The same for pg_stat_database_conflicts as it shares
+-- the same stats_reset as pg_stat_database.
SELECT pg_stat_reset();
pg_stat_reset
---------------
@@ -1140,6 +1142,13 @@ SELECT pg_stat_reset();
(1 row)
SELECT stats_reset AS db_reset_ts FROM pg_stat_database WHERE datname = (SELECT current_database()) \gset
+SELECT stats_reset AS dbc_reset_ts FROM pg_stat_database_conflicts WHERE datname = (SELECT current_database()) \gset
+SELECT :'db_reset_ts'::timestamptz = :'dbc_reset_ts'::timestamptz;
+ ?column?
+----------
+ t
+(1 row)
+
SELECT pg_stat_reset();
pg_stat_reset
---------------
@@ -1152,6 +1161,12 @@ SELECT stats_reset > :'db_reset_ts'::timestamptz FROM pg_stat_database WHERE dat
t
(1 row)
+SELECT stats_reset > :'dbc_reset_ts'::timestamptz FROM pg_stat_database_conflicts WHERE datname = (SELECT current_database());
+ ?column?
+----------
+ t
+(1 row)
+
----
-- pg_stat_get_snapshot_timestamp behavior
----
diff --git a/src/test/regress/sql/stats.sql b/src/test/regress/sql/stats.sql
index 8768e0f27fd..3593bd8ab76 100644
--- a/src/test/regress/sql/stats.sql
+++ b/src/test/regress/sql/stats.sql
@@ -525,11 +525,16 @@ 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
+-- Since pg_stat_database stats_reset starts out as NULL, reset it once first so that we
+-- have a baseline for comparison. The same for pg_stat_database_conflicts as it shares
+-- the same stats_reset as pg_stat_database.
SELECT pg_stat_reset();
SELECT stats_reset AS db_reset_ts FROM pg_stat_database WHERE datname = (SELECT current_database()) \gset
+SELECT stats_reset AS dbc_reset_ts FROM pg_stat_database_conflicts WHERE datname = (SELECT current_database()) \gset
+SELECT :'db_reset_ts'::timestamptz = :'dbc_reset_ts'::timestamptz;
SELECT pg_stat_reset();
SELECT stats_reset > :'db_reset_ts'::timestamptz FROM pg_stat_database WHERE datname = (SELECT current_database());
+SELECT stats_reset > :'dbc_reset_ts'::timestamptz FROM pg_stat_database_conflicts WHERE datname = (SELECT current_database());
----
--
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], [email protected], [email protected], [email protected]
Subject: Re: Add missing stats_reset column to pg_stat_database_conflicts view
In-Reply-To: <CAA5RZ0uStx0cWUCNPDPhBprAVmcdOX-uuKd9B_W9qLa6Tsp46w@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