Subject: [PATCH] Only process inheritance for primary keys, not other constraint types

From: Alvaro Herrera <[email-masked]>
Date: Fri, 1 Sep 2023 13:41:09 +0200

---
 src/backend/commands/tablecmds.c      | 12 +++++++++---
 src/test/regress/expected/inherit.out | 25 ++++++++++++++++++++++++-
 src/test/regress/sql/inherit.sql      | 15 ++++++++++++++-
 3 files changed, 47 insertions(+), 5 deletions(-)

diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index d097da3c78..0339774672 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -8906,19 +8906,25 @@ ATPrepAddPrimaryKey(List **wqueue, Relation rel, AlterTableCmd *cmd,
 {
 	List	   *children;
 	List	   *newconstrs = NIL;
 	ListCell   *lc;
-	IndexStmt  *stmt;
+	IndexStmt  *indexstmt;
+
+	/* No work if not creating a primary key */
+	if (!IsA(cmd->def, IndexStmt))
+		return;
+	indexstmt = castNode(IndexStmt, cmd->def);
+	if (!indexstmt->primary)
+		return;
 
 	/* No work if no legacy inheritance children are present */
 	if (rel->rd_rel->relkind != RELKIND_RELATION ||
 		!rel->rd_rel->relhassubclass)
 		return;
 
 	children = find_inheritance_children(RelationGetRelid(rel), lockmode);
 
-	stmt = castNode(IndexStmt, cmd->def);
-	foreach(lc, stmt->indexParams)
+	foreach(lc, indexstmt->indexParams)
 	{
 		IndexElem  *elem = lfirst_node(IndexElem, lc);
 		Constraint *nnconstr;
 
diff --git a/src/test/regress/expected/inherit.out b/src/test/regress/expected/inherit.out
index dae61b9a0b..59583e1e41 100644
--- a/src/test/regress/expected/inherit.out
+++ b/src/test/regress/expected/inherit.out
@@ -2308,9 +2308,32 @@ create table inh_child (a int primary key);
 alter table inh_child inherit inh_parent;		-- nope
 ERROR:  column "a" in child table must be marked NOT NULL
 alter table inh_child alter a set not null;
 alter table inh_child inherit inh_parent;		-- now it works
-drop table inh_parent, inh_child;
+-- don't interfere with other types of constraints
+alter table inh_parent add constraint inh_parent_excl exclude ((1) with =);
+alter table inh_parent add constraint inh_parent_uq unique (a);
+alter table inh_parent add constraint inh_parent_fk foreign key (a) references inh_parent (a);
+create table inh_child2 () inherits (inh_parent);
+create table inh_child3 (like inh_parent);
+alter table inh_child3 inherit inh_parent;
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint
+ where conrelid::regclass::text in ('inh_parent', 'inh_child', 'inh_child2', 'inh_child3')
+ order by 2, 1;
+  conrelid  |        conname        | contype | coninhcount | conislocal 
+------------+-----------------------+---------+-------------+------------
+ inh_child2 | inh_child2_a_not_null | n       |           1 | f
+ inh_child3 | inh_child3_a_not_null | n       |           1 | t
+ inh_child  | inh_child_a_not_null  | n       |           1 | t
+ inh_child  | inh_child_pkey        | p       |           0 | t
+ inh_parent | inh_parent_excl       | x       |           0 | t
+ inh_parent | inh_parent_fk         | f       |           0 | t
+ inh_parent | inh_parent_pkey       | p       |           0 | t
+ inh_parent | inh_parent_uq         | u       |           0 | t
+(8 rows)
+
+drop table inh_parent, inh_child, inh_child2, inh_child3;
 --
 -- test multi inheritance tree
 --
 create table inh_parent(f1 int not null);
diff --git a/src/test/regress/sql/inherit.sql b/src/test/regress/sql/inherit.sql
index 9ceaec1d78..abe8602682 100644
--- a/src/test/regress/sql/inherit.sql
+++ b/src/test/regress/sql/inherit.sql
@@ -845,9 +845,22 @@ create table inh_parent (a int primary key);
 create table inh_child (a int primary key);
 alter table inh_child inherit inh_parent;		-- nope
 alter table inh_child alter a set not null;
 alter table inh_child inherit inh_parent;		-- now it works
-drop table inh_parent, inh_child;
+
+-- don't interfere with other types of constraints
+alter table inh_parent add constraint inh_parent_excl exclude ((1) with =);
+alter table inh_parent add constraint inh_parent_uq unique (a);
+alter table inh_parent add constraint inh_parent_fk foreign key (a) references inh_parent (a);
+create table inh_child2 () inherits (inh_parent);
+create table inh_child3 (like inh_parent);
+alter table inh_child3 inherit inh_parent;
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint
+ where conrelid::regclass::text in ('inh_parent', 'inh_child', 'inh_child2', 'inh_child3')
+ order by 2, 1;
+
+drop table inh_parent, inh_child, inh_child2, inh_child3;
 
 --
 -- test multi inheritance tree
 --
-- 
2.39.2

--miijhugul73fa3rt--