On Fri, Nov 13, 2026 at 12:16 PM Melanie Plageman <[email protected]> wrote: > > Here's a reproducer for how you can get -1 for approx_tuple_count even > in versions <= 18 > > BEGIN; > CREATE TABLE t(id int); > COPY t FROM PROGRAM 'printf "1\n"' WITH (FREEZE); > COMMIT; > SELECT scanned_percent, approx_tuple_count FROM pgstattuple_approx('t'); > > But the documentation doesn't say anything about how "unknown" is > represented. In that sense, there is no documented contract about > whether 0 or -1 means unknown. I would find it much clearer to return > -1 when it is unknown, but we do not want to change this in > backbranches. We only want to make it consistent. Though, people may > be getting -1 today, as we can see from the repro. From the code it's > clear that the intent was for the value to be clamped to 0. None of > the other pgstattuple_approx() fields return -1 when we scanned 0 or 1 > pages. They do a variety of other things that are inconsistent and > confusing, but they don't return -1. > > As for other places in Postgres, reltuples in pg_class is -1 when > unknown, but cumulative relation stat counters return 0 when there > isn't yet a stats entry. But these are not stats functions like > pgstattuple_approx(). > > My current inclination is to patch 15-master so pgstattuple_approx() > returns 0 instead of -1, which is consistent with what the code was > trying to do and document this. Then propose returning either NULL or > -1 starting in master when we didn't scan enough pages to get good > estimates but to do so for all the relevant return values from > pgstattuple_approx().
I've pushed the last patch in the original series (the one excluding new pages from the extrapolation calculation), however, I'm hesitant to make this change (the -1/0 change) until we have consensus on the desired behavior and review of the docs change. I'm pretty sure the right immediate step is to make the 0 clamp work, though. The downside of not committing this now is that in 19, getting -1 from pgstat_approx() is more common than in older releases and 19 GA code freeze is tomorrow. If we wait and commit the fix later, it will change in 19.1 which might be confusing. However, it's probably better to wait and get consensus and not change it twice in two versions. Attached is what I propose doing and backpatching through 15. - Melanie
From 751162fb6701d84747c753457a995833ff8313bb Mon Sep 17 00:00:00 2001 From: Melanie Plageman <[email protected]> Date: Fri, 13 Nov 2026 15:43:23 -0500 Subject: [PATCH v1] Fix pgstattuple_approx handling of unknown tuple estimates vac_estimate_reltuples() can return -1 when there is not enough information to estimate the number of live tuples. pgstattuple_approx() assigned that value to an unsigned counter before attempting to clamp it, making the clamp ineffective and exposing -1 through the bigint result. Clamp the signed estimate before assigning it to the unsigned counter. Reported-by: Zsolt Parragi <[email protected]> Author: Melanie Plageman <[email protected]> Discussion: https://postgr.es/m/CAN4CZFM0y8t%2BAQ8PWTeuBXnFtRDUNvkfVzzA_yo6uJ2bceK%3DxA%40mail.gmail.com Backpatch-through: 15 --- contrib/pgstattuple/expected/pgstattuple.out | 12 ++++++++++++ contrib/pgstattuple/pgstatapprox.c | 11 ++++++----- contrib/pgstattuple/sql/pgstattuple.sql | 10 ++++++++++ doc/src/sgml/pgstattuple.sgml | 3 ++- 4 files changed, 30 insertions(+), 6 deletions(-) diff --git a/contrib/pgstattuple/expected/pgstattuple.out b/contrib/pgstattuple/expected/pgstattuple.out index 9176dc98b6a..32f055b2d53 100644 --- a/contrib/pgstattuple/expected/pgstattuple.out +++ b/contrib/pgstattuple/expected/pgstattuple.out @@ -250,6 +250,18 @@ select pg_relpages((select reltoastrelid from pg_class where relname = 'test')); 0 (1 row) +-- An unknown approximate tuple count is reported as zero. +begin; +create temp table test_approx_unknown (id int); +copy test_approx_unknown from stdin with (freeze); +commit; +select scanned_percent, approx_tuple_count + from pgstattuple_approx('test_approx_unknown'); + scanned_percent | approx_tuple_count +-----------------+-------------------- + 0 | 0 +(1 row) + -- not for the index calls though, of course select pgstatindex('test_partition'); ERROR: relation "test_partition" is not a btree index diff --git a/contrib/pgstattuple/pgstatapprox.c b/contrib/pgstattuple/pgstatapprox.c index bed904206d7..8fd5e8d877e 100644 --- a/contrib/pgstattuple/pgstatapprox.c +++ b/contrib/pgstattuple/pgstatapprox.c @@ -119,6 +119,7 @@ statapprox_heap(Relation rel, output_type *stat) { BlockNumber nblocks; BufferAccessStrategy bstrategy; + double estimated_tuple_count; TransactionId OldestXmin; StatApproxReadStreamPrivate p; ReadStream *stream; @@ -254,12 +255,12 @@ statapprox_heap(Relation rel, output_type *stat) * tuples in all-visible pages, so no correction is needed for that, and * we already accounted for the space in those pages, too. */ - stat->tuple_count = vac_estimate_reltuples(rel, nblocks, p.scanned, - p.scanned_new, - stat->tuple_count); + estimated_tuple_count = vac_estimate_reltuples(rel, nblocks, p.scanned, + p.scanned_new, + stat->tuple_count); - /* It's not clear if we could get -1 here, but be safe. */ - stat->tuple_count = Max(stat->tuple_count, 0); + /* Report zero when there is not enough information for an estimate. */ + stat->tuple_count = Max(estimated_tuple_count, 0); /* * Calculate percentages if the relation has one or more pages. diff --git a/contrib/pgstattuple/sql/pgstattuple.sql b/contrib/pgstattuple/sql/pgstattuple.sql index 7e72c567a06..0853a23c985 100644 --- a/contrib/pgstattuple/sql/pgstattuple.sql +++ b/contrib/pgstattuple/sql/pgstattuple.sql @@ -107,6 +107,16 @@ select pgstattuple((select reltoastrelid from pg_class where relname = 'test')); select pgstattuple_approx((select reltoastrelid from pg_class where relname = 'test')); select pg_relpages((select reltoastrelid from pg_class where relname = 'test')); +-- An unknown approximate tuple count is reported as zero. +begin; +create temp table test_approx_unknown (id int); +copy test_approx_unknown from stdin with (freeze); +1 +\. +commit; +select scanned_percent, approx_tuple_count + from pgstattuple_approx('test_approx_unknown'); + -- not for the index calls though, of course select pgstatindex('test_partition'); select pgstatginindex('test_partition'); diff --git a/doc/src/sgml/pgstattuple.sgml b/doc/src/sgml/pgstattuple.sgml index 54d8f90245e..8828f4f1272 100644 --- a/doc/src/sgml/pgstattuple.sgml +++ b/doc/src/sgml/pgstattuple.sgml @@ -572,7 +572,8 @@ approx_free_percent | 2.09 <row> <entry><structfield>approx_tuple_count</structfield></entry> <entry><type>bigint</type></entry> - <entry>Number of live tuples (estimated)</entry> + <entry>Number of live tuples (estimated), or zero if there is not + enough information to estimate it</entry> </row> <row> <entry><structfield>approx_tuple_len</structfield></entry> -- 2.43.0
