Re: Temporary tables prevent autovacuum, leading to XID wraparound

On Thu, Jan 25, 2018 at 06:14:41AM +0000, Tsunakawa, Takayuki wrote:
> I don't know why pg_temp3.fetchchunks still exists. Maybe the user
> ran pg_ctl stop -mi while pg_rewind was running.

Likely that was the case :(

As a superuser, DROP TABLE should work on the temporary schema of
another session. Have you tried that to solve the situation?

> * I think temporary tables should not require vacuuming for XID
> wraparound. Furtherover, should updates/deletes to temporary tables
> be in-place instead of creating garbage, so that any form of vacuum is
> unnecessary? Other sessions do not need to read temporary tables.

Yeah, there are many areas of improvements in this area. Temp tables
also generate WAL..

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