Another fun fact about temp tables and wraparound

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!
vacuum during get_rel_oids() ignored temp table but didn`t when it comes
to calculating oldest relfrozenxid.
Dropping all temp schemas helped

Crude way to reproduce:

postgres=# create temp table t1();

gdb: set ShmemVariableCache->nextXid = ShmemVariableCache->xidStopLimit
+ 100

pg_ctl stop -D PGDATA

with open('path_to_clog_file', 'w') as f:
x = 0
while x < 200000:
f.write(chr(1))
x = x + 1

postgres --single -D $PGDATA

PostgreSQL stand-alone backend 9.6.9
backend> vacuum freeze;
WARNING: database "postgres" must be vacuumed within 999947 transactions
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>
backend> <DROP ALL TEMP SCHEMAS HERE>
backend> vacuum freeze;
backend>

I think the root case of all temp table problems is that they are
present in catalog. I think they should not be present in catalog.
And vacuum probably should ignore them during datfrozenxid calculation.
In single mode at least. Or just drop them in single mode.
And it would be good to have advice 'drop temp schemas' in HINT message.

--
Grigory Smolkin
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

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