Re: Reaping Temp tables to avoid XID wraparound

On Thu, Feb 14, 2019 at 1:43 AM Michael Paquier <michael(at)paquier(dot)xyz> wrote:

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

Oh, that's a good point.

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

We could I guess add a field specifically for temp_namespace_xid or such.
The question is if it's worth the overhead to do that.

Just having the namespace oid is at least enough to know that there is
potentially something to go look at it. But it doesn't make for automated
monitoring very well, at least not in systems that have a larger number of
databases.

--
Magnus Hagander
Me: https://www.hagander.net/ <http://www.hagander.net/>
Work: https://www.redpill-linpro.com/ <http://www.redpill-linpro.com/>