The sequence of events that led our databases to the wraparound issue is the following:
The application creates temporary tables that should be by default cleaned at the end of the session. As a matter of fact the pooler doesn't actually close the session, neither - apparently - issues a DISCARD ALL, creating leaks among sessions.
Since sessions are kept alive with stale temporary tables the autovacuum process cannot do maintenance on transaction ids.
Since we have no evidence on what is going on, the database reaches the 1 million stop limit to avoid transaction wraparound.
The application gets then out of order. We stop the Postgres server gracefully and we enter single user mode to run the vacuum
Any attempt to vacuum the database ends up with the usual "WARNING: database "mydb" must be vacuumed within XXX transactions" message, revealing that the vacuum could not fix up the transaction ids.
By running this SQL (still in single user mode) SELECT nspname || '.' || relname AS objname FROM pg_class JOIN pg_namespace ON pg_namespace.oid = pg_class.relnamespace WHERE relpersistence = 't' AND relkind = 'r' We notice the presence of orphaned tables. Sessions are gone at this point, so they are for sure orphaned.
If we manually drop the orphaned tables and run vacuum again the warning message disappears and the database is restored to normal functioning.
No matter this situation is caused by an application misbehavior or a missed temporary table cleanup, I'm asking if there is a reasonable way to prevent this situation improving the alerting with:
When vacuum cannot repeatedly perform a transaction fix up in regular multi user mode.
When vacuum cannot perform a transaction fix up in single user mode because there are orphaned tables in the way.
If we had these alerts we would not have lost a database.
Kind regards
Girogio Saviane
Il giorno sab 12 set 2020 alle ore 12:59 Francisco Olarte <folarte@peoplecall.com> ha scritto:
On Sat, Sep 12, 2020 at 10:59 AM Giorgio Saviane <gsaviane@gmail.com> wrote: .... > The question is: can you implement some warning thrown by vacuum/autovacuum in case orphaned temporary tables are in the way? > The connection pooler we are using is the one provided by Tomcat 7. I'm gonna check if it supports DISCARD ALL on close.
From what I've read the thing is, if the pooler does not work correctly ( by issuing discard all or otherwise taking care of temporary tables ), there are few things as the tables are not orphaned on the server side.
What the server sees is someone connects, creates the temporary tables, issues a lot of other commands but does not disconnect, so the tables must be kept alive in case the the session wants to use them again. DISCARD ALL will make them orphans.
