Re: Fun fact about autovacuum and orphan temp tables

On 09/05/2016 04:34 PM, Alvaro Herrera wrote:
> 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