Re: BUG #16614: Stale temporary objects makes vacuum ineffective when 1 million transactions remain

On Thu, Sep 10, 2020 at 10:43:54AM -0400, Tom Lane wrote:
> Alternatively, maybe you don't have any temp-table-owning sessions
> that live that long, but for some reason some temp tables failed to
> get dropped when their owning session exited. (AFAIK this could only
> happen as part of a database crash --- is that a common occurrence
> for you?) They're still there and still holding back the global XID
> horizon, so the only way to fix things is to forcibly drop them.
>
> PG versions later than 9.5 have added increasingly aggressive rules for
> automatically dropping such orphan temp tables. From memory, 9.5 does
> have some behavior of that sort; but it's evidently not firing for you.
> What you'll need to do is find the old tables (look for pg_class entries
> with large values of age(relfrozenxid)) and manually DROP those tables.

Yeah. 11 and newer versions have been made even more aggressive with
the cleanup of orphaned tables in autovacuum, particularly the case
where a backend reuses the ID of a past session that crashed, leaving
behind some temporary tables. Perhaps that was the case here?

> I would not recommend the wholesale removal of temp schemas shown in
> your script, because that carries too much risk of removing tables
> that are in active use.

Dropping directly a temporary schema would put sessions still relying
on them in geopardy, as sessions that have created at least one
temporary table store statically the namespace OID of the temp
schema. The result will get fun if you create some temp tables
afterwards on those live sessions, and unlikely the result you'd
expect, but the operation can be useful in some cases as that's a
no-brainer, and only a superuser can do that. Dropping directly the
tables from another session would be fine.
--
Michael