Skip to content

Commit f27a6b1

Browse files
robertmhaasrobertmhaas
committed
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.
1 parent 0625dbb commit f27a6b1

File tree

5 files changed

+60
-5
lines changed

5 files changed

+60
-5
lines changed

‎src/backend/catalog/heap.c

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -2349,7 +2349,7 @@ AddRelationNewConstraints(Relation rel,
23492349
* OK, store it.
23502350
*/
23512351
constrOid =
2352-
StoreRelCheck(rel, ccname, expr, !cdef->skip_validation, is_local,
2352+
StoreRelCheck(rel, ccname, expr, cdef->initially_valid, is_local,
23532353
is_local ? 0 : 1, cdef->is_no_inherit, is_internal);
23542354

23552355
numchecks++;

‎src/backend/parser/gram.y

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -3080,6 +3080,8 @@ ColConstraintElem:
30803080
n->is_no_inherit = $5;
30813081
n->raw_expr = $3;
30823082
n->cooked_expr = NULL;
3083+
n->skip_validation = false;
3084+
n->initially_valid = true;
30833085
$$ = (Node *)n;
30843086
}
30853087
| DEFAULT b_expr

‎src/backend/parser/parse_utilcmd.c

Lines changed: 43 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -120,6 +120,8 @@ static IndexStmt *transformIndexConstraint(Constraint *constraint,
120120
static void transformFKConstraints(CreateStmtContext *cxt,
121121
bool skipValidation,
122122
bool isAddConstraint);
123+
static void transformCheckConstraints(CreateStmtContext *cxt,
124+
bool skipValidation);
123125
static void transformConstraintAttrs(CreateStmtContext *cxt,
124126
List *constraintList);
125127
static void transformColumnType(CreateStmtContext *cxt, ColumnDef *column);
@@ -319,6 +321,11 @@ transformCreateStmt(CreateStmt *stmt, const char *queryString)
319321
*/
320322
transformFKConstraints(&cxt, true, false);
321323

324+
/*
325+
* Postprocess check constraints.
326+
*/
327+
transformCheckConstraints(&cxt, true);
328+
322329
/*
323330
* Output results.
324331
*/
@@ -1914,6 +1921,40 @@ transformIndexConstraint(Constraint *constraint, CreateStmtContext *cxt)
19141921
return index;
19151922
}
19161923

1924+
/*
1925+
* transformCheckConstraints
1926+
* handle CHECK constraints
1927+
*
1928+
* Right now, there's nothing to do here when called from ALTER TABLE,
1929+
* but the other constraint-transformation functions are called in both
1930+
* the CREATE TABLE and ALTER TABLE paths, so do the same here, and just
1931+
* don't do anything if we're not authorized to skip validation.
1932+
*/
1933+
static void
1934+
transformCheckConstraints(CreateStmtContext *cxt, bool skipValidation)
1935+
{
1936+
ListCell *ckclist;
1937+
1938+
if (cxt->ckconstraints == NIL)
1939+
return;
1940+
1941+
/*
1942+
* If creating a new table, we can safely skip validation of check
1943+
* constraints, and nonetheless mark them valid. (This will override
1944+
* any user-supplied NOT VALID flag.)
1945+
*/
1946+
if (skipValidation)
1947+
{
1948+
foreach(ckclist, cxt->ckconstraints)
1949+
{
1950+
Constraint *constraint = (Constraint *) lfirst(ckclist);
1951+
1952+
constraint->skip_validation = true;
1953+
constraint->initially_valid = true;
1954+
}
1955+
}
1956+
}
1957+
19171958
/*
19181959
* transformFKConstraints
19191960
* handle FOREIGN KEY constraints
@@ -2567,10 +2608,10 @@ transformAlterTableStmt(Oid relid, AlterTableStmt *stmt,
25672608
save_alist = cxt.alist;
25682609
cxt.alist = NIL;
25692610

2570-
/* Postprocess index and FK constraints */
2611+
/* Postprocess constraints */
25712612
transformIndexConstraints(&cxt);
2572-
25732613
transformFKConstraints(&cxt, skipValidation, true);
2614+
transformCheckConstraints(&cxt, false);
25742615

25752616
/*
25762617
* Push any index-creation commands into the ALTER, so that they can be

‎src/test/regress/expected/alter_table.out

Lines changed: 10 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -380,7 +380,16 @@ DROP TABLE tmp2;
380380
-- NOT VALID with plan invalidation -- ensure we don't use a constraint for
381381
-- exclusion until validated
382382
set constraint_exclusion TO 'partition';
383-
create table nv_parent (d date);
383+
create table nv_parent (d date, check (false) no inherit not valid);
384+
-- not valid constraint added at creation time should automatically become valid
385+
\d nv_parent
386+
Table "public.nv_parent"
387+
Column | Type | Modifiers
388+
--------+------+-----------
389+
d | date |
390+
Check constraints:
391+
"nv_parent_check" CHECK (false) NO INHERIT
392+
384393
create table nv_child_2010 () inherits (nv_parent);
385394
create table nv_child_2011 () inherits (nv_parent);
386395
alter table nv_child_2010 add check (d between '2010-01-01'::date and '2010-12-31'::date) not valid;

‎src/test/regress/sql/alter_table.sql

Lines changed: 4 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -327,7 +327,10 @@ DROP TABLE tmp2;
327327
-- NOT VALID with plan invalidation -- ensure we don't use a constraint for
328328
-- exclusion until validated
329329
set constraint_exclusion TO 'partition';
330-
create table nv_parent (d date);
330+
create table nv_parent (d date, check (false) no inherit not valid);
331+
-- not valid constraint added at creation time should automatically become valid
332+
\d nv_parent
333+
331334
create table nv_child_2010 () inherits (nv_parent);
332335
create table nv_child_2011 () inherits (nv_parent);
333336
alter table nv_child_2010 add check (d between '2010-01-01'::date and '2010-12-31'::date) not valid;

0 commit comments

Comments
 (0)