public inbox for [email protected]
help / color / mirror / Atom feedAdd missing stats_reset column to pg_stat_database_conflicts view
24+ messages / 5 participants
[nested] [flat]
* Add missing stats_reset column to pg_stat_database_conflicts view
@ 2026-03-09 22:11 shihao zhong <[email protected]>
2026-03-09 23:33 ` Re: Add missing stats_reset column to pg_stat_database_conflicts view Sami Imseih <[email protected]>
0 siblings, 1 reply; 24+ messages in thread
From: shihao zhong @ 2026-03-09 22:11 UTC (permalink / raw)
To: pgsql-hackers
Hi hackers,
Currently, pg_stat_database and pg_stat_database_conflicts are
decoupled into two separate views. However, there is an inconsistency:
pg_stat_database_conflicts is missing the stats_reset column.
Implementation wise, both views expose data from PgStat_StatDBEntry
and share the same reset lifecycle when pg_stat_reset() is called.
For now, users monitoring recovery conflicts have to get the reset
time from pg_stat_database. I would like these two views to be purely
decoupled to avoid such confusion.
The attached patch adds pg_stat_get_db_stat_reset_time() to fix this
inconsistency.
Please let me know your thoughts.
Thanks,
Shihao
Attachments:
[application/octet-stream] pg_stat_database_conflict.patch (1.5K, 2-pg_stat_database_conflict.patch)
download | inline diff:
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -1109,7 +1109,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
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -1904,7 +1904,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,
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Add missing stats_reset column to pg_stat_database_conflicts view
2026-03-09 22:11 Add missing stats_reset column to pg_stat_database_conflicts view shihao zhong <[email protected]>
@ 2026-03-09 23:33 ` Sami Imseih <[email protected]>
2026-03-10 01:17 ` Re: Add missing stats_reset column to pg_stat_database_conflicts view Fujii Masao <[email protected]>
0 siblings, 1 reply; 24+ messages in thread
From: Sami Imseih @ 2026-03-09 23:33 UTC (permalink / raw)
To: shihao zhong <[email protected]>; +Cc: pgsql-hackers
> The attached patch adds pg_stat_get_db_stat_reset_time() to fix this
> inconsistency.
>
> Please let me know your thoughts.
This view was introduced 15 years ago, and surprisingly this is the
first complaint about this. I am also not very surprised.
I also noticed that pg_statio_all_sequences does not have a reset
column. We should fix this one also. What do you think?
With regards to your attached patch, it's missing the doc changes.
but also the patch does not apply.
--
Sami Imseih
Amazon Web Services (AWS)
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Add missing stats_reset column to pg_stat_database_conflicts view
2026-03-09 22:11 Add missing stats_reset column to pg_stat_database_conflicts view shihao zhong <[email protected]>
2026-03-09 23:33 ` Re: Add missing stats_reset column to pg_stat_database_conflicts view Sami Imseih <[email protected]>
@ 2026-03-10 01:17 ` Fujii Masao <[email protected]>
2026-03-10 11:32 ` Re: Add missing stats_reset column to pg_stat_database_conflicts view Kirill Reshke <[email protected]>
0 siblings, 1 reply; 24+ messages in thread
From: Fujii Masao @ 2026-03-10 01:17 UTC (permalink / raw)
To: Sami Imseih <[email protected]>; +Cc: shihao zhong <[email protected]>; pgsql-hackers
On Tue, Mar 10, 2026 at 8:33 AM Sami Imseih <[email protected]> wrote:
>
> > The attached patch adds pg_stat_get_db_stat_reset_time() to fix this
> > inconsistency.
> >
> > Please let me know your thoughts.
>
> This view was introduced 15 years ago, and surprisingly this is the
> first complaint about this. I am also not very surprised.
>
> I also noticed that pg_statio_all_sequences does not have a reset
> column. We should fix this one also. What do you think?
+1
Also it might be better to update the docs together so that the description of
pg_stat_reset_single_table_counters mentions sequences in addition to
tables and indexes.
Regards,
--
Fujii Masao
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Add missing stats_reset column to pg_stat_database_conflicts view
2026-03-09 22:11 Add missing stats_reset column to pg_stat_database_conflicts view shihao zhong <[email protected]>
2026-03-09 23:33 ` Re: Add missing stats_reset column to pg_stat_database_conflicts view Sami Imseih <[email protected]>
2026-03-10 01:17 ` Re: Add missing stats_reset column to pg_stat_database_conflicts view Fujii Masao <[email protected]>
@ 2026-03-10 11:32 ` Kirill Reshke <[email protected]>
2026-03-10 18:43 ` Re: Add missing stats_reset column to pg_stat_database_conflicts view shihao zhong <[email protected]>
0 siblings, 1 reply; 24+ messages in thread
From: Kirill Reshke @ 2026-03-10 11:32 UTC (permalink / raw)
To: Fujii Masao <[email protected]>; +Cc: Sami Imseih <[email protected]>; shihao zhong <[email protected]>; pgsql-hackers
On Tue, 10 Mar 2026 at 06:17, Fujii Masao <[email protected]> wrote:
>
> On Tue, Mar 10, 2026 at 8:33 AM Sami Imseih <[email protected]> wrote:
> >
> > > The attached patch adds pg_stat_get_db_stat_reset_time() to fix this
> > > inconsistency.
> > >
> > > Please let me know your thoughts.
> >
> > This view was introduced 15 years ago, and surprisingly this is the
> > first complaint about this. I am also not very surprised.
> >
> > I also noticed that pg_statio_all_sequences does not have a reset
> > column. We should fix this one also. What do you think?
>
> +1
>
> Also it might be better to update the docs together so that the description of
> pg_stat_reset_single_table_counters mentions sequences in addition to
> tables and indexes.
>
> Regards,
>
> --
> Fujii Masao
>
Well, it looks like there are not so many users of this view. Anyway,
+1 on change.
This also need catversion bump
--
Best regards,
Kirill Reshke
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Add missing stats_reset column to pg_stat_database_conflicts view
2026-03-09 22:11 Add missing stats_reset column to pg_stat_database_conflicts view shihao zhong <[email protected]>
2026-03-09 23:33 ` Re: Add missing stats_reset column to pg_stat_database_conflicts view Sami Imseih <[email protected]>
2026-03-10 01:17 ` Re: Add missing stats_reset column to pg_stat_database_conflicts view Fujii Masao <[email protected]>
2026-03-10 11:32 ` Re: Add missing stats_reset column to pg_stat_database_conflicts view Kirill Reshke <[email protected]>
@ 2026-03-10 18:43 ` shihao zhong <[email protected]>
2026-03-10 19:27 ` Re: Add missing stats_reset column to pg_stat_database_conflicts view Sami Imseih <[email protected]>
0 siblings, 1 reply; 24+ messages in thread
From: shihao zhong @ 2026-03-10 18:43 UTC (permalink / raw)
To: Kirill Reshke <[email protected]>; +Cc: Fujii Masao <[email protected]>; Sami Imseih <[email protected]>; pgsql-hackers
The patch has been rebased, and the documentation and catversion
updates have been added.
> 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.
On Tue, Mar 10, 2026 at 7:32 AM Kirill Reshke <[email protected]> wrote:
>
> On Tue, 10 Mar 2026 at 06:17, Fujii Masao <[email protected]> wrote:
> >
> > On Tue, Mar 10, 2026 at 8:33 AM Sami Imseih <[email protected]> wrote:
> > >
> > > > The attached patch adds pg_stat_get_db_stat_reset_time() to fix this
> > > > inconsistency.
> > > >
> > > > Please let me know your thoughts.
> > >
> > > This view was introduced 15 years ago, and surprisingly this is the
> > > first complaint about this. I am also not very surprised.
> > >
> > > I also noticed that pg_statio_all_sequences does not have a reset
> > > column. We should fix this one also. What do you think?
> >
> > +1
> >
> > Also it might be better to update the docs together so that the description of
> > pg_stat_reset_single_table_counters mentions sequences in addition to
> > tables and indexes.
> >
> > Regards,
> >
> > --
> > Fujii Masao
> >
>
> Well, it looks like there are not so many users of this view. Anyway,
> +1 on change.
> This also need catversion bump
>
> --
> Best regards,
> Kirill Reshke
Attachments:
[application/octet-stream] pg_stat_database_conflicts_v2.patch (1.8K, 2-pg_stat_database_conflicts_v2.patch)
download | inline diff:
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index 6878b2d..e8568c7 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -4030,2 +4030,11 @@
</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>
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index a5d3e23..1552a92 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -1165,3 +1165,4 @@
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;
diff --git a/src/include/catalog/catversion.h b/src/include/catalog/catversion.h
index dfd7b14..a459343 100644
--- a/src/include/catalog/catversion.h
+++ b/src/include/catalog/catversion.h
@@ -59,3 +59,3 @@
/* yyyymmddN */
-#define CATALOG_VERSION_NO 202603062
+#define CATALOG_VERSION_NO 202603101
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index 05307a5..831a2c9 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -1924,2 +1924,3 @@
- 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;
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Add missing stats_reset column to pg_stat_database_conflicts view
2026-03-09 22:11 Add missing stats_reset column to pg_stat_database_conflicts view shihao zhong <[email protected]>
2026-03-09 23:33 ` Re: Add missing stats_reset column to pg_stat_database_conflicts view Sami Imseih <[email protected]>
2026-03-10 01:17 ` Re: Add missing stats_reset column to pg_stat_database_conflicts view Fujii Masao <[email protected]>
2026-03-10 11:32 ` Re: Add missing stats_reset column to pg_stat_database_conflicts view Kirill Reshke <[email protected]>
2026-03-10 18:43 ` Re: Add missing stats_reset column to pg_stat_database_conflicts view shihao zhong <[email protected]>
@ 2026-03-10 19:27 ` Sami Imseih <[email protected]>
2026-03-11 14:10 ` Re: Add missing stats_reset column to pg_stat_database_conflicts view shihao zhong <[email protected]>
0 siblings, 1 reply; 24+ messages in thread
From: Sami Imseih @ 2026-03-10 19:27 UTC (permalink / raw)
To: shihao zhong <[email protected]>; +Cc: Kirill Reshke <[email protected]>; Fujii Masao <[email protected]>; pgsql-hackers
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).
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Add missing stats_reset column to pg_stat_database_conflicts view
2026-03-09 22:11 Add missing stats_reset column to pg_stat_database_conflicts view shihao zhong <[email protected]>
2026-03-09 23:33 ` Re: Add missing stats_reset column to pg_stat_database_conflicts view Sami Imseih <[email protected]>
2026-03-10 01:17 ` Re: Add missing stats_reset column to pg_stat_database_conflicts view Fujii Masao <[email protected]>
2026-03-10 11:32 ` Re: Add missing stats_reset column to pg_stat_database_conflicts view Kirill Reshke <[email protected]>
2026-03-10 18:43 ` Re: Add missing stats_reset column to pg_stat_database_conflicts view shihao zhong <[email protected]>
2026-03-10 19:27 ` Re: Add missing stats_reset column to pg_stat_database_conflicts view Sami Imseih <[email protected]>
@ 2026-03-11 14:10 ` shihao zhong <[email protected]>
2026-03-12 04:50 ` Re: Add missing stats_reset column to pg_stat_database_conflicts view Fujii Masao <[email protected]>
0 siblings, 1 reply; 24+ messages in thread
From: shihao zhong @ 2026-03-11 14:10 UTC (permalink / raw)
To: Sami Imseih <[email protected]>; +Cc: Kirill Reshke <[email protected]>; Fujii Masao <[email protected]>; pgsql-hackers
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
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Add missing stats_reset column to pg_stat_database_conflicts view
2026-03-09 22:11 Add missing stats_reset column to pg_stat_database_conflicts view shihao zhong <[email protected]>
2026-03-09 23:33 ` Re: Add missing stats_reset column to pg_stat_database_conflicts view Sami Imseih <[email protected]>
2026-03-10 01:17 ` Re: Add missing stats_reset column to pg_stat_database_conflicts view Fujii Masao <[email protected]>
2026-03-10 11:32 ` Re: Add missing stats_reset column to pg_stat_database_conflicts view Kirill Reshke <[email protected]>
2026-03-10 18:43 ` Re: Add missing stats_reset column to pg_stat_database_conflicts view shihao zhong <[email protected]>
2026-03-10 19:27 ` Re: Add missing stats_reset column to pg_stat_database_conflicts view Sami Imseih <[email protected]>
2026-03-11 14:10 ` Re: Add missing stats_reset column to pg_stat_database_conflicts view shihao zhong <[email protected]>
@ 2026-03-12 04:50 ` Fujii Masao <[email protected]>
2026-03-12 06:04 ` Re: Add missing stats_reset column to pg_stat_database_conflicts view Chao Li <[email protected]>
0 siblings, 1 reply; 24+ messages in thread
From: Fujii Masao @ 2026-03-12 04:50 UTC (permalink / raw)
To: shihao zhong <[email protected]>; +Cc: Sami Imseih <[email protected]>; Kirill Reshke <[email protected]>; pgsql-hackers
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
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Add missing stats_reset column to pg_stat_database_conflicts view
2026-03-09 22:11 Add missing stats_reset column to pg_stat_database_conflicts view shihao zhong <[email protected]>
2026-03-09 23:33 ` Re: Add missing stats_reset column to pg_stat_database_conflicts view Sami Imseih <[email protected]>
2026-03-10 01:17 ` Re: Add missing stats_reset column to pg_stat_database_conflicts view Fujii Masao <[email protected]>
2026-03-10 11:32 ` Re: Add missing stats_reset column to pg_stat_database_conflicts view Kirill Reshke <[email protected]>
2026-03-10 18:43 ` Re: Add missing stats_reset column to pg_stat_database_conflicts view shihao zhong <[email protected]>
2026-03-10 19:27 ` Re: Add missing stats_reset column to pg_stat_database_conflicts view Sami Imseih <[email protected]>
2026-03-11 14:10 ` Re: Add missing stats_reset column to pg_stat_database_conflicts view shihao zhong <[email protected]>
2026-03-12 04:50 ` Re: Add missing stats_reset column to pg_stat_database_conflicts view Fujii Masao <[email protected]>
@ 2026-03-12 06:04 ` Chao Li <[email protected]>
2026-03-12 16:21 ` Re: Add missing stats_reset column to pg_stat_database_conflicts view Sami Imseih <[email protected]>
0 siblings, 1 reply; 24+ messages in thread
From: Chao Li @ 2026-03-12 06:04 UTC (permalink / raw)
To: Fujii Masao <[email protected]>; +Cc: shihao zhong <[email protected]>; Sami Imseih <[email protected]>; Kirill Reshke <[email protected]>; pgsql-hackers
> On Mar 12, 2026, at 12:50, Fujii Masao <[email protected]> wrote:
>
> 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
> <v4-0001-Add-stats_reset-column-to-pg_stat_database_confli.patch>
V4 overall LGTM. A couple of nitpicks:
1 - stats.sql
```
+WHERE D.datname = (SELECT current_database()) AND D.datname = DC.datname \gset
+WHERE D.datname = (SELECT current_database()) AND D.datname = DC.datname;
```
(SELECT current_database()) can be just current_database(). I tried to fix this in my local, and the fix works for me.
2 - stats.sql
```
+-- 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
```
I think “starts” should be “start”, because there are two stats_reset fields from two tables in this context.
Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Add missing stats_reset column to pg_stat_database_conflicts view
2026-03-09 22:11 Add missing stats_reset column to pg_stat_database_conflicts view shihao zhong <[email protected]>
2026-03-09 23:33 ` Re: Add missing stats_reset column to pg_stat_database_conflicts view Sami Imseih <[email protected]>
2026-03-10 01:17 ` Re: Add missing stats_reset column to pg_stat_database_conflicts view Fujii Masao <[email protected]>
2026-03-10 11:32 ` Re: Add missing stats_reset column to pg_stat_database_conflicts view Kirill Reshke <[email protected]>
2026-03-10 18:43 ` Re: Add missing stats_reset column to pg_stat_database_conflicts view shihao zhong <[email protected]>
2026-03-10 19:27 ` Re: Add missing stats_reset column to pg_stat_database_conflicts view Sami Imseih <[email protected]>
2026-03-11 14:10 ` Re: Add missing stats_reset column to pg_stat_database_conflicts view shihao zhong <[email protected]>
2026-03-12 04:50 ` Re: Add missing stats_reset column to pg_stat_database_conflicts view Fujii Masao <[email protected]>
2026-03-12 06:04 ` Re: Add missing stats_reset column to pg_stat_database_conflicts view Chao Li <[email protected]>
@ 2026-03-12 16:21 ` Sami Imseih <[email protected]>
2026-03-12 18:09 ` Re: Add missing stats_reset column to pg_stat_database_conflicts view Fujii Masao <[email protected]>
0 siblings, 1 reply; 24+ messages in thread
From: Sami Imseih @ 2026-03-12 16:21 UTC (permalink / raw)
To: Chao Li <[email protected]>; +Cc: Fujii Masao <[email protected]>; shihao zhong <[email protected]>; Kirill Reshke <[email protected]>; pgsql-hackers
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
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Add missing stats_reset column to pg_stat_database_conflicts view
2026-03-09 22:11 Add missing stats_reset column to pg_stat_database_conflicts view shihao zhong <[email protected]>
2026-03-09 23:33 ` Re: Add missing stats_reset column to pg_stat_database_conflicts view Sami Imseih <[email protected]>
2026-03-10 01:17 ` Re: Add missing stats_reset column to pg_stat_database_conflicts view Fujii Masao <[email protected]>
2026-03-10 11:32 ` Re: Add missing stats_reset column to pg_stat_database_conflicts view Kirill Reshke <[email protected]>
2026-03-10 18:43 ` Re: Add missing stats_reset column to pg_stat_database_conflicts view shihao zhong <[email protected]>
2026-03-10 19:27 ` Re: Add missing stats_reset column to pg_stat_database_conflicts view Sami Imseih <[email protected]>
2026-03-11 14:10 ` Re: Add missing stats_reset column to pg_stat_database_conflicts view shihao zhong <[email protected]>
2026-03-12 04:50 ` Re: Add missing stats_reset column to pg_stat_database_conflicts view Fujii Masao <[email protected]>
2026-03-12 06:04 ` Re: Add missing stats_reset column to pg_stat_database_conflicts view Chao Li <[email protected]>
2026-03-12 16:21 ` Re: Add missing stats_reset column to pg_stat_database_conflicts view Sami Imseih <[email protected]>
@ 2026-03-12 18:09 ` Fujii Masao <[email protected]>
2026-03-12 20:39 ` Re: Add missing stats_reset column to pg_stat_database_conflicts view Sami Imseih <[email protected]>
0 siblings, 1 reply; 24+ messages in thread
From: Fujii Masao @ 2026-03-12 18:09 UTC (permalink / raw)
To: Sami Imseih <[email protected]>; +Cc: Chao Li <[email protected]>; shihao zhong <[email protected]>; Kirill Reshke <[email protected]>; pgsql-hackers
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
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Add missing stats_reset column to pg_stat_database_conflicts view
2026-03-09 22:11 Add missing stats_reset column to pg_stat_database_conflicts view shihao zhong <[email protected]>
2026-03-09 23:33 ` Re: Add missing stats_reset column to pg_stat_database_conflicts view Sami Imseih <[email protected]>
2026-03-10 01:17 ` Re: Add missing stats_reset column to pg_stat_database_conflicts view Fujii Masao <[email protected]>
2026-03-10 11:32 ` Re: Add missing stats_reset column to pg_stat_database_conflicts view Kirill Reshke <[email protected]>
2026-03-10 18:43 ` Re: Add missing stats_reset column to pg_stat_database_conflicts view shihao zhong <[email protected]>
2026-03-10 19:27 ` Re: Add missing stats_reset column to pg_stat_database_conflicts view Sami Imseih <[email protected]>
2026-03-11 14:10 ` Re: Add missing stats_reset column to pg_stat_database_conflicts view shihao zhong <[email protected]>
2026-03-12 04:50 ` Re: Add missing stats_reset column to pg_stat_database_conflicts view Fujii Masao <[email protected]>
2026-03-12 06:04 ` Re: Add missing stats_reset column to pg_stat_database_conflicts view Chao Li <[email protected]>
2026-03-12 16:21 ` Re: Add missing stats_reset column to pg_stat_database_conflicts view Sami Imseih <[email protected]>
2026-03-12 18:09 ` Re: Add missing stats_reset column to pg_stat_database_conflicts view Fujii Masao <[email protected]>
@ 2026-03-12 20:39 ` Sami Imseih <[email protected]>
2026-03-13 13:25 ` Re: Add missing stats_reset column to pg_stat_database_conflicts view Fujii Masao <[email protected]>
0 siblings, 1 reply; 24+ messages in thread
From: Sami Imseih @ 2026-03-12 20:39 UTC (permalink / raw)
To: Fujii Masao <[email protected]>; +Cc: Chao Li <[email protected]>; shihao zhong <[email protected]>; Kirill Reshke <[email protected]>; pgsql-hackers
> I also made some cosmetic indentation fixes in the docs.
> The v6 patch is attached.
Just one more minor comment fix I noticed.
"Test that reset works for pg_stat_database"
to:
"Test that reset works for pg_stat_database and pg_stat_database_conflicts"
--
Sami
Attachments:
[application/octet-stream] v7-0001-Add-stats_reset-column-to-pg_stat_database_confli.patch (6.8K, 2-v7-0001-Add-stats_reset-column-to-pg_stat_database_confli.patch)
download | inline diff:
From c2cbb9289f7b50c8a1b11036d310152e20f2aa80 Mon Sep 17 00:00:00 2001
From: Fujii Masao <[email protected]>
Date: Fri, 13 Mar 2026 02:31:50 +0900
Subject: [PATCH v7 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 | 19 +++++++++++++++++--
src/test/regress/sql/stats.sql | 9 +++++++--
6 files changed, 38 insertions(+), 7 deletions(-)
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index 3bf6d6d54df..9c5c6dc490f 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..981d7c90822 100644
--- a/src/test/regress/expected/stats.out
+++ b/src/test/regress/expected/stats.out
@@ -1131,8 +1131,10 @@ 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 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..70af96f739f 100644
--- a/src/test/regress/sql/stats.sql
+++ b/src/test/regress/sql/stats.sql
@@ -523,13 +523,18 @@ 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 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
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Add missing stats_reset column to pg_stat_database_conflicts view
2026-03-09 22:11 Add missing stats_reset column to pg_stat_database_conflicts view shihao zhong <[email protected]>
2026-03-09 23:33 ` Re: Add missing stats_reset column to pg_stat_database_conflicts view Sami Imseih <[email protected]>
2026-03-10 01:17 ` Re: Add missing stats_reset column to pg_stat_database_conflicts view Fujii Masao <[email protected]>
2026-03-10 11:32 ` Re: Add missing stats_reset column to pg_stat_database_conflicts view Kirill Reshke <[email protected]>
2026-03-10 18:43 ` Re: Add missing stats_reset column to pg_stat_database_conflicts view shihao zhong <[email protected]>
2026-03-10 19:27 ` Re: Add missing stats_reset column to pg_stat_database_conflicts view Sami Imseih <[email protected]>
2026-03-11 14:10 ` Re: Add missing stats_reset column to pg_stat_database_conflicts view shihao zhong <[email protected]>
2026-03-12 04:50 ` Re: Add missing stats_reset column to pg_stat_database_conflicts view Fujii Masao <[email protected]>
2026-03-12 06:04 ` Re: Add missing stats_reset column to pg_stat_database_conflicts view Chao Li <[email protected]>
2026-03-12 16:21 ` Re: Add missing stats_reset column to pg_stat_database_conflicts view Sami Imseih <[email protected]>
2026-03-12 18:09 ` Re: Add missing stats_reset column to pg_stat_database_conflicts view Fujii Masao <[email protected]>
2026-03-12 20:39 ` Re: Add missing stats_reset column to pg_stat_database_conflicts view Sami Imseih <[email protected]>
@ 2026-03-13 13:25 ` Fujii Masao <[email protected]>
2026-03-16 02:10 ` Re: Add missing stats_reset column to pg_stat_database_conflicts view Chao Li <[email protected]>
0 siblings, 1 reply; 24+ messages in thread
From: Fujii Masao @ 2026-03-13 13:25 UTC (permalink / raw)
To: Chao Li <[email protected]>; +Cc: Sami Imseih <[email protected]>; shihao zhong <[email protected]>; Kirill Reshke <[email protected]>; pgsql-hackers
On Fri, Mar 13, 2026 at 5:47 PM Chao Li <[email protected]> wrote:
>
>
>
> > On Mar 13, 2026, at 04:39, Sami Imseih <[email protected]> wrote:
> >
> >> I also made some cosmetic indentation fixes in the docs.
> >> The v6 patch is attached.
> >
> > Just one more minor comment fix I noticed.
> >
> > "Test that reset works for pg_stat_database"
> >
> > to:
> >
> > "Test that reset works for pg_stat_database and pg_stat_database_conflicts"
> >
> > --
> > Sami
> > <v7-0001-Add-stats_reset-column-to-pg_stat_database_confli.patch>
I've pushed the patch. Thanks!
> I still saw (SELECT current_database()) in v7. Does it have any special meaning than just current_database()?
No. I think it was used by the patch simply because the existing tests
for stats reset already use it.
Regards,
--
Fujii Masao
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Add missing stats_reset column to pg_stat_database_conflicts view
2026-03-09 22:11 Add missing stats_reset column to pg_stat_database_conflicts view shihao zhong <[email protected]>
2026-03-09 23:33 ` Re: Add missing stats_reset column to pg_stat_database_conflicts view Sami Imseih <[email protected]>
2026-03-10 01:17 ` Re: Add missing stats_reset column to pg_stat_database_conflicts view Fujii Masao <[email protected]>
2026-03-10 11:32 ` Re: Add missing stats_reset column to pg_stat_database_conflicts view Kirill Reshke <[email protected]>
2026-03-10 18:43 ` Re: Add missing stats_reset column to pg_stat_database_conflicts view shihao zhong <[email protected]>
2026-03-10 19:27 ` Re: Add missing stats_reset column to pg_stat_database_conflicts view Sami Imseih <[email protected]>
2026-03-11 14:10 ` Re: Add missing stats_reset column to pg_stat_database_conflicts view shihao zhong <[email protected]>
2026-03-12 04:50 ` Re: Add missing stats_reset column to pg_stat_database_conflicts view Fujii Masao <[email protected]>
2026-03-12 06:04 ` Re: Add missing stats_reset column to pg_stat_database_conflicts view Chao Li <[email protected]>
2026-03-12 16:21 ` Re: Add missing stats_reset column to pg_stat_database_conflicts view Sami Imseih <[email protected]>
2026-03-12 18:09 ` Re: Add missing stats_reset column to pg_stat_database_conflicts view Fujii Masao <[email protected]>
2026-03-12 20:39 ` Re: Add missing stats_reset column to pg_stat_database_conflicts view Sami Imseih <[email protected]>
2026-03-13 13:25 ` Re: Add missing stats_reset column to pg_stat_database_conflicts view Fujii Masao <[email protected]>
@ 2026-03-16 02:10 ` Chao Li <[email protected]>
2026-04-06 04:22 ` Re: Add missing stats_reset column to pg_stat_database_conflicts view Fujii Masao <[email protected]>
0 siblings, 1 reply; 24+ messages in thread
From: Chao Li @ 2026-03-16 02:10 UTC (permalink / raw)
To: Fujii Masao <[email protected]>; +Cc: Sami Imseih <[email protected]>; shihao zhong <[email protected]>; Kirill Reshke <[email protected]>; pgsql-hackers
> On Mar 13, 2026, at 21:25, Fujii Masao <[email protected]> wrote:
>
> On Fri, Mar 13, 2026 at 5:47 PM Chao Li <[email protected]> wrote:
>>
>>
>>
>>> On Mar 13, 2026, at 04:39, Sami Imseih <[email protected]> wrote:
>>>
>>>> I also made some cosmetic indentation fixes in the docs.
>>>> The v6 patch is attached.
>>>
>>> Just one more minor comment fix I noticed.
>>>
>>> "Test that reset works for pg_stat_database"
>>>
>>> to:
>>>
>>> "Test that reset works for pg_stat_database and pg_stat_database_conflicts"
>>>
>>> --
>>> Sami
>>> <v7-0001-Add-stats_reset-column-to-pg_stat_database_confli.patch>
>
> I've pushed the patch. Thanks!
>
>
>> I still saw (SELECT current_database()) in v7. Does it have any special meaning than just current_database()?
>
> No. I think it was used by the patch simply because the existing tests
> for stats reset already use it.
>
> Regards,
>
> --
> Fujii Masao
Hi Fujii-san,
If the only reason for using (SELECT current_database()) is to follow the existing code, would it make sense to update the code to use current_database() instead?
Looking at the execution plans below:
```
evantest=# explain select (select current_database());
QUERY PLAN
---------------------------------------------------
Result (cost=0.01..0.02 rows=1 width=64)
InitPlan expr_1
-> Result (cost=0.00..0.01 rows=1 width=64)
(3 rows)
evantest=# explain select current_database();
QUERY PLAN
-------------------------------------------
Result (cost=0.00..0.01 rows=1 width=64)
(1 row)
```
The nested SELECT introduces an unnecessary InitPlan, which adds extra work to execute.
Another point is that the nested SELECT may mislead users or code readers into thinking it is required. For example, I often look at regression test scripts to check how SQL statements are used in practice, and I consider them an important source of reference. Because of that, I think it's better for the test scripts to demonstrate the simplest and most appropriate usage when possible.
I searched through the test scripts, and it looks like only stats.sql uses this pattern. So I attached a small patch that replaces all (SELECT current_database()) with current_database().
Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/
Attachments:
[application/octet-stream] v1-0001-regress-remove-unnecessary-subquery-around-curren.patch (4.9K, 2-v1-0001-regress-remove-unnecessary-subquery-around-curren.patch)
download | inline diff:
From 52891896d06c9ffba01dde369d47cf02f441185f Mon Sep 17 00:00:00 2001
From: "Chao Li (Evan)" <[email protected]>
Date: Mon, 16 Mar 2026 10:01:11 +0800
Subject: [PATCH v1] regress: remove unnecessary subquery around
current_database()
Replace uses of (SELECT current_database()) with current_database()
in stats.sql and the corresponding expected output.
Author: Chao Li <[email protected]>
---
src/test/regress/expected/stats.out | 12 ++++++------
src/test/regress/sql/stats.sql | 12 ++++++------
2 files changed, 12 insertions(+), 12 deletions(-)
diff --git a/src/test/regress/expected/stats.out b/src/test/regress/expected/stats.out
index 981d7c90822..0917d2c1266 100644
--- a/src/test/regress/expected/stats.out
+++ b/src/test/regress/expected/stats.out
@@ -931,7 +931,7 @@ SELECT (n_tup_ins + n_tup_upd) > 0 AS has_data FROM pg_stat_all_tables
-- Test that various stats views are being properly populated
-----
-- Test that sessions is incremented when a new session is started in pg_stat_database
-SELECT sessions AS db_stat_sessions FROM pg_stat_database WHERE datname = (SELECT current_database()) \gset
+SELECT sessions AS db_stat_sessions FROM pg_stat_database WHERE datname = current_database() \gset
\c
SELECT pg_stat_force_next_flush();
pg_stat_force_next_flush
@@ -939,7 +939,7 @@ SELECT pg_stat_force_next_flush();
(1 row)
-SELECT sessions > :db_stat_sessions FROM pg_stat_database WHERE datname = (SELECT current_database());
+SELECT sessions > :db_stat_sessions FROM pg_stat_database WHERE datname = current_database();
?column?
----------
t
@@ -1141,8 +1141,8 @@ 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 stats_reset AS db_reset_ts FROM pg_stat_database WHERE datname = current_database() \gset
+SELECT stats_reset AS dbc_reset_ts FROM pg_stat_database_conflicts WHERE datname = current_database() \gset
SELECT :'db_reset_ts'::timestamptz = :'dbc_reset_ts'::timestamptz;
?column?
----------
@@ -1155,13 +1155,13 @@ SELECT pg_stat_reset();
(1 row)
-SELECT stats_reset > :'db_reset_ts'::timestamptz FROM pg_stat_database WHERE datname = (SELECT current_database());
+SELECT stats_reset > :'db_reset_ts'::timestamptz FROM pg_stat_database WHERE datname = current_database();
?column?
----------
t
(1 row)
-SELECT stats_reset > :'dbc_reset_ts'::timestamptz FROM pg_stat_database_conflicts WHERE datname = (SELECT current_database());
+SELECT stats_reset > :'dbc_reset_ts'::timestamptz FROM pg_stat_database_conflicts WHERE datname = current_database();
?column?
----------
t
diff --git a/src/test/regress/sql/stats.sql b/src/test/regress/sql/stats.sql
index 70af96f739f..f782f5f80b5 100644
--- a/src/test/regress/sql/stats.sql
+++ b/src/test/regress/sql/stats.sql
@@ -432,10 +432,10 @@ SELECT (n_tup_ins + n_tup_upd) > 0 AS has_data FROM pg_stat_all_tables
-----
-- Test that sessions is incremented when a new session is started in pg_stat_database
-SELECT sessions AS db_stat_sessions FROM pg_stat_database WHERE datname = (SELECT current_database()) \gset
+SELECT sessions AS db_stat_sessions FROM pg_stat_database WHERE datname = current_database() \gset
\c
SELECT pg_stat_force_next_flush();
-SELECT sessions > :db_stat_sessions FROM pg_stat_database WHERE datname = (SELECT current_database());
+SELECT sessions > :db_stat_sessions FROM pg_stat_database WHERE datname = current_database();
-- Test pg_stat_checkpointer checkpointer-related stats, together with pg_stat_wal
SELECT num_requested AS rqst_ckpts_before FROM pg_stat_checkpointer \gset
@@ -529,12 +529,12 @@ SELECT pg_stat_reset_shared('unknown');
-- 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 stats_reset AS db_reset_ts FROM pg_stat_database WHERE datname = current_database() \gset
+SELECT stats_reset AS dbc_reset_ts FROM pg_stat_database_conflicts WHERE datname = 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());
+SELECT stats_reset > :'db_reset_ts'::timestamptz FROM pg_stat_database WHERE datname = current_database();
+SELECT stats_reset > :'dbc_reset_ts'::timestamptz FROM pg_stat_database_conflicts WHERE datname = current_database();
----
--
2.50.1 (Apple Git-155)
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Add missing stats_reset column to pg_stat_database_conflicts view
2026-03-09 22:11 Add missing stats_reset column to pg_stat_database_conflicts view shihao zhong <[email protected]>
2026-03-09 23:33 ` Re: Add missing stats_reset column to pg_stat_database_conflicts view Sami Imseih <[email protected]>
2026-03-10 01:17 ` Re: Add missing stats_reset column to pg_stat_database_conflicts view Fujii Masao <[email protected]>
2026-03-10 11:32 ` Re: Add missing stats_reset column to pg_stat_database_conflicts view Kirill Reshke <[email protected]>
2026-03-10 18:43 ` Re: Add missing stats_reset column to pg_stat_database_conflicts view shihao zhong <[email protected]>
2026-03-10 19:27 ` Re: Add missing stats_reset column to pg_stat_database_conflicts view Sami Imseih <[email protected]>
2026-03-11 14:10 ` Re: Add missing stats_reset column to pg_stat_database_conflicts view shihao zhong <[email protected]>
2026-03-12 04:50 ` Re: Add missing stats_reset column to pg_stat_database_conflicts view Fujii Masao <[email protected]>
2026-03-12 06:04 ` Re: Add missing stats_reset column to pg_stat_database_conflicts view Chao Li <[email protected]>
2026-03-12 16:21 ` Re: Add missing stats_reset column to pg_stat_database_conflicts view Sami Imseih <[email protected]>
2026-03-12 18:09 ` Re: Add missing stats_reset column to pg_stat_database_conflicts view Fujii Masao <[email protected]>
2026-03-12 20:39 ` Re: Add missing stats_reset column to pg_stat_database_conflicts view Sami Imseih <[email protected]>
2026-03-13 13:25 ` Re: Add missing stats_reset column to pg_stat_database_conflicts view Fujii Masao <[email protected]>
2026-03-16 02:10 ` Re: Add missing stats_reset column to pg_stat_database_conflicts view Chao Li <[email protected]>
@ 2026-04-06 04:22 ` Fujii Masao <[email protected]>
2026-04-07 03:58 ` Re: Add missing stats_reset column to pg_stat_database_conflicts view Chao Li <[email protected]>
0 siblings, 1 reply; 24+ messages in thread
From: Fujii Masao @ 2026-04-06 04:22 UTC (permalink / raw)
To: Chao Li <[email protected]>; +Cc: Sami Imseih <[email protected]>; shihao zhong <[email protected]>; Kirill Reshke <[email protected]>; pgsql-hackers
On Mon, Mar 16, 2026 at 11:11 AM Chao Li <[email protected]> wrote:
> I searched through the test scripts, and it looks like only stats.sql uses this pattern. So I attached a small patch that replaces all (SELECT current_database()) with current_database().
OK, I've pushed the patch. Thanks!
Regards,
--
Fujii Masao
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Add missing stats_reset column to pg_stat_database_conflicts view
2026-03-09 22:11 Add missing stats_reset column to pg_stat_database_conflicts view shihao zhong <[email protected]>
2026-03-09 23:33 ` Re: Add missing stats_reset column to pg_stat_database_conflicts view Sami Imseih <[email protected]>
2026-03-10 01:17 ` Re: Add missing stats_reset column to pg_stat_database_conflicts view Fujii Masao <[email protected]>
2026-03-10 11:32 ` Re: Add missing stats_reset column to pg_stat_database_conflicts view Kirill Reshke <[email protected]>
2026-03-10 18:43 ` Re: Add missing stats_reset column to pg_stat_database_conflicts view shihao zhong <[email protected]>
2026-03-10 19:27 ` Re: Add missing stats_reset column to pg_stat_database_conflicts view Sami Imseih <[email protected]>
2026-03-11 14:10 ` Re: Add missing stats_reset column to pg_stat_database_conflicts view shihao zhong <[email protected]>
2026-03-12 04:50 ` Re: Add missing stats_reset column to pg_stat_database_conflicts view Fujii Masao <[email protected]>
2026-03-12 06:04 ` Re: Add missing stats_reset column to pg_stat_database_conflicts view Chao Li <[email protected]>
2026-03-12 16:21 ` Re: Add missing stats_reset column to pg_stat_database_conflicts view Sami Imseih <[email protected]>
2026-03-12 18:09 ` Re: Add missing stats_reset column to pg_stat_database_conflicts view Fujii Masao <[email protected]>
2026-03-12 20:39 ` Re: Add missing stats_reset column to pg_stat_database_conflicts view Sami Imseih <[email protected]>
2026-03-13 13:25 ` Re: Add missing stats_reset column to pg_stat_database_conflicts view Fujii Masao <[email protected]>
2026-03-16 02:10 ` Re: Add missing stats_reset column to pg_stat_database_conflicts view Chao Li <[email protected]>
2026-04-06 04:22 ` Re: Add missing stats_reset column to pg_stat_database_conflicts view Fujii Masao <[email protected]>
@ 2026-04-07 03:58 ` Chao Li <[email protected]>
0 siblings, 0 replies; 24+ messages in thread
From: Chao Li @ 2026-04-07 03:58 UTC (permalink / raw)
To: Fujii Masao <[email protected]>; +Cc: Sami Imseih <[email protected]>; shihao zhong <[email protected]>; Kirill Reshke <[email protected]>; pgsql-hackers
> On Apr 6, 2026, at 12:22, Fujii Masao <[email protected]> wrote:
>
> On Mon, Mar 16, 2026 at 11:11 AM Chao Li <[email protected]> wrote:
>> I searched through the test scripts, and it looks like only stats.sql uses this pattern. So I attached a small patch that replaces all (SELECT current_database()) with current_database().
>
> OK, I've pushed the patch. Thanks!
>
> Regards,
>
> --
> Fujii Masao
Thank you very much for pushing.
Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/
^ permalink raw reply [nested|flat] 24+ messages in thread
* [PATCH] Add missing stats_reset column to pg_statio_all_sequences view
@ 2026-03-11 18:04 shihao zhong <[email protected]>
0 siblings, 0 replies; 24+ messages in thread
From: shihao zhong @ 2026-03-11 18:04 UTC (permalink / raw)
To: pgsql-hackers
Hi hackers,
Currently, the pg_statio_all_* views have a stats_reset column, but
pg_statio_all_sequences is missing it, as Sami pointed out in [1].
This patch adds the stats_reset column and updates the documentation
to ensure it remains in sync with the other views.
[1] [CAA5RZ0vSjCEAuKXhxacnp0ftrYkf9QRcyPK-AvTi_otFVDWphA@mail.gmail.com]
Thanks,
Shihao
Attachments:
[application/octet-stream] pg_statio_all_sequences.patch (5.2K, 2-pg_statio_all_sequences.patch)
download | inline diff:
From e0b18c8674a19133bd9133b8f5a523bdc9280589 Mon Sep 17 00:00:00 2001
From: shihao zhong <[email protected]>
Date: Wed, 11 Mar 2026 16:55:22 +0000
Subject: [PATCH] Add stats_reset column to pg_statio_all_sequences view
This commit adds the stats_reset column to pg_statio_all_sequences,
allowing users to see when the reset was last performed. This brings
consistency with pg_statio_all_tables and other statistics views.
Includes:
- system_views.sql: View definition update
- monitoring.sgml: Documentation update and indentation fix
- catversion.h: Mandatory catalog version bump
- Regression tests: Updated rules.out and stats.out, and added stats.sql test case
---
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 | 8 ++++++++
src/test/regress/sql/stats.sql | 4 ++++
6 files changed, 30 insertions(+), 5 deletions(-)
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index 763f6731dee..22100d12955 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -4908,6 +4908,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 3361030d34c..0f1e61210cf 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..ff91cff866f 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 202603111
#endif
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index 71d7262049e..9ed0a1756c0 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -2451,7 +2451,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");
@@ -2493,7 +2494,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,
@@ -2524,7 +2526,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 0d634c3af42..8b19602c503 100644
--- a/src/test/regress/expected/stats.out
+++ b/src/test/regress/expected/stats.out
@@ -1924,4 +1924,12 @@ SELECT stats_reset IS NOT NULL AS has_stats_reset
t
(1 row)
+-- Test that the stats_reset column in pg_statio_all_sequences is correctly maintained
+SELECT stats_reset IS NOT NULL AS has_stats_reset
+ FROM pg_statio_all_sequences LIMIT 1;
+ 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 e5fb90cf0c3..0d1fac95e9f 100644
--- a/src/test/regress/sql/stats.sql
+++ b/src/test/regress/sql/stats.sql
@@ -949,4 +949,8 @@ SELECT pg_stat_reset();
SELECT stats_reset IS NOT NULL AS has_stats_reset
FROM pg_stat_database_conflicts WHERE datname = current_database();
+-- Test that the stats_reset column in pg_statio_all_sequences is correctly maintained
+SELECT stats_reset IS NOT NULL AS has_stats_reset
+ FROM pg_statio_all_sequences LIMIT 1;
+
-- End of Stats Test
--
2.53.0.880.g73c4285caa-goog
^ permalink raw reply [nested|flat] 24+ messages in thread
* Add missing stats_reset column to pg_statio_all_sequences view
@ 2026-03-12 22:06 Sami Imseih <[email protected]>
2026-03-12 22:44 ` Re: Add missing stats_reset column to pg_statio_all_sequences view Sami Imseih <[email protected]>
0 siblings, 1 reply; 24+ messages in thread
From: Sami Imseih @ 2026-03-12 22:06 UTC (permalink / raw)
To: pgsql-hackers; +Cc: Fujii Masao <[email protected]>
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
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Add missing stats_reset column to pg_statio_all_sequences view
2026-03-12 22:06 Add missing stats_reset column to pg_statio_all_sequences view Sami Imseih <[email protected]>
@ 2026-03-12 22:44 ` Sami Imseih <[email protected]>
2026-03-13 01:39 ` Re: Add missing stats_reset column to pg_statio_all_sequences view Fujii Masao <[email protected]>
0 siblings, 1 reply; 24+ messages in thread
From: Sami Imseih @ 2026-03-12 22:44 UTC (permalink / raw)
To: pgsql-hackers; +Cc: Fujii Masao <[email protected]>; shihao zhong <[email protected]>
After Creating this thread and the CF entry for it [1], I realized Shihao also
created [2] yesterday. Sorry about that.
We can stick with 6582 and Shihao can also get author credit.
[1] [https://commitfest.postgresql.org/patch/6582/]
[2] [https://commitfest.postgresql.org/patch/6577/]
--
Sami Imseih
Amazon Web Services (AWS)
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Add missing stats_reset column to pg_statio_all_sequences view
2026-03-12 22:06 Add missing stats_reset column to pg_statio_all_sequences view Sami Imseih <[email protected]>
2026-03-12 22:44 ` Re: Add missing stats_reset column to pg_statio_all_sequences view Sami Imseih <[email protected]>
@ 2026-03-13 01:39 ` Fujii Masao <[email protected]>
2026-03-13 02:52 ` Re: Add missing stats_reset column to pg_statio_all_sequences view Sami Imseih <[email protected]>
0 siblings, 1 reply; 24+ messages in thread
From: Fujii Masao @ 2026-03-13 01:39 UTC (permalink / raw)
To: Sami Imseih <[email protected]>; +Cc: pgsql-hackers; shihao zhong <[email protected]>
On Fri, Mar 13, 2026 at 7:44 AM Sami Imseih <[email protected]> wrote:
>
> After Creating this thread and the CF entry for it [1], I realized Shihao also
> created [2] yesterday. Sorry about that.
>
> We can stick with 6582 and Shihao can also get author credit.
Thanks to both Shihao and Sami for proposing patches!
Since the two patches are almost the same, I reviewed Sami's patch.
It looks good to me overall. One comment: for the tests added to stats.sql,
it would be better to verify that the stats of the specified sequence are
actually reset by using pg_stat_reset_single_table_counters().
Also, the documentation for pg_stat_reset_single_table_counters() should
mention that it can reset sequence statistics as well.
Regards,
--
Fujii Masao
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Add missing stats_reset column to pg_statio_all_sequences view
2026-03-12 22:06 Add missing stats_reset column to pg_statio_all_sequences view Sami Imseih <[email protected]>
2026-03-12 22:44 ` Re: Add missing stats_reset column to pg_statio_all_sequences view Sami Imseih <[email protected]>
2026-03-13 01:39 ` Re: Add missing stats_reset column to pg_statio_all_sequences view Fujii Masao <[email protected]>
@ 2026-03-13 02:52 ` Sami Imseih <[email protected]>
2026-03-16 08:27 ` Re: Add missing stats_reset column to pg_statio_all_sequences view Fujii Masao <[email protected]>
0 siblings, 1 reply; 24+ messages in thread
From: Sami Imseih @ 2026-03-13 02:52 UTC (permalink / raw)
To: Fujii Masao <[email protected]>; +Cc: pgsql-hackers; shihao zhong <[email protected]>
> it would be better to verify that the stats of the specified sequence are
> actually reset by using pg_stat_reset_single_table_counters().
>
> Also, the documentation for pg_stat_reset_single_table_counters() should
> mention that it can reset sequence statistics as well.
good points.
For the documentation of pg_stat_reset_single_table_counters(), I also
mentioned materialized views for completeness.
--
Sami Imseih
Amazon Web Services (AWS)
Attachments:
[application/octet-stream] v2-0001-Add-stats_reset-column-to-pg_statio_all_sequences.patch (6.9K, 2-v2-0001-Add-stats_reset-column-to-pg_statio_all_sequences.patch)
download | inline diff:
From 2beb2ce74626833cc2f5e45a13fa5883102b6f07 Mon Sep 17 00:00:00 2001
From: "Sami Imseih (AWS)"
<[email protected]>
Date: Thu, 12 Mar 2026 21:47:56 +0000
Subject: [PATCH v2 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.
While at it, update the pg_stat_reset_single_table_counters
documentation to mention that it also works for sequences and
materialized views.
Catalog version bumped.
---
doc/src/sgml/monitoring.sgml | 10 ++++++++++
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, 61 insertions(+), 5 deletions(-)
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index 3bf6d6d54df..7be85232d43 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>
@@ -5397,6 +5406,7 @@ description | Waiting for a newly initialized WAL file to reach durable storage
<para>
Resets statistics for a single table or index in the current database
or shared across all databases in the cluster to zero.
+ This also works for sequences and materialized views.
</para>
<para>
This function is restricted to superusers by default, but other users
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..19599095100 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_single_table_counters('test_seq1'::regclass);
+ pg_stat_reset_single_table_counters
+-------------------------------------
+
+(1 row)
+
+SELECT stats_reset AS seq_reset_ts FROM pg_statio_all_sequences WHERE relname ='test_seq1' \gset
+SELECT pg_stat_reset_single_table_counters('test_seq1'::regclass);
+ pg_stat_reset_single_table_counters
+-------------------------------------
+
+(1 row)
+
+SELECT stats_reset > :'seq_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..d46e2c5fbef 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_single_table_counters('test_seq1'::regclass);
+SELECT stats_reset AS seq_reset_ts FROM pg_statio_all_sequences WHERE relname ='test_seq1' \gset
+SELECT pg_stat_reset_single_table_counters('test_seq1'::regclass);
+SELECT stats_reset > :'seq_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
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Add missing stats_reset column to pg_statio_all_sequences view
2026-03-12 22:06 Add missing stats_reset column to pg_statio_all_sequences view Sami Imseih <[email protected]>
2026-03-12 22:44 ` Re: Add missing stats_reset column to pg_statio_all_sequences view Sami Imseih <[email protected]>
2026-03-13 01:39 ` Re: Add missing stats_reset column to pg_statio_all_sequences view Fujii Masao <[email protected]>
2026-03-13 02:52 ` Re: Add missing stats_reset column to pg_statio_all_sequences view Sami Imseih <[email protected]>
@ 2026-03-16 08:27 ` Fujii Masao <[email protected]>
2026-03-16 10:01 ` Re: Add missing stats_reset column to pg_statio_all_sequences view Fujii Masao <[email protected]>
0 siblings, 1 reply; 24+ messages in thread
From: Fujii Masao @ 2026-03-16 08:27 UTC (permalink / raw)
To: Sami Imseih <[email protected]>; +Cc: pgsql-hackers; shihao zhong <[email protected]>
On Fri, Mar 13, 2026 at 11:52 AM Sami Imseih <[email protected]> wrote:
>
> > it would be better to verify that the stats of the specified sequence are
> > actually reset by using pg_stat_reset_single_table_counters().
> >
> > Also, the documentation for pg_stat_reset_single_table_counters() should
> > mention that it can reset sequence statistics as well.
>
> good points.
>
> For the documentation of pg_stat_reset_single_table_counters(), I also
> mentioned materialized views for completeness.
Thanks for updating the patch!
I made a few cosmetic changes and pushed the patch. Thanks!
Regards,
--
Fujii Masao
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Add missing stats_reset column to pg_statio_all_sequences view
2026-03-12 22:06 Add missing stats_reset column to pg_statio_all_sequences view Sami Imseih <[email protected]>
2026-03-12 22:44 ` Re: Add missing stats_reset column to pg_statio_all_sequences view Sami Imseih <[email protected]>
2026-03-13 01:39 ` Re: Add missing stats_reset column to pg_statio_all_sequences view Fujii Masao <[email protected]>
2026-03-13 02:52 ` Re: Add missing stats_reset column to pg_statio_all_sequences view Sami Imseih <[email protected]>
2026-03-16 08:27 ` Re: Add missing stats_reset column to pg_statio_all_sequences view Fujii Masao <[email protected]>
@ 2026-03-16 10:01 ` Fujii Masao <[email protected]>
2026-03-16 14:49 ` Re: Add missing stats_reset column to pg_statio_all_sequences view Fujii Masao <[email protected]>
0 siblings, 1 reply; 24+ messages in thread
From: Fujii Masao @ 2026-03-16 10:01 UTC (permalink / raw)
To: Sami Imseih <[email protected]>; +Cc: pgsql-hackers; shihao zhong <[email protected]>
On Mon, Mar 16, 2026 at 5:27 PM Fujii Masao <[email protected]> wrote:
>
> On Fri, Mar 13, 2026 at 11:52 AM Sami Imseih <[email protected]> wrote:
> >
> > > it would be better to verify that the stats of the specified sequence are
> > > actually reset by using pg_stat_reset_single_table_counters().
> > >
> > > Also, the documentation for pg_stat_reset_single_table_counters() should
> > > mention that it can reset sequence statistics as well.
> >
> > good points.
> >
> > For the documentation of pg_stat_reset_single_table_counters(), I also
> > mentioned materialized views for completeness.
>
> Thanks for updating the patch!
>
> I made a few cosmetic changes and pushed the patch. Thanks!
Hmm... buildfarm member crake reported a pg_upgradeCheck failure, and it seems
the test I added last time is causing it :(
--- /home/andrew/bf/root/HEAD/pgsql/src/test/regress/expected/stats.out
2026-03-16 04:27:05.805748763 -0400
+++ /home/andrew/bf/root/HEAD/pgsql.build/testrun/pg_upgrade/002_pg_upgrade/data/results/stats.out
2026-03-16 05:17:15.460202339 -0400
@@ -1196,7 +1196,7 @@
FROM pg_statio_all_sequences WHERE relname ='test_seq1';
?column? | ?column?
----------+----------
- t | 0
+ t | 1
(1 row)
The test assumed that blks_read and blks_hit in pg_statio_all_sequences would
be zero immediately after calling pg_stat_reset_single_table_counters().
However, on crake, either of them became 1 even right after the reset.
This might happen if another process (e.g., autovacuum) accesses
the sequence in the short window between the reset and the query of
pg_statio_all_sequences??? More investigation would be necessary.
Since checking the blks_read and blks_hit counters in pg_statio_all_sequences
can make the test unstable, I'm thinking of removing that part of the test.
Regards,
--
Fujii Masao
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Add missing stats_reset column to pg_statio_all_sequences view
2026-03-12 22:06 Add missing stats_reset column to pg_statio_all_sequences view Sami Imseih <[email protected]>
2026-03-12 22:44 ` Re: Add missing stats_reset column to pg_statio_all_sequences view Sami Imseih <[email protected]>
2026-03-13 01:39 ` Re: Add missing stats_reset column to pg_statio_all_sequences view Fujii Masao <[email protected]>
2026-03-13 02:52 ` Re: Add missing stats_reset column to pg_statio_all_sequences view Sami Imseih <[email protected]>
2026-03-16 08:27 ` Re: Add missing stats_reset column to pg_statio_all_sequences view Fujii Masao <[email protected]>
2026-03-16 10:01 ` Re: Add missing stats_reset column to pg_statio_all_sequences view Fujii Masao <[email protected]>
@ 2026-03-16 14:49 ` Fujii Masao <[email protected]>
0 siblings, 0 replies; 24+ messages in thread
From: Fujii Masao @ 2026-03-16 14:49 UTC (permalink / raw)
To: Sami Imseih <[email protected]>; +Cc: pgsql-hackers; shihao zhong <[email protected]>
On Mon, Mar 16, 2026 at 7:01 PM Fujii Masao <[email protected]> wrote:
> Hmm... buildfarm member crake reported a pg_upgradeCheck failure, and it seems
> the test I added last time is causing it :(
>
> --- /home/andrew/bf/root/HEAD/pgsql/src/test/regress/expected/stats.out
> 2026-03-16 04:27:05.805748763 -0400
> +++ /home/andrew/bf/root/HEAD/pgsql.build/testrun/pg_upgrade/002_pg_upgrade/data/results/stats.out
> 2026-03-16 05:17:15.460202339 -0400
> @@ -1196,7 +1196,7 @@
> FROM pg_statio_all_sequences WHERE relname ='test_seq1';
> ?column? | ?column?
> ----------+----------
> - t | 0
> + t | 1
> (1 row)
>
> The test assumed that blks_read and blks_hit in pg_statio_all_sequences would
> be zero immediately after calling pg_stat_reset_single_table_counters().
> However, on crake, either of them became 1 even right after the reset.
> This might happen if another process (e.g., autovacuum) accesses
> the sequence in the short window between the reset and the query of
> pg_statio_all_sequences??? More investigation would be necessary.
>
> Since checking the blks_read and blks_hit counters in pg_statio_all_sequences
> can make the test unstable, I'm thinking of removing that part of the test.
Done.
Regards,
--
Fujii Masao
^ permalink raw reply [nested|flat] 24+ messages in thread
end of thread, other threads:[~2026-04-07 03:58 UTC | newest]
Thread overview: 24+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-03-09 22:11 Add missing stats_reset column to pg_stat_database_conflicts view shihao zhong <[email protected]>
2026-03-09 23:33 ` Sami Imseih <[email protected]>
2026-03-10 01:17 ` Fujii Masao <[email protected]>
2026-03-10 11:32 ` Kirill Reshke <[email protected]>
2026-03-10 18:43 ` shihao zhong <[email protected]>
2026-03-10 19:27 ` Sami Imseih <[email protected]>
2026-03-11 14:10 ` shihao zhong <[email protected]>
2026-03-12 04:50 ` Fujii Masao <[email protected]>
2026-03-12 06:04 ` Chao Li <[email protected]>
2026-03-12 16:21 ` Sami Imseih <[email protected]>
2026-03-12 18:09 ` Fujii Masao <[email protected]>
2026-03-12 20:39 ` Sami Imseih <[email protected]>
2026-03-13 13:25 ` Fujii Masao <[email protected]>
2026-03-16 02:10 ` Chao Li <[email protected]>
2026-04-06 04:22 ` Fujii Masao <[email protected]>
2026-04-07 03:58 ` Chao Li <[email protected]>
2026-03-11 18:04 [PATCH] Add missing stats_reset column to pg_statio_all_sequences view shihao zhong <[email protected]>
2026-03-12 22:06 Add missing stats_reset column to pg_statio_all_sequences view Sami Imseih <[email protected]>
2026-03-12 22:44 ` Re: Add missing stats_reset column to pg_statio_all_sequences view Sami Imseih <[email protected]>
2026-03-13 01:39 ` Re: Add missing stats_reset column to pg_statio_all_sequences view Fujii Masao <[email protected]>
2026-03-13 02:52 ` Re: Add missing stats_reset column to pg_statio_all_sequences view Sami Imseih <[email protected]>
2026-03-16 08:27 ` Re: Add missing stats_reset column to pg_statio_all_sequences view Fujii Masao <[email protected]>
2026-03-16 10:01 ` Re: Add missing stats_reset column to pg_statio_all_sequences view Fujii Masao <[email protected]>
2026-03-16 14:49 ` Re: Add missing stats_reset column to pg_statio_all_sequences view Fujii Masao <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox