Re: Reaping Temp tables to avoid XID wraparound

On Wed, Feb 13, 2019 at 05:48:39PM +0100, Magnus Hagander wrote:
> On Wed, Feb 13, 2019 at 2:26 AM Michael Paquier <michael(at)paquier(dot)xyz> wrote:
>> 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.

One thing to keep in mind here is that tempNamespaceId in PGPROC gets
set before the transaction creating it has committed, hence it is
necessary to also check that the namespace actually exists from the
point of view of the session running pg_stat_get_activity() before
showing it, which can be done with a simple
SearchSysCacheExists1(NAMESPACEOID) normally.

> 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?

Yeah, possibly. I think that it could be tricky though to get that at
a global level in a cheap way. It makes also little sense to only
show the temp namespace OID if that information is not enough.
--
Michael