Re: admin control over cancelling autovacuum when blocked by a lock

On 04/30/2019 04:34 PM, Rui DeSousa wrote:
>
>
>> On Apr 30, 2019, at 4:04 PM, John Lumby <johnlumby(at)hotmail(dot)com
>> <mailto:johnlumby(at)hotmail(dot)com>> wrote:
>>
>> surely the user would prefer that the
>> ANALYZE would wait until after
>> the autovacuum has finished
>
> Not really… analyze takes an exclusive lock; I believe. The result is
> that readers/analyze will block other readers and writes which is bad
> for concurrency. Readers should never be blocked :)…

Apparently not - at least, not on the table being analyzed : from
the 11.2 Reference :

|ANALYZE| requires only a read lock on the target table, so it can run
in parallel with other activity on the table.

Maybe it takes an exclusive lock on the catalog(?), but (if it does)
that should be brief at the very end
and not conflict with queries except rarely with the planner reading the
catalog.

>
> I had the follow problem occur; which I hope will illustrate why you
> wouldn’t what do what you are recommending.
>
> [ ... ]
> * The real response is we had to kill the session attempting to issue
> analyze and issue a hot fix otherwise the blocking would cause all
> sorts of application issues.
>
>
>