Re: Fun fact about autovacuum and orphan temp tables

On Sun, Nov 20, 2016 at 10:42 PM, Michael Paquier
<michael(dot)paquier(at)gmail(dot)com> wrote:
> On Sat, Nov 19, 2016 at 2:16 PM, Michael Paquier
> <michael(dot)paquier(at)gmail(dot)com> wrote:
>> On Fri, Nov 18, 2016 at 1:11 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>>> That might sound adding unnecessary work just for the sake of
>>> paranoia, but I don't think it is. Failures here won't be common, but
>>> since they are entirely automated there will be no human intelligence
>>> available to straighten things out. Barring considerable caution,
>>> we'll just enter a flaming death spiral.
>>
>> Thinking more paranoid, an extra way to enter in this flaming death
>> spiral is to not limit the maximum number of failures authorized when
>> dropping a set of orphaned tables and transactions fail multiple
>> times. This is basically not important as the relation on which the
>> drop failed gets dropped from the list but failing on each one of them
>> is a good way to slow down autovacuum, so putting a limit of say 10
>> transactions failing is I think really important.
>
> By the way, when hacking this patch I asked myself three questions:
> 1) How many objects should be dropped per transaction? 50 sounds like
> a fine number to me so the patch I sent is doing so. This should
> definitely not be more than the default for max_locks_per_transaction,
> aka 64. Another thing to consider would be to use a number depending
> on max_locks_per_transaction, like half of it.
> 2) How many transaction failures should autovacuum forgive? The patch
> uses 10. Honestly I put that number out of thin air.
> 3) Should the drop of orphaned tables be done for a wraparound
> autovacuum? Obviously, the answer is no. It is vital not to consume
> transaction XIDs in this case. The patch I sent is dropping the
> objects even for a wraparound job, that's easily switchable.

I don't think that you should skip it in the wraparound case, because
it might be one of the temp tables that is threatening wraparound.

I've committed the patch after doing some work on the comments.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company