public inbox for [email protected]
help / color / mirror / Atom feedFrom: Fujii Masao <[email protected]>
To: Sami Imseih <[email protected]>
Cc: Chao Li <[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: Fri, 13 Mar 2026 03:09:19 +0900
Message-ID: <CAHGQGwF3=y-j5LxHdXtthEsmJYY5U6WtcAsgwbestfCtNvvuaw@mail.gmail.com> (raw)
In-Reply-To: <CAA5RZ0uStx0cWUCNPDPhBprAVmcdOX-uuKd9B_W9qLa6Tsp46w@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>
<CAHGQGwHaHnRrz9hqpDdn984mqToJDdkjMRVvEXXgFwCnXJb89Q@mail.gmail.com>
<[email protected]>
<CAA5RZ0uStx0cWUCNPDPhBprAVmcdOX-uuKd9B_W9qLa6Tsp46w@mail.gmail.com>
On Fri, Mar 13, 2026 at 1:21 AM Sami Imseih <[email protected]> wrote:
>
> 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.
Thanks for updating the patch! I like the simplified test.
+SELECT stats_reset AS dbc_reset_ts FROM pg_stat_database_conflicts
WHERE datname = (SELECT current_database()) \gset
I removed the extra space.
I also made some cosmetic indentation fixes in the docs.
The v6 patch is attached.
Unless there are objections, I'll update the catversion and commit it.
Regards,
--
Fujii Masao
Attachments:
[application/octet-stream] v6-0001-Add-stats_reset-column-to-pg_stat_database_confli.patch (6.4K, 2-v6-0001-Add-stats_reset-column-to-pg_stat_database_confli.patch)
download | inline diff:
From eca799afe498e687252dcbf5a0a3015645b9e254 Mon Sep 17 00:00:00 2001
From: Fujii Masao <[email protected]>
Date: Fri, 13 Mar 2026 02:31:50 +0900
Subject: [PATCH v6] 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..6110a68b2aa 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..cbc5c939ea2 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..78c2009ddd1 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.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], [email protected]
Subject: Re: Add missing stats_reset column to pg_stat_database_conflicts view
In-Reply-To: <CAHGQGwF3=y-j5LxHdXtthEsmJYY5U6WtcAsgwbestfCtNvvuaw@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