On Thu, Sep 4, 2025 at 8:00 PM Álvaro Herrera <alvhe...@kurilemu.de> wrote:
>
> > @@ -1272,33 +1294,41 @@ transformTableLikeClause(CreateStmtContext *cxt,
> > TableLikeClause *table_like_cla
> > * Reproduce not-null constraints, if any, by copying them. We do
> > this
> > * regardless of options given.
> > */
> > - if (tupleDesc->constr && tupleDesc->constr->has_not_null)
> > - {
> > - List *lst;
> > + lst = RelationGetNotNullConstraints(RelationGetRelid(relation), false,
> > +
> > true);
> > + cxt->nnconstraints = list_concat(cxt->nnconstraints, lst);
> >
> > - lst =
> > RelationGetNotNullConstraints(RelationGetRelid(relation), false,
> > -
> > true);
>
> > + /*
> > + * When creating a new relation, marking the enforced not-null
> > constraint as
> > + * not valid doesn't make sense, so we treat it as valid.
> > + */
> > + foreach_node(Constraint, nnconstr, lst)
> > + {
> > + if (nnconstr->is_enforced)
> > + {
> > + nnconstr->skip_validation = false;
> > + nnconstr->initially_valid = true;
> > + }
> > + }
>
> Hmmm, this bit here (making constraints as valid if they're not valid in
> the source table) looks like a fix for the existing code. I think it
> should be a separate patch, perhaps back-patchable to 18. Or maybe I'm
> missing something ...?
>
it's indeed a bug, which was introduced
https://git.postgresql.org/cgit/postgresql.git/diff/src/backend/parser/parse_utilcmd.c?id=ca87c415e2fccf81cec6fd45698dde9fae0ab570
attached is the fix, also added a test on create_table_like.sql
From 6ee61e8b107a3fabe9cadb35701c7d9033889722 Mon Sep 17 00:00:00 2001
From: jian he <jian.universal...@gmail.com>
Date: Thu, 4 Sep 2025 23:16:49 +0800
Subject: [PATCH v1 1/1] fix CREATE TABLE LIKE with INVALID CHECK CONSTRAINT
bug was introduced
https://git.postgresql.org/cgit/postgresql.git/diff/src/backend/parser/parse_utilcmd.c?id=ca87c415e2fccf81cec6fd45698dde9fae0ab570
CREATE TABLE ... LIKE, any new check constraints copied from the source table
are expected to be valid if they are ENFORCED.
add a test on it.
---
src/backend/parser/parse_utilcmd.c | 3 +--
src/test/regress/expected/create_table_like.out | 8 ++++++++
src/test/regress/sql/create_table_like.sql | 1 +
3 files changed, 10 insertions(+), 2 deletions(-)
diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c
index afcf54169c3..f8673a82c49 100644
--- a/src/backend/parser/parse_utilcmd.c
+++ b/src/backend/parser/parse_utilcmd.c
@@ -1461,7 +1461,6 @@ expandTableLikeClause(RangeVar *heapRel, TableLikeClause *table_like_clause)
char *ccname = constr->check[ccnum].ccname;
char *ccbin = constr->check[ccnum].ccbin;
bool ccenforced = constr->check[ccnum].ccenforced;
- bool ccvalid = constr->check[ccnum].ccvalid;
bool ccnoinherit = constr->check[ccnum].ccnoinherit;
Node *ccbin_node;
bool found_whole_row;
@@ -1492,7 +1491,7 @@ expandTableLikeClause(RangeVar *heapRel, TableLikeClause *table_like_clause)
n->conname = pstrdup(ccname);
n->location = -1;
n->is_enforced = ccenforced;
- n->initially_valid = ccvalid;
+ n->initially_valid = ccenforced ? true : false;
n->is_no_inherit = ccnoinherit;
n->raw_expr = NULL;
n->cooked_expr = nodeToString(ccbin_node);
diff --git a/src/test/regress/expected/create_table_like.out b/src/test/regress/expected/create_table_like.out
index 29a779c2e90..d3c35c14847 100644
--- a/src/test/regress/expected/create_table_like.out
+++ b/src/test/regress/expected/create_table_like.out
@@ -320,6 +320,7 @@ DROP TABLE inhz;
-- including storage and comments
CREATE TABLE ctlt1 (a text CHECK (length(a) > 2) ENFORCED PRIMARY KEY,
b text CHECK (length(b) > 100) NOT ENFORCED);
+ALTER TABLE ctlt1 ADD CONSTRAINT cc CHECK (length(b) > 100) NOT VALID;
CREATE INDEX ctlt1_b_key ON ctlt1 (b);
CREATE INDEX ctlt1_fnidx ON ctlt1 ((a || b));
CREATE STATISTICS ctlt1_a_b_stat ON a,b FROM ctlt1;
@@ -378,6 +379,7 @@ SELECT conname, description FROM pg_description, pg_constraint c WHERE classoid
CREATE TABLE ctlt1_inh (LIKE ctlt1 INCLUDING CONSTRAINTS INCLUDING COMMENTS) INHERITS (ctlt1);
NOTICE: merging column "a" with inherited definition
NOTICE: merging column "b" with inherited definition
+NOTICE: merging constraint "cc" with inherited definition
NOTICE: merging constraint "ctlt1_a_check" with inherited definition
NOTICE: merging constraint "ctlt1_b_check" with inherited definition
\d+ ctlt1_inh
@@ -387,6 +389,7 @@ NOTICE: merging constraint "ctlt1_b_check" with inherited definition
a | text | | not null | | main | | A
b | text | | | | extended | | B
Check constraints:
+ "cc" CHECK (length(b) > 100)
"ctlt1_a_check" CHECK (length(a) > 2)
"ctlt1_b_check" CHECK (length(b) > 100) NOT ENFORCED
Not-null constraints:
@@ -409,6 +412,7 @@ NOTICE: merging multiple inherited definitions of column "a"
b | text | | | | extended | |
c | text | | | | external | |
Check constraints:
+ "cc" CHECK (length(b) > 100)
"ctlt1_a_check" CHECK (length(a) > 2)
"ctlt1_b_check" CHECK (length(b) > 100) NOT ENFORCED
"ctlt3_a_check" CHECK (length(a) < 5)
@@ -430,6 +434,7 @@ NOTICE: merging column "a" with inherited definition
Indexes:
"ctlt13_like_expr_idx" btree ((a || c))
Check constraints:
+ "cc" CHECK (length(b) > 100)
"ctlt1_a_check" CHECK (length(a) > 2)
"ctlt1_b_check" CHECK (length(b) > 100) NOT ENFORCED
"ctlt3_a_check" CHECK (length(a) < 5)
@@ -456,6 +461,7 @@ Indexes:
"ctlt_all_b_idx" btree (b)
"ctlt_all_expr_idx" btree ((a || b))
Check constraints:
+ "cc" CHECK (length(b) > 100)
"ctlt1_a_check" CHECK (length(a) > 2)
"ctlt1_b_check" CHECK (length(b) > 100) NOT ENFORCED
Statistics objects:
@@ -499,6 +505,7 @@ Indexes:
"pg_attrdef_b_idx" btree (b)
"pg_attrdef_expr_idx" btree ((a || b))
Check constraints:
+ "cc" CHECK (length(b) > 100)
"ctlt1_a_check" CHECK (length(a) > 2)
"ctlt1_b_check" CHECK (length(b) > 100) NOT ENFORCED
Statistics objects:
@@ -524,6 +531,7 @@ Indexes:
"ctlt1_b_idx" btree (b)
"ctlt1_expr_idx" btree ((a || b))
Check constraints:
+ "cc" CHECK (length(b) > 100)
"ctlt1_a_check" CHECK (length(a) > 2)
"ctlt1_b_check" CHECK (length(b) > 100) NOT ENFORCED
Statistics objects:
diff --git a/src/test/regress/sql/create_table_like.sql b/src/test/regress/sql/create_table_like.sql
index bf8702116a7..93389b57dbf 100644
--- a/src/test/regress/sql/create_table_like.sql
+++ b/src/test/regress/sql/create_table_like.sql
@@ -130,6 +130,7 @@ DROP TABLE inhz;
-- including storage and comments
CREATE TABLE ctlt1 (a text CHECK (length(a) > 2) ENFORCED PRIMARY KEY,
b text CHECK (length(b) > 100) NOT ENFORCED);
+ALTER TABLE ctlt1 ADD CONSTRAINT cc CHECK (length(b) > 100) NOT VALID;
CREATE INDEX ctlt1_b_key ON ctlt1 (b);
CREATE INDEX ctlt1_fnidx ON ctlt1 ((a || b));
CREATE STATISTICS ctlt1_a_b_stat ON a,b FROM ctlt1;
--
2.34.1
