Horton

Thread

Commits

  1. Remove unstable test for pg_statio_all_sequences stats reset

  2. Add stats_reset column to pg_statio_all_sequences

  1. Add missing stats_reset column to pg_statio_all_sequences view

    Sami Imseih <samimseih@gmail.com> — 2026-03-12T22:06:13Z

    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.gmail.com]

--
Sami Imseih
Amazon Web Services (AWS)

  2. Re: Add missing stats_reset column to pg_statio_all_sequences view

    Sami Imseih <samimseih@gmail.com> — 2026-03-12T22:44:08Z

    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)

  3. Re: Add missing stats_reset column to pg_statio_all_sequences view

    Fujii Masao <masao.fujii@gmail.com> — 2026-03-13T01:39:36Z

    On Fri, Mar 13, 2026 at 7:44 AM Sami Imseih <samimseih@gmail.com> 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

  4. Re: Add missing stats_reset column to pg_statio_all_sequences view

    Sami Imseih <samimseih@gmail.com> — 2026-03-13T02:52:10Z

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

  5. Re: Add missing stats_reset column to pg_statio_all_sequences view

    Fujii Masao <masao.fujii@gmail.com> — 2026-03-16T08:27:19Z

    On Fri, Mar 13, 2026 at 11:52 AM Sami Imseih <samimseih@gmail.com> 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

  6. Re: Add missing stats_reset column to pg_statio_all_sequences view

    Fujii Masao <masao.fujii@gmail.com> — 2026-03-16T10:01:47Z

    On Mon, Mar 16, 2026 at 5:27 PM Fujii Masao <masao.fujii@gmail.com> wrote:
>
> On Fri, Mar 13, 2026 at 11:52 AM Sami Imseih <samimseih@gmail.com> 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

  7. Re: Add missing stats_reset column to pg_statio_all_sequences view

    Fujii Masao <masao.fujii@gmail.com> — 2026-03-16T14:49:01Z

    On Mon, Mar 16, 2026 at 7:01 PM Fujii Masao <masao.fujii@gmail.com> 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