Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xRSuZ-00000000zcZ-1rBf for pgsql-bugs@arkaria.postgresql.org; Thu, 12 Nov 2026 11:24:11 +0000 Received: from localhost ([127.0.0.1]:59984 helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.98.2) (envelope-from ) id 1xRSuY-0000000GBOX-19gi for pgsql-bugs@arkaria.postgresql.org; Thu, 12 Nov 2026 11:24:10 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]:32846) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xRSuX-0000000GBOP-477I for pgsql-bugs@lists.postgresql.org; Thu, 12 Nov 2026 11:24:10 +0000 Received: from mail-dl1-x122d.google.com ([2607:f8b0:4864:20::122d]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.98.2) (envelope-from ) id 1xRSuV-00000000dRb-2VHk for pgsql-bugs@lists.postgresql.org; Thu, 12 Nov 2026 11:24:09 +0000 Received: by mail-dl1-x122d.google.com with SMTP id a92af1059eb24-1931ff67a29so7827845c88.1 for ; Thu, 12 Nov 2026 03:24:06 -0800 (PST) ARC-Seal: i=1; a=rsa-sha256; t=1794482644; cv=none; d=google.com; s=arc-20260327; b=M2CfDOdMCY5tRAC8aEkuetU73ZK3rcohhWU0bAb8E86wYwTCTZSw1YxPRyfMjAkJGB WPv5HQNJLi9gCSlKtUOxlaZXO/ALYt+Po6LXTMpIEHOUPEzVVOlc7BqxNpCzNWD9JTsH LOdag8VNLDEZGjTmorcE1Pzq6lN5hS4peb/BDPvjmA3qQ4qggBlZxe2BHc2TiRkwTXLe dWNLtYyz+o+YJGRqSzICvaVAS718v7cjOR76KAT8JrZC3yAXn03bVrdzPs49cZ2AQzMA dOy+KRjE3cuwpNtOZXJKGwo2W7S6ck0XRkMjnft/2f2pJhzjc6vZuLpXmGAXCKM+Yj/A Ns4Q== ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=google.com; s=arc-20260327; h=content-transfer-encoding:cc:to:subject:message-id:date:from :in-reply-to:references:mime-version:dkim-signature; bh=Cod32pvC0CTUG+HFSDGZ0ugQZfhKg6qL/DyadQUU4iU=; fh=QOIlEj33kaN5K3xnSidit83SHuj7nFisRCR5eQgkIFs=; b=RLCjzopZgdutuwHUdao2T1eQh+swEUGC3zIFCgNzeVF9NoLmesWuETwdlzTU9mGtMs uWVdLr8sWMSE5DupZX+PGGZF10quSV+EpW3zU0fxJDKkf73/XXTjgKtyIGYMFG2DVKpY iFotfCbwQZ9qi51ilDcE6GFlyfvAZnO1SpQXNklxASMZbrI4IN/dlJEjQG3YyWNAhp+9 A0Qy77dbWTNZUYHMldIMgZx7GNnx3Dwses3RBFd81p+T23iLyg+rL/XVEAxUlbSUl2xs Hg5Zt3+r/HKHAo2OLZd2+nPtpg47wRtZpJN+vhpQuZFuzzOJJ+icxTU061LP3PTqcMHR m2jA==; darn=lists.postgresql.org ARC-Authentication-Results: i=1; mx.google.com; arc=none DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20260707; t=1794482644; x=1795087444; darn=lists.postgresql.org; h=content-transfer-encoding:content-type:cc:to:subject:message-id :date:from:in-reply-to:references:mime-version:from:to:cc:subject :date:message-id:reply-to:content-type; bh=Cod32pvC0CTUG+HFSDGZ0ugQZfhKg6qL/DyadQUU4iU=; b=Z8T43m6MW5c5o2H3VaIRpE3wlWzG2PorEt6oLrsPFlhfTJpQk/DlZBMTbFBfUZmPno BZpu5U87us0mqrkRLABTe89mDufIgf2IcLqsZN9HfG3CgFjLTH6rwdwpIbbQplm8PqAH rLMFLBh7igwIottEJgQABY5PYiOcIKxHubd0RWtINuL+yjNduTBiVx0pFjWUCAAFK2vq cWlieSbFYz5H5fI6nriRWxYQh514JxSS6frXJGbpD6hT1Ciwd5dLNHsTprB9/d9QiyPO z+Ij0b0CARmBDg5qDFMgoP42/F7KU0Bz7bG1iumC3vs34oHDN3jpnzQ2o9tb+6GZy36r RwrA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20260707; t=1794482644; x=1795087444; h=content-transfer-encoding:content-type:cc:to:subject:message-id :date:from:in-reply-to:references:mime-version:x-gm-gg :x-gm-message-state:from:to:cc:subject:date:message-id:reply-to :content-type; bh=Cod32pvC0CTUG+HFSDGZ0ugQZfhKg6qL/DyadQUU4iU=; b=EjxQv4o13VM6yEDnIpAZVnMKOKzrSVhVZxlW2+bvYh3uGubwAdImDv+/Gh2Ld/xiJA XdX3fujN1xcV1VkYzGR8Jy5GfTESG/uIuMnopAE9mRbsFEw967Lu8TIy96ZNLFmF6ETW Z8uMlB6I/gXx5N67WvYgnXJgJuJLPbk2vL8Wddu1RZS31NHzBInh+eS43ULX//SufEVa gD8mBsvr2tPBEr7QwDQ5O3i0q8MLr8/xzxXALzmgrNxRB3BV2KEn9L6rvoaGY4oam05j bVmtp5VLkii95Y9jLxC0HeUYJfGj87qt1HF74srs+uJjKvcnmOTkdpjfLQmuzqp3tgAi L2rQ== X-Forwarded-Encrypted: i=1; AL+hErHjjt7vlp3IW6p+3Cun1EyCFvR+TLlER8rjBf6sMUM1xj7wWZw+znlA+sMjYx7rmWOchs5lGljIC2Mu@lists.postgresql.org X-Gm-Message-State: APuU4Z+6ZjBT9orRjB00QCMXGSvieicurb8BUn8Kd9TcibVUkbrgImDH k1sB7DV5GoMg8U8B8TVCi7aKxoH7mCnVUlt2yV9or7Rl46jEb31LlfGUQUa2fGXKqDEsPp0vJ0R 24Q0GkF8uA0bvkSwohiICeucmeEsJBLk= X-Gm-Gg: AYBFou0xIyowmKB+2v/CZrcub6U3Go12y1P3SXP/wkhRRFQwi0EPJZQGutd1moBG0Ms W5J4lnhZ1+1t8r+iuPq4GWunFluy0vO7pZwfestc+5TttuRK7bCV1A2eEnttxn1K++x6y0AEv99 PVyU9xxx1iiqoa0m3yOFO6atPSGr0QJQlm52StPeY5zPu8AiaE85gCND82/ARTS8ngn3o+90t+/ 6xvK+A+2Qu03C36nPGwo4r5IBIkwX0smDdzN2T+L1/aWPMXyws7gahgjsID3TdSi7TnXm2IK+PG cbY3U3+6evwwCyWhnPm7IRpcuah99r04aRsUW4K134Y4AMjg36JumTOUXIKZu082PxHN1uYD3ov uEY45vBn4w+Ypyh8magYHtjnceKkTWWJ3LigwZy4FzKqSoEdSaJj8Dv/UnFnR7LF1IhDvuzbUM7 5/dvPGoW6vRAN18NcumFtEM6+nkfXiCdv2fl3qlkvKZ0IPGPEpt73+3Tagb7284Gl4OWj8dYkIS d5e8B3Grek= X-Received: by 2002:a05:7022:7e82:b0:1ab:fe29:5081 with SMTP id a92af1059eb24-1b3c0311fd5mr5505898c88.20.1794482644400; Thu, 12 Nov 2026 03:24:04 -0800 (PST) MIME-Version: 1.0 References: <1DC9D815-8754-4A76-B2B1-99C1A614A55A@localhost.localdomain> <2C6AA9AE-4348-4952-B971-E0D63B2B1A53@localhost.localdomain> <5AE9744F-6C5F-4E99-9704-3361FCC9D9B3@localhost.localdomain> In-Reply-To: <5AE9744F-6C5F-4E99-9704-3361FCC9D9B3@localhost.localdomain> From: =?UTF-8?Q?Jo=C3=A3o_Rito?= Date: Thu, 12 Nov 2026 08:23:53 -0300 X-Gm-Features: AclHuK-01wxI5lrKhR-0bJrJ2w62WjTr58qrHDzj2FiobNZTGdOJUw_ewTa2a1g Message-ID: Subject: Re: VACUUM/ANALYZE double-count the backend's own pending table stats To: Staroverov Ilja Cc: Bertrand Drouvot , "pgsql-bugs@lists.postgresql.org" , Manu , "sebastian@swebber.me" Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk Hi Ilja, Thanks for the quick reply and for the link to the 2021 thread. I agree that n_mod_since_analyze staying at 1300 after TRUNCATE follows the existing policy and is not something this patch should change. Here are the scripts and output. Build: master c1694b7, with and without v2, --enable-cassert --enable-debug, autovacuum=3Doff, a fresh initdb for each run, all statements run from one psql -f session. E4 (TRUNCATE and SAVEPOINT): CREATE TABLE e4(id int); INSERT INTO e4 SELECT generate_series(1,1000); SELECT pg_stat_force_next_flush(); BEGIN; TRUNCATE e4; SAVEPOINT s; INSERT INTO e4 SELECT generate_series(1,5); ROLLBACK TO s; COMMIT; INSERT INTO e4 SELECT generate_series(1,300); SELECT pg_stat_force_next_flush(); SELECT n_tup_ins, n_live_tup, n_mod_since_analyze, n_ins_since_vacuum FROM pg_stat_user_tables WHERE relname =3D 'e4'; n_tup_ins | n_live_tup | mod_since_an | ins_since_vac unpatched 1305 | 1300 | 1300 | 1305 v2 1305 | 300 | 1300 | 305 count(*) is 300. So the TRUNCATE comes before the SAVEPOINT, and the 5 rows are inserted inside the savepoint and rolled back. The 305 is the 300 committed rows plus the 5 aborted inserts, which matches what you said. n_live_tup is correct with v2. pg_stat_reset() cases: E1, reset between INSERT and ANALYZE: CREATE TABLE e1(id int); INSERT INTO e1 SELECT generate_series(1,1000); SELECT pg_stat_reset(); ANALYZE e1; SELECT pg_stat_force_next_flush(); n_tup_ins | n_live_tup | mod_since_an | ins_since_vac unpatched 1000 | 2000 | 1000 | 1000 v2 1000 | 1000 | 0 | 1000 count(*) is 1000. Here v2 fixes the double count. E2, reset after ANALYZE, then 50 more rows: CREATE TABLE e2(id int); INSERT INTO e2 SELECT generate_series(1,1000); ANALYZE e2; SELECT pg_stat_reset(); INSERT INTO e2 SELECT generate_series(1,50); SELECT pg_stat_force_next_flush(); n_tup_ins | n_live_tup | mod_since_an | ins_since_vac unpatched 1050 | 1050 | 1050 | 1050 v2 1050 | 50 | 50 | 1050 count(*) is 1050. In my first mail I only gave the 50 vs 1050 and did not explain it, sorry. Looking closer, I don't think this is a problem with v2. With v2, ANALYZE takes in the 1000 pending inserts, and then pg_stat_reset() clears the value ANALYZE stored, so only the 50 new rows remain. Without the patch, the 1000 pending inserts reach shared stats only after the reset, so the result happens to match count(*). To check this, I ran two variants on v2: - E2b: the same, but with pg_stat_force_next_flush() and one more statement between the INSERT and ANALYZE, so the 1000 inserts are flushed before ANALYZE. Result: n_tup_ins 50, n_live_tup 50, n_mod_since_analyze 50, n_ins_since_vacuum 50. - E2c: the same as E2, but ANALYZE runs in a second session. Result: 1050 for all four columns. So with v2, E2 gives the same n_live_tup as when the inserts are flushed before ANALYZE. The only odd part is that n_tup_ins and n_ins_since_vacuum are still 1050 while n_live_tup is 50, because only the live/dead/mod counters are taken in by ANALYZE. After a reset these numbers are approximate anyway, so I think this is fine, maybe worth a line in the commit message. E3 (partitioned parent): as you said, the parent shows zero, and that is expected. No assertion failures or warnings in the server logs in any run. The runner script, in case it helps: B=3D$HOME/inst7460/bin; D=3D$HOME/d7460 rm -rf $D; $B/initdb -D $D echo "port=3D5492 autovacuum=3Doff unix_socket_directories=3D'/tmp' track_counts=3Don" >> $D/postgresql.conf $B/pg_ctl -D $D -l $D/log -w start $B/psql -X -h /tmp -p 5492 -d postgres -f edge.sql $B/pg_ctl -D $D -m fast -w stop Regards, Joao Rito Em qui., 12 de nov. de 2026 =C3=A0s 01:43, Staroverov Ilja escreveu: > > Hi Joao, > > Thanks for testing both builds and for spelling out the coverage and > limitations. Yes, please send the scripts and output, particularly for > the TRUNCATE/SAVEPOINT and pg_stat_reset() cases. > > On n_mod_since_analyze, 1300 is consistent with the current behavior, > since TRUNCATE does not reset that counter. Whether it should do so > was discussed in 2021 [1], with a patch to reset it and discussion of > whether that would delay a useful autoanalyze after TRUNCATE. I would > not treat this observation as a new bug or propose changing that policy > as part of this patch. It is distinct from losing a pending TRUNCATE > flag when a later transaction commits. > > The 305 for n_ins_since_vacuum is also consistent with the existing > accounting of aborted inserts. pgstat_relation_flush_cb() uses > tuples_inserted for this counter and explicitly notes that it includes > aborted inserts. I'd like to see the exact SAVEPOINT placement before > relating that result to Manu's reproducers. It does not by itself show > the same problem. > > The parent's zero counters are expected here too. Inserts are counted > on the leaf partitions, and ANALYZE of a partitioned parent explicitly > reports zero live/dead tuples to the cumulative stats system. The > parent's pg_class.reltuples estimate is maintained separately. > > [1] https://www.postgresql.org/message-id/flat/CAD21AoAwdh5FqoVobm9qbQUR%= 3DtJjqnX8u5-d1RVFS1V%3DEexFMw%40mail.gmail.com > > -- > Ilya Staroverov >