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

> On Apr 30, 2019, at 5:03 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> John Lumby <johnlumby@hotmail.com> writes:
>> On 04/30/2019 04:34 PM, Rui DeSousa wrote:
>>> 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.
>
> That's kind of inaccurate.  A moment's experimentation will show you
> that what it really takes is ShareUpdateExclusiveLock, which it does
> mostly to ensure that no other ANALYZE is running concurrently on the
> same table.  That lock type doesn't block ordinary reads or writes
> on the table.  It probably conflicts with autovacuum though ...
>
>             regards, tom lane


Looking back at some notes from 2017.  It was certain readers/writes that where being blocked as they we all issuing
analyzeon the same set of tables. 

Nov  1 08:06:35 pgdb02 postgres[27386]: [2232-1] dbc1-LOG:  process 27386 acquired ShareUpdateExclusiveLock on relation
419539of database 417843 after 1001.837 ms 
Nov  1 08:06:35 pgdb02 postgres[27386]: [2232-2] dbc1-CONTEXT:  SQL statement "ANALYZE xxx.t1"
...
Nov  1 08:06:35 pgdb02 postgres[27386]: [2233-1] dbc1-WARNING:  skipping "yyy" --- only table or database owner can
analyzeit