pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Manu <manuelreyesbravo@gmail.com>
To: pgsql-bugs@lists.postgresql.org
Cc: Staroverov Ilja <i.staroverov@ftdata.ru>
Subject: Re: VACUUM/ANALYZE double-count the backend's own pending table stats
Date: Wed, 14 Oct 2026 15:47:04 -0300
Message-ID: <179200362437.252424.7000490135467740876@gmail.com> (raw)
In-Reply-To: <1DC9D815-8754-4A76-B2B1-99C1A614A55A@localhost.localdomain>
References: <1DC9D815-8754-4A76-B2B1-99C1A614A55A@localhost.localdomain>

Hi Ilya,

I reproduced v1 and stress-tested it: it holds up. 8000 randomized
single-session sequences (lazy vs eager flush as the oracle), plus
2PC, the truncdropped paths and post-snapshot inserts all match
count(*) with the patch. Nice work.

One thing, which is the corner you already called out:

> One thing this patch doesn't fix: after INSERT + VACUUM in the same
> session, ins_since_vacuum is still N, because the flush adds the
> pending tuples_inserted to it.

It is the same double count your patch fixes for live/dead, in the same
function, and it does fire a spurious insert-triggered autovacuum right
after a manual VACUUM. Minimal:

    INSERT 1000; VACUUM t; SELECT pg_stat_force_next_flush();
    -- n_ins_since_vacuum = 1000, should be 0

The attached delta sits on top of yours, untouched; it applies clean
(+18/-8) and closes it: a pending ins_before_vacuum field, appended at
the end of the struct so the inline pgstat_count_* offsets do not move
(keeps a back-branch fix viable). pgstat_report_vacuum() records what it
already covered; the flush adds only the difference, so tuples_inserted
and n_tup_ins stay cumulative. make check is green and the oracle drops
to zero failures (minus TRUNCATE, below). Fold it into v2 if you like,
or I can post it as a separate 0002.

Two pre-existing things the oracle flagged that predate your patch (same
on unpatched master, so not v1's doing): pending inserts before a
TRUNCATE, and n_dead_tup after a subxact rolled back past a TRUNCATE.
Both look like they need the truncdrop save/restore across
subtransactions. Happy to send the repros separately if useful.

Regards,
Manu

Attachments:

ins-since-vacuum.diff (text/x-patch, 4.3K) download diff --git a/src/backend/utils/activity/pgstat_relation.c b/src/backend/utils/activity/pgstat_relation.c index cfebd0cd6dd..9e6559596e7 100644 --- a/src/backend/utils/activity/pgstat_relation.c +++ b/src/backend/utils/activity/pgstat_relation.c @@ -279,6 +279,11 @@ pgstat_report_vacuum(Relation rel, PgStat_Counter livetuples, * stats have not been flushed yet. Forget the pending deltas for those * counters, else they would be added on top of the new values at the * next flush. A pending truncation reset is superseded as well. + * + * Likewise, the pending inserts must not count towards ins_since_vacuum, + * which is reset below. We can't just zero tuples_inserted, which also + * feeds the cumulative tuples_inserted counter, so remember how many of + * them the flush has to skip. */ if (pgstat_should_count_relation(rel)) { @@ -288,6 +293,8 @@ pgstat_report_vacuum(Relation rel, PgStat_Counter livetuples, pending->tab.counts_xact.delta_live_tuples = 0; pending->tab.counts_xact.delta_dead_tuples = 0; pending->tab.truncdropped = false; + pending->tab.ins_before_vacuum = + pending->tab.counts_xact.tuples_inserted; } /* Store the data in the table's hash table entry. */ @@ -993,7 +1000,8 @@ pgstat_relation_flush_cb(PgStat_EntryRef *entry_ref, bool nowait) * triggering for inserts more often than they maybe should, which is * probably not going to be common enough to be too concerned about here. */ - tabentry->ins_since_vacuum += lstats->tab.counts_xact.tuples_inserted; + tabentry->ins_since_vacuum += lstats->tab.counts_xact.tuples_inserted - + lstats->tab.ins_before_vacuum; tabentry->blocks_fetched += lstats->tab.counts.blocks_fetched; tabentry->blocks_hit += lstats->tab.counts.blocks_hit; diff --git a/src/include/pgstat.h b/src/include/pgstat.h index 187d82c96fe..6b6a705aba5 100644 --- a/src/include/pgstat.h +++ b/src/include/pgstat.h @@ -236,6 +236,8 @@ typedef struct PgStat_RelationStatus struct PgStat_TableXactStatus *trans; /* lowest subxact's counts */ PgStat_TableCounts counts; /* event counts to be sent */ PgStat_TableCountsXact counts_xact; /* transactional counts */ + /* pending tuples_inserted already covered by a VACUUM */ + PgStat_Counter ins_before_vacuum; } tab; /* index counters */ diff --git a/src/test/regress/expected/stats.out b/src/test/regress/expected/stats.out index 5e28db6e57d..154b99a4c12 100644 --- a/src/test/regress/expected/stats.out +++ b/src/test/regress/expected/stats.out @@ -322,14 +322,14 @@ SELECT pg_stat_force_next_flush(); (1 row) -SELECT relname, n_live_tup, n_dead_tup, n_mod_since_analyze +SELECT relname, n_live_tup, n_dead_tup, n_mod_since_analyze, n_ins_since_vacuum FROM pg_stat_user_tables WHERE relname LIKE 'stats_pending_%' ORDER BY relname; - relname | n_live_tup | n_dead_tup | n_mod_since_analyze ------------------------+------------+------------+--------------------- - stats_pending_analyze | 6 | 4 | 0 - stats_pending_trunc | 3 | 0 | 13 - stats_pending_vacuum | 10 | 0 | 10 + relname | n_live_tup | n_dead_tup | n_mod_since_analyze | n_ins_since_vacuum +-----------------------+------------+------------+---------------------+-------------------- + stats_pending_analyze | 6 | 4 | 0 | 10 + stats_pending_trunc | 3 | 0 | 13 | 3 + stats_pending_vacuum | 10 | 0 | 10 | 0 (3 rows) DROP TABLE stats_pending_trunc, stats_pending_analyze, stats_pending_vacuum; diff --git a/src/test/regress/sql/stats.sql b/src/test/regress/sql/stats.sql index 302ff9af05c..b7b4570e5a3 100644 --- a/src/test/regress/sql/stats.sql +++ b/src/test/regress/sql/stats.sql @@ -173,7 +173,7 @@ ANALYZE stats_pending_analyze; INSERT INTO stats_pending_vacuum SELECT generate_series(1, 10); VACUUM stats_pending_vacuum; SELECT pg_stat_force_next_flush(); -SELECT relname, n_live_tup, n_dead_tup, n_mod_since_analyze +SELECT relname, n_live_tup, n_dead_tup, n_mod_since_analyze, n_ins_since_vacuum FROM pg_stat_user_tables WHERE relname LIKE 'stats_pending_%' ORDER BY relname; DROP TABLE stats_pending_trunc, stats_pending_analyze, stats_pending_vacuum;

view thread (15+ messages)  latest in thread

Message-ID: <179200362437.252424.7000490135467740876@gmail.com>
Permalink:  ../179200362437.252424.7000490135467740876@gmail.com/
Also on:    postgresql.org/message-id/179200362437.252424.7000490135467740876@gmail.com

 ·  · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-bugs@postgresql.org
  Cc: manuelreyesbravo@gmail.com, i.staroverov@ftdata.ru
  Subject: Re: VACUUM/ANALYZE double-count the backend's own pending table stats
  In-Reply-To: <179200362437.252424.7000490135467740876@gmail.com>
  References: <1DC9D815-8754-4A76-B2B1-99C1A614A55A@localhost.localdomain> <179200362437.252424.7000490135467740876@gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox