From: Tom Lane <tgl@sss.pgh.pa.us>
To: Japin Li <japinli@hotmail.com>
cc: Michael Paquier <michael@paquier.xyz>, holly.roberts@starlingbank.com,
        peter@eisentraut.org, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #17409: Unable to alter data type of clustered column which
 is referenced by foreign key
In-reply-to: <933033.1647019404@sss.pgh.pa.us>
References: <17409-52871dda8b5741cb@postgresql.org>
 <MEYP282MB16691B29F4FEA0CC45CD5FFAB6369@MEYP282MB1669.AUSP282.PROD.OUTLOOK.COM>
 <ME3P282MB1667E2B286DA22A16FD8A8FFB6369@ME3P282MB1667.AUSP282.PROD.OUTLOOK.COM>
 <ME3P282MB1667F5A392BB4F26E6B0D82AB6369@ME3P282MB1667.AUSP282.PROD.OUTLOOK.COM>
 <Yg8gGrJebITuPKu8@paquier.xyz>
 <MEYP282MB1669405BB9B46FBED34EB309B6379@MEYP282MB1669.AUSP282.PROD.OUTLOOK.COM>
 <755474.1646959065@sss.pgh.pa.us>
 <MEYP282MB1669CD6C93FD1DCA85CAB2C1B60C9@MEYP282MB1669.AUSP282.PROD.OUTLOOK.COM>
 <933033.1647019404@sss.pgh.pa.us>
Comments: In-reply-to Tom Lane <tgl@sss.pgh.pa.us>
	message dated "Fri, 11 Mar 2022 12:23:24 -0500"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <934539.1647020237.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Fri, 11 Mar 2022 12:37:17 -0500
Message-ID: <934540.1647020237@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/934540.1647020237%40sss.pgh.pa.us>
Precedence: bulk

I wrote:
> ... The old behavior is what
> ATExecAlterColumnType wants, I judge.  As this stands, we will
> rebuild indexes that don't need to be rebuilt, and indeed might
> be on other tables altogether from the one that is being modified
> (which opens all sorts of potential locking problems).

Oh, well, not so much:

regression=3D# create table atref (p1 int constraint pk_atref primary key)=
;
CREATE TABLE
regression=3D# create table attbl (c1 int, constraint fk_atref foreign key=
 (c1) references atref(p1));
CREATE TABLE
regression=3D# cluster atref using pk_atref;
CLUSTER
regression=3D# alter table attbl alter column c1 set data type bigint;
ERROR:  "pk_atref" is not an index for table "attbl"

Still, that's just as unpleasant as the originally-reported case.
(I've not figured out yet why the "cluster" step is required to
make this repro work.)

			regards, tom lane