We recently ran into a funny situation, where autovacuum would not remove very old temp tables. The relfrozenxid of those tables was about to reach the max freeze age, so monitoring started to complain. It turned out that autovacuum saw that the backendId was used by a live backend ... but that session was in reality not using those temp tables, and had not used any temp table at all. They were left-overs from a crash months ago, and since the session was not using temp tables, they had not been removed. DISCARD ALL may have run, but had no effect.
I think the best way to fix this is to call RemoveTempRelations() unconditionally at session start (without doing the rest of the temp table setup, just the removal.) In versions earlier than pg11, related issues occur if you have a crash with autovacuum off and/or workers disabled, and temp tables are leaked in backendID 1 or 2; then start with normal values. In that case, those backendIDs are used by the logical replication launcher and the autovacuum launcher, so autovacuum does not remove them either. This was fixed in PG11 inadvertently by this commit: https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=943576bddcb52971041d9f5f806789921fa107ee The reason the commit fixes it is that now the databaseID of the PGPROC entry is compared to the temp table's database; and for those worker processes, the DatabaseId is InvalidOid so it all works out. This isn't a terribly interesting bug, as restarting with changed worker/autovacuum options after a crash that happens to leak temp tables should be quite rare. But anyway we can fix this second issue in prior branches by adding a comparison to databaseId to the existing 'if' test in autovacuum; easy enough, no compatibility concerns. This should also cover the case that one session crashes leaking temp tables, then the same session connects to a different database for a long time. (I didn't verify this last point to be a real problem.) -- Álvaro Herrera https://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services