Constraint's NO INHERIT option is ignored in CREATE TABLE LIKE statement
Hi hackers,
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.
CREATE TABLE test (
x INT CHECK (x > 0) NO INHERIT
);
\d test
Table "public.test"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
x | integer | | |
Check constraints:
"test_x_check1" CHECK (x > 0) NO INHERIT
Now when we want to make a copy of the table structure into a new table
the `NO INHERIT` option is ignored.
CREATE TABLE test2 (LIKE test INCLUDING CONSTRAINTS);
\d test2
Table "public.test2"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
x | integer | | |
Check constraints:
"test_x_check1" CHECK (x > 0)
Is this a bug or expected behaviour? Just in case I've attached a patch
that fixes this.
Regards,
Ildar
Attachments:
copy_constr_noinherit.patchtext/x-patch; charset=US-ASCII; name=copy_constr_noinherit.patchDownload
diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c
index 2406ca7a5d..d75ec6766a 100644
--- a/src/backend/parser/parse_utilcmd.c
+++ b/src/backend/parser/parse_utilcmd.c
@@ -1154,6 +1154,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);
Ildar Musin <ildar@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.
However, I'd be disinclined to back-patch, since it's barely possible
somebody out there is depending on the existing behavior.
regards, tom lane
On Wed, Feb 19, 2020 at 4:02 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Ildar Musin <ildar@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 NOINHERIT.
...
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.However, I'd be disinclined to back-patch, since it's barely possible
somebody out there is depending on the existing behavior.
Not sure I agree with the premise that it is not supposed to be copied; is
there some other object type the allows NO INHERIT that isn't copied when
CREATE TABLE LIKE is used and check constraints are the odd ones out?
Inheritance is what NO INHERIT is about and CREATE TABLE LIKE pointedly
doesn't setup an inheritance structure. The documentation seems ok since
saying that NO INHERIT is ignored when inheritance is not being used seems
self-evident. Sure, maybe some clarity here could be had, but its not like
this comes up with any regularity.
David J.
On Thu, Feb 20, 2020 at 8:02 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Ildar Musin <ildar@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
Attachments:
copy_constr_noinherit_v2.patchtext/plain; charset=US-ASCII; name=copy_constr_noinherit_v2.patchDownload
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);
On Thu, Feb 20, 2020 at 8:20 AM David G. Johnston
<david.g.johnston@gmail.com> wrote:
On Wed, Feb 19, 2020 at 4:02 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Ildar Musin <ildar@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.However, I'd be disinclined to back-patch, since it's barely possible
somebody out there is depending on the existing behavior.Not sure I agree with the premise that it is not supposed to be copied; is there some other object type the allows NO INHERIT that isn't copied when CREATE TABLE LIKE is used and check constraints are the odd ones out?
Syntax currently allows only CHECK constraints to be marked NO INHERIT.
Thanks,
Amit
Amit Langote <amitlangote09@gmail.com> writes:
On Thu, Feb 20, 2020 at 8:20 AM David G. Johnston
<david.g.johnston@gmail.com> wrote:Not sure I agree with the premise that it is not supposed to be copied; is there some other object type the allows NO INHERIT that isn't copied when CREATE TABLE LIKE is used and check constraints are the odd ones out?
Syntax currently allows only CHECK constraints to be marked NO INHERIT.
Hearing no further comments, pushed, with a bit of cosmetic polishing.
regards, tom lane