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) downloaddiff --git a/src/backend/utils/activity/pgstat_relation.c b/src/backend/utils/activity/pgstat_relation.cindex 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.hindex 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.outindex 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.sqlindex 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;
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