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@gmail.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
offact 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
transactionids.
 
> 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
thevacuum
 
> 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
restoredto 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.