[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgresql-admin
Subject:    Re: admin control over cancelling autovacuum when blocked by a lock
From:       Rui DeSousa <rui () crazybean ! net>
Date:       2019-04-30 21:28:55
Message-ID: 7CA68854-CD5F-4E5C-BB34-B3BB11E79D9D () crazybean ! net
[Download RAW message or body]



> 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 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


[prev in list] [next in list] [prev in thread] [next in thread]