Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pFd5M-0000Xh-Lz for pgsql-general@arkaria.postgresql.org; Wed, 11 Jan 2023 15:32:16 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pFd5L-0000FV-9l for pgsql-general@arkaria.postgresql.org; Wed, 11 Jan 2023 15:32:15 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pFd5K-0000FM-Vc for pgsql-general@lists.postgresql.org; Wed, 11 Jan 2023 15:32:14 +0000 Received: from sss.pgh.pa.us ([66.207.139.130]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pFd5I-0005ne-Nx for pgsql-general@lists.postgresql.org; Wed, 11 Jan 2023 15:32:13 +0000 Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.pgh.pa.us (8.15.2/8.15.2) with ESMTP id 30BFWAcU493123; Wed, 11 Jan 2023 10:32:10 -0500 From: Tom Lane To: Fred Habash cc: pgsql-general@lists.postgresql.org Subject: Re: Autovacuum Hung Due to Bufferpin In-reply-to: References: Comments: In-reply-to Fred Habash message dated "Wed, 11 Jan 2023 08:59:38 -0500" MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-ID: <493121.1673451130.1@sss.pgh.pa.us> Date: Wed, 11 Jan 2023 10:32:10 -0500 Message-ID: <493122.1673451130@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk Fred Habash writes: > pg_locks shows no blockers while this is happening. This view shows a > constant 13 sessions running SELECT statements on this table posting > AccessShareLock. Of course, these is also the AV sessions > with ShareUpdateExclusiveLock > ... > Why is AV blocked by bufferpin given the fact that this table does not get > an DML changes. It is purely read only. What can be done to resolve this? Apparently there has been some DML on it in the past, leaving dead rows that vacuum now needs to clean up --- but it needs a transient buffer lock for long enough to do that. If you have a constant stream of readers it will never be able to get that lock. You'll need to find a way to momentarily block those readers. regards, tom lane