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

> On Apr 30, 2019, at 5:03 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> John Lumby <johnlumby(at)hotmail(dot)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 analyze on the same set of tables.

Nov 1 08:06:35 pgdb02 postgres[27386]: [2232-1] dbc1-LOG: process 27386 acquired ShareUpdateExclusiveLock on relation 419539 of 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 analyze it