Re: Another fun fact about temp tables and wraparound

From: Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>
To: Grigory Smolkin <g(dot)smolkin(at)postgrespro(dot)ru>
Cc: pgsql-hackers(at)lists(dot)postgresql(dot)org
Subject: Re: Another fun fact about temp tables and wraparound
Date: 2018-07-17 14:46:08
Message-ID: 20180717144608.bphejyvdfy5l2hnf@alvherre.pgsql
Views: Raw Message | Whole Thread | Download mbox
Lists: pgsql-hackers

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
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

In response to

Browse pgsql-hackers by date

  From Date Subject
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