From f27a6b15e6566fba7748d0d9a3fc5bcfd52c4a1b Mon Sep 17 00:00:00 2001 From: Robert Haas Date: Wed, 16 Dec 2015 07:43:56 -0500 Subject: [PATCH] Mark CHECK constraints declared NOT VALID valid if created with table. FOREIGN KEY constraints have behaved this way for a long time, but for some reason the behavior of CHECK constraints has been inconsistent up until now. Amit Langote and Amul Sul, with assorted tweaks by me. --- src/backend/catalog/heap.c | 2 +- src/backend/parser/gram.y | 2 + src/backend/parser/parse_utilcmd.c | 45 ++++++++++++++++++++++- src/test/regress/expected/alter_table.out | 11 +++++- src/test/regress/sql/alter_table.sql | 5 ++- 5 files changed, 60 insertions(+), 5 deletions(-) diff --git a/src/backend/catalog/heap.c b/src/backend/catalog/heap.c index 7d7d062c06..04c4f8f1ef 100644 --- a/src/backend/catalog/heap.c +++ b/src/backend/catalog/heap.c @@ -2349,7 +2349,7 @@ AddRelationNewConstraints(Relation rel, * OK, store it. */ constrOid = - StoreRelCheck(rel, ccname, expr, !cdef->skip_validation, is_local, + StoreRelCheck(rel, ccname, expr, cdef->initially_valid, is_local, is_local ? 0 : 1, cdef->is_no_inherit, is_internal); numchecks++; diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index 7916df8b09..c4bed8a5ef 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -3080,6 +3080,8 @@ ColConstraintElem: n->is_no_inherit = $5; n->raw_expr = $3; n->cooked_expr = NULL; + n->skip_validation = false; + n->initially_valid = true; $$ = (Node *)n; } | DEFAULT b_expr diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c index 344a40cf58..4c24c13cf8 100644 --- a/src/backend/parser/parse_utilcmd.c +++ b/src/backend/parser/parse_utilcmd.c @@ -120,6 +120,8 @@ static IndexStmt *transformIndexConstraint(Constraint *constraint, static void transformFKConstraints(CreateStmtContext *cxt, bool skipValidation, bool isAddConstraint); +static void transformCheckConstraints(CreateStmtContext *cxt, + bool skipValidation); static void transformConstraintAttrs(CreateStmtContext *cxt, List *constraintList); static void transformColumnType(CreateStmtContext *cxt, ColumnDef *column); @@ -319,6 +321,11 @@ transformCreateStmt(CreateStmt *stmt, const char *queryString) */ transformFKConstraints(&cxt, true, false); + /* + * Postprocess check constraints. + */ + transformCheckConstraints(&cxt, true); + /* * Output results. */ @@ -1914,6 +1921,40 @@ transformIndexConstraint(Constraint *constraint, CreateStmtContext *cxt) return index; } +/* + * transformCheckConstraints + * handle CHECK constraints + * + * Right now, there's nothing to do here when called from ALTER TABLE, + * but the other constraint-transformation functions are called in both + * the CREATE TABLE and ALTER TABLE paths, so do the same here, and just + * don't do anything if we're not authorized to skip validation. + */ +static void +transformCheckConstraints(CreateStmtContext *cxt, bool skipValidation) +{ + ListCell *ckclist; + + if (cxt->ckconstraints == NIL) + return; + + /* + * If creating a new table, we can safely skip validation of check + * constraints, and nonetheless mark them valid. (This will override + * any user-supplied NOT VALID flag.) + */ + if (skipValidation) + { + foreach(ckclist, cxt->ckconstraints) + { + Constraint *constraint = (Constraint *) lfirst(ckclist); + + constraint->skip_validation = true; + constraint->initially_valid = true; + } + } +} + /* * transformFKConstraints * handle FOREIGN KEY constraints @@ -2567,10 +2608,10 @@ transformAlterTableStmt(Oid relid, AlterTableStmt *stmt, save_alist = cxt.alist; cxt.alist = NIL; - /* Postprocess index and FK constraints */ + /* Postprocess constraints */ transformIndexConstraints(&cxt); - transformFKConstraints(&cxt, skipValidation, true); + transformCheckConstraints(&cxt, false); /* * Push any index-creation commands into the ALTER, so that they can be diff --git a/src/test/regress/expected/alter_table.out b/src/test/regress/expected/alter_table.out index 61669b6c7f..228ae6ec22 100644 --- a/src/test/regress/expected/alter_table.out +++ b/src/test/regress/expected/alter_table.out @@ -380,7 +380,16 @@ DROP TABLE tmp2; -- NOT VALID with plan invalidation -- ensure we don't use a constraint for -- exclusion until validated set constraint_exclusion TO 'partition'; -create table nv_parent (d date); +create table nv_parent (d date, check (false) no inherit not valid); +-- not valid constraint added at creation time should automatically become valid +\d nv_parent + Table "public.nv_parent" + Column | Type | Modifiers +--------+------+----------- + d | date | +Check constraints: + "nv_parent_check" CHECK (false) NO INHERIT + create table nv_child_2010 () inherits (nv_parent); create table nv_child_2011 () inherits (nv_parent); alter table nv_child_2010 add check (d between '2010-01-01'::date and '2010-12-31'::date) not valid; diff --git a/src/test/regress/sql/alter_table.sql b/src/test/regress/sql/alter_table.sql index c0bb77ee22..3db3460973 100644 --- a/src/test/regress/sql/alter_table.sql +++ b/src/test/regress/sql/alter_table.sql @@ -327,7 +327,10 @@ DROP TABLE tmp2; -- NOT VALID with plan invalidation -- ensure we don't use a constraint for -- exclusion until validated set constraint_exclusion TO 'partition'; -create table nv_parent (d date); +create table nv_parent (d date, check (false) no inherit not valid); +-- not valid constraint added at creation time should automatically become valid +\d nv_parent + create table nv_child_2010 () inherits (nv_parent); create table nv_child_2011 () inherits (nv_parent); alter table nv_child_2010 add check (d between '2010-01-01'::date and '2010-12-31'::date) not valid; -- 2.40.0