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

The thing is that we realized this only when the wraparound stop limit was
reached, that seems an inevitable cul-de-sac if your application is leaking
temporary tables. Even worse, when you try to vacuum in single user mode
there is nothing that tells you "drop those bloody temp tables". It keeps
telling you that less than 1 mln transactions are remaining, without
explaining why. We lost an entire database of one of our customers before
being aware of the fact that vacuum is ineffective with temp tables.
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.

Kind regards

P.S. sorry for replying here, couldn't get how to reply on the thread
publicly.

Il giorno ven 11 set 2020 alle ore 16:21 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> ha
scritto:

> Michael Paquier <michael(at)paquier(dot)xyz> writes:
> > Yeah. 11 and newer versions have been made even more aggressive with
> > the cleanup of orphaned tables in autovacuum, particularly the case
> > where a backend reuses the ID of a past session that crashed, leaving
> > behind some temporary tables. Perhaps that was the case here?
>
> Off-list, the OP indicated that the problem is actually of the other
> category, ie the problematic tables belong to extremely long-lived
> sessions that are managed by a connection pooler. I don't quite
> understand why the pooler isn't issuing DISCARD ALL between clients,
> but anyway it seems that PG is operating as designed here.
>
> regards, tom lane
>