From: Tom Lane <tgl@sss.pgh.pa.us>
To: Japin Li <japinli@hotmail.com>
Cc: Michael Paquier <michael@paquier.xyz>
Cc: holly.roberts@starlingbank.com
Cc: peter@eisentraut.org
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #17409: Unable to alter data type of clustered column which is referenced by foreign key
Date: Fri, 11 Mar 2022 13:29:05 -0500
Message-ID: <972551.1647023345@sss.pgh.pa.us> (raw)
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>
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
Attachments:v5-fix-alter-data-type-of-clustered-column.patch (text/x-diff, 3.6K) downloaddiff --git a/src/backend/utils/cache/lsyscache.c b/src/backend/utils/cache/lsyscache.cindex 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 = (Form_pg_constraint) GETSTRUCT(tp);
Oid result;
- result = contup->conindid;+ if (contup->contype == CONSTRAINT_UNIQUE ||+ contup->contype == CONSTRAINT_PRIMARY ||+ contup->contype == CONSTRAINT_EXCLUSION)+ result = contup->conindid;+ else+ result = InvalidOid;
ReleaseSysCache(tp);
return result;
}
diff --git a/src/test/regress/expected/alter_table.out b/src/test/regress/expected/alter_table.outindex 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 = a + 1;
INFO: a=1, b=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);
diff --git a/src/test/regress/sql/alter_table.sql b/src/test/regress/sql/alter_table.sqlindex 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 = 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);
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: tgl@sss.pgh.pa.us, japinli@hotmail.com, 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: <972551.1647023345@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox