Re: Another fun fact about temp tables and wraparound

On 2018-Jul-17, Grigory Smolkin wrote:

> Hello, hackers!
>
> Recently I was investigating the case of 'stuck in wraparaound' problem.
> PostgreSQL instance(9.6.9) in question reached 'million-before-wraparound'
> threshold and switched to read-only mode.
> Running vacuum in single-mode gives not results, datfrozenxid was not
> advancing:
>
> backend> vacuum freeze;
> 2018-07-13 16:43:58 MSK [3666-3] WARNING: database "database_name" must be
> vacuumed within 991565 transactions
> 2018-07-13 16:43:58 MSK [3666-4] HINT: To avoid a database shutdown,
> execute a database-wide VACUUM in that database.
> You might also need to commit or roll back old prepared
> transactions.
> backend>
>
> pg_prepared_xacts was empty.
> After some poking around it became clear that some old temp table was
> holding the oldest relfrozenxid!

Hmm, autovacuum is supposed to drop temp tables that are above the
wraparound xid age to avoid this problem -- see autovacuum lines 2046ff.
(Except it doesn't do anything if the owning backend is active. I guess
this could be a problem if the owning backend fails to do anything about
those tables. Maybe this part is a mistake.) Obviously, during
single-user mode autovacuum doesn't run anyway.

--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services