Re: Reaping Temp tables to avoid XID wraparound

On Mon, Feb 25, 2019 at 10:45 PM Michael Paquier <michael(at)paquier(dot)xyz>
wrote:

> On Fri, Feb 22, 2019 at 04:01:02PM +0100, Magnus Hagander wrote:
> > I did the "insert column in the middle of pg_stat_get_activity", I'm not
> > sure that is right -- how do we treate that one? Do we just append at the
> > end because people are expected to use the pg_stat_activity view? It's a
> > nontrivial part of the patch.
>
> I think that it would be more confusing to add the new column at the
> tail, after all the SSL fields.
>
> > That one aside, does the general way to track it appear reasonable? (docs
> > excluded until we have agreement on that)
>
> It does. A temp table is associated to a session so it's not like
> autovacuum can work on it. With this information it is at least
> possible to take actions. We could even get autovacuum to kill such
> sessions. /me hides
>
> > And should we also expose the oid in pg_stat_activity in this case, since
> > we have it?
>
> For the case reported here, just knowing the XID where the temporary
> namespace has been created is enough so as the goal is to kill the
> session associated with it. Still, it seems to me that knowing the
> temporary schema name used by a given session is useful, and that's
> cheap to get as the information is already there.
>

it should be since it's in pgproc.

One problem that I can see with your patch is that you would set the
> XID once any temporary object created, including when objects other
> than tables are created in pg_temp, including functions, etc. And it
> does not really matter for wraparound monitoring. Still, the patch is
> simple..
>

I'm not entirely sure what you mean here. Sure, it will log it even when a
temp function is created, but the namespace is still created then is it
not?

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