Re: Fun fact about autovacuum and orphan temp tables

Grigory Smolkin wrote:

Funny part is that it never drops them. So when backend is finally
terminated, it tries to drop them and fails with error:

FATAL:  out of shared memory
HINT:  You might need to increase max_locks_per_transaction

If I understand that rigth, we are trying to drop all these temp tables in
one transaction and running out of locks to do so.

Hmm, yeah, I suppose it does that, and it does seem pretty inconvenient.
It is certainly pointless to hold onto these locks for temp tables.  I
wonder how ugly would be to fix this problem ...

Thank you for your interest in this problem.
I dont think this is a source of problem. Ugly fix here would only force backend to terminate properly.
It will not help at all in cause of server crash or power outage.
We need a way to tell autovacuum, that we don`t need orphan temp tables, so they can be removed using existing routine.

The least invasive solution would be to have a guc, something like 'keep_orphan_temp_tables' with boolean value.
Which would determine a autovacuum worker policy toward encountered orphan temp tables.

-- 
Grigory Smolkin
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company