|From:||Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>|
|To:||Grigory Smolkin <g(dot)smolkin(at)postgrespro(dot)ru>|
|Subject:||Re: Another fun fact about temp tables and wraparound|
|Views:||Raw Message | Whole Thread | Download mbox|
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
> 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
> 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
|Next Message||Daniel Gustafsson||2018-07-17 16:04:44||Re: Allow auto_explain to log to NOTICE|
|Previous Message||Grigory Smolkin||2018-07-17 14:33:00||Another fun fact about temp tables and wraparound|