From: Manu <manuelreyesbravo@gmail.com>
To: pgsql-bugs@lists.postgresql.org
Cc: Staroverov Ilja <i.staroverov@ftdata.ru>
Cc: Sebastian Webber <sebastian@swebber.me>
Subject: Re: VACUUM/ANALYZE double-count the backend's own pending table stats
Date: Thu, 15 Oct 2026 04:11:27 -0300
Message-ID: <179204828715.3237120.899220695784427126@gmail.com> (raw)
In-Reply-To: <1CBF745F-CB0D-4D35-9E25-D6B5208335E4@localhost.localdomain>
References: <1CBF745F-CB0D-4D35-9E25-D6B5208335E4@localhost.localdomain>
Hi Ilya,
> about the two pre-existing TRUNCATE/subtransaction cases: yes, please
> send the repros.
Attached: p1 and p2. They reproduce on 14 through master, and neither
patch changes them, so they aren't from this one. Agreed they're better
as a separate, back-patchable patch; I left the probable fix area (the
truncdrop save/restore across subxacts) in a comment, no fix.
> Manu, if you had a way to pin that one too, I'd be glad to use it.
I did find a deterministic one (diffs attached, works on 14..master and
also catches the ins_since_vacuum case), but let me be upfront: it's
unconventional. The test function is marked IMMUTABLE only so it can be
indexed, and it does DML during ANALYZE (an armed expression index
inserts into the table whose TOAST the same VACUUM then cleans). A
committer may not want that in a test. The clean alternative is a
test-only injection point in the flush path, but that's a core change.
My honest lean is the injection point, or just leaving the VACUUM case
best-effort as you have it, rather than the index trick. Your call; the
diffs are there if you want them.
Regards,
Manu
Attachments:download all (4 files, .tar.gz)p1_repro.sql.txt (text/plain, 1.7K) download
-- P1: inserts still pending when a TRUNCATE commits are added to
-- n_ins_since_vacuum after the TRUNCATE resets it.
--
-- One session. The procedure keeps the backend busy between the steps, and
-- pending stats are only flushed while the backend is idle, so the first
-- INSERT is still pending when the TRUNCATE commits. Runs on 14 and later.
\set ON_ERROR_STOP 1
\pset footer off
SELECT current_setting('server_version_num')::int >= 150000 AS has_force_flush \gset
\if :has_force_flush
\set flush 'SELECT pg_stat_force_next_flush()'
\else
\set flush 'SELECT pg_sleep(1.1)'
\endif
CREATE TABLE p1_pending (id int) WITH (autovacuum_enabled = off);
CREATE TABLE p1_flushed (id int) WITH (autovacuum_enabled = off);
CREATE PROCEDURE p1_steps() LANGUAGE plpgsql AS $$
BEGIN
INSERT INTO p1_pending SELECT generate_series(1, 9);
COMMIT;
TRUNCATE p1_pending;
COMMIT;
INSERT INTO p1_pending SELECT generate_series(1, 6);
END
$$;
\echo == P1: INSERT 9; TRUNCATE; INSERT 6 (one procedure, nothing flushed in between)
CALL p1_steps();
:flush \g /dev/null
SELECT n_live_tup, n_ins_since_vacuum, (SELECT count(*) FROM p1_pending) AS count
FROM pg_stat_user_tables WHERE relname = 'p1_pending';
\echo expected n_ins_since_vacuum = 6 (rows inserted since the TRUNCATE); bug: 15
\echo == control: same statements, stats flushed after each step
INSERT INTO p1_flushed SELECT generate_series(1, 9);
:flush \g /dev/null
TRUNCATE p1_flushed;
:flush \g /dev/null
INSERT INTO p1_flushed SELECT generate_series(1, 6);
:flush \g /dev/null
SELECT n_live_tup, n_ins_since_vacuum, (SELECT count(*) FROM p1_flushed) AS count
FROM pg_stat_user_tables WHERE relname = 'p1_flushed';
\echo control gives n_ins_since_vacuum = 6
DROP PROCEDURE p1_steps();
DROP TABLE p1_pending, p1_flushed;
p2_repro.sql.txt (text/plain, 2.3K) download
-- P2: TRUNCATE followed, in the same transaction, by work that is undone or
-- by an ANALYZE, leaves wrong live/dead counts. Deterministic: no
-- dependency on flush timing. Runs on 14 and later.
\set ON_ERROR_STOP 1
\pset footer off
SELECT current_setting('server_version_num')::int >= 150000 AS has_force_flush \gset
\if :has_force_flush
\set flush 'SELECT pg_stat_force_next_flush()'
\else
\set flush 'SELECT pg_sleep(1.1)'
\endif
\echo == P2a: BEGIN; TRUNCATE; SAVEPOINT; INSERT 100; ROLLBACK TO; COMMIT
CREATE TABLE p2a (id int) WITH (autovacuum_enabled = off);
BEGIN;
TRUNCATE p2a;
SAVEPOINT s;
INSERT INTO p2a SELECT generate_series(1, 100);
ROLLBACK TO s;
COMMIT;
:flush \g /dev/null
SELECT n_live_tup, n_dead_tup FROM pg_stat_user_tables WHERE relname = 'p2a';
\echo expected n_dead_tup = 100 (the aborted rows are in the new relfilenode); bug: 0
\echo VACUUM VERBOSE shows the 100 dead rows it removes:
VACUUM (VERBOSE) p2a;
\echo == P2a control: same without the TRUNCATE
CREATE TABLE p2a_ctl (id int) WITH (autovacuum_enabled = off);
BEGIN;
SAVEPOINT s;
INSERT INTO p2a_ctl SELECT generate_series(1, 100);
ROLLBACK TO s;
COMMIT;
:flush \g /dev/null
SELECT n_live_tup, n_dead_tup FROM pg_stat_user_tables WHERE relname = 'p2a_ctl';
\echo control gives n_dead_tup = 100
\echo == P2b: 288 rows; BEGIN; SAVEPOINT; TRUNCATE; ANALYZE; ROLLBACK TO; COMMIT
CREATE TABLE p2b (id int) WITH (autovacuum_enabled = off);
INSERT INTO p2b SELECT generate_series(1, 288);
ANALYZE p2b;
:flush \g /dev/null
BEGIN;
SAVEPOINT s;
TRUNCATE p2b;
ANALYZE p2b;
ROLLBACK TO s;
COMMIT;
:flush \g /dev/null
SELECT s.n_live_tup, c.reltuples, (SELECT count(*) FROM p2b) AS count
FROM pg_stat_user_tables s JOIN pg_class c ON c.oid = s.relid
WHERE s.relname = 'p2b';
\echo expected n_live_tup = 288; bug: 0. pg_class.reltuples keeps the right value
\echo == P2b, top-level variant: BEGIN; TRUNCATE; ANALYZE; ROLLBACK
CREATE TABLE p2c (id int) WITH (autovacuum_enabled = off);
INSERT INTO p2c SELECT generate_series(1, 288);
ANALYZE p2c;
:flush \g /dev/null
BEGIN;
TRUNCATE p2c;
ANALYZE p2c;
ROLLBACK;
:flush \g /dev/null
SELECT s.n_live_tup, c.reltuples, (SELECT count(*) FROM p2c) AS count
FROM pg_stat_user_tables s JOIN pg_class c ON c.oid = s.relid
WHERE s.relname = 'p2c';
DROP TABLE p2a, p2a_ctl, p2b, p2c;
vacuum-pin-0001.diff (text/x-patch, 6.9K) downloaddiff --git a/src/test/regress/expected/stats.out b/src/test/regress/expected/stats.outindex ea6b260..9e16f0d 100644--- a/src/test/regress/expected/stats.out+++ b/src/test/regress/expected/stats.out@@ -298,13 +298,10 @@ COMMIT;
-- forgotten when another transaction commits before the stats are flushed.
-- Pending stats are only flushed while the backend is idle, so run the
-- TRUNCATE and ANALYZE cases inside a procedure to make sure they are not
--- flushed in between. VACUUM cannot run in a procedure; if the stats happen--- to get flushed between the INSERT and the VACUUM, that case still passes,--- but doesn't test anything.+-- flushed in between. VACUUM is tested separately below.
-----
CREATE TABLE stats_pending_trunc(id int);
CREATE TABLE stats_pending_analyze(id int);
-CREATE TABLE stats_pending_vacuum(id int);
INSERT INTO stats_pending_trunc SELECT generate_series(1, 10);
SELECT pg_stat_force_next_flush();
pg_stat_force_next_flush
@@ -326,8 +323,6 @@ BEGIN
END
$$;
CALL stats_pending_proc();
-INSERT INTO stats_pending_vacuum SELECT generate_series(1, 10);-VACUUM stats_pending_vacuum;
SELECT pg_stat_force_next_flush();
pg_stat_force_next_flush
--------------------------
@@ -341,11 +336,58 @@ SELECT 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-(3 rows)+(2 rows)
DROP PROCEDURE stats_pending_proc();
-DROP TABLE stats_pending_trunc, stats_pending_analyze, stats_pending_vacuum;+DROP TABLE stats_pending_trunc, stats_pending_analyze;+-- VACUUM cannot run in a procedure, so let the VACUUM command do the INSERT+-- itself: analyzing stats_vacuum_src evaluates its index expression, which+-- (while armed) inserts into stats_vacuum_pending, whose TOAST table the same+-- command vacuums next. The backend is never idle in between, so nothing+-- can be flushed, and VACUUM (ANALYZE) doesn't analyze TOAST tables, so the+-- TOAST table's stats come from VACUUM only. reltuples is set by that VACUUM.+-- stats_vacuum_expr() is labeled IMMUTABLE only so that it can be indexed.+CREATE TABLE stats_vacuum_pending(t text);+ALTER TABLE stats_vacuum_pending ALTER t SET STORAGE EXTERNAL;+CREATE TABLE stats_vacuum_src(id int);+CREATE FUNCTION stats_vacuum_fill() RETURNS void LANGUAGE plpgsql AS $$+BEGIN+ INSERT INTO public.stats_vacuum_pending+ SELECT repeat('x', 10000) FROM generate_series(1, 10);+END+$$;+CREATE FUNCTION stats_vacuum_expr(int) RETURNS int IMMUTABLE+ LANGUAGE plpgsql AS $$+BEGIN+ IF current_setting('stats_vacuum.armed', true) = 'on' THEN+ PERFORM public.stats_vacuum_fill();+ END IF;+ RETURN $1;+END+$$;+CREATE INDEX ON stats_vacuum_src (stats_vacuum_expr(id));+INSERT INTO stats_vacuum_src VALUES (1);+SET stats_vacuum.armed = on;+VACUUM (ANALYZE) stats_vacuum_src, stats_vacuum_pending;+RESET stats_vacuum.armed;+SELECT pg_stat_force_next_flush();+ pg_stat_force_next_flush +--------------------------+ +(1 row)++SELECT s.n_live_tup > 0 AS toasted, s.n_live_tup = c.reltuples AS live_ok,+ s.n_dead_tup+ FROM pg_class c JOIN pg_stat_all_tables s ON s.relid = c.oid+ WHERE c.oid = (SELECT reltoastrelid FROM pg_class+ WHERE oid = 'stats_vacuum_pending'::regclass);+ toasted | live_ok | n_dead_tup +---------+---------+------------+ t | t | 0+(1 row)++DROP TABLE stats_vacuum_src, stats_vacuum_pending;+DROP FUNCTION stats_vacuum_expr(int), stats_vacuum_fill();
-----
-- Basic tests for pg_stat_all_indexes
-----
diff --git a/src/test/regress/sql/stats.sql b/src/test/regress/sql/stats.sqlindex d446a7f..b9c65af 100644--- a/src/test/regress/sql/stats.sql+++ b/src/test/regress/sql/stats.sql@@ -159,13 +159,10 @@ COMMIT;
-- forgotten when another transaction commits before the stats are flushed.
-- Pending stats are only flushed while the backend is idle, so run the
-- TRUNCATE and ANALYZE cases inside a procedure to make sure they are not
--- flushed in between. VACUUM cannot run in a procedure; if the stats happen--- to get flushed between the INSERT and the VACUUM, that case still passes,--- but doesn't test anything.+-- flushed in between. VACUUM is tested separately below.
-----
CREATE TABLE stats_pending_trunc(id int);
CREATE TABLE stats_pending_analyze(id int);
-CREATE TABLE stats_pending_vacuum(id int);
INSERT INTO stats_pending_trunc SELECT generate_series(1, 10);
SELECT pg_stat_force_next_flush();
CREATE PROCEDURE stats_pending_proc() LANGUAGE plpgsql AS $$
@@ -182,14 +179,51 @@ BEGIN
END
$$;
CALL stats_pending_proc();
-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
FROM pg_stat_user_tables
WHERE relname LIKE 'stats_pending_%' ORDER BY relname;
DROP PROCEDURE stats_pending_proc();
-DROP TABLE stats_pending_trunc, stats_pending_analyze, stats_pending_vacuum;+DROP TABLE stats_pending_trunc, stats_pending_analyze;++-- VACUUM cannot run in a procedure, so let the VACUUM command do the INSERT+-- itself: analyzing stats_vacuum_src evaluates its index expression, which+-- (while armed) inserts into stats_vacuum_pending, whose TOAST table the same+-- command vacuums next. The backend is never idle in between, so nothing+-- can be flushed, and VACUUM (ANALYZE) doesn't analyze TOAST tables, so the+-- TOAST table's stats come from VACUUM only. reltuples is set by that VACUUM.+-- stats_vacuum_expr() is labeled IMMUTABLE only so that it can be indexed.+CREATE TABLE stats_vacuum_pending(t text);+ALTER TABLE stats_vacuum_pending ALTER t SET STORAGE EXTERNAL;+CREATE TABLE stats_vacuum_src(id int);+CREATE FUNCTION stats_vacuum_fill() RETURNS void LANGUAGE plpgsql AS $$+BEGIN+ INSERT INTO public.stats_vacuum_pending+ SELECT repeat('x', 10000) FROM generate_series(1, 10);+END+$$;+CREATE FUNCTION stats_vacuum_expr(int) RETURNS int IMMUTABLE+ LANGUAGE plpgsql AS $$+BEGIN+ IF current_setting('stats_vacuum.armed', true) = 'on' THEN+ PERFORM public.stats_vacuum_fill();+ END IF;+ RETURN $1;+END+$$;+CREATE INDEX ON stats_vacuum_src (stats_vacuum_expr(id));+INSERT INTO stats_vacuum_src VALUES (1);+SET stats_vacuum.armed = on;+VACUUM (ANALYZE) stats_vacuum_src, stats_vacuum_pending;+RESET stats_vacuum.armed;+SELECT pg_stat_force_next_flush();+SELECT s.n_live_tup > 0 AS toasted, s.n_live_tup = c.reltuples AS live_ok,+ s.n_dead_tup+ FROM pg_class c JOIN pg_stat_all_tables s ON s.relid = c.oid+ WHERE c.oid = (SELECT reltoastrelid FROM pg_class+ WHERE oid = 'stats_vacuum_pending'::regclass);+DROP TABLE stats_vacuum_src, stats_vacuum_pending;+DROP FUNCTION stats_vacuum_expr(int), stats_vacuum_fill();
-----
-- Basic tests for pg_stat_all_indexes
vacuum-pin-0002-tests.diff (text/x-patch, 2.9K) downloaddiff --git a/src/test/regress/expected/stats.out b/src/test/regress/expected/stats.outindex 9e16f0d..a5eb65b 100644--- a/src/test/regress/expected/stats.out+++ b/src/test/regress/expected/stats.out@@ -329,13 +329,13 @@ 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+ 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
(2 rows)
DROP PROCEDURE stats_pending_proc();
@@ -377,13 +377,13 @@ SELECT pg_stat_force_next_flush();
(1 row)
SELECT s.n_live_tup > 0 AS toasted, s.n_live_tup = c.reltuples AS live_ok,
- s.n_dead_tup+ s.n_dead_tup, s.n_ins_since_vacuum
FROM pg_class c JOIN pg_stat_all_tables s ON s.relid = c.oid
WHERE c.oid = (SELECT reltoastrelid FROM pg_class
WHERE oid = 'stats_vacuum_pending'::regclass);
- toasted | live_ok | n_dead_tup ----------+---------+------------- t | t | 0+ toasted | live_ok | n_dead_tup | n_ins_since_vacuum +---------+---------+------------+--------------------+ t | t | 0 | 0
(1 row)
DROP TABLE stats_vacuum_src, stats_vacuum_pending;
diff --git a/src/test/regress/sql/stats.sql b/src/test/regress/sql/stats.sqlindex b9c65af..32ae660 100644--- a/src/test/regress/sql/stats.sql+++ b/src/test/regress/sql/stats.sql@@ -180,7 +180,7 @@ END
$$;
CALL stats_pending_proc();
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 PROCEDURE stats_pending_proc();
@@ -218,7 +218,7 @@ VACUUM (ANALYZE) stats_vacuum_src, stats_vacuum_pending;
RESET stats_vacuum.armed;
SELECT pg_stat_force_next_flush();
SELECT s.n_live_tup > 0 AS toasted, s.n_live_tup = c.reltuples AS live_ok,
- s.n_dead_tup+ s.n_dead_tup, s.n_ins_since_vacuum
FROM pg_class c JOIN pg_stat_all_tables s ON s.relid = c.oid
WHERE c.oid = (SELECT reltoastrelid FROM pg_class
WHERE oid = 'stats_vacuum_pending'::regclass);
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, sebastian@swebber.me
Subject: Re: VACUUM/ANALYZE double-count the backend's own pending table stats
In-Reply-To: <179204828715.3237120.899220695784427126@gmail.com>
References: <1CBF745F-CB0D-4D35-9E25-D6B5208335E4@localhost.localdomain> <179204828715.3237120.899220695784427126@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