Re: Temporary tables prevent autovacuum, leading to XID wraparound

On Thu, Jan 25, 2018 at 08:10:00AM +0000, Tsunakawa, Takayuki wrote:
>>> * In this incident, autovacuum worker misjudged that
>>> pg_temp_3.fetchchunks can't be deleted, although the creator
>>> (pg_rewind) is no longer active. How can we delete orphan temporary
>>> tables safely?
>>
>> As long as Postgres sees that its temporary schema is in use, it would think
>> that the table is not orphaned. Another thing possible would be to have
>> the session now holding this schema space to reuse fetchchunks so as things
>> are reset.
>
> I understood you suggested a new session which recycle the temp schema
> should erase the zombie metadata of old temp tables or recreate the
> temp schema. That sounds easy.

If the new session makes use of the same temporary schema where the
orphan table is, cleanup is possible. Now you have a problem if this is
not available as this depends on the backend ID uniquely assigned. It
would be better to just drop the table manually at the end.
--
Michael