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 1hLTUa-0008Rw-1K for pgsql-admin@arkaria.postgresql.org; Tue, 30 Apr 2019 14:12:20 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1hLTUY-0001Jv-PJ for pgsql-admin@arkaria.postgresql.org; Tue, 30 Apr 2019 14:12:18 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1hLTUY-0001G2-Dc for pgsql-admin@lists.postgresql.org; Tue, 30 Apr 2019 14:12:18 +0000 Received: from impout004aa.msg.chrl.nc.charter.net ([47.43.20.28] helo=impout004.msg.chrl.nc.charter.net) by magus.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1hLTUW-00080I-28 for pgsql-admin@postgresql.org; Tue, 30 Apr 2019 14:12:17 +0000 Received: from cerberus.crazybean.net ([24.151.10.150]) by cmsmtp with ESMTP id LTUShOQJ3mrIrLTUThCgWi; Tue, 30 Apr 2019 14:12:13 +0000 Authentication-Results: crazybean.net; none X-Authority-Analysis: v=2.3 cv=eO2nc0h1 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=chEfRKY2JoBsPiyHnkQA:9 a=CjuIK1q_8ugA:10 a=Vx7_FEM89Tl8v89B: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 96c97cbe (TLSv1.2:ECDHE-RSA-AES256-GCM-SHA384:256:NO); Tue, 30 Apr 2019 10:12:12 -0400 (EDT) From: Rui DeSousa Message-Id: Content-Type: multipart/alternative; boundary="Apple-Mail=_92C47BB8-936E-4796-8F8C-3D4192A1D257" 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 10:12:11 -0400 In-Reply-To: Cc: Simon Riggs , Tom Lane , pgsql-admin To: John Lumby References: <19835.1556588662@sss.pgh.pa.us> X-Mailer: Apple Mail (2.3445.104.8) X-CMAE-Envelope: MS4wfO9gDH+28B2bRLVY+yoOH9zjhXmb59hsSC7HA+E83INsUN+6dBHm95Dfbsz5L3klx+3LJwS3VDtzvzZJ6HCnULcY5vsyQDVeWSbjxGcaymaCf81GF9E+ glgq6OufeKIv1KXEjafRxlAaMVn/2MRopdsL9cASvhrszUaiLH/h5evSHoHuv1lQgr3ywdeYCe81WYGQIgRyRCObbgDu7md/xVqF07orsFkRGwM654oLmTrW FsIaxear/ctkROqVL+aXXihKZv9+OT0vfitr0CfHFAXkAVXeaGWermdBEx94Z3gF List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --Apple-Mail=_92C47BB8-936E-4796-8F8C-3D4192A1D257 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=us-ascii > On Apr 30, 2019, at 9:27 AM, John Lumby wrote: >=20 > it does do ANALYZE - in this context is that "DDL"? I would consider that DDL and the application should avoid doing it; = otherwise, you end up with blocking issues where both readers and = writers are blocked unnecessarily. The application issuing analyze on = its own temp tables is fine and non blocking. If you must analyze = tables then I would limit which processes have access to those tables to = avoid blocking issues. I would look at your auto vacuum/analyze settings as oppose to the = application issuing analyze commands as those are going to be a non = blocking option. --Apple-Mail=_92C47BB8-936E-4796-8F8C-3D4192A1D257 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=us-ascii

On Apr 30, 2019, at 9:27 AM, John Lumby <johnlumby@hotmail.com> wrote:

 it does do ANALYZE  - =  in this context is that "DDL"?

I would consider that DDL and the application = should avoid doing it; otherwise, you end up with blocking issues where = both readers and writers are blocked unnecessarily.  The = application issuing analyze on its own temp tables is fine and non = blocking.  If you must analyze tables then I would limit which = processes have access to those tables to avoid blocking = issues.

I = would look at your auto vacuum/analyze settings as oppose to the = application issuing analyze commands as those are going to be a non = blocking option.

= --Apple-Mail=_92C47BB8-936E-4796-8F8C-3D4192A1D257--