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 1kAu6w-0008KN-HK for pgsql-bugs@arkaria.postgresql.org; Wed, 26 Aug 2020 12:01:02 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1kAu6v-00083o-83 for pgsql-bugs@arkaria.postgresql.org; Wed, 26 Aug 2020 12:01:01 +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 1kAu6v-00083h-1m for pgsql-bugs@lists.postgresql.org; Wed, 26 Aug 2020 12:01:01 +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 1kAu6o-00005e-TP for pgsql-bugs@postgresql.org; Wed, 26 Aug 2020 12:01:00 +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 07QC0nkW2810131; Wed, 26 Aug 2020 08:00:49 -0400 From: Tom Lane To: Michael Paquier cc: Bruce Momjian , Robins Tharakan , pgsql-bugs@postgresql.org Subject: Re: ALTER TABLE ALTER COLUMN SET TYPE crash In-reply-to: <20200826073001.GH2017@paquier.xyz> References: <20200825060511.GF2017@paquier.xyz> <20200825155544.GG32540@momjian.us> <2745154.1598372060@sss.pgh.pa.us> <20200825173511.GA20679@momjian.us> <20200826073001.GH2017@paquier.xyz> Comments: In-reply-to Michael Paquier message dated "Wed, 26 Aug 2020 16:30:01 +0900" MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-ID: <2810129.1598443249.1@sss.pgh.pa.us> Date: Wed, 26 Aug 2020 08:00:49 -0400 Message-ID: <2810130.1598443249@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk Michael Paquier writes: > On Tue, Aug 25, 2020 at 01:35:11PM -0400, Bruce Momjian wrote: >> 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. > There are already some safeguards to prevent directly the use of > aggregates in USING, and here we have a function that itself calls an > aggregate on the table. That's an independent issue though. It stems mostly from not wanting to use the full-scale planner or executor for subexpressions of utility commands. (Of course, the PL function handler does so internally, but that's its problem.) The core issue here is that the table's catalog entries, as visible within this transaction, don't match what's in its disk file. ALTER TABLE knows that and is careful not to touch the old table using the new rowtype --- but nothing else knows that. So I think we need to block other code from touching the table-under-modification. As you say, there's not any existing infrastructure that will serve for that directly. We might be able to invent something comparable to the existing relcache entry refcount, but counting exclusive opens ("there can be only one"). regards, tom lane