Обсуждение: Another fun fact about temp tables and wraparound
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
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