Re: Add sub-transaction overflow status in pg_stat_activity

On Mon, Nov 14, 2022 at 2:17 PM David G. Johnston
<david(dot)g(dot)johnston(at)gmail(dot)com> wrote:
> Assuming getting an actual count value to print is fairly cheap, or even a sunk cost if you are going to report overflow, I don't see why we wouldn't want to provide the more detailed data.
>
> My concern, through ignorance, with reporting a number is that it would have no context in the query result itself. If I have two rows with numbers, one with 10 and one with 1,000, is the two orders of magnitude of the second number important or does overflow happen at, say, 65,000 and so both numbers are exceedingly small and thus not worth worrying about? That can be handled by documentation just fine, so long as the reference number in question isn't a per-session variable. Otherwise, showing some kind of "percent of max" computation seems warranted. In which case maybe the two presentation outputs would be:
>
> 1,000 (13%)
> Overflowed

I think the idea of cramming a bunch of stuff into a text field is
dead on arrival. Data types are a wonderful invention because they let
people write queries, say looking for backends where overflowed =
true, or backends where subxids > 64. that gets much harder if the
query has to try to make sense of some random text representation.

If both values are separately important, then we need to report them
both, and the only question is whether to do that in pg_stat_activity
or via a side mechanism. What I don't yet understand is why that's
true. I think the important question is whether there are overflowed
backends, and Andres thinks it's how many subtransaction XIDs there
are, so there is a reasonable chance that both things actually matter
in separate scenarios. But I only know the scenario in which
overflowed matters, not the one in which subtransaction XID count
matters.

--
Robert Haas
EDB: http://www.enterprisedb.com