Hi, On Fri, Nov 13, 2026 at 2:13 PM Melanie Plageman <[email protected]> wrote: > > 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.
I haven't chimed in on this thread previously, but I assume that a review is still welcome to help get this addressed before GA. Reading back through the thread, and specifically this issue, I agree with your earlier sentiment that if we could, NULL or -1 is the better result here, but I see why we can't do that on the released back branches. Specifically, as someone who writes software that reads from statistics views and related functions in a Go program, suddenly getting NULL where it was an integer before would cause an error in the client program, so we shouldn't do that. I do think we should return NULL on master though, if we can - at least to me that's the most logical value. Theoretically, one could argue that we should fix this in 19 so we return NULL - its not a practical problem there in terms of breaking client programs (just yet), and the code change is small - but not sure if that is too much to ask for at this point. Otherwise, +1 on the patch as is. I think the documentation change looks good as well. Thanks, Lukas -- Lukas Fittl
