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
