Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1hLZS8-0005V2-Sp for pgsql-admin@arkaria.postgresql.org; Tue, 30 Apr 2019 20:34:13 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1hLZS7-0003EA-OC for pgsql-admin@arkaria.postgresql.org; Tue, 30 Apr 2019 20:34:11 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1hLZS7-00037J-Ax for pgsql-admin@lists.postgresql.org; Tue, 30 Apr 2019 20:34:11 +0000 Received: from impout008aa.msg.chrl.nc.charter.net ([47.43.20.32] helo=impout008.msg.chrl.nc.charter.net) by makus.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1hLZS4-00012T-QR for pgsql-admin@postgresql.org; Tue, 30 Apr 2019 20:34:09 +0000 Received: from cerberus.crazybean.net ([24.151.10.150]) by cmsmtp with ESMTP id LZS3hFUKlMMJtLZS3hqzGb; Tue, 30 Apr 2019 20:34:08 +0000 Authentication-Results: crazybean.net; none X-Authority-Analysis: v=2.3 cv=QtjqIm6d c=1 sm=1 tr=0 a=D2HTw/M6/8MmcHighGO7rg==:117 a=D2HTw/M6/8MmcHighGO7rg==:17 a=jpOVt7BSZ2e4Z31A5e1TngXxSK0=:19 a=xqWC_Br6kY4A:10 a=oexKYjalfGEA:10 a=69EAbJreAAAA:8 a=pigCZx8T_bOJMOdH0N4A:9 a=QEXdDO2ut3YA:10 a=3neAToBv-55y4_rT1KEA:9 a=X2LJl0xZl_W68-Tw:21 a=_W_S_7VecoQA:10 Received: from [IPv6:2001:470:e117:4:8c6b:d27f:4211:fbca] (2001:470:e117:4:8c6b:d27f:4211:fbca [IPv6:2001:470:e117:4:8c6b:d27f:4211:fbca]) by cerberus.crazybean.net (OpenSMTPD) with ESMTPSA id bb82983d (TLSv1.2:ECDHE-RSA-AES256-GCM-SHA384:256:NO); Tue, 30 Apr 2019 16:34:06 -0400 (EDT) From: Rui DeSousa Message-Id: Content-Type: multipart/alternative; boundary="Apple-Mail=_45E3FD22-6F5C-4CB7-BCE2-DDE618B97F16" Mime-Version: 1.0 (Mac OS X Mail 12.4 \(3445.104.8\)) Subject: Re: admin control over cancelling autovacuum when blocked by a lock Date: Tue, 30 Apr 2019 16:34:06 -0400 In-Reply-To: Cc: pgsql-admin , Simon Riggs , Tom Lane To: John Lumby References: <19835.1556588662@sss.pgh.pa.us> X-Mailer: Apple Mail (2.3445.104.8) X-CMAE-Envelope: MS4wfNJG4ttf68rYWIunZRu5krQ2z41LSEyOwM7btbmtvrULm9zsgIEuml4L4Wylw0vFrnWsPmes6m1qeFWb6aqWQFP7W0Sf8VJf7QveqFKv81vwk7IJWma5 LOh+NaA02OITJQPykds7Btwgs7MZPr9aLRvzQThpe2h+aRmYZZR4obcUaqQWMLLikYJPj9C1iRTj8Vm6UfMZPfLdgLMcO55N6LfHxcO8wltpk8vveLmMOrN9 Up7eWiD9/ux1JOpLkIJY0Q9RuhWKmLizsnA6Z8U6c3Lv/8EhaEt42aYdvvXFCxWq List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --Apple-Mail=_45E3FD22-6F5C-4CB7-BCE2-DDE618B97F16 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 > On Apr 30, 2019, at 4:04 PM, John Lumby wrote: >=20 > surely the user would prefer that the=20 > ANALYZE would wait until after > the autovacuum has finished Not really=E2=80=A6 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 :)=E2=80=A6=20 I had the follow problem occur; which I hope will illustrate why you = wouldn=E2=80=99t what do what you are recommending.=20 I explicitly do not allow the application to issue DDL including the = analyze command against the base tables; however, developers are = developers and they will try anything at least once. The application = was coded with an analyze command and the result was huge blocking = issues. 1. Application issue analyze 2. Analyze waits on exclusive lock 3. As, there is already a long running query running against the table 4. New readers; get blocked and are now waiting behind the analyze = command. 5. Writes are also blocked and waiting behind the analyze command. 6. Long running query completes. 7. Analyze command fails due to lack of permissions. 8. Application retries; rinse and repeat. * 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. =20 --Apple-Mail=_45E3FD22-6F5C-4CB7-BCE2-DDE618B97F16 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8

On Apr 30, 2019, at 4:04 PM, John Lumby <johnlumby@hotmail.com> wrote:

surely the user would prefer = that the 
ANALYZE would wait until = after
the = autovacuum has finished

Not really=E2=80=A6 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 :)=E2=80=A6 

I had the follow problem occur; which I = hope will illustrate why you wouldn=E2=80=99t what do what you are = recommending. 

I explicitly do not allow the application to issue DDL = including the analyze command against the base tables; however, = developers are developers and they will try anything at least once. =  The application was coded with an analyze command and the result = was huge blocking issues.

1. Application issue analyze
2. Analyze = waits on exclusive lock
3. As, there is already a = long running query running against the table
4. New = readers; get blocked and are now waiting behind the analyze = command.
5. Writes are also blocked and waiting = behind the analyze command.
6. Long running query = completes.
7. Analyze command fails due to lack of = permissions.
8. Application retries; rinse and = repeat.

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



= --Apple-Mail=_45E3FD22-6F5C-4CB7-BCE2-DDE618B97F16--