From: Tom Lane <tgl@sss.pgh.pa.us>
To: Japin Li <japinli@hotmail.com>
cc: adrien.gilmore+pg@gmail.com, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #17376: Adding unique column with a function() default
 results in "could not read block 0 in file" error
In-reply-to: 
 <MEYP282MB1669B9CC5478AF0421227FA8B65C9@MEYP282MB1669.AUSP282.PROD.OUTLOOK.COM>
References: <17376-07b867ccc817828f@postgresql.org>
 <MEYP282MB166905F8F98ED50AD619CB54B65B9@MEYP282MB1669.AUSP282.PROD.OUTLOOK.COM>
 <1196187.1642786604@sss.pgh.pa.us>
 <MEYP282MB1669B9CC5478AF0421227FA8B65C9@MEYP282MB1669.AUSP282.PROD.OUTLOOK.COM>
Comments: In-reply-to Japin Li <japinli@hotmail.com>
	message dated "Sat, 22 Jan 2022 08:18:14 +0800"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <1318099.1642824925.1@sss.pgh.pa.us>
Date: Fri, 21 Jan 2022 23:15:25 -0500
Message-ID: <1318100.1642824925@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/1318100.1642824925%40sss.pgh.pa.us>
Precedence: bulk

Japin Li <japinli@hotmail.com> writes:
> On Sat, 22 Jan 2022 at 01:36, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> However, I fear we don't have adequate infrastructure to tell which
>> table accesses *are* part of the ALTER TABLE machinery and which aren't.

> Right.

>> Maybe it'd be sufficient to check for an active ALTER TABLE in the
>> parser, but I'm not sure.

> Do you mean check the table is accessed by ALTER TABLE when calling SPI to
> execute the function?  How can we get the table that is be accessed by
> ALTER TABLE in parser?

The reason there's not a problem like this with respect to cross-session
accesses is that the exclusive lock acquired by ALTER TABLE prevents
other sessions from touching the table.  Conversely, the reason we do
have a problem here is that such locks don't prevent accesses within
our own session.  So a rough sketch for fixing this might be "at
the point where we acquire a lock on some table for a new statement,
also check to see if some internal-to-our-session operation has
declared a need for exclusive access".  The problem is that we don't
have a well-defined line as to what's part of the ALTER and what
is not.  For example, I recall that ALTER TABLE handles index rebuilding
by creating textual ALTER ADD INDEX commands, and parsing and executing
those.  How can we distinguish that behavior from a SQL function within
an index or CHECK constraint trying to execute ALTER ADD INDEX?

			regards, tom lane