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