diff options
Diffstat (limited to 'src')
32 files changed, 545 insertions, 58 deletions
diff --git a/src/backend/access/common/tupdesc.c b/src/backend/access/common/tupdesc.c index 2e4666c469c..fe197447912 100644 --- a/src/backend/access/common/tupdesc.c +++ b/src/backend/access/common/tupdesc.c @@ -376,6 +376,7 @@ CreateTupleDescCopyConstr(TupleDesc tupdesc) { cpy->check[i].ccname = pstrdup(constr->check[i].ccname); cpy->check[i].ccbin = pstrdup(constr->check[i].ccbin); + cpy->check[i].ccenforced = constr->check[i].ccenforced; cpy->check[i].ccvalid = constr->check[i].ccvalid; cpy->check[i].ccnoinherit = constr->check[i].ccnoinherit; } @@ -692,6 +693,7 @@ equalTupleDescs(TupleDesc tupdesc1, TupleDesc tupdesc2) if (!(strcmp(check1->ccname, check2->ccname) == 0 && strcmp(check1->ccbin, check2->ccbin) == 0 && + check1->ccenforced == check2->ccenforced && check1->ccvalid == check2->ccvalid && check1->ccnoinherit == check2->ccnoinherit)) return false; diff --git a/src/backend/catalog/heap.c b/src/backend/catalog/heap.c index 024521c66c0..57ef466acce 100644 --- a/src/backend/catalog/heap.c +++ b/src/backend/catalog/heap.c @@ -102,12 +102,13 @@ static ObjectAddress AddNewRelationType(const char *typeName, Oid new_array_type); static void RelationRemoveInheritance(Oid relid); static Oid StoreRelCheck(Relation rel, const char *ccname, Node *expr, - bool is_validated, bool is_local, int16 inhcount, - bool is_no_inherit, bool is_internal); + bool is_enforced, bool is_validated, bool is_local, + int16 inhcount, bool is_no_inherit, bool is_internal); static void StoreConstraints(Relation rel, List *cooked_constraints, bool is_internal); static bool MergeWithExistingConstraint(Relation rel, const char *ccname, Node *expr, bool allow_merge, bool is_local, + bool is_enforced, bool is_initially_valid, bool is_no_inherit); static void SetRelationNumChecks(Relation rel, int numchecks); @@ -2066,8 +2067,8 @@ SetAttrMissing(Oid relid, char *attname, char *value) */ static Oid StoreRelCheck(Relation rel, const char *ccname, Node *expr, - bool is_validated, bool is_local, int16 inhcount, - bool is_no_inherit, bool is_internal) + bool is_enforced, bool is_validated, bool is_local, + int16 inhcount, bool is_no_inherit, bool is_internal) { char *ccbin; List *varList; @@ -2132,6 +2133,7 @@ StoreRelCheck(Relation rel, const char *ccname, Node *expr, CONSTRAINT_CHECK, /* Constraint Type */ false, /* Is Deferrable */ false, /* Is Deferred */ + is_enforced, /* Is Enforced */ is_validated, InvalidOid, /* no parent constraint */ RelationGetRelid(rel), /* relation */ @@ -2185,6 +2187,7 @@ StoreRelNotNull(Relation rel, const char *nnname, AttrNumber attnum, CONSTRAINT_NOTNULL, false, false, + true, /* Is Enforced */ is_validated, InvalidOid, RelationGetRelid(rel), @@ -2254,9 +2257,9 @@ StoreConstraints(Relation rel, List *cooked_constraints, bool is_internal) case CONSTR_CHECK: con->conoid = StoreRelCheck(rel, con->name, con->expr, - !con->skip_validation, con->is_local, - con->inhcount, con->is_no_inherit, - is_internal); + con->is_enforced, !con->skip_validation, + con->is_local, con->inhcount, + con->is_no_inherit, is_internal); numchecks++; break; @@ -2390,6 +2393,7 @@ AddRelationNewConstraints(Relation rel, cooked->name = NULL; cooked->attnum = colDef->attnum; cooked->expr = expr; + cooked->is_enforced = true; cooked->skip_validation = false; cooked->is_local = is_local; cooked->inhcount = is_local ? 0 : 1; @@ -2461,6 +2465,7 @@ AddRelationNewConstraints(Relation rel, */ if (MergeWithExistingConstraint(rel, ccname, expr, allow_merge, is_local, + cdef->is_enforced, cdef->initially_valid, cdef->is_no_inherit)) continue; @@ -2509,8 +2514,10 @@ AddRelationNewConstraints(Relation rel, * OK, store it. */ constrOid = - StoreRelCheck(rel, ccname, expr, cdef->initially_valid, is_local, - is_local ? 0 : 1, cdef->is_no_inherit, is_internal); + StoreRelCheck(rel, ccname, expr, cdef->is_enforced, + cdef->initially_valid, is_local, + is_local ? 0 : 1, cdef->is_no_inherit, + is_internal); numchecks++; @@ -2520,6 +2527,7 @@ AddRelationNewConstraints(Relation rel, cooked->name = ccname; cooked->attnum = 0; cooked->expr = expr; + cooked->is_enforced = cdef->is_enforced; cooked->skip_validation = cdef->skip_validation; cooked->is_local = is_local; cooked->inhcount = is_local ? 0 : 1; @@ -2590,6 +2598,7 @@ AddRelationNewConstraints(Relation rel, nncooked->name = nnname; nncooked->attnum = colnum; nncooked->expr = NULL; + nncooked->is_enforced = true; nncooked->skip_validation = cdef->skip_validation; nncooked->is_local = is_local; nncooked->inhcount = inhcount; @@ -2624,6 +2633,7 @@ AddRelationNewConstraints(Relation rel, static bool MergeWithExistingConstraint(Relation rel, const char *ccname, Node *expr, bool allow_merge, bool is_local, + bool is_enforced, bool is_initially_valid, bool is_no_inherit) { @@ -2714,12 +2724,24 @@ MergeWithExistingConstraint(Relation rel, const char *ccname, Node *expr, * If the child constraint is "not valid" then cannot merge with a * valid parent constraint. */ - if (is_initially_valid && !con->convalidated) + if (is_initially_valid && con->conenforced && !con->convalidated) ereport(ERROR, (errcode(ERRCODE_INVALID_OBJECT_DEFINITION), errmsg("constraint \"%s\" conflicts with NOT VALID constraint on relation \"%s\"", ccname, RelationGetRelationName(rel)))); + /* + * A non-enforced child constraint cannot be merged with an enforced + * parent constraint. However, the reverse is allowed, where the child + * constraint is enforced. + */ + if ((!is_local && is_enforced && !con->conenforced) || + (is_local && !is_enforced && con->conenforced)) + ereport(ERROR, + (errcode(ERRCODE_INVALID_OBJECT_DEFINITION), + errmsg("constraint \"%s\" conflicts with NOT ENFORCED constraint on relation \"%s\"", + ccname, RelationGetRelationName(rel)))); + /* OK to update the tuple */ ereport(NOTICE, (errmsg("merging constraint \"%s\" with inherited definition", @@ -2755,6 +2777,19 @@ MergeWithExistingConstraint(Relation rel, const char *ccname, Node *expr, con->connoinherit = true; } + /* + * If the child constraint is required to be enforced while the parent + * constraint is not, this should be allowed by marking the child + * constraint as enforced. In the reverse case, an error would have + * already been thrown before reaching this point. + */ + if (is_enforced && !con->conenforced) + { + Assert(is_local); + con->conenforced = true; + con->convalidated = true; + } + CatalogTupleUpdate(conDesc, &tup->t_self, tup); } diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c index 221fbb4e286..7377912b41e 100644 --- a/src/backend/catalog/index.c +++ b/src/backend/catalog/index.c @@ -1958,6 +1958,7 @@ index_constraint_create(Relation heapRelation, constraintType, deferrable, initdeferred, + true, /* Is Enforced */ true, parentConstraintId, RelationGetRelid(heapRelation), diff --git a/src/backend/catalog/information_schema.sql b/src/backend/catalog/information_schema.sql index 16036fdec91..a7bffca93d1 100644 --- a/src/backend/catalog/information_schema.sql +++ b/src/backend/catalog/information_schema.sql @@ -1844,7 +1844,7 @@ CREATE VIEW table_constraints AS AS is_deferrable, CAST(CASE WHEN c.condeferred THEN 'YES' ELSE 'NO' END AS yes_or_no) AS initially_deferred, - CAST('YES' AS yes_or_no) AS enforced, + CAST(CASE WHEN c.conenforced THEN 'YES' ELSE 'NO' END AS yes_or_no) AS enforced, CAST(CASE WHEN c.contype = 'u' THEN CASE WHEN (SELECT NOT indnullsnotdistinct FROM pg_index WHERE indexrelid = conindid) THEN 'YES' ELSE 'NO' END END diff --git a/src/backend/catalog/pg_constraint.c b/src/backend/catalog/pg_constraint.c index 0c6ac0be41c..8693ec3c884 100644 --- a/src/backend/catalog/pg_constraint.c +++ b/src/backend/catalog/pg_constraint.c @@ -53,6 +53,7 @@ CreateConstraintEntry(const char *constraintName, char constraintType, bool isDeferrable, bool isDeferred, + bool isEnforced, bool isValidated, Oid parentConstrId, Oid relId, @@ -99,6 +100,11 @@ CreateConstraintEntry(const char *constraintName, ObjectAddresses *addrs_auto; ObjectAddresses *addrs_normal; + /* Only CHECK constraint can be not enforced */ + Assert(isEnforced || constraintType == CONSTRAINT_CHECK); + /* NOT ENFORCED constraint must be NOT VALID */ + Assert(isEnforced || !isValidated); + conDesc = table_open(ConstraintRelationId, RowExclusiveLock); Assert(constraintName); @@ -182,6 +188,7 @@ CreateConstraintEntry(const char *constraintName, values[Anum_pg_constraint_contype - 1] = CharGetDatum(constraintType); values[Anum_pg_constraint_condeferrable - 1] = BoolGetDatum(isDeferrable); values[Anum_pg_constraint_condeferred - 1] = BoolGetDatum(isDeferred); + values[Anum_pg_constraint_conenforced - 1] = BoolGetDatum(isEnforced); values[Anum_pg_constraint_convalidated - 1] = BoolGetDatum(isValidated); values[Anum_pg_constraint_conrelid - 1] = ObjectIdGetDatum(relId); values[Anum_pg_constraint_contypid - 1] = ObjectIdGetDatum(domainId); @@ -822,6 +829,7 @@ RelationGetNotNullConstraints(Oid relid, bool cooked, bool include_noinh) cooked->name = pstrdup(NameStr(conForm->conname)); cooked->attnum = colnum; cooked->expr = NULL; + cooked->is_enforced = true; cooked->skip_validation = false; cooked->is_local = true; cooked->inhcount = 0; @@ -841,6 +849,7 @@ RelationGetNotNullConstraints(Oid relid, bool cooked, bool include_noinh) constr->location = -1; constr->keys = list_make1(makeString(get_attname(relid, colnum, false))); + constr->is_enforced = true; constr->skip_validation = false; constr->initially_valid = true; constr->is_no_inherit = conForm->connoinherit; diff --git a/src/backend/catalog/sql_features.txt b/src/backend/catalog/sql_features.txt index c002f37202f..2f250d2c57b 100644 --- a/src/backend/catalog/sql_features.txt +++ b/src/backend/catalog/sql_features.txt @@ -281,7 +281,7 @@ F461 Named character sets NO F471 Scalar subquery values YES F481 Expanded NULL predicate YES F491 Constraint management YES -F492 Optional table constraint enforcement NO +F492 Optional table constraint enforcement NO check constraints only F501 Features and conformance views YES F501 Features and conformance views 01 SQL_FEATURES view YES F501 Features and conformance views 02 SQL_SIZING view YES diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index 54575fcd287..4fc54bd6eba 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -373,7 +373,7 @@ static void RangeVarCallbackForTruncate(const RangeVar *relation, static List *MergeAttributes(List *columns, const List *supers, char relpersistence, bool is_partition, List **supconstr, List **supnotnulls); -static List *MergeCheckConstraint(List *constraints, const char *name, Node *expr); +static List *MergeCheckConstraint(List *constraints, const char *name, Node *expr, bool is_enforced); static void MergeChildAttribute(List *inh_columns, int exist_attno, int newcol_attno, const ColumnDef *newdef); static ColumnDef *MergeInheritedAttribute(List *inh_columns, int exist_attno, const ColumnDef *newdef); static void MergeAttributesIntoExisting(Relation child_rel, Relation parent_rel, bool ispartition); @@ -973,6 +973,7 @@ DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId, cooked->name = NULL; cooked->attnum = attnum; cooked->expr = colDef->cooked_default; + cooked->is_enforced = true; cooked->skip_validation = false; cooked->is_local = true; /* not used for defaults */ cooked->inhcount = 0; /* ditto */ @@ -2890,7 +2891,8 @@ MergeAttributes(List *columns, const List *supers, char relpersistence, name, RelationGetRelationName(relation)))); - constraints = MergeCheckConstraint(constraints, name, expr); + constraints = MergeCheckConstraint(constraints, name, expr, + check[i].ccenforced); } } @@ -3104,7 +3106,7 @@ MergeAttributes(List *columns, const List *supers, char relpersistence, * the list. */ static List * -MergeCheckConstraint(List *constraints, const char *name, Node *expr) +MergeCheckConstraint(List *constraints, const char *name, Node *expr, bool is_enforced) { ListCell *lc; CookedConstraint *newcon; @@ -3127,6 +3129,17 @@ MergeCheckConstraint(List *constraints, const char *name, Node *expr) ereport(ERROR, errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED), errmsg("too many inheritance parents")); + + /* + * When enforceability differs, the merged constraint should be + * marked as ENFORCED because one of the parents is ENFORCED. + */ + if (!ccon->is_enforced && is_enforced) + { + ccon->is_enforced = true; + ccon->skip_validation = false; + } + return constraints; } @@ -3145,6 +3158,8 @@ MergeCheckConstraint(List *constraints, const char *name, Node *expr) newcon->name = pstrdup(name); newcon->expr = expr; newcon->inhcount = 1; + newcon->is_enforced = is_enforced; + newcon->skip_validation = !is_enforced; return lappend(constraints, newcon); } @@ -10428,6 +10443,7 @@ addFkConstraint(addFkConstraintSides fkside, CONSTRAINT_FOREIGN, fkconstraint->deferrable, fkconstraint->initdeferred, + true, /* Is Enforced */ fkconstraint->initially_valid, parentConstr, RelationGetRelid(rel), @@ -12014,6 +12030,11 @@ ATExecValidateConstraint(List **wqueue, Relation rel, char *constrName, errmsg("constraint \"%s\" of relation \"%s\" is not a foreign key or check constraint", constrName, RelationGetRelationName(rel)))); + if (!con->conenforced) + ereport(ERROR, + (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE), + errmsg("cannot validate NOT ENFORCED constraint"))); + if (!con->convalidated) { AlteredTableInfo *tab; @@ -16259,6 +16280,9 @@ decompile_conbin(HeapTuple contup, TupleDesc tupdesc) * The test we apply is to see whether they reverse-compile to the same * source string. This insulates us from issues like whether attributes * have the same physical column numbers in parent and child relations. + * + * Note that we ignore enforceability as there are cases where constraints + * with differing enforceability are allowed. */ static bool constraints_equivalent(HeapTuple a, HeapTuple b, TupleDesc tupleDesc) @@ -16528,13 +16552,25 @@ MergeConstraintsIntoExisting(Relation child_rel, Relation parent_rel) * If the child constraint is "not valid" then cannot merge with a * valid parent constraint */ - if (parent_con->convalidated && !child_con->convalidated) + if (parent_con->convalidated && child_con->conenforced && + !child_con->convalidated) ereport(ERROR, (errcode(ERRCODE_INVALID_OBJECT_DEFINITION), errmsg("constraint \"%s\" conflicts with NOT VALID constraint on child table \"%s\"", NameStr(child_con->conname), RelationGetRelationName(child_rel)))); /* + * A non-enforced child constraint cannot be merged with an + * enforced parent constraint. However, the reverse is allowed, + * where the child constraint is enforced. + */ + if (parent_con->conenforced && !child_con->conenforced) + ereport(ERROR, + (errcode(ERRCODE_INVALID_OBJECT_DEFINITION), + errmsg("constraint \"%s\" conflicts with NOT ENFORCED constraint on child table \"%s\"", + NameStr(child_con->conname), RelationGetRelationName(child_rel)))); + + /* * OK, bump the child constraint's inheritance count. (If we fail * later on, this change will just roll back.) */ @@ -18885,6 +18921,12 @@ ConstraintImpliedByRelConstraint(Relation scanrel, List *testConstraint, List *p if (!constr->check[i].ccvalid) continue; + /* + * NOT ENFORCED constraints are always marked as invalid, which should + * have been ignored. + */ + Assert(constr->check[i].ccenforced); + cexpr = stringToNode(constr->check[i].ccbin); /* @@ -20195,6 +20237,7 @@ DetachAddConstraintIfNeeded(List **wqueue, Relation partRel) n->is_no_inherit = false; n->raw_expr = NULL; n->cooked_expr = nodeToString(make_ands_explicit(constraintExpr)); + n->is_enforced = true; n->initially_valid = true; n->skip_validation = true; /* It's a re-add, since it nominally already exists */ diff --git a/src/backend/commands/trigger.c b/src/backend/commands/trigger.c index 32f25f4d911..acf3e4a3f1f 100644 --- a/src/backend/commands/trigger.c +++ b/src/backend/commands/trigger.c @@ -809,6 +809,7 @@ CreateTriggerFiringOn(CreateTrigStmt *stmt, const char *queryString, CONSTRAINT_TRIGGER, stmt->deferrable, stmt->initdeferred, + true, /* Is Enforced */ true, InvalidOid, /* no parent */ RelationGetRelid(rel), diff --git a/src/backend/commands/typecmds.c b/src/backend/commands/typecmds.c index 6b1d2383514..0ea82262865 100644 --- a/src/backend/commands/typecmds.c +++ b/src/backend/commands/typecmds.c @@ -1028,6 +1028,14 @@ DefineDomain(ParseState *pstate, CreateDomainStmt *stmt) parser_errposition(pstate, constr->location))); break; + case CONSTR_ATTR_ENFORCED: + case CONSTR_ATTR_NOT_ENFORCED: + ereport(ERROR, + (errcode(ERRCODE_INVALID_OBJECT_DEFINITION), + errmsg("specifying constraint enforceability not supported for domains"), + parser_errposition(pstate, constr->location))); + break; + /* no default, to let compiler warn about missing case */ } } @@ -2985,6 +2993,13 @@ AlterDomainAddConstraint(List *names, Node *newConstraint, errmsg("specifying constraint deferrability not supported for domains"))); break; + case CONSTR_ATTR_ENFORCED: + case CONSTR_ATTR_NOT_ENFORCED: + ereport(ERROR, + (errcode(ERRCODE_INVALID_OBJECT_DEFINITION), + errmsg("specifying constraint enforceability not supported for domains"))); + break; + default: elog(ERROR, "unrecognized constraint subtype: %d", (int) constr->contype); @@ -3614,6 +3629,7 @@ domainAddCheckConstraint(Oid domainOid, Oid domainNamespace, Oid baseTypeOid, CONSTRAINT_CHECK, /* Constraint Type */ false, /* Is Deferrable */ false, /* Is Deferred */ + true, /* Is Enforced */ !constr->skip_validation, /* Is Validated */ InvalidOid, /* no parent constraint */ InvalidOid, /* not a relation constraint */ @@ -3721,6 +3737,7 @@ domainAddNotNullConstraint(Oid domainOid, Oid domainNamespace, Oid baseTypeOid, CONSTRAINT_NOTNULL, /* Constraint Type */ false, /* Is Deferrable */ false, /* Is Deferred */ + true, /* Is Enforced */ !constr->skip_validation, /* Is Validated */ InvalidOid, /* no parent constraint */ InvalidOid, /* not a relation constraint */ diff --git a/src/backend/executor/execMain.c b/src/backend/executor/execMain.c index a06295b6ba7..2d28ec65fc4 100644 --- a/src/backend/executor/execMain.c +++ b/src/backend/executor/execMain.c @@ -1751,11 +1751,15 @@ ExecRelCheck(ResultRelInfo *resultRelInfo, { oldContext = MemoryContextSwitchTo(estate->es_query_cxt); resultRelInfo->ri_ConstraintExprs = - (ExprState **) palloc(ncheck * sizeof(ExprState *)); + (ExprState **) palloc0(ncheck * sizeof(ExprState *)); for (i = 0; i < ncheck; i++) { Expr *checkconstr; + /* Skip not enforced constraint */ + if (!check[i].ccenforced) + continue; + checkconstr = stringToNode(check[i].ccbin); resultRelInfo->ri_ConstraintExprs[i] = ExecPrepareExpr(checkconstr, estate); @@ -1782,7 +1786,7 @@ ExecRelCheck(ResultRelInfo *resultRelInfo, * is not to be treated as a failure. Therefore, use ExecCheck not * ExecQual. */ - if (!ExecCheck(checkconstr, econtext)) + if (checkconstr && !ExecCheck(checkconstr, econtext)) return check[i].ccname; } diff --git a/src/backend/nodes/makefuncs.c b/src/backend/nodes/makefuncs.c index 6b66bc18286..b14d4d6adf4 100644 --- a/src/backend/nodes/makefuncs.c +++ b/src/backend/nodes/makefuncs.c @@ -453,6 +453,7 @@ makeNotNullConstraint(String *colname) notnull->initdeferred = false; notnull->location = -1; notnull->keys = list_make1(colname); + notnull->is_enforced = true; notnull->skip_validation = false; notnull->initially_valid = true; diff --git a/src/backend/optimizer/util/plancat.c b/src/backend/optimizer/util/plancat.c index b9759c31252..f2d319101d3 100644 --- a/src/backend/optimizer/util/plancat.c +++ b/src/backend/optimizer/util/plancat.c @@ -1304,9 +1304,20 @@ get_relation_constraints(PlannerInfo *root, */ if (!constr->check[i].ccvalid) continue; + + /* + * NOT ENFORCED constraints are always marked as invalid, which + * should have been ignored. + */ + Assert(constr->check[i].ccenforced); + + /* + * Also ignore if NO INHERIT and we weren't told that that's safe. + */ if (constr->check[i].ccnoinherit && !include_noinherit) continue; + cexpr = stringToNode(constr->check[i].ccbin); /* diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index b4c1e2c69dd..6079de70e09 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -143,6 +143,8 @@ typedef struct KeyActions #define CAS_INITIALLY_DEFERRED 0x08 #define CAS_NOT_VALID 0x10 #define CAS_NO_INHERIT 0x20 +#define CAS_NOT_ENFORCED 0x40 +#define CAS_ENFORCED 0x80 #define parser_yyerror(msg) scanner_yyerror(msg, yyscanner) @@ -196,8 +198,8 @@ static void SplitColQualList(List *qualList, List **constraintList, CollateClause **collClause, core_yyscan_t yyscanner); static void processCASbits(int cas_bits, int location, const char *constrType, - bool *deferrable, bool *initdeferred, bool *not_valid, - bool *no_inherit, core_yyscan_t yyscanner); + bool *deferrable, bool *initdeferred, bool *is_enforced, + bool *not_valid, bool *no_inherit, core_yyscan_t yyscanner); static PartitionStrategy parsePartitionStrategy(char *strategy, int location, core_yyscan_t yyscanner); static void preprocess_pubobj_list(List *pubobjspec_list, @@ -711,9 +713,9 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); DETACH DICTIONARY DISABLE_P DISCARD DISTINCT DO DOCUMENT_P DOMAIN_P DOUBLE_P DROP - EACH ELSE EMPTY_P ENABLE_P ENCODING ENCRYPTED END_P ENUM_P ERROR_P ESCAPE - EVENT EXCEPT EXCLUDE EXCLUDING EXCLUSIVE EXECUTE EXISTS EXPLAIN EXPRESSION - EXTENSION EXTERNAL EXTRACT + EACH ELSE EMPTY_P ENABLE_P ENCODING ENCRYPTED END_P ENFORCED ENUM_P ERROR_P + ESCAPE EVENT EXCEPT EXCLUDE EXCLUDING EXCLUSIVE EXECUTE EXISTS EXPLAIN + EXPRESSION EXTENSION EXTERNAL EXTRACT FALSE_P FAMILY FETCH FILTER FINALIZE FIRST_P FLOAT_P FOLLOWING FOR FORCE FOREIGN FORMAT FORWARD FREEZE FROM FULL FUNCTION FUNCTIONS @@ -2658,7 +2660,7 @@ alter_table_cmd: processCASbits($4, @4, "ALTER CONSTRAINT statement", &c->deferrable, &c->initdeferred, - NULL, NULL, yyscanner); + NULL, NULL, NULL, yyscanner); $$ = (Node *) n; } /* ALTER TABLE <name> VALIDATE CONSTRAINT ... */ @@ -3915,6 +3917,7 @@ ColConstraintElem: n->contype = CONSTR_NOTNULL; n->location = @1; n->is_no_inherit = $3; + n->is_enforced = true; n->skip_validation = false; n->initially_valid = true; $$ = (Node *) n; @@ -3961,6 +3964,7 @@ ColConstraintElem: n->is_no_inherit = $5; n->raw_expr = $3; n->cooked_expr = NULL; + n->is_enforced = true; n->skip_validation = false; n->initially_valid = true; $$ = (Node *) n; @@ -4022,6 +4026,7 @@ ColConstraintElem: n->fk_upd_action = ($5)->updateAction->action; n->fk_del_action = ($5)->deleteAction->action; n->fk_del_set_cols = ($5)->deleteAction->cols; + n->is_enforced = true; n->skip_validation = false; n->initially_valid = true; $$ = (Node *) n; @@ -4087,6 +4092,22 @@ ConstraintAttr: n->location = @1; $$ = (Node *) n; } + | ENFORCED + { + Constraint *n = makeNode(Constraint); + + n->contype = CONSTR_ATTR_ENFORCED; + n->location = @1; + $$ = (Node *) n; + } + | NOT ENFORCED + { + Constraint *n = makeNode(Constraint); + + n->contype = CONSTR_ATTR_NOT_ENFORCED; + n->location = @1; + $$ = (Node *) n; + } ; @@ -4148,7 +4169,7 @@ ConstraintElem: n->raw_expr = $3; n->cooked_expr = NULL; processCASbits($5, @5, "CHECK", - NULL, NULL, &n->skip_validation, + NULL, NULL, &n->is_enforced, &n->skip_validation, &n->is_no_inherit, yyscanner); n->initially_valid = !n->skip_validation; $$ = (Node *) n; @@ -4162,7 +4183,7 @@ ConstraintElem: n->keys = list_make1(makeString($3)); /* no NOT VALID support yet */ processCASbits($4, @4, "NOT NULL", - NULL, NULL, NULL, + NULL, NULL, NULL, NULL, &n->is_no_inherit, yyscanner); n->initially_valid = true; $$ = (Node *) n; @@ -4183,7 +4204,7 @@ ConstraintElem: n->indexspace = $9; processCASbits($10, @10, "UNIQUE", &n->deferrable, &n->initdeferred, NULL, - NULL, yyscanner); + NULL, NULL, yyscanner); $$ = (Node *) n; } | UNIQUE ExistingIndex ConstraintAttributeSpec @@ -4199,7 +4220,7 @@ ConstraintElem: n->indexspace = NULL; processCASbits($3, @3, "UNIQUE", &n->deferrable, &n->initdeferred, NULL, - NULL, yyscanner); + NULL, NULL, yyscanner); $$ = (Node *) n; } | PRIMARY KEY '(' columnList opt_without_overlaps ')' opt_c_include opt_definition OptConsTableSpace @@ -4217,7 +4238,7 @@ ConstraintElem: n->indexspace = $9; processCASbits($10, @10, "PRIMARY KEY", &n->deferrable, &n->initdeferred, NULL, - NULL, yyscanner); + NULL, NULL, yyscanner); $$ = (Node *) n; } | PRIMARY KEY ExistingIndex ConstraintAttributeSpec @@ -4233,7 +4254,7 @@ ConstraintElem: n->indexspace = NULL; processCASbits($4, @4, "PRIMARY KEY", &n->deferrable, &n->initdeferred, NULL, - NULL, yyscanner); + NULL, NULL, yyscanner); $$ = (Node *) n; } | EXCLUDE access_method_clause '(' ExclusionConstraintList ')' @@ -4253,7 +4274,7 @@ ConstraintElem: n->where_clause = $9; processCASbits($10, @10, "EXCLUDE", &n->deferrable, &n->initdeferred, NULL, - NULL, yyscanner); + NULL, NULL, yyscanner); $$ = (Node *) n; } | FOREIGN KEY '(' columnList optionalPeriodName ')' REFERENCES qualified_name @@ -4282,7 +4303,7 @@ ConstraintElem: n->fk_del_set_cols = ($11)->deleteAction->cols; processCASbits($12, @12, "FOREIGN KEY", &n->deferrable, &n->initdeferred, - &n->skip_validation, NULL, + NULL, &n->skip_validation, NULL, yyscanner); n->initially_valid = !n->skip_validation; $$ = (Node *) n; @@ -4322,8 +4343,9 @@ DomainConstraintElem: n->raw_expr = $3; n->cooked_expr = NULL; processCASbits($5, @5, "CHECK", - NULL, NULL, &n->skip_validation, + NULL, NULL, NULL, &n->skip_validation, &n->is_no_inherit, yyscanner); + n->is_enforced = true; n->initially_valid = !n->skip_validation; $$ = (Node *) n; } @@ -4337,7 +4359,7 @@ DomainConstraintElem: /* no NOT VALID, NO INHERIT support */ processCASbits($3, @3, "NOT NULL", NULL, NULL, NULL, - NULL, yyscanner); + NULL, NULL, yyscanner); n->initially_valid = true; $$ = (Node *) n; } @@ -6000,7 +6022,7 @@ CreateTrigStmt: n->transitionRels = NIL; processCASbits($11, @11, "TRIGGER", &n->deferrable, &n->initdeferred, NULL, - NULL, yyscanner); + NULL, NULL, yyscanner); n->constrrel = $10; $$ = (Node *) n; } @@ -6169,7 +6191,8 @@ ConstraintAttributeSpec: parser_errposition(@2))); /* generic message for other conflicts */ if ((newspec & (CAS_NOT_DEFERRABLE | CAS_DEFERRABLE)) == (CAS_NOT_DEFERRABLE | CAS_DEFERRABLE) || - (newspec & (CAS_INITIALLY_IMMEDIATE | CAS_INITIALLY_DEFERRED)) == (CAS_INITIALLY_IMMEDIATE | CAS_INITIALLY_DEFERRED)) + (newspec & (CAS_INITIALLY_IMMEDIATE | CAS_INITIALLY_DEFERRED)) == (CAS_INITIALLY_IMMEDIATE | CAS_INITIALLY_DEFERRED) || + (newspec & (CAS_NOT_ENFORCED | CAS_ENFORCED)) == (CAS_NOT_ENFORCED | CAS_ENFORCED)) ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR), errmsg("conflicting constraint properties"), @@ -6185,6 +6208,8 @@ ConstraintAttributeElem: | INITIALLY DEFERRED { $$ = CAS_INITIALLY_DEFERRED; } | NOT VALID { $$ = CAS_NOT_VALID; } | NO INHERIT { $$ = CAS_NO_INHERIT; } + | NOT ENFORCED { $$ = CAS_NOT_ENFORCED; } + | ENFORCED { $$ = CAS_ENFORCED; } ; @@ -17688,6 +17713,7 @@ unreserved_keyword: | ENABLE_P | ENCODING | ENCRYPTED + | ENFORCED | ENUM_P | ERROR_P | ESCAPE @@ -18265,6 +18291,7 @@ bare_label_keyword: | ENCODING | ENCRYPTED | END_P + | ENFORCED | ENUM_P | ERROR_P | ESCAPE @@ -19404,8 +19431,8 @@ SplitColQualList(List *qualList, */ static void processCASbits(int cas_bits, int location, const char *constrType, - bool *deferrable, bool *initdeferred, bool *not_valid, - bool *no_inherit, core_yyscan_t yyscanner) + bool *deferrable, bool *initdeferred, bool *is_enforced, + bool *not_valid, bool *no_inherit, core_yyscan_t yyscanner) { /* defaults */ if (deferrable) @@ -19414,6 +19441,8 @@ processCASbits(int cas_bits, int location, const char *constrType, *initdeferred = false; if (not_valid) *not_valid = false; + if (is_enforced) + *is_enforced = true; if (cas_bits & (CAS_DEFERRABLE | CAS_INITIALLY_DEFERRED)) { @@ -19466,6 +19495,41 @@ processCASbits(int cas_bits, int location, const char *constrType, constrType), parser_errposition(location))); } + + if (cas_bits & CAS_NOT_ENFORCED) + { + if (is_enforced) + *is_enforced = false; + else + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + /* translator: %s is CHECK, UNIQUE, or similar */ + errmsg("%s constraints cannot be marked NOT ENFORCED", + constrType), + parser_errposition(location))); + + /* + * NB: The validated status is irrelevant when the constraint is set to + * NOT ENFORCED, but for consistency, it should be set accordingly. + * This ensures that if the constraint is later changed to ENFORCED, it + * will automatically be in the correct NOT VALIDATED state. + */ + if (not_valid) + *not_valid = true; + } + + if (cas_bits & CAS_ENFORCED) + { + if (is_enforced) + *is_enforced = true; + else + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + /* translator: %s is CHECK, UNIQUE, or similar */ + errmsg("%s constraints cannot be marked ENFORCED", + constrType), + parser_errposition(location))); + } } /* diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c index d2d82c9c596..ca028d2a66d 100644 --- a/src/backend/parser/parse_utilcmd.c +++ b/src/backend/parser/parse_utilcmd.c @@ -954,6 +954,8 @@ transformColumnDefinition(CreateStmtContext *cxt, ColumnDef *column) case CONSTR_ATTR_NOT_DEFERRABLE: case CONSTR_ATTR_DEFERRED: case CONSTR_ATTR_IMMEDIATE: + case CONSTR_ATTR_ENFORCED: + case CONSTR_ATTR_NOT_ENFORCED: /* transformConstraintAttrs took care of these */ break; @@ -1093,6 +1095,8 @@ transformTableConstraint(CreateStmtContext *cxt, Constraint *constraint) case CONSTR_ATTR_NOT_DEFERRABLE: case CONSTR_ATTR_DEFERRED: case CONSTR_ATTR_IMMEDIATE: + case CONSTR_ATTR_ENFORCED: + case CONSTR_ATTR_NOT_ENFORCED: elog(ERROR, "invalid context for constraint type %d", constraint->contype); break; @@ -1433,6 +1437,8 @@ 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; @@ -1462,13 +1468,14 @@ expandTableLikeClause(RangeVar *heapRel, TableLikeClause *table_like_clause) n->contype = CONSTR_CHECK; n->conname = pstrdup(ccname); n->location = -1; + n->is_enforced = ccenforced; + n->initially_valid = ccvalid; n->is_no_inherit = ccnoinherit; n->raw_expr = NULL; n->cooked_expr = nodeToString(ccbin_node); /* We can skip validation, since the new table should be empty. */ n->skip_validation = true; - n->initially_valid = true; atsubcmd = makeNode(AlterTableCmd); atsubcmd->subtype = AT_AddConstraint; @@ -2921,9 +2928,11 @@ transformCheckConstraints(CreateStmtContext *cxt, bool skipValidation) return; /* - * If creating a new table (but not a foreign table), we can safely skip - * validation of check constraints, and nonetheless mark them valid. (This - * will override any user-supplied NOT VALID flag.) + * When creating a new table (but not a foreign table), we can safely skip + * the validation of check constraints and mark them as valid based on the + * constraint enforcement flag, since NOT ENFORCED constraints must always + * be marked as NOT VALID. (This will override any user-supplied NOT VALID + * flag.) */ if (skipValidation) { @@ -2932,7 +2941,7 @@ transformCheckConstraints(CreateStmtContext *cxt, bool skipValidation) Constraint *constraint = (Constraint *) lfirst(ckclist); constraint->skip_validation = true; - constraint->initially_valid = true; + constraint->initially_valid = constraint->is_enforced; } } } @@ -3859,6 +3868,7 @@ transformConstraintAttrs(CreateStmtContext *cxt, List *constraintList) Constraint *lastprimarycon = NULL; bool saw_deferrability = false; bool saw_initially = false; + bool saw_enforced = false; ListCell *clist; #define SUPPORTS_ATTRS(node) \ @@ -3954,12 +3964,49 @@ transformConstraintAttrs(CreateStmtContext *cxt, List *constraintList) lastprimarycon->initdeferred = false; break; + case CONSTR_ATTR_ENFORCED: + if (lastprimarycon == NULL || + lastprimarycon->contype != CONSTR_CHECK) + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("misplaced ENFORCED clause"), + parser_errposition(cxt->pstate, con->location))); + if (saw_enforced) + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("multiple ENFORCED/NOT ENFORCED clauses not allowed"), + parser_errposition(cxt->pstate, con->location))); + saw_enforced = true; + lastprimarycon->is_enforced = true; + break; + + case CONSTR_ATTR_NOT_ENFORCED: + if (lastprimarycon == NULL || + lastprimarycon->contype != CONSTR_CHECK) + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("misplaced NOT ENFORCED clause"), + parser_errposition(cxt->pstate, con->location))); + if (saw_enforced) + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("multiple ENFORCED/NOT ENFORCED clauses not allowed"), + parser_errposition(cxt->pstate, con->location))); + saw_enforced = true; + lastprimarycon->is_enforced = false; + + /* A NOT ENFORCED constraint must be marked as invalid. */ + lastprimarycon->skip_validation = true; + lastprimarycon->initially_valid = false; + break; + default: /* Otherwise it's not an attribute */ lastprimarycon = con; /* reset flags for new primary node */ saw_deferrability = false; saw_initially = false; + saw_enforced = false; break; } } diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c index 2089b52d575..16d15f9efb9 100644 --- a/src/backend/utils/adt/ruleutils.c +++ b/src/backend/utils/adt/ruleutils.c @@ -2591,7 +2591,11 @@ pg_get_constraintdef_worker(Oid constraintId, bool fullCommand, appendStringInfoString(&buf, " DEFERRABLE"); if (conForm->condeferred) appendStringInfoString(&buf, " INITIALLY DEFERRED"); - if (!conForm->convalidated) + + /* Validated status is irrelevant when the constraint is NOT ENFORCED. */ + if (!conForm->conenforced) + appendStringInfoString(&buf, " NOT ENFORCED"); + else if (!conForm->convalidated) appendStringInfoString(&buf, " NOT VALID"); /* Cleanup */ diff --git a/src/backend/utils/cache/relcache.c b/src/backend/utils/cache/relcache.c index 3fe74b580a5..43219a9629c 100644 --- a/src/backend/utils/cache/relcache.c +++ b/src/backend/utils/cache/relcache.c @@ -4574,6 +4574,7 @@ CheckConstraintFetch(Relation relation) break; } + check[found].ccenforced = conform->conenforced; check[found].ccvalid = conform->convalidated; check[found].ccnoinherit = conform->connoinherit; check[found].ccname = MemoryContextStrdup(CacheMemoryContext, diff --git a/src/include/access/tupdesc.h b/src/include/access/tupdesc.h index 504ce222500..ff27df9e9a6 100644 --- a/src/include/access/tupdesc.h +++ b/src/include/access/tupdesc.h @@ -29,6 +29,7 @@ typedef struct ConstrCheck { char *ccname; char *ccbin; /* nodeToString representation of expr */ + bool ccenforced; bool ccvalid; bool ccnoinherit; /* this is a non-inheritable constraint */ } ConstrCheck; diff --git a/src/include/catalog/catversion.h b/src/include/catalog/catversion.h index 24212ba57fa..fa300eaa2d2 100644 --- a/src/include/catalog/catversion.h +++ b/src/include/catalog/catversion.h @@ -57,6 +57,6 @@ */ /* yyyymmddN */ -#define CATALOG_VERSION_NO 202412201 +#define CATALOG_VERSION_NO 202501101 #endif diff --git a/src/include/catalog/heap.h b/src/include/catalog/heap.h index 9dea49c52b4..cad830dc39c 100644 --- a/src/include/catalog/heap.h +++ b/src/include/catalog/heap.h @@ -40,6 +40,7 @@ typedef struct CookedConstraint char *name; /* name, or NULL if none */ AttrNumber attnum; /* which attr (only for NOTNULL, DEFAULT) */ Node *expr; /* transformed default or check expr */ + bool is_enforced; /* is enforced? (only for CHECK) */ bool skip_validation; /* skip validation? (only for CHECK) */ bool is_local; /* constraint has local (non-inherited) def */ int16 inhcount; /* number of times constraint is inherited */ diff --git a/src/include/catalog/pg_constraint.h b/src/include/catalog/pg_constraint.h index ba35d481db3..ccc047e5e7a 100644 --- a/src/include/catalog/pg_constraint.h +++ b/src/include/catalog/pg_constraint.h @@ -51,6 +51,7 @@ CATALOG(pg_constraint,2606,ConstraintRelationId) char contype; /* constraint type; see codes below */ bool condeferrable; /* deferrable constraint? */ bool condeferred; /* deferred by default? */ + bool conenforced; /* enforced constraint? */ bool convalidated; /* constraint has been validated? */ /* @@ -222,6 +223,7 @@ extern Oid CreateConstraintEntry(const char *constraintName, char constraintType, bool isDeferrable, bool isDeferred, + bool isEnforced, bool isValidated, Oid parentConstrId, Oid relId, diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h index 38d6ad7dcbd..b191eaaecab 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -2736,6 +2736,8 @@ typedef enum ConstrType /* types of constraints */ CONSTR_ATTR_NOT_DEFERRABLE, CONSTR_ATTR_DEFERRED, CONSTR_ATTR_IMMEDIATE, + CONSTR_ATTR_ENFORCED, + CONSTR_ATTR_NOT_ENFORCED, } ConstrType; /* Foreign key action codes */ @@ -2757,6 +2759,7 @@ typedef struct Constraint char *conname; /* Constraint name, or NULL if unnamed */ bool deferrable; /* DEFERRABLE? */ bool initdeferred; /* INITIALLY DEFERRED? */ + bool is_enforced; /* enforced constraint? */ bool skip_validation; /* skip validation of existing rows? */ bool initially_valid; /* mark the new constraint as valid? */ bool is_no_inherit; /* is constraint non-inheritable? */ diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h index 24c22a8694b..cf2917ad07e 100644 --- a/src/include/parser/kwlist.h +++ b/src/include/parser/kwlist.h @@ -153,6 +153,7 @@ PG_KEYWORD("enable", ENABLE_P, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("encoding", ENCODING, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("encrypted", ENCRYPTED, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("end", END_P, RESERVED_KEYWORD, BARE_LABEL) +PG_KEYWORD("enforced", ENFORCED, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("enum", ENUM_P, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("error", ERROR_P, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("escape", ESCAPE, UNRESERVED_KEYWORD, BARE_LABEL) diff --git a/src/test/regress/expected/alter_table.out b/src/test/regress/expected/alter_table.out index 12852aa612a..dd8cdec2905 100644 --- a/src/test/regress/expected/alter_table.out +++ b/src/test/regress/expected/alter_table.out @@ -507,11 +507,14 @@ ALTER TABLE attmp3 validate constraint attmpconstr; ALTER TABLE attmp3 ADD CONSTRAINT b_greater_than_ten CHECK (b > 10); -- fail ERROR: check constraint "b_greater_than_ten" of relation "attmp3" is violated by some row ALTER TABLE attmp3 ADD CONSTRAINT b_greater_than_ten CHECK (b > 10) NOT VALID; -- succeeds +ALTER TABLE attmp3 ADD CONSTRAINT b_greater_than_ten_not_enforced CHECK (b > 10) NOT ENFORCED; -- succeeds ALTER TABLE attmp3 VALIDATE CONSTRAINT b_greater_than_ten; -- fails ERROR: check constraint "b_greater_than_ten" of relation "attmp3" is violated by some row DELETE FROM attmp3 WHERE NOT b > 10; ALTER TABLE attmp3 VALIDATE CONSTRAINT b_greater_than_ten; -- succeeds ALTER TABLE attmp3 VALIDATE CONSTRAINT b_greater_than_ten; -- succeeds +ALTER TABLE attmp3 VALIDATE CONSTRAINT b_greater_than_ten_not_enforced; -- fail +ERROR: cannot validate NOT ENFORCED constraint -- Test inherited NOT VALID CHECK constraints select * from attmp3; a | b @@ -1689,6 +1692,13 @@ alter table renameColumn add column w int; -- this should fail alter table only renameColumn add column x int; ERROR: column must be added to child tables too +-- this should work +alter table renameColumn add column x int check (x > 0) not enforced; +-- this should fail +alter table renameColumn add column y int check (x > 0) not enforced enforced; +ERROR: multiple ENFORCED/NOT ENFORCED clauses not allowed +LINE 1: ...Column add column y int check (x > 0) not enforced enforced; + ^ -- Test corner cases in dropping of inherited columns create table p1 (f1 int, f2 int); create table c1 (f1 int not null) inherits(p1); diff --git a/src/test/regress/expected/constraints.out b/src/test/regress/expected/constraints.out index 71200c90ed3..692a69fe457 100644 --- a/src/test/regress/expected/constraints.out +++ b/src/test/regress/expected/constraints.out @@ -87,6 +87,25 @@ SELECT * FROM CHECK_TBL; 6 (3 rows) +CREATE TABLE NE_CHECK_TBL (x int, + CONSTRAINT CHECK_CON CHECK (x > 3) NOT ENFORCED); +INSERT INTO NE_CHECK_TBL VALUES (5); +INSERT INTO NE_CHECK_TBL VALUES (4); +INSERT INTO NE_CHECK_TBL VALUES (3); +INSERT INTO NE_CHECK_TBL VALUES (2); +INSERT INTO NE_CHECK_TBL VALUES (6); +INSERT INTO NE_CHECK_TBL VALUES (1); +SELECT * FROM NE_CHECK_TBL; + x +--- + 5 + 4 + 3 + 2 + 6 + 1 +(6 rows) + CREATE SEQUENCE CHECK_SEQ; CREATE TABLE CHECK2_TBL (x int, y text, z int, CONSTRAINT SEQUENCE_CON @@ -120,7 +139,8 @@ CREATE TABLE INSERT_TBL (x INT DEFAULT nextval('insert_seq'), y TEXT DEFAULT '-NULL-', z INT DEFAULT -1 * currval('insert_seq'), CONSTRAINT INSERT_TBL_CON CHECK (x >= 3 AND y <> 'check failed' AND x < 8), - CHECK (x + z = 0)); + CHECK (x + z = 0) ENFORCED, /* no change it is a default */ + CONSTRAINT NE_INSERT_TBL_CON CHECK (x + z = 1) NOT ENFORCED); INSERT INTO INSERT_TBL(x,z) VALUES (2, -2); ERROR: new row for relation "insert_tbl" violates check constraint "insert_tbl_con" DETAIL: Failing row contains (2, -NULL-, -2). @@ -715,6 +735,24 @@ SELECT * FROM unique_tbl; 3 | threex (5 rows) +-- enforcibility cannot be specified or set for unique constrain +CREATE TABLE UNIQUE_EN_TBL(i int UNIQUE ENFORCED); +ERROR: misplaced ENFORCED clause +LINE 1: CREATE TABLE UNIQUE_EN_TBL(i int UNIQUE ENFORCED); + ^ +CREATE TABLE UNIQUE_NOTEN_TBL(i int UNIQUE NOT ENFORCED); +ERROR: misplaced NOT ENFORCED clause +LINE 1: CREATE TABLE UNIQUE_NOTEN_TBL(i int UNIQUE NOT ENFORCED); + ^ +-- XXX: error message is misleading here +ALTER TABLE unique_tbl ALTER CONSTRAINT unique_tbl_i_key ENFORCED; +ERROR: ALTER CONSTRAINT statement constraints cannot be marked ENFORCED +LINE 1: ...TABLE unique_tbl ALTER CONSTRAINT unique_tbl_i_key ENFORCED; + ^ +ALTER TABLE unique_tbl ALTER CONSTRAINT unique_tbl_i_key NOT ENFORCED; +ERROR: ALTER CONSTRAINT statement constraints cannot be marked NOT ENFORCED +LINE 1: ...ABLE unique_tbl ALTER CONSTRAINT unique_tbl_i_key NOT ENFORC... + ^ DROP TABLE unique_tbl; -- -- EXCLUDE constraints diff --git a/src/test/regress/expected/create_table_like.out b/src/test/regress/expected/create_table_like.out index d091da5a1ef..e0613891351 100644 --- a/src/test/regress/expected/create_table_like.out +++ b/src/test/regress/expected/create_table_like.out @@ -315,7 +315,8 @@ Referenced by: DROP TABLE inhz; -- including storage and comments -CREATE TABLE ctlt1 (a text CHECK (length(a) > 2) PRIMARY KEY, b text); +CREATE TABLE ctlt1 (a text CHECK (length(a) > 2) ENFORCED PRIMARY KEY, + b text CHECK (length(b) > 100) NOT ENFORCED); 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; @@ -366,6 +367,7 @@ CREATE TABLE ctlt1_inh (LIKE ctlt1 INCLUDING CONSTRAINTS INCLUDING COMMENTS) INH NOTICE: merging column "a" with inherited definition NOTICE: merging column "b" with inherited definition NOTICE: merging constraint "ctlt1_a_check" with inherited definition +NOTICE: merging constraint "ctlt1_b_check" with inherited definition \d+ ctlt1_inh Table "public.ctlt1_inh" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description @@ -374,6 +376,7 @@ NOTICE: merging constraint "ctlt1_a_check" with inherited definition b | text | | | | extended | | B Check constraints: "ctlt1_a_check" CHECK (length(a) > 2) + "ctlt1_b_check" CHECK (length(b) > 100) NOT ENFORCED Not-null constraints: "ctlt1_a_not_null" NOT NULL "a" (local, inherited) Inherits: ctlt1 @@ -395,6 +398,7 @@ NOTICE: merging multiple inherited definitions of column "a" c | text | | | | external | | Check constraints: "ctlt1_a_check" CHECK (length(a) > 2) + "ctlt1_b_check" CHECK (length(b) > 100) NOT ENFORCED "ctlt3_a_check" CHECK (length(a) < 5) "ctlt3_c_check" CHECK (length(c) < 7) Not-null constraints: @@ -415,6 +419,7 @@ Indexes: "ctlt13_like_expr_idx" btree ((a || c)) Check constraints: "ctlt1_a_check" CHECK (length(a) > 2) + "ctlt1_b_check" CHECK (length(b) > 100) NOT ENFORCED "ctlt3_a_check" CHECK (length(a) < 5) "ctlt3_c_check" CHECK (length(c) < 7) Not-null constraints: @@ -440,6 +445,7 @@ Indexes: "ctlt_all_expr_idx" btree ((a || b)) Check constraints: "ctlt1_a_check" CHECK (length(a) > 2) + "ctlt1_b_check" CHECK (length(b) > 100) NOT ENFORCED Statistics objects: "public.ctlt_all_a_b_stat" ON a, b FROM ctlt_all "public.ctlt_all_expr_stat" ON (a || b) FROM ctlt_all @@ -482,6 +488,7 @@ Indexes: "pg_attrdef_expr_idx" btree ((a || b)) Check constraints: "ctlt1_a_check" CHECK (length(a) > 2) + "ctlt1_b_check" CHECK (length(b) > 100) NOT ENFORCED Statistics objects: "public.pg_attrdef_a_b_stat" ON a, b FROM public.pg_attrdef "public.pg_attrdef_expr_stat" ON (a || b) FROM public.pg_attrdef @@ -506,6 +513,7 @@ Indexes: "ctlt1_expr_idx" btree ((a || b)) Check constraints: "ctlt1_a_check" CHECK (length(a) > 2) + "ctlt1_b_check" CHECK (length(b) > 100) NOT ENFORCED Statistics objects: "ctl_schema.ctlt1_a_b_stat" ON a, b FROM ctlt1 "ctl_schema.ctlt1_expr_stat" ON (a || b) FROM ctlt1 diff --git a/src/test/regress/expected/domain.out b/src/test/regress/expected/domain.out index 7a2a717aeae..ba6f05eeb7d 100644 --- a/src/test/regress/expected/domain.out +++ b/src/test/regress/expected/domain.out @@ -1350,6 +1350,28 @@ select pg_basetype(1); -- expect NULL not error drop domain mytext cascade; NOTICE: drop cascades to type mytext_child_1 -- +-- Explicit enforceability specification not allowed +--- +CREATE DOMAIN constraint_enforced_dom AS int CONSTRAINT the_constraint CHECK (value > 0) ENFORCED; +ERROR: specifying constraint enforceability not supported for domains +LINE 1: ...AS int CONSTRAINT the_constraint CHECK (value > 0) ENFORCED; + ^ +CREATE DOMAIN constraint_not_enforced_dom AS int CONSTRAINT the_constraint CHECK (value > 0) NOT ENFORCED; +ERROR: specifying constraint enforceability not supported for domains +LINE 1: ...S int CONSTRAINT the_constraint CHECK (value > 0) NOT ENFORC... + ^ +CREATE DOMAIN constraint_enforced_dom AS int; +-- XXX misleading error messages +ALTER DOMAIN constraint_enforced_dom ADD CONSTRAINT the_constraint CHECK (value > 0) ENFORCED; +ERROR: CHECK constraints cannot be marked ENFORCED +LINE 1: ...om ADD CONSTRAINT the_constraint CHECK (value > 0) ENFORCED; + ^ +ALTER DOMAIN constraint_enforced_dom ADD CONSTRAINT the_constraint CHECK (value > 0) NOT ENFORCED; +ERROR: CHECK constraints cannot be marked NOT ENFORCED +LINE 1: ...m ADD CONSTRAINT the_constraint CHECK (value > 0) NOT ENFORC... + ^ +DROP DOMAIN constraint_enforced_dom; +-- -- Information schema -- SELECT * FROM information_schema.column_domain_usage diff --git a/src/test/regress/expected/inherit.out b/src/test/regress/expected/inherit.out index bb81f6d2b4d..dbf3835cb14 100644 --- a/src/test/regress/expected/inherit.out +++ b/src/test/regress/expected/inherit.out @@ -1319,19 +1319,97 @@ NOTICE: merging constraint "inh_check_constraint1" with inherited definition alter table p1_c1 add constraint inh_check_constraint2 check (f1 < 10); alter table p1 add constraint inh_check_constraint2 check (f1 < 10); NOTICE: merging constraint "inh_check_constraint2" with inherited definition -select conrelid::regclass::text as relname, conname, conislocal, coninhcount +alter table p1 add constraint inh_check_constraint3 check (f1 > 0) not enforced; +alter table p1_c1 add constraint inh_check_constraint3 check (f1 > 0) not enforced; +NOTICE: merging constraint "inh_check_constraint3" with inherited definition +alter table p1_c1 add constraint inh_check_constraint4 check (f1 < 10) not enforced; +alter table p1 add constraint inh_check_constraint4 check (f1 < 10) not enforced; +NOTICE: merging constraint "inh_check_constraint4" with inherited definition +-- allowed to merge enforced constraint with parent's not enforced constraint +alter table p1_c1 add constraint inh_check_constraint5 check (f1 < 10) enforced; +alter table p1 add constraint inh_check_constraint5 check (f1 < 10) not enforced; +NOTICE: merging constraint "inh_check_constraint5" with inherited definition +alter table p1 add constraint inh_check_constraint6 check (f1 < 10) not enforced; +alter table p1_c1 add constraint inh_check_constraint6 check (f1 < 10) enforced; +NOTICE: merging constraint "inh_check_constraint6" with inherited definition +create table p1_c2(f1 int constraint inh_check_constraint4 check (f1 < 10)) inherits(p1); +NOTICE: merging column "f1" with inherited definition +NOTICE: merging constraint "inh_check_constraint4" with inherited definition +-- but reverse is not allowed +alter table p1_c1 add constraint inh_check_constraint7 check (f1 < 10) not enforced; +alter table p1 add constraint inh_check_constraint7 check (f1 < 10) enforced; +ERROR: constraint "inh_check_constraint7" conflicts with NOT ENFORCED constraint on relation "p1_c1" +alter table p1 add constraint inh_check_constraint8 check (f1 < 10) enforced; +alter table p1_c1 add constraint inh_check_constraint8 check (f1 < 10) not enforced; +ERROR: constraint "inh_check_constraint8" conflicts with NOT ENFORCED constraint on relation "p1_c1" +create table p1_fail(f1 int constraint inh_check_constraint2 check (f1 < 10) not enforced) inherits(p1); +NOTICE: merging column "f1" with inherited definition +ERROR: constraint "inh_check_constraint2" conflicts with NOT ENFORCED constraint on relation "p1_fail" +-- constraints with different enforceability can be merged by marking them as ENFORCED +create table p1_c3() inherits(p1, p1_c1); +NOTICE: merging multiple inherited definitions of column "f1" +-- but not allowed if the child constraint is explicitly asked to be NOT ENFORCED +create table p1_fail(f1 int constraint inh_check_constraint6 check (f1 < 10) not enforced) inherits(p1, p1_c1); +NOTICE: merging multiple inherited definitions of column "f1" +NOTICE: merging column "f1" with inherited definition +ERROR: constraint "inh_check_constraint6" conflicts with NOT ENFORCED constraint on relation "p1_fail" +select conrelid::regclass::text as relname, conname, conislocal, coninhcount, conenforced from pg_constraint where conname like 'inh\_check\_constraint%' order by 1, 2; - relname | conname | conislocal | coninhcount ----------+-----------------------+------------+------------- - p1 | inh_check_constraint1 | t | 0 - p1 | inh_check_constraint2 | t | 0 - p1_c1 | inh_check_constraint1 | t | 1 - p1_c1 | inh_check_constraint2 | t | 1 -(4 rows) + relname | conname | conislocal | coninhcount | conenforced +---------+-----------------------+------------+-------------+------------- + p1 | inh_check_constraint1 | t | 0 | t + p1 | inh_check_constraint2 | t | 0 | t + p1 | inh_check_constraint3 | t | 0 | f + p1 | inh_check_constraint4 | t | 0 | f + p1 | inh_check_constraint5 | t | 0 | f + p1 | inh_check_constraint6 | t | 0 | f + p1 | inh_check_constraint8 | t | 0 | t + p1_c1 | inh_check_constraint1 | t | 1 | t + p1_c1 | inh_check_constraint2 | t | 1 | t + p1_c1 | inh_check_constraint3 | t | 1 | f + p1_c1 | inh_check_constraint4 | t | 1 | f + p1_c1 | inh_check_constraint5 | t | 1 | t + p1_c1 | inh_check_constraint6 | t | 1 | t + p1_c1 | inh_check_constraint7 | t | 0 | f + p1_c1 | inh_check_constraint8 | f | 1 | t + p1_c2 | inh_check_constraint1 | f | 1 | t + p1_c2 | inh_check_constraint2 | f | 1 | t + p1_c2 | inh_check_constraint3 | f | 1 | f + p1_c2 | inh_check_constraint4 | t | 1 | t + p1_c2 | inh_check_constraint5 | f | 1 | f + p1_c2 | inh_check_constraint6 | f | 1 | f + p1_c2 | inh_check_constraint8 | f | 1 | t + p1_c3 | inh_check_constraint1 | f | 2 | t + p1_c3 | inh_check_constraint2 | f | 2 | t + p1_c3 | inh_check_constraint3 | f | 2 | f + p1_c3 | inh_check_constraint4 | f | 2 | f + p1_c3 | inh_check_constraint5 | f | 2 | t + p1_c3 | inh_check_constraint6 | f | 2 | t + p1_c3 | inh_check_constraint7 | f | 1 | f + p1_c3 | inh_check_constraint8 | f | 2 | t +(30 rows) drop table p1 cascade; +NOTICE: drop cascades to 3 other objects +DETAIL: drop cascades to table p1_c1 +drop cascades to table p1_c2 +drop cascades to table p1_c3 +-- +-- Similarly, check the merging of existing constraints; a parent constraint +-- marked as NOT ENFORCED can merge with an ENFORCED child constraint, but the +-- reverse is not allowed. +-- +create table p1(f1 int constraint p1_a_check check (f1 > 0) not enforced); +create table p1_c1(f1 int constraint p1_a_check check (f1 > 0) enforced); +alter table p1_c1 inherit p1; +drop table p1 cascade; NOTICE: drop cascades to table p1_c1 +create table p1(f1 int constraint p1_a_check check (f1 > 0) enforced); +create table p1_c1(f1 int constraint p1_a_check check (f1 > 0) not enforced); +alter table p1_c1 inherit p1; +ERROR: constraint "p1_a_check" conflicts with NOT ENFORCED constraint on child table "p1_c1" +drop table p1, p1_c1; -- -- Test DROP behavior of multiply-defined CHECK constraints -- diff --git a/src/test/regress/sql/alter_table.sql b/src/test/regress/sql/alter_table.sql index c88f9eaab04..84e93ef575e 100644 --- a/src/test/regress/sql/alter_table.sql +++ b/src/test/regress/sql/alter_table.sql @@ -387,10 +387,12 @@ ALTER TABLE attmp3 validate constraint attmpconstr; -- Try a non-verified CHECK constraint ALTER TABLE attmp3 ADD CONSTRAINT b_greater_than_ten CHECK (b > 10); -- fail ALTER TABLE attmp3 ADD CONSTRAINT b_greater_than_ten CHECK (b > 10) NOT VALID; -- succeeds +ALTER TABLE attmp3 ADD CONSTRAINT b_greater_than_ten_not_enforced CHECK (b > 10) NOT ENFORCED; -- succeeds ALTER TABLE attmp3 VALIDATE CONSTRAINT b_greater_than_ten; -- fails DELETE FROM attmp3 WHERE NOT b > 10; ALTER TABLE attmp3 VALIDATE CONSTRAINT b_greater_than_ten; -- succeeds ALTER TABLE attmp3 VALIDATE CONSTRAINT b_greater_than_ten; -- succeeds +ALTER TABLE attmp3 VALIDATE CONSTRAINT b_greater_than_ten_not_enforced; -- fail -- Test inherited NOT VALID CHECK constraints select * from attmp3; @@ -1188,6 +1190,11 @@ alter table renameColumn add column w int; -- this should fail alter table only renameColumn add column x int; +-- this should work +alter table renameColumn add column x int check (x > 0) not enforced; + +-- this should fail +alter table renameColumn add column y int check (x > 0) not enforced enforced; -- Test corner cases in dropping of inherited columns diff --git a/src/test/regress/sql/constraints.sql b/src/test/regress/sql/constraints.sql index e607eb1fddb..d6742f83fb9 100644 --- a/src/test/regress/sql/constraints.sql +++ b/src/test/regress/sql/constraints.sql @@ -67,6 +67,18 @@ INSERT INTO CHECK_TBL VALUES (1); SELECT * FROM CHECK_TBL; +CREATE TABLE NE_CHECK_TBL (x int, + CONSTRAINT CHECK_CON CHECK (x > 3) NOT ENFORCED); + +INSERT INTO NE_CHECK_TBL VALUES (5); +INSERT INTO NE_CHECK_TBL VALUES (4); +INSERT INTO NE_CHECK_TBL VALUES (3); +INSERT INTO NE_CHECK_TBL VALUES (2); +INSERT INTO NE_CHECK_TBL VALUES (6); +INSERT INTO NE_CHECK_TBL VALUES (1); + +SELECT * FROM NE_CHECK_TBL; + CREATE SEQUENCE CHECK_SEQ; CREATE TABLE CHECK2_TBL (x int, y text, z int, @@ -92,7 +104,8 @@ CREATE TABLE INSERT_TBL (x INT DEFAULT nextval('insert_seq'), y TEXT DEFAULT '-NULL-', z INT DEFAULT -1 * currval('insert_seq'), CONSTRAINT INSERT_TBL_CON CHECK (x >= 3 AND y <> 'check failed' AND x < 8), - CHECK (x + z = 0)); + CHECK (x + z = 0) ENFORCED, /* no change it is a default */ + CONSTRAINT NE_INSERT_TBL_CON CHECK (x + z = 1) NOT ENFORCED); INSERT INTO INSERT_TBL(x,z) VALUES (2, -2); @@ -518,6 +531,13 @@ COMMIT; SELECT * FROM unique_tbl; +-- enforcibility cannot be specified or set for unique constrain +CREATE TABLE UNIQUE_EN_TBL(i int UNIQUE ENFORCED); +CREATE TABLE UNIQUE_NOTEN_TBL(i int UNIQUE NOT ENFORCED); +-- XXX: error message is misleading here +ALTER TABLE unique_tbl ALTER CONSTRAINT unique_tbl_i_key ENFORCED; +ALTER TABLE unique_tbl ALTER CONSTRAINT unique_tbl_i_key NOT ENFORCED; + DROP TABLE unique_tbl; -- diff --git a/src/test/regress/sql/create_table_like.sql b/src/test/regress/sql/create_table_like.sql index dea8942c71f..a41f8b83d77 100644 --- a/src/test/regress/sql/create_table_like.sql +++ b/src/test/regress/sql/create_table_like.sql @@ -128,7 +128,8 @@ CREATE TABLE inhz (x text REFERENCES inhz, LIKE inhx INCLUDING INDEXES); DROP TABLE inhz; -- including storage and comments -CREATE TABLE ctlt1 (a text CHECK (length(a) > 2) PRIMARY KEY, b text); +CREATE TABLE ctlt1 (a text CHECK (length(a) > 2) ENFORCED PRIMARY KEY, + b text CHECK (length(b) > 100) NOT ENFORCED); 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; diff --git a/src/test/regress/sql/domain.sql b/src/test/regress/sql/domain.sql index ad14de355ac..b752a63ab5f 100644 --- a/src/test/regress/sql/domain.sql +++ b/src/test/regress/sql/domain.sql @@ -880,6 +880,16 @@ select pg_basetype(1); -- expect NULL not error drop domain mytext cascade; +-- +-- Explicit enforceability specification not allowed +--- +CREATE DOMAIN constraint_enforced_dom AS int CONSTRAINT the_constraint CHECK (value > 0) ENFORCED; +CREATE DOMAIN constraint_not_enforced_dom AS int CONSTRAINT the_constraint CHECK (value > 0) NOT ENFORCED; +CREATE DOMAIN constraint_enforced_dom AS int; +-- XXX misleading error messages +ALTER DOMAIN constraint_enforced_dom ADD CONSTRAINT the_constraint CHECK (value > 0) ENFORCED; +ALTER DOMAIN constraint_enforced_dom ADD CONSTRAINT the_constraint CHECK (value > 0) NOT ENFORCED; +DROP DOMAIN constraint_enforced_dom; -- -- Information schema diff --git a/src/test/regress/sql/inherit.sql b/src/test/regress/sql/inherit.sql index f51c70d6b03..49aae426f3c 100644 --- a/src/test/regress/sql/inherit.sql +++ b/src/test/regress/sql/inherit.sql @@ -468,13 +468,58 @@ alter table p1_c1 add constraint inh_check_constraint1 check (f1 > 0); alter table p1_c1 add constraint inh_check_constraint2 check (f1 < 10); alter table p1 add constraint inh_check_constraint2 check (f1 < 10); -select conrelid::regclass::text as relname, conname, conislocal, coninhcount +alter table p1 add constraint inh_check_constraint3 check (f1 > 0) not enforced; +alter table p1_c1 add constraint inh_check_constraint3 check (f1 > 0) not enforced; + +alter table p1_c1 add constraint inh_check_constraint4 check (f1 < 10) not enforced; +alter table p1 add constraint inh_check_constraint4 check (f1 < 10) not enforced; + +-- allowed to merge enforced constraint with parent's not enforced constraint +alter table p1_c1 add constraint inh_check_constraint5 check (f1 < 10) enforced; +alter table p1 add constraint inh_check_constraint5 check (f1 < 10) not enforced; + +alter table p1 add constraint inh_check_constraint6 check (f1 < 10) not enforced; +alter table p1_c1 add constraint inh_check_constraint6 check (f1 < 10) enforced; + +create table p1_c2(f1 int constraint inh_check_constraint4 check (f1 < 10)) inherits(p1); + +-- but reverse is not allowed +alter table p1_c1 add constraint inh_check_constraint7 check (f1 < 10) not enforced; +alter table p1 add constraint inh_check_constraint7 check (f1 < 10) enforced; + +alter table p1 add constraint inh_check_constraint8 check (f1 < 10) enforced; +alter table p1_c1 add constraint inh_check_constraint8 check (f1 < 10) not enforced; + +create table p1_fail(f1 int constraint inh_check_constraint2 check (f1 < 10) not enforced) inherits(p1); + +-- constraints with different enforceability can be merged by marking them as ENFORCED +create table p1_c3() inherits(p1, p1_c1); + +-- but not allowed if the child constraint is explicitly asked to be NOT ENFORCED +create table p1_fail(f1 int constraint inh_check_constraint6 check (f1 < 10) not enforced) inherits(p1, p1_c1); + +select conrelid::regclass::text as relname, conname, conislocal, coninhcount, conenforced from pg_constraint where conname like 'inh\_check\_constraint%' order by 1, 2; drop table p1 cascade; -- +-- Similarly, check the merging of existing constraints; a parent constraint +-- marked as NOT ENFORCED can merge with an ENFORCED child constraint, but the +-- reverse is not allowed. +-- +create table p1(f1 int constraint p1_a_check check (f1 > 0) not enforced); +create table p1_c1(f1 int constraint p1_a_check check (f1 > 0) enforced); +alter table p1_c1 inherit p1; +drop table p1 cascade; + +create table p1(f1 int constraint p1_a_check check (f1 > 0) enforced); +create table p1_c1(f1 int constraint p1_a_check check (f1 > 0) not enforced); +alter table p1_c1 inherit p1; +drop table p1, p1_c1; + +-- -- Test DROP behavior of multiply-defined CHECK constraints -- create table p1(f1 int constraint f1_pos CHECK (f1 > 0)); |