Subject: Re: Fun fact about autovacuum and orphan temp tables
To: Alvaro Herrera <alvherre@2ndquadrant.com>
References: <20160905133440.GA671130@alvherre.pgsql>
Cc: pgsql-hackers@postgresql.org
From: Grigory Smolkin <g.smolkin@postgrespro.ru>
Message-ID: <5b48a2e8-571a-d24f-6a4a-cd5744e6f1dc@postgrespro.ru>
Date: Mon, 5 Sep 2016 17:05:00 +0300
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:45.0) Gecko/20100101
 Thunderbird/45.1.0
MIME-Version: 1.0
In-Reply-To: <20160905133440.GA671130@alvherre.pgsql>
Content-Type: multipart/alternative;
 boundary="------------CACD60254A772A7CCFD55BED"
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org

This is a multi-part message in MIME format.
--------------CACD60254A772A7CCFD55BED
Content-Type: text/plain; charset=windows-1252; format=flowed
Content-Transfer-Encoding: 7bit


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


--------------CACD60254A772A7CCFD55BED
Content-Type: text/html; charset=windows-1252
Content-Transfer-Encoding: 7bit

<html>
  <head>
    <meta content="text/html; charset=windows-1252"
      http-equiv="Content-Type">
  </head>
  <body bgcolor="#FFFFFF" text="#000000">
    <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. Ugly fix here would only
    force backend to terminate properly.<br>
    It will not help at all in cause of server crash or power outage.<br>
    We need a way to tell autovacuum, that we don`t need orphan temp
    tables, so they can be removed using existing 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
    orphan temp 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>
  </body>
</html>

--------------CACD60254A772A7CCFD55BED--