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: <934540.1647020237@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> <934540.1647020237@sss.pgh.pa.us>
Comments: In-reply-to Tom Lane <tgl@sss.pgh.pa.us>
	message dated "Fri, 11 Mar 2022 12:37:17 -0500"
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="----- =_aaaaaaaaaa0"
Content-ID: <972523.1647023322.0@sss.pgh.pa.us>
Date: Fri, 11 Mar 2022 13:29:05 -0500
Message-ID: <972551.1647023345@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/972551.1647023345%40sss.pgh.pa.us>
Precedence: bulk

------- =_aaaaaaaaaa0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <972523.1647023322.1@sss.pgh.pa.us>

I wrote:
> (I've not figured out yet why the "cluster" step is required to
> make this repro work.)

Oh, of course: the failure only occurs if we think the index is clustered
or a replica-identity index; else we don't store a request to rebuild it.

The attached seems to do the trick.

			regards, tom lane


------- =_aaaaaaaaaa0
Content-Type: text/x-diff;
	name="v5-fix-alter-data-type-of-clustered-column.patch";
	charset="us-ascii"
Content-ID: <972523.1647023322.2@sss.pgh.pa.us>
Content-Description: v5-fix-alter-data-type-of-clustered-column.patch
Content-Transfer-Encoding: quoted-printable

diff --git a/src/backend/utils/cache/lsyscache.c b/src/backend/utils/cache=
/lsyscache.c
index feef999863..1b7e11b93e 100644
--- a/src/backend/utils/cache/lsyscache.c
+++ b/src/backend/utils/cache/lsyscache.c
@@ -1126,8 +1126,13 @@ get_constraint_name(Oid conoid)
  *		Given the OID of a unique, primary-key, or exclusion constraint,
  *		return the OID of the underlying index.
  *
- * Return InvalidOid if the index couldn't be found; this suggests the
- * given OID is bogus, but we leave it to caller to decide what to do.
+ * Returns InvalidOid if the constraint could not be found or is of
+ * the wrong type.
+ *
+ * The intent of this function is to return the index "owned" by the
+ * specified constraint.  Therefore we must check contype, since some
+ * pg_constraint entries (e.g. for foreign-key constraints) store the
+ * OID of an index that is referenced but not owned by the constraint.
  */
 Oid
 get_constraint_index(Oid conoid)
@@ -1140,7 +1145,12 @@ get_constraint_index(Oid conoid)
 		Form_pg_constraint contup =3D (Form_pg_constraint) GETSTRUCT(tp);
 		Oid			result;
 =

-		result =3D contup->conindid;
+		if (contup->contype =3D=3D CONSTRAINT_UNIQUE ||
+			contup->contype =3D=3D CONSTRAINT_PRIMARY ||
+			contup->contype =3D=3D CONSTRAINT_EXCLUSION)
+			result =3D contup->conindid;
+		else
+			result =3D InvalidOid;
 		ReleaseSysCache(tp);
 		return result;
 	}
diff --git a/src/test/regress/expected/alter_table.out b/src/test/regress/=
expected/alter_table.out
index 16e0475663..aabc564e2c 100644
--- a/src/test/regress/expected/alter_table.out
+++ b/src/test/regress/expected/alter_table.out
@@ -4502,6 +4502,20 @@ create trigger xtrig
 update bar1 set a =3D a + 1;
 INFO:  a=3D1, b=3D1
 /* End test case for bug #16242 */
+/* Test case for bug #17409 */
+create table attbl (p1 int constraint pk_attbl primary key);
+create table atref (c1 int references attbl(p1));
+cluster attbl using pk_attbl;
+alter table attbl alter column p1 set data type bigint;
+alter table atref alter column c1 set data type bigint;
+drop table attbl, atref;
+create table attbl (p1 int constraint pk_attbl primary key);
+alter table attbl replica identity using index pk_attbl;
+create table atref (c1 int references attbl(p1));
+alter table attbl alter column p1 set data type bigint;
+alter table atref alter column c1 set data type bigint;
+drop table attbl, atref;
+/* End test case for bug #17409 */
 -- Test that ALTER TABLE rewrite preserves a clustered index
 -- for normal indexes and indexes on constraints.
 create table alttype_cluster (a int);
diff --git a/src/test/regress/sql/alter_table.sql b/src/test/regress/sql/a=
lter_table.sql
index ac894c0602..cce1cb1dd3 100644
--- a/src/test/regress/sql/alter_table.sql
+++ b/src/test/regress/sql/alter_table.sql
@@ -2958,6 +2958,24 @@ update bar1 set a =3D a + 1;
 =

 /* End test case for bug #16242 */
 =

+/* Test case for bug #17409 */
+
+create table attbl (p1 int constraint pk_attbl primary key);
+create table atref (c1 int references attbl(p1));
+cluster attbl using pk_attbl;
+alter table attbl alter column p1 set data type bigint;
+alter table atref alter column c1 set data type bigint;
+drop table attbl, atref;
+
+create table attbl (p1 int constraint pk_attbl primary key);
+alter table attbl replica identity using index pk_attbl;
+create table atref (c1 int references attbl(p1));
+alter table attbl alter column p1 set data type bigint;
+alter table atref alter column c1 set data type bigint;
+drop table attbl, atref;
+
+/* End test case for bug #17409 */
+
 -- Test that ALTER TABLE rewrite preserves a clustered index
 -- for normal indexes and indexes on constraints.
 create table alttype_cluster (a int);

------- =_aaaaaaaaaa0--