Re: Fun fact about autovacuum and orphan temp tables

<br /><div class="moz-cite-prefix">On 09/05/2016 04:34 PM, Alvaro Herrera wrote:<br /></div><blockquote
cite="mid:20160905133440.GA671130@alvherre.pgsql"type="cite"><pre wrap="">Grigory Smolkin wrote:
 

</pre><blockquote type="cite"><pre wrap="">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.
</pre></blockquote><pre wrap="">
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 ...

</pre></blockquote><br /> Thank you for your interest in this problem.<br /> I dont think this is a source of problem.
Uglyfix here would only force backend to terminate properly.<br /> It will not help at all in cause of server crash or
poweroutage.<br /> We need a way to tell autovacuum, that we don`t need orphan temp tables, so they can be removed
usingexisting routine.<br /><br /> The least invasive solution would be to have a guc, something like
'keep_orphan_temp_tables'with boolean value.<br /> Which would determine a autovacuum worker policy toward encountered
orphantemp tables.<br /><br /><pre class="moz-signature" cols="72">-- 
 
Grigory Smolkin
Postgres Professional: <a class="moz-txt-link-freetext"
href="http://www.postgrespro.com">http://www.postgrespro.com</a>
The Russian Postgres Company</pre>