Re: Add sub-transaction overflow status in pg_stat_activity

On Wed, Nov 23, 2022 at 3:56 PM Andres Freund <andres(at)anarazel(dot)de> wrote:
> Indeed. This is why I was thinking that just alerting for overflowed xact
> isn't particularly helpful. You really want to see how much they overflow and
> how often.

I think if we just expose the is-overflowed feld and the count, people
can poll. It works fine for wait events and I think it's fine here,
too.

> But even that might not be that helpful. Perhaps what we actually need is an
> aggregate measure showing the time spent doing subxact lookups due to
> overflowed snapshots? Seeing a substantial amount of time spent doing subxact
> lookups would be much more accurate call to action than seeing a that some
> sessions have a lot of subxacts.

That's not responsive to the need that I have. I need users to be able
to figure out which backend(s) are overflowing their snapshots -- and
perhaps how badly and how often --- not which backends are incurring
an expense as a result. There may well be a use case for the latter
thing but it's a different problem.

> I wonder if we could lower the impact of suboverflowed snapshots by improving
> the representation in PGPROC and SnapshotData. What if we
>
> a) Recorded the min and max assigned subxid in PGPROC
>
> b) Instead of giving up in GetSnapshotData() once we see a suboverflowed
> PGPROC, store the min/max subxid of the proc in SnapshotData. We could
> reliably "steal" space for that from ->subxip, as we won't need to store
> subxids for that proc.
>
> c) When determining visibility with a suboverflowed snapshot we use the
> ranges from b) to check whether we need to do a subtrans lookup. I think
> that'll often prevent subtrans lookups.

Wouldn't you basically need to take the union of all the ranges,
probably by keeping the lowest min and the highest max? I'm not sure
how much that would really help at that point.

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