Re: Fun fact about autovacuum and orphan temp tables

On Sat, Nov 19, 2016 at 2:16 PM, Michael Paquier
<michael.paquier@gmail.com> wrote:
> On Fri, Nov 18, 2016 at 1:11 PM, Robert Haas <robertmhaas@gmail.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.
-- 
Michael