Re: BUG #16614: Stale temporary objects makes vacuum ineffective when 1 million transactions remain

Giorgio.

On Sat, Sep 12, 2020 at 1:57 PM Giorgio Saviane <gsaviane(at)gmail(dot)com> wrote:
... It seems I've hit a new gmail "bug-feature" and it has stripped
numbering on the reply, doing it by hand, misnumberings are mine.....

> The sequence of events that led our databases to the wraparound issue is the following:
>
> 1.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.
> 2.Since sessions are kept alive with stale temporary tables the autovacuum process cannot do maintenance on transaction ids.
> 3.Since we have no evidence on what is going on, the database reaches the 1 million stop limit to avoid transaction wraparound.
> 4.The application gets then out of order. We stop the Postgres server gracefully and we enter single user mode to run the vacuum
> 5.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'
> 6.We notice the presence of orphaned tables. Sessions are gone at this point, so they are for sure orphaned.
> 7.If we manually drop the orphaned tables and run vacuum again the warning message disappears and the database is restored to normal functioning.

I see your point now. IMO postgres should either have dropped the
temporary tables in 4., graceful shutdown or left them in a state
where thay could be vacuumed, I did not notice the graceful shutdown
step before.

Just one question, PG documents shutdown as smart, fast and immediate,
which one are you using for "graceful"? ( anyway, after a server
restart I would expect tables to be vacuumable, even if it was a power
loss followed by recovery ), they are not supposed to outlive the
session and as you say the sessions are gone.

Francisco Olarte.