Re: Add sub-transaction overflow status in pg_stat_activity

On Mon, Nov 14, 2022 at 12:47 PM Andres Freund <andres(at)anarazel(dot)de> wrote:
> I'd go the other way. It's pretty unimportant whether it overflowed, it's
> important how many subtxns there are. The cases where overflowing causes real
> problems are when there's many thousand subtxns - which one can't judge just
> from suboverflowed alone. Nor can monitoring a boolean tell you whether you're
> creeping closer to the danger zone.

This is the opposite of what I believe to be true. I thought the
problem is that once a single backend overflows the subxid array, all
snapshots have to be created suboverflowed, and this makes visibility
checking more expensive. It's my impression that for some users this
creates and extremely steep performance cliff: the difference between
no backends overflowing and 1 backend overflowing is large, but
whether you are close to the limit makes no difference as long as you
don't reach it, and once you've passed it it makes little difference
how far past it you go.

> But TBH, to me this still is something that'd be better addressed with a
> tracepoint.

I think that makes it far, far less accessible to the typical user.

> I don't buy the argument that the ship of pg_stat_activity width has entirely
> sailed. A session still fits onto a reasonably sized terminal in \x output -
> but not much longer.

I guess it depends on what you mean by reasonable. For me, without \x,
it wraps across five times on an idle system with the 24x80 window
that I normally use, and even if I full screen my terminal window, it
still wraps around. With \x, sure, it fits, both only if the query is
shorter than the width of my window minus ~25 characters, which isn't
that likely to be the case IME because users write long queries. I
don't even try to use \x most of the time because the queries are
likely to be long enough to destroy any benefit, but it all depends on
how big your terminal is and how long your queries are.

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