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.
Michael Paquier <michael@paquier.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