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 1kAcqr-00078A-N4 for pgsql-bugs@arkaria.postgresql.org; Tue, 25 Aug 2020 17:35:17 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1kAcqq-0001II-LR for pgsql-bugs@arkaria.postgresql.org; Tue, 25 Aug 2020 17:35:16 +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 1kAcqq-0001IB-FM for pgsql-bugs@lists.postgresql.org; Tue, 25 Aug 2020 17:35:16 +0000 Received: from momjian.us ([72.94.173.45]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kAcqo-0006qn-2T for pgsql-bugs@postgresql.org; Tue, 25 Aug 2020 17:35:15 +0000 Received: from bruce by momjian.us with local (Exim 4.92) (envelope-from ) id 1kAcql-000628-Hy; Tue, 25 Aug 2020 13:35:11 -0400 Date: Tue, 25 Aug 2020 13:35:11 -0400 From: Bruce Momjian To: Tom Lane Cc: Michael Paquier , Robins Tharakan , pgsql-bugs@postgresql.org Subject: Re: ALTER TABLE ALTER COLUMN SET TYPE crash Message-ID: <20200825173511.GA20679@momjian.us> References: <20200825060511.GF2017@paquier.xyz> <20200825155544.GG32540@momjian.us> <2745154.1598372060@sss.pgh.pa.us> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <2745154.1598372060@sss.pgh.pa.us> User-Agent: Mutt/1.10.1 (2018-07-13) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk On Tue, Aug 25, 2020 at 12:14:20PM -0400, Tom Lane wrote: > I think the nature of the problem (and Robins' other report too) is pretty > clear. We have a SQL or plpgsql function that's trying to access a table > that is inconsistent during an ALTER TABLE operation. The function would > be locked out from seeing that transient state if it were in another > session, thanks to normal locking rules; but the lock acquisition rules > don't prevent same-session accesses. > > ALTER TABLE and related utility commands contain guards against the > reverse form of this problem: CheckTableNotInUse will bitch if there's > some already-active outer query referencing the table. But we haven't > thought about the possibility of one of these commands executing > user-defined code midway through. That does explain why it fails on the _second_ time through those lower-level functions. -- Bruce Momjian https://momjian.us EnterpriseDB https://enterprisedb.com The usefulness of a cup is in its emptiness, Bruce Lee