From: Tom Lane <tgl@sss.pgh.pa.us>
To: Simon Riggs <simon@2ndquadrant.com>
cc: John Lumby <johnlumby@hotmail.com>,
        pgsql-admin <pgsql-admin@postgresql.org>
Subject: Re: admin control over cancelling autovacuum when blocked by a lock
In-reply-to: 
 <CANP8+jJSysBHF_QRdYmT-iKQDy4yw8OaqgiLoNJPdjTX1bJB8w@mail.gmail.com>
References: 
 <DM6PR06MB556272F9CB7629B10CE86829A3390@DM6PR06MB5562.namprd06.prod.outlook.com>
 <CANP8+jJSysBHF_QRdYmT-iKQDy4yw8OaqgiLoNJPdjTX1bJB8w@mail.gmail.com>
Comments: In-reply-to Simon Riggs <simon@2ndquadrant.com>
	message dated "Tue, 30 Apr 2019 00:15:08 +0100"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <19578.1556588264.1@sss.pgh.pa.us>
Date: Mon, 29 Apr 2019 21:37:44 -0400
Message-ID: <19579.1556588264@sss.pgh.pa.us>
Precedence: bulk

Simon Riggs <simon@2ndquadrant.com> writes:
> On Mon, 29 Apr 2019 at 22:02, John Lumby <johnlumby@hotmail.com> wrote:
>> If a regular backend worker becomes blocked waiting for a lock held by an
>> autovacuum worker,
>> then ,  depending on some decision made by deadlock detector,
>> the autovacuum worker may be signalled to terminate
>> Is there any configuration parameter which controls this?
>> including deferring or even completely eliminating this cancelling?

> Why would you want this?
> If the autovacuum is cancelled, it will re-execute again in the near
> future, once your interrupting SQL has released locks.

I think the OP is looking for the opposite, ie prevent autovacuum from
being kicked off the lock.  It already is resistant to that if we're
doing an anti-wraparound vacuum, so maybe the simplest answer is to
tighten up autovacuum_freeze_max_age.  (Whether that's a good idea
is a different question.)  Or you could just launch a manual vacuum.

			regards, tom lane