Обсуждение: Another fun fact about temp tables and wraparound

Поиск
Список
Период
Сортировка

Another fun fact about temp tables and wraparound

От
Grigory Smolkin
Дата:
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

Re: Another fun fact about temp tables and wraparound

От
Alvaro Herrera
Дата:
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