Re: Reaping Temp tables to avoid XID wraparound

On Wed, Feb 13, 2019 at 2:26 AM Michael Paquier <michael(at)paquier(dot)xyz> wrote:

> On Wed, Feb 13, 2019 at 12:38:51AM +0000, Andrew Gierth wrote:
> > Doesn't work - that function's idea of "backend id" doesn't match the
> > real one, since it's looking at a local copy of the stats from which
> > unused slots have been removed.
>
> The temporary namespace OID is added to PGPROC since v11, so it could
> be easy enough to add a system function which maps a temp schema to a
> PID. Now, it could actually make sense to add this information into
> pg_stat_get_activity() and that would be cheaper.
>
>
I think that would be useful and make sense.

And while at it, what would in this particular case have been even more
useful to the OP would be to actually identify that there is a temp table
*and which xid it's blocking at*. For regular transactions we can look at
backend_xid, but IIRC that doesn't work for temp tables (unless they are
inside a transaction). Maybe we can find a way to expose that type of
relevant information at a similar level while poking around that code?

//Magnus