From 6f1b2af3b38b4d0e9258bc0ff500d003882dd8bd Mon Sep 17 00:00:00 2001 From: Amul Sul Date: Fri, 4 Oct 2024 18:22:42 +0530 Subject: [PATCH v2 1/5] Add support for NOT ENFORCED in CHECK constraints. This added basic infrastructure for the NOT ENFORCED/ENFORCED constraint supports where incluing grammar and catalog flags. Note that CHECK constraints do not currently support ALTER operations, so changing the enforceability of an existing constraint isn't possible without dropping and recreating it. We could consider adding support for altering CHECK constraints either in this patch series or as a separatly. --- doc/src/sgml/ddl.sgml | 8 ++- doc/src/sgml/ref/alter_table.sgml | 14 +++-- doc/src/sgml/ref/create_table.sgml | 18 +++++- src/backend/access/common/tupdesc.c | 4 +- src/backend/catalog/heap.c | 12 ++-- src/backend/catalog/index.c | 1 + src/backend/catalog/pg_constraint.c | 5 ++ src/backend/commands/tablecmds.c | 54 +++++++++++------ src/backend/commands/trigger.c | 1 + src/backend/commands/typecmds.c | 14 +++++ src/backend/executor/execMain.c | 8 ++- src/backend/parser/gram.y | 72 ++++++++++++++++++----- src/backend/parser/parse_utilcmd.c | 39 ++++++++++++ src/backend/utils/adt/ruleutils.c | 7 +++ src/backend/utils/cache/relcache.c | 1 + src/include/access/tupdesc.h | 1 + src/include/catalog/heap.h | 1 + src/include/catalog/pg_constraint.h | 2 + src/include/nodes/parsenodes.h | 3 + src/include/parser/kwlist.h | 1 + src/test/regress/expected/constraints.out | 22 ++++++- src/test/regress/sql/constraints.sql | 15 ++++- 22 files changed, 250 insertions(+), 53 deletions(-) diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml index f02f67d7b86..5f801903ddd 100644 --- a/doc/src/sgml/ddl.sgml +++ b/doc/src/sgml/ddl.sgml @@ -582,7 +582,10 @@ CREATE TABLE products ( consists of the key word CHECK followed by an expression in parentheses. The check constraint expression should involve the column thus constrained, otherwise the constraint - would not make too much sense. + would not make too much sense. The check constraint expression can be + followed by the ENFORCED, which is the default, or + NOT ENFORCED keyword, determining whether the + constraint check is performed or skipped after each statement. @@ -718,7 +721,8 @@ CREATE TABLE products ( to implement that. (This approach avoids the dump/restore problem because pg_dump does not reinstall triggers until after restoring data, so that the check will not be enforced during a - dump/restore.) + dump/restore.) You can add that CHECK constraint with NOT ENFORCED, + allowing the underlying constraint rule to be represented without being enforced. diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml index 36770c012a6..88d9169d7a6 100644 --- a/doc/src/sgml/ref/alter_table.sgml +++ b/doc/src/sgml/ref/alter_table.sgml @@ -58,7 +58,7 @@ ALTER TABLE [ IF EXISTS ] name ALTER [ COLUMN ] column_name SET COMPRESSION compression_method ADD table_constraint [ NOT VALID ] ADD table_constraint_using_index - ALTER CONSTRAINT constraint_name [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ] + ALTER CONSTRAINT constraint_name [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ] [ ENFORCED | NOT ENFORCED ] VALIDATE CONSTRAINT constraint_name DROP CONSTRAINT [ IF EXISTS ] constraint_name [ RESTRICT | CASCADE ] DISABLE TRIGGER [ trigger_name | ALL | USER ] @@ -108,7 +108,7 @@ WITH ( MODULUS numeric_literal, REM PRIMARY KEY index_parameters | REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE referential_action ] [ ON UPDATE referential_action ] } -[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ] +[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ] [ ENFORCED | NOT ENFORCED ] and table_constraint is: @@ -119,7 +119,7 @@ WITH ( MODULUS numeric_literal, REM EXCLUDE [ USING index_method ] ( exclude_element WITH operator [, ... ] ) index_parameters [ WHERE ( predicate ) ] | FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE referential_action ] [ ON UPDATE referential_action ] } -[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ] +[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ] [ ENFORCED | NOT ENFORCED ] and table_constraint_using_index is: @@ -1427,9 +1427,11 @@ WITH ( MODULUS numeric_literal, REM - Adding a CHECK or NOT NULL constraint requires - scanning the table to verify that existing rows meet the constraint, - but does not require a table rewrite. + Adding an enforced CHECK or NOT NULL + constraint requires scanning the table to verify that existing rows meet the + constraint, but does not require a table rewrite. If a CHECK + constraint is added as NOT ENFORCED, the validation will + not be performed. diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml index 83859bac76f..55b51602136 100644 --- a/doc/src/sgml/ref/create_table.sgml +++ b/doc/src/sgml/ref/create_table.sgml @@ -71,7 +71,7 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI PRIMARY KEY index_parameters | REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE referential_action ] [ ON UPDATE referential_action ] } -[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ] +[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ] [ ENFORCED | NOT ENFORCED ] and table_constraint is: @@ -83,7 +83,7 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI FOREIGN KEY ( column_name [, ... ] [, PERIOD column_name ] ) REFERENCES reftable [ ( refcolumn [, ... ] [, PERIOD column_name ] ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE referential_action ] [ ON UPDATE referential_action ] } -[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ] +[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ] [ ENFORCED | NOT ENFORCED ] and like_option is: @@ -1362,6 +1362,20 @@ WITH ( MODULUS numeric_literal, REM + + ENFORCED + NOT ENFORCED + + + This is currently only allowed for CHECK constraints. + If the constraint is NOT ENFORCED, this clause + specifies that the constraint check will be skipped. When the constraint + is ENFORCED, check is performed after each statement. + This is the default. + + + + USING method diff --git a/src/backend/access/common/tupdesc.c b/src/backend/access/common/tupdesc.c index 47379fef220..41dd00e5547 100644 --- a/src/backend/access/common/tupdesc.c +++ b/src/backend/access/common/tupdesc.c @@ -226,6 +226,7 @@ CreateTupleDescCopyConstr(TupleDesc tupdesc) cpy->check[i].ccbin = pstrdup(constr->check[i].ccbin); cpy->check[i].ccvalid = constr->check[i].ccvalid; cpy->check[i].ccnoinherit = constr->check[i].ccnoinherit; + cpy->check[i].ccenforced = constr->check[i].ccenforced; } } @@ -548,7 +549,8 @@ equalTupleDescs(TupleDesc tupdesc1, TupleDesc tupdesc2) if (!(strcmp(check1->ccname, check2->ccname) == 0 && strcmp(check1->ccbin, check2->ccbin) == 0 && check1->ccvalid == check2->ccvalid && - check1->ccnoinherit == check2->ccnoinherit)) + check1->ccnoinherit == check2->ccnoinherit && + check1->ccenforced == check2->ccenforced)) return false; } } diff --git a/src/backend/catalog/heap.c b/src/backend/catalog/heap.c index c54a543c536..39bc76e3361 100644 --- a/src/backend/catalog/heap.c +++ b/src/backend/catalog/heap.c @@ -103,7 +103,7 @@ static ObjectAddress AddNewRelationType(const char *typeName, 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_no_inherit, bool is_enforced, bool is_internal); static void StoreConstraints(Relation rel, List *cooked_constraints, bool is_internal); static bool MergeWithExistingConstraint(Relation rel, const char *ccname, Node *expr, @@ -2075,7 +2075,7 @@ 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_no_inherit, bool is_enforced, bool is_internal) { char *ccbin; List *varList; @@ -2141,6 +2141,7 @@ StoreRelCheck(Relation rel, const char *ccname, Node *expr, false, /* Is Deferrable */ false, /* Is Deferred */ is_validated, + is_enforced, /* Is enforced */ InvalidOid, /* no parent constraint */ RelationGetRelid(rel), /* relation */ attNos, /* attrs in the constraint */ @@ -2214,7 +2215,7 @@ StoreConstraints(Relation rel, List *cooked_constraints, bool is_internal) StoreRelCheck(rel, con->name, con->expr, !con->skip_validation, con->is_local, con->inhcount, con->is_no_inherit, - is_internal); + con->is_enforced, is_internal); numchecks++; break; default: @@ -2347,6 +2348,7 @@ AddRelationNewConstraints(Relation rel, cooked->attnum = colDef->attnum; cooked->expr = expr; cooked->skip_validation = false; + cooked->is_enforced = true; cooked->is_local = is_local; cooked->inhcount = is_local ? 0 : 1; cooked->is_no_inherit = false; @@ -2465,7 +2467,8 @@ AddRelationNewConstraints(Relation rel, */ constrOid = StoreRelCheck(rel, ccname, expr, cdef->initially_valid, is_local, - is_local ? 0 : 1, cdef->is_no_inherit, is_internal); + is_local ? 0 : 1, cdef->is_no_inherit, + cdef->is_enforced, is_internal); numchecks++; @@ -2476,6 +2479,7 @@ AddRelationNewConstraints(Relation rel, cooked->attnum = 0; cooked->expr = expr; cooked->skip_validation = cdef->skip_validation; + cooked->is_enforced = cdef->is_enforced; cooked->is_local = is_local; cooked->inhcount = is_local ? 0 : 1; cooked->is_no_inherit = cdef->is_no_inherit; diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c index f9bb721c5fe..d8c88d4294f 100644 --- a/src/backend/catalog/index.c +++ b/src/backend/catalog/index.c @@ -1958,6 +1958,7 @@ index_constraint_create(Relation heapRelation, deferrable, initdeferred, true, + true, /* Is enforced */ parentConstraintId, RelationGetRelid(heapRelation), indexInfo->ii_IndexAttrNumbers, diff --git a/src/backend/catalog/pg_constraint.c b/src/backend/catalog/pg_constraint.c index 54f3fb50a57..2b461abf47e 100644 --- a/src/backend/catalog/pg_constraint.c +++ b/src/backend/catalog/pg_constraint.c @@ -53,6 +53,7 @@ CreateConstraintEntry(const char *constraintName, bool isDeferrable, bool isDeferred, bool isValidated, + bool isEnforced, Oid parentConstrId, Oid relId, const int16 *constraintKey, @@ -98,6 +99,9 @@ CreateConstraintEntry(const char *constraintName, ObjectAddresses *addrs_auto; ObjectAddresses *addrs_normal; + /* Only CHECK constraint can be not enforced */ + Assert(isEnforced || constraintType == CONSTRAINT_CHECK); + conDesc = table_open(ConstraintRelationId, RowExclusiveLock); Assert(constraintName); @@ -182,6 +186,7 @@ CreateConstraintEntry(const char *constraintName, values[Anum_pg_constraint_condeferrable - 1] = BoolGetDatum(isDeferrable); values[Anum_pg_constraint_condeferred - 1] = BoolGetDatum(isDeferred); values[Anum_pg_constraint_convalidated - 1] = BoolGetDatum(isValidated); + values[Anum_pg_constraint_conenforced - 1] = BoolGetDatum(isEnforced); values[Anum_pg_constraint_conrelid - 1] = ObjectIdGetDatum(relId); values[Anum_pg_constraint_contypid - 1] = ObjectIdGetDatum(domainId); values[Anum_pg_constraint_conindid - 1] = ObjectIdGetDatum(indexRelId); diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index 4345b96de5e..96b77cb3484 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -372,7 +372,7 @@ static void RangeVarCallbackForTruncate(const RangeVar *relation, Oid relId, Oid oldRelId, void *arg); static List *MergeAttributes(List *columns, const List *supers, char relpersistence, bool is_partition, List **supconstr); -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); @@ -964,6 +964,7 @@ DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId, cooked->attnum = attnum; cooked->expr = colDef->cooked_default; cooked->skip_validation = false; + cooked->is_enforced = true; cooked->is_local = true; /* not used for defaults */ cooked->inhcount = 0; /* ditto */ cooked->is_no_inherit = false; @@ -2842,7 +2843,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); } } @@ -3044,7 +3046,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; @@ -3085,6 +3087,7 @@ MergeCheckConstraint(List *constraints, const char *name, Node *expr) newcon->name = pstrdup(name); newcon->expr = expr; newcon->inhcount = 1; + newcon->is_enforced = is_enforced; return lappend(constraints, newcon); } @@ -9481,6 +9484,9 @@ ATAddCheckConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel, { CookedConstraint *ccon = (CookedConstraint *) lfirst(lcon); + /* Only CHECK constraint can be not enforced */ + Assert(ccon->is_enforced || ccon->contype == CONSTRAINT_CHECK); + if (!ccon->skip_validation) { NewConstraint *newcon; @@ -10253,6 +10259,7 @@ addFkConstraint(addFkConstraintSides fkside, fkconstraint->deferrable, fkconstraint->initdeferred, fkconstraint->initially_valid, + true, /* Is enforced */ parentConstr, RelationGetRelid(rel), fkattnum, @@ -11922,22 +11929,29 @@ ATExecValidateConstraint(List **wqueue, Relation rel, char *constrName, table_close(childrel, NoLock); } - /* Queue validation for phase 3 */ - newcon = (NewConstraint *) palloc0(sizeof(NewConstraint)); - newcon->name = constrName; - newcon->contype = CONSTR_CHECK; - newcon->refrelid = InvalidOid; - newcon->refindid = InvalidOid; - newcon->conid = con->oid; + /* + * Queue validation for phase 3 only if constraint is enforced; + * otherwise, adding it to the validation queue won't be very + * effective, as the verification will be skipped. + */ + if (con->conenforced) + { + newcon = (NewConstraint *) palloc0(sizeof(NewConstraint)); + newcon->name = constrName; + newcon->contype = CONSTR_CHECK; + newcon->refrelid = InvalidOid; + newcon->refindid = InvalidOid; + newcon->conid = con->oid; - val = SysCacheGetAttrNotNull(CONSTROID, tuple, - Anum_pg_constraint_conbin); - conbin = TextDatumGetCString(val); - newcon->qual = (Node *) stringToNode(conbin); + val = SysCacheGetAttrNotNull(CONSTROID, tuple, + Anum_pg_constraint_conbin); + conbin = TextDatumGetCString(val); + newcon->qual = (Node *) stringToNode(conbin); - /* Find or create work queue entry for this table */ - tab = ATGetQueueEntry(wqueue, rel); - tab->constraints = lappend(tab->constraints, newcon); + /* Find or create work queue entry for this table */ + tab = ATGetQueueEntry(wqueue, rel); + tab->constraints = lappend(tab->constraints, newcon); + } /* * Invalidate relcache so that others see the new validated @@ -11947,7 +11961,9 @@ ATExecValidateConstraint(List **wqueue, Relation rel, char *constrName, } /* - * Now update the catalog, while we have the door open. + * Now update the catalog regardless of enforcement; the validated + * flag will not take effect until the constraint is marked as + * enforced. */ copyTuple = heap_copytuple(tuple); copy_con = (Form_pg_constraint) GETSTRUCT(copyTuple); @@ -15952,6 +15968,7 @@ constraints_equivalent(HeapTuple a, HeapTuple b, TupleDesc tupleDesc) if (acon->condeferrable != bcon->condeferrable || acon->condeferred != bcon->condeferred || + acon->conenforced != bcon->conenforced || strcmp(decompile_conbin(a, tupleDesc), decompile_conbin(b, tupleDesc)) != 0) return false; @@ -19769,6 +19786,7 @@ DetachAddConstraintIfNeeded(List **wqueue, Relation partRel) n->cooked_expr = nodeToString(make_ands_explicit(constraintExpr)); n->initially_valid = true; n->skip_validation = true; + n->is_enforced = true; /* It's a re-add, since it nominally already exists */ ATAddCheckConstraint(wqueue, tab, partRel, n, true, false, true, ShareUpdateExclusiveLock); diff --git a/src/backend/commands/trigger.c b/src/backend/commands/trigger.c index 09356e46d16..a490d44548a 100644 --- a/src/backend/commands/trigger.c +++ b/src/backend/commands/trigger.c @@ -810,6 +810,7 @@ CreateTriggerFiringOn(CreateTrigStmt *stmt, const char *queryString, stmt->deferrable, stmt->initdeferred, true, + true, /* Is enforced */ InvalidOid, /* no parent */ RelationGetRelid(rel), NULL, /* no conkey */ diff --git a/src/backend/commands/typecmds.c b/src/backend/commands/typecmds.c index 2a6550de907..e77fd2f140a 100644 --- a/src/backend/commands/typecmds.c +++ b/src/backend/commands/typecmds.c @@ -1007,6 +1007,12 @@ DefineDomain(CreateDomainStmt *stmt) errmsg("specifying constraint deferrability not supported for domains"))); break; + case CONSTR_ATTR_NOT_ENFORCED: + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("specifying not enforced constraint not supported for domains"))); + break; + default: elog(ERROR, "unrecognized constraint subtype: %d", (int) constr->contype); @@ -2967,6 +2973,12 @@ AlterDomainAddConstraint(List *names, Node *newConstraint, errmsg("specifying constraint deferrability not supported for domains"))); break; + case CONSTR_ATTR_NOT_ENFORCED: + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("specifying not enforced constraint not supported for domains"))); + break; + default: elog(ERROR, "unrecognized constraint subtype: %d", (int) constr->contype); @@ -3597,6 +3609,7 @@ domainAddCheckConstraint(Oid domainOid, Oid domainNamespace, Oid baseTypeOid, false, /* Is Deferrable */ false, /* Is Deferred */ !constr->skip_validation, /* Is Validated */ + true, /* Is enforced */ InvalidOid, /* no parent constraint */ InvalidOid, /* not a relation constraint */ NULL, @@ -3704,6 +3717,7 @@ domainAddNotNullConstraint(Oid domainOid, Oid domainNamespace, Oid baseTypeOid, false, /* Is Deferrable */ false, /* Is Deferred */ !constr->skip_validation, /* Is Validated */ + true, /* Is enforced */ InvalidOid, /* no parent constraint */ InvalidOid, /* not a relation constraint */ NULL, diff --git a/src/backend/executor/execMain.c b/src/backend/executor/execMain.c index cc9a594cba5..cec69afe188 100644 --- a/src/backend/executor/execMain.c +++ b/src/backend/executor/execMain.c @@ -1746,11 +1746,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); @@ -1777,7 +1781,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/parser/gram.y b/src/backend/parser/gram.y index 89fdb94c237..7c632881226 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) @@ -197,7 +199,7 @@ static void SplitColQualList(List *qualList, 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 *no_inherit, bool *is_enforced, 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, &c->is_enforced, yyscanner); $$ = (Node *) n; } /* ALTER TABLE VALIDATE CONSTRAINT ... */ @@ -3960,6 +3962,7 @@ ColConstraintElem: n->cooked_expr = NULL; n->skip_validation = false; n->initially_valid = true; + n->is_enforced = true; $$ = (Node *) n; } | DEFAULT b_expr @@ -4084,6 +4087,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; + } ; @@ -4146,7 +4165,7 @@ ConstraintElem: n->cooked_expr = NULL; processCASbits($5, @5, "CHECK", NULL, NULL, &n->skip_validation, - &n->is_no_inherit, yyscanner); + &n->is_no_inherit, &n->is_enforced, yyscanner); n->initially_valid = !n->skip_validation; $$ = (Node *) n; } @@ -4166,7 +4185,7 @@ ConstraintElem: n->indexspace = $9; processCASbits($10, @10, "UNIQUE", &n->deferrable, &n->initdeferred, NULL, - NULL, yyscanner); + NULL, NULL, yyscanner); $$ = (Node *) n; } | UNIQUE ExistingIndex ConstraintAttributeSpec @@ -4182,7 +4201,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 @@ -4200,7 +4219,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 @@ -4216,7 +4235,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 ')' @@ -4236,7 +4255,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 @@ -4266,6 +4285,7 @@ ConstraintElem: processCASbits($12, @12, "FOREIGN KEY", &n->deferrable, &n->initdeferred, &n->skip_validation, NULL, + NULL, yyscanner); n->initially_valid = !n->skip_validation; $$ = (Node *) n; @@ -4306,7 +4326,7 @@ DomainConstraintElem: n->cooked_expr = NULL; processCASbits($5, @5, "CHECK", NULL, NULL, &n->skip_validation, - &n->is_no_inherit, yyscanner); + &n->is_no_inherit, NULL, yyscanner); n->initially_valid = !n->skip_validation; $$ = (Node *) n; } @@ -4320,7 +4340,7 @@ DomainConstraintElem: /* no NOT VALID support yet */ processCASbits($3, @3, "NOT NULL", NULL, NULL, NULL, - &n->is_no_inherit, yyscanner); + &n->is_no_inherit, NULL, yyscanner); n->initially_valid = true; $$ = (Node *) n; } @@ -5983,7 +6003,7 @@ CreateTrigStmt: n->transitionRels = NIL; processCASbits($11, @11, "TRIGGER", &n->deferrable, &n->initdeferred, NULL, - NULL, yyscanner); + NULL, NULL, yyscanner); n->constrrel = $10; $$ = (Node *) n; } @@ -6152,7 +6172,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"), @@ -6168,6 +6189,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; } ; @@ -17671,6 +17694,7 @@ unreserved_keyword: | ENABLE_P | ENCODING | ENCRYPTED + | ENFORCED | ENUM_P | ERROR_P | ESCAPE @@ -18248,6 +18272,7 @@ bare_label_keyword: | ENCODING | ENCRYPTED | END_P + | ENFORCED | ENUM_P | ERROR_P | ESCAPE @@ -19387,7 +19412,7 @@ 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 *no_inherit, bool *is_enforced, core_yyscan_t yyscanner) { /* defaults */ if (deferrable) @@ -19396,6 +19421,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)) { @@ -19448,6 +19475,19 @@ 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))); + } } /* diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c index 1e15ce10b48..024cc256b7d 100644 --- a/src/backend/parser/parse_utilcmd.c +++ b/src/backend/parser/parse_utilcmd.c @@ -835,6 +835,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; @@ -955,6 +957,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; @@ -1317,6 +1321,7 @@ expandTableLikeClause(RangeVar *heapRel, TableLikeClause *table_like_clause) n->is_no_inherit = ccnoinherit; n->raw_expr = NULL; n->cooked_expr = nodeToString(ccbin_node); + n->is_enforced = true; /* We can skip validation, since the new table should be empty. */ n->skip_validation = true; @@ -3715,6 +3720,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) \ @@ -3810,12 +3816,45 @@ transformConstraintAttrs(CreateStmtContext *cxt, List *constraintList) lastprimarycon->initdeferred = false; break; + case CONSTR_ATTR_ENFORCED: + if (lastprimarycon && + 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 && + 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; + 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 2177d17e278..cfdc4e31cd4 100644 --- a/src/backend/utils/adt/ruleutils.c +++ b/src/backend/utils/adt/ruleutils.c @@ -2372,6 +2372,9 @@ pg_get_constraintdef_worker(Oid constraintId, bool fullCommand, appendStringInfoChar(&buf, ')'); } + if (!conForm->conenforced) + appendStringInfoString(&buf, " NOT ENFORCED"); + break; } case CONSTRAINT_PRIMARY: @@ -2514,6 +2517,10 @@ pg_get_constraintdef_worker(Oid constraintId, bool fullCommand, appendStringInfo(&buf, "CHECK (%s)%s", consrc, conForm->connoinherit ? " NO INHERIT" : ""); + + if (!conForm->conenforced) + appendStringInfoString(&buf, " NOT ENFORCED"); + break; } case CONSTRAINT_TRIGGER: diff --git a/src/backend/utils/cache/relcache.c b/src/backend/utils/cache/relcache.c index 5bbb654a5db..6347b20971c 100644 --- a/src/backend/utils/cache/relcache.c +++ b/src/backend/utils/cache/relcache.c @@ -4587,6 +4587,7 @@ CheckConstraintFetch(Relation relation) } check[found].ccvalid = conform->convalidated; + check[found].ccenforced = conform->conenforced; check[found].ccnoinherit = conform->connoinherit; check[found].ccname = MemoryContextStrdup(CacheMemoryContext, NameStr(conform->conname)); diff --git a/src/include/access/tupdesc.h b/src/include/access/tupdesc.h index 8930a28d660..96bac018083 100644 --- a/src/include/access/tupdesc.h +++ b/src/include/access/tupdesc.h @@ -30,6 +30,7 @@ typedef struct ConstrCheck char *ccname; char *ccbin; /* nodeToString representation of expr */ bool ccvalid; + bool ccenforced; bool ccnoinherit; /* this is a non-inheritable constraint */ } ConstrCheck; diff --git a/src/include/catalog/heap.h b/src/include/catalog/heap.h index d6a2c791290..5c6a86c0f58 100644 --- a/src/include/catalog/heap.h +++ b/src/include/catalog/heap.h @@ -40,6 +40,7 @@ typedef struct CookedConstraint AttrNumber attnum; /* which attr (only for DEFAULT) */ Node *expr; /* transformed default or check expr */ bool skip_validation; /* skip validation? (only for CHECK) */ + bool is_enforced; /* is enforced? (only for CHECK) */ bool is_local; /* constraint has local (non-inherited) def */ int16 inhcount; /* number of times constraint is inherited */ bool is_no_inherit; /* constraint has local def and cannot be diff --git a/src/include/catalog/pg_constraint.h b/src/include/catalog/pg_constraint.h index 35788315bc4..3772a6431d1 100644 --- a/src/include/catalog/pg_constraint.h +++ b/src/include/catalog/pg_constraint.h @@ -52,6 +52,7 @@ CATALOG(pg_constraint,2606,ConstraintRelationId) bool condeferrable; /* deferrable constraint? */ bool condeferred; /* deferred by default? */ bool convalidated; /* constraint has been validated? */ + bool conenforced; /* enforced constraint? */ /* * conrelid and conkey are only meaningful if the constraint applies to a @@ -223,6 +224,7 @@ extern Oid CreateConstraintEntry(const char *constraintName, bool isDeferrable, bool isDeferred, bool isValidated, + bool isEnforced, Oid parentConstrId, Oid relId, const int16 *constraintKey, diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h index 0d96db56386..e029568b2e1 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 */ @@ -2759,6 +2761,7 @@ typedef struct Constraint bool initdeferred; /* INITIALLY DEFERRED? */ bool skip_validation; /* skip validation of existing rows? */ bool initially_valid; /* mark the new constraint as valid? */ + bool is_enforced; /* enforced constraint? */ bool is_no_inherit; /* is constraint non-inheritable? */ Node *raw_expr; /* CHECK or DEFAULT expression, as * untransformed parse tree */ diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h index 899d64ad55f..f2bc378394e 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/constraints.out b/src/test/regress/expected/constraints.out index cf0b80d6169..7fc1ced7efc 100644 --- a/src/test/regress/expected/constraints.out +++ b/src/test/regress/expected/constraints.out @@ -86,6 +86,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 @@ -119,7 +138,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). diff --git a/src/test/regress/sql/constraints.sql b/src/test/regress/sql/constraints.sql index e3e3bea7091..dac4f03bd71 100644 --- a/src/test/regress/sql/constraints.sql +++ b/src/test/regress/sql/constraints.sql @@ -66,6 +66,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, @@ -91,7 +103,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); -- 2.43.5