The following documentation comment has been logged on the website:
Page: https://www.postgresql.org/docs/16/routine-vacuuming.html
Description:
In
https://www.postgresql.org/docs/current/routine-vacuuming.html
section "25.1.5. Preventing Transaction ID Wraparound Failures" there is a
5-step procedure to handle the "database is not accepting commands"
situation, see "Instead, follow these steps:"
Please, add a notice about temporary tables - they can also prevent
vacuuming!
This is hinted in section "25.1.6. The Autovacuum Daemon", but only
indirectly:
"Temporary tables cannot be accessed by autovacuum. Therefore, appropriate
vacuum and analyze operations should be performed via session SQL
commands."
Just today on a production, I performed (in a single-user mode) vacuum, even
vacuum freeze, and after ending it, DB still didn't accept users.
The useful detection SELECT was
SELECT oid, oid::regclass, relkind, relfrozenxid, age(relfrozenxid) FROM
pg_class WHERE relfrozenxid <> 0 ORDER BY age(relfrozenxid);
Then I realized these were the temporary tables and dropped all the
temporary schemas ... once in a single-user mode :(