Re: Reaping Temp tables to avoid XID wraparound

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.

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