Subject: Add missing stats_reset column to pg_stat_database_conflicts view

From: shihao zhong <[email-masked]>
Date: Mon, 9 Mar 2026 18:11:33 -0400

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

---

From: Sami Imseih <[email-masked]>
Date: Mon, 9 Mar 2026 18:33:08 -0500

> 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)

---

From: Fujii Masao <[email-masked]>
Date: Tue, 10 Mar 2026 10:17:08 +0900

> 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

---

From: Kirill Reshke <[email-masked]>
Date: Tue, 10 Mar 2026 16:32:02 +0500

> pg_stat_reset_single_table_counters mentions sequences in addition to

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

---

From: shihao zhong <[email-masked]>
Date: Tue, 10 Mar 2026 14:43:19 -0400

The patch has been rebased, and the documentation and catversion
updates have been added.

> 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.

---

From: Sami Imseih <[email-masked]>
Date: Tue, 10 Mar 2026 14:27:14 -0500

Hi,

Please don't top-post. it makes following the thread difficult.

> support for these view. For now, let's quickly address the db conflict

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).

---

From: shihao zhong <[email-masked]>
Date: Wed, 11 Mar 2026 10:10:32 -0400

> [2] [https://wiki.postgresql.org/wiki/Submitting_a_Patch]

Thanks for pointing that out. I've added new tests and used git
format-patch to generate a new patch.

---

From: Fujii Masao <[email-masked]>
Date: Thu, 12 Mar 2026 13:50:28 +0900

> 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

---

From: Chao Li <[email-masked]>
Date: Thu, 12 Mar 2026 14:04:39 +0800

> On Mar 12, 2026, at 12:50, Fujii Masao <[email-masked]> wrote:
> 
> <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/

---

From: Sami Imseih <[email-masked]>
Date: Thu, 12 Mar 2026 11:21:18 -0500

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.

---

From: Fujii Masao <[email-masked]>
Date: Fri, 13 Mar 2026 03:09:19 +0900

> this is good to have as well. I also updated the existing comment for

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

---

From: Sami Imseih <[email-masked]>
Date: Thu, 12 Mar 2026 15:39:04 -0500

> 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

---

From: Fujii Masao <[email-masked]>
Date: Fri, 13 Mar 2026 22:25:00 +0900

On Fri, Mar 13, 2026 at 5:47 PM Chao Li <[email-masked]> wrote:
>
>
>
> > On Mar 13, 2026, at 04:39, Sami Imseih <[email-masked]> wrote:
> >
> > <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

---

From: Chao Li <[email-masked]>
Date: Mon, 16 Mar 2026 10:10:34 +0800

> On Mar 13, 2026, at 21:25, Fujii Masao <[email-masked]> wrote:
> 

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,

---

From: Fujii Masao <[email-masked]>
Date: Mon, 6 Apr 2026 13:22:34 +0900

> 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

---

From: Chao Li <[email-masked]>
Date: Tue, 7 Apr 2026 11:58:08 +0800

> On Apr 6, 2026, at 12:22, Fujii Masao <[email-masked]> wrote:
> 

Thank you very much for pushing.

Best regards,