Re: Constraint's NO INHERIT option is ignored in CREATE TABLE LIKE statement

[Amit Langote]

On Thu, Feb 20, 2020 at 8:02 AM Tom Lane <t...@sss.pgh.pa.us> wrote:
> Ildar Musin <il...@adjust.com> writes:
> > My colleague Chris Travers discovered something that looks like a bug.
> > Let's say we have a table with a constraint that is declared as NO INHERIT.
> > ...
> > Now when we want to make a copy of the table structure into a new table
> > the `NO INHERIT` option is ignored.
>
> Hm, I agree that's a bug, since the otherwise-pretty-detailed CREATE TABLE
> LIKE documentation makes no mention of such a difference between original
> and cloned constraint.

By the way, partitioned tables to not allow constraints that are
marked NO INHERIT.  For example,

create table b (a int check (a > 0) no inherit) partition by list (a);
ERROR:  cannot add NO INHERIT constraint to partitioned table "b"

We must ensure that partitioned tables don't accidentally end up with
one via CREATE TABLE LIKE path.  I tested Ildar's patch and things
seem fine, but it might be better to add a test.  Attached updated
patch with that taken care of.

Thanks,
Amit

diff --git a/src/backend/parser/parse_utilcmd.c 
b/src/backend/parser/parse_utilcmd.c
index ee2d2b54a1..38d8849fdb 100644
--- a/src/backend/parser/parse_utilcmd.c
+++ b/src/backend/parser/parse_utilcmd.c
@@ -1165,6 +1165,7 @@ transformTableLikeClause(CreateStmtContext *cxt, 
TableLikeClause *table_like_cla
                        n->contype = CONSTR_CHECK;
                        n->location = -1;
                        n->conname = pstrdup(ccname);
+                       n->is_no_inherit = 
tupleDesc->constr->check[ccnum].ccnoinherit;
                        n->raw_expr = NULL;
                        n->cooked_expr = nodeToString(ccbin_node);
                        cxt->ckconstraints = lappend(cxt->ckconstraints, n);
diff --git a/src/test/regress/expected/create_table_like.out 
b/src/test/regress/expected/create_table_like.out
index 94d48582db..632a970567 100644
--- a/src/test/regress/expected/create_table_like.out
+++ b/src/test/regress/expected/create_table_like.out
@@ -405,3 +405,17 @@ DROP TYPE ctlty1;
 DROP VIEW ctlv1;
 DROP TABLE IF EXISTS ctlt4, ctlt10, ctlt11, ctlt11a, ctlt12;
 NOTICE:  table "ctlt10" does not exist, skipping
+/* LIKE must respect NO INHERIT property of constraints */
+CREATE TABLE noinh_con_copy (a int CHECK (a > 0) NO INHERIT);
+CREATE TABLE noinh_con_copy1 (LIKE noinh_con_copy INCLUDING ALL);
+\d noinh_con_copy1
+          Table "public.noinh_con_copy1"
+ Column |  Type   | Collation | Nullable | Default 
+--------+---------+-----------+----------+---------
+ a      | integer |           |          | 
+Check constraints:
+    "noinh_con_copy_a_check" CHECK (a > 0) NO INHERIT
+
+-- error as partitioned tables don't allow NO INHERIT constraints
+CREATE TABLE noinh_con_copy1_parted (LIKE noinh_con_copy INCLUDING ALL) 
PARTITION BY LIST (a);
+ERROR:  cannot add NO INHERIT constraint to partitioned table 
"noinh_con_copy1_parted"
diff --git a/src/test/regress/sql/create_table_like.sql 
b/src/test/regress/sql/create_table_like.sql
index 589ee12ebc..a873812576 100644
--- a/src/test/regress/sql/create_table_like.sql
+++ b/src/test/regress/sql/create_table_like.sql
@@ -170,3 +170,10 @@ DROP SEQUENCE ctlseq1;
 DROP TYPE ctlty1;
 DROP VIEW ctlv1;
 DROP TABLE IF EXISTS ctlt4, ctlt10, ctlt11, ctlt11a, ctlt12;
+
+/* LIKE must respect NO INHERIT property of constraints */
+CREATE TABLE noinh_con_copy (a int CHECK (a > 0) NO INHERIT);
+CREATE TABLE noinh_con_copy1 (LIKE noinh_con_copy INCLUDING ALL);
+\d noinh_con_copy1
+-- error as partitioned tables don't allow NO INHERIT constraints
+CREATE TABLE noinh_con_copy1_parted (LIKE noinh_con_copy INCLUDING ALL) 
PARTITION BY LIST (a);