hi. attached patch will remove sql_features.txt item F492: "Optional table constraint enforcement" Comment: "except not-null constraints". see [1]
main points about NOT NULL NOT ENFORCED * one column can have at most one NOT-NULL constraint, regardless constraints property (not enforced or enforced) * if column already have not enforced not-null constraint then: ALTER TABLE ALTER COLUMN SET NOT NULL: error out, can not validate not enforced not-null constraint ALTER TABLE ADD NOT NULL: error out, can not add another not-null constraint, one column can only have one. not null in partitioned table: * If the partitioned table has an enforced not-null constraint, its partitions cannot have not enforced. * If the partitioned table has a NOT ENFORCED not-null constraint, its partitions may have either ENFORCED or NOT ENFORCED not-null constraints, but the constraint itself is still required. not null in table inheritance: OK: parent is not enforced, while child is enforced NOT OK: parent is enforced, while child is not enforced If a column inherits from multiple tables and the ancestor tables have conflicting ENFORCED statuses, raise an error. I have written extensive tests to cover the corner case, the tests may be overwhelming. [1]: https://www.postgresql.org/docs/devel/features-sql-standard.html
From 12e06cc4c964b6c9c17c55dc94edac0a18653f2f Mon Sep 17 00:00:00 2001 From: jian he <jian.universal...@gmail.com> Date: Thu, 4 Sep 2025 10:52:30 +0800 Subject: [PATCH v1 1/1] NOT NULL NOT ENFORCED this will remove sql_features.txt item F492: "Optional table constraint enforcement" remarks: "except not-null constraints". See [1]. main points about NOT NULL NOT ENFORCED * one column can have at most one NOT-NULL constraint, regardless constraints property (not enforced or enforced) * If column already have not enforced not-null constraint then: ALTER TABLE ALTER COLUMN SET NOT NULL: error out, can not validate not enforced not-null constraint ALTER TABLE ADD NOT NULL: error out, can not add another not-null constraint, one column can only have one. not null in partitioned table: * If the partitioned table has an enforced not-null constraint, its partitions cannot have not enforced. * If the partitioned table has a NOT ENFORCED not-null constraint, its partitions may have either ENFORCED or NOT ENFORCED not-null constraints, but the constraint itself is still required. not null in table inheritance: OK: parent is not enforced, while child is enforced NOT OK: parent is enforced, while child is not enforced If a column inherits from multiple tables and the ancestor tables have conflicting ENFORCED statuses, raise an error. reference: https://git.postgresql.org/cgit/postgresql.git/commit/?id=a379061a22a8fdf421e1a457cc6af8503def6252 discussion: https://postgr.es/m/ [1]: https://www.postgresql.org/docs/devel/features-sql-standard.html --- doc/src/sgml/ref/alter_table.sgml | 4 +- doc/src/sgml/ref/create_table.sgml | 4 +- src/backend/catalog/heap.c | 61 +++++-- src/backend/catalog/pg_constraint.c | 38 ++-- src/backend/catalog/sql_features.txt | 2 +- src/backend/commands/tablecmds.c | 129 +++++++++++--- src/backend/parser/gram.y | 2 +- src/backend/parser/parse_utilcmd.c | 80 ++++++--- src/backend/utils/cache/relcache.c | 2 +- src/bin/psql/describe.c | 20 ++- src/include/catalog/heap.h | 2 +- src/include/catalog/pg_constraint.h | 4 +- src/test/regress/expected/constraints.out | 162 ++++++++++++++++++ .../regress/expected/create_table_like.out | 22 +++ src/test/regress/expected/inherit.out | 107 ++++++++++++ src/test/regress/sql/constraints.sql | 88 ++++++++++ src/test/regress/sql/create_table_like.sql | 12 ++ src/test/regress/sql/inherit.sql | 62 +++++++ 18 files changed, 715 insertions(+), 86 deletions(-) diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml index 8867da6c693..e8a93c97432 100644 --- a/doc/src/sgml/ref/alter_table.sgml +++ b/doc/src/sgml/ref/alter_table.sgml @@ -1481,8 +1481,8 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM Adding a <literal>CHECK</literal> or <literal>NOT NULL</literal> constraint requires scanning the table to verify that existing rows meet the constraint, but does not require a table rewrite. If a <literal>CHECK</literal> - constraint is added as <literal>NOT ENFORCED</literal>, no verification will - be performed. + or <literal>NOT NULL</literal> constraint is added as <literal>NOT ENFORCED</literal>, + no verification will be performed. </para> <para> diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml index dc000e913c1..4d0d0e9cbf4 100644 --- a/doc/src/sgml/ref/create_table.sgml +++ b/doc/src/sgml/ref/create_table.sgml @@ -1425,8 +1425,8 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM </para> <para> - This is currently only supported for foreign key and <literal>CHECK</literal> - constraints. + This is currently only supported for foreign key, <literal>CHECK</literal> + and <literal>NOT NULL</literal> constraints. </para> </listitem> </varlistentry> diff --git a/src/backend/catalog/heap.c b/src/backend/catalog/heap.c index fd6537567ea..2a8f4c42bac 100644 --- a/src/backend/catalog/heap.c +++ b/src/backend/catalog/heap.c @@ -2252,8 +2252,8 @@ StoreRelCheck(Relation rel, const char *ccname, Node *expr, */ static Oid StoreRelNotNull(Relation rel, const char *nnname, AttrNumber attnum, - bool is_validated, bool is_local, int inhcount, - bool is_no_inherit) + bool is_enforced, bool is_validated, bool is_local, + int inhcount, bool is_no_inherit) { Oid constrOid; @@ -2265,7 +2265,7 @@ StoreRelNotNull(Relation rel, const char *nnname, AttrNumber attnum, CONSTRAINT_NOTNULL, false, false, - true, /* Is Enforced */ + is_enforced, /* Is Enforced */ is_validated, InvalidOid, RelationGetRelid(rel), @@ -2627,16 +2627,18 @@ AddRelationNewConstraints(Relation rel, strVal(linitial(cdef->keys)))); Assert(cdef->initially_valid != cdef->skip_validation); + Assert(cdef->is_enforced || !cdef->initially_valid); /* * If the column already has a not-null constraint, we don't want * to add another one; adjust inheritance status as needed. This * also checks whether the existing constraint matches the - * requested validity. + * requested validity or enforceability. */ if (AdjustNotNullInheritance(RelationGetRelid(rel), colnum, is_local, cdef->is_no_inherit, - cdef->skip_validation)) + cdef->skip_validation, + cdef->is_enforced)) continue; /* @@ -2664,6 +2666,7 @@ AddRelationNewConstraints(Relation rel, constrOid = StoreRelNotNull(rel, nnname, colnum, + cdef->is_enforced, cdef->initially_valid, is_local, inhcount, @@ -2675,7 +2678,7 @@ AddRelationNewConstraints(Relation rel, nncooked->name = nnname; nncooked->attnum = colnum; nncooked->expr = NULL; - nncooked->is_enforced = true; + nncooked->is_enforced = cdef->is_enforced; nncooked->skip_validation = cdef->skip_validation; nncooked->is_local = is_local; nncooked->inhcount = inhcount; @@ -2949,7 +2952,7 @@ AddRelationNotNullConstraints(Relation rel, List *constraints, /* * A column can only have one not-null constraint, so discard any * additional ones that appear for columns we already saw; but check - * that the NO INHERIT flags match. + * that the NO INHERIT, NOT ENFORCED flags match. */ for (int restpos = outerpos + 1; restpos < list_length(constraints);) { @@ -2965,6 +2968,11 @@ AddRelationNotNullConstraints(Relation rel, List *constraints, errmsg("conflicting NO INHERIT declaration for not-null constraint on column \"%s\"", strVal(linitial(constr->keys)))); + if (other->is_enforced != constr->is_enforced) + ereport(ERROR, + errcode(ERRCODE_SYNTAX_ERROR), + errmsg("conflicting NOT ENFORCED declaration for not-null constraint on column \"%s\"", + strVal(linitial(constr->keys)))); /* * Preserve constraint name if one is specified, but raise an * error if conflicting ones are specified. @@ -3010,6 +3018,17 @@ AddRelationNotNullConstraints(Relation rel, List *constraints, strVal(linitial(constr->keys))), errdetail("The column has an inherited not-null constraint."))); + /* + * If we get a ENFORCED constraint from the parent, having a + * local NOT ENFORCED one doesn't work. + */ + if (old->is_enforced && !constr->is_enforced) + ereport(ERROR, + errcode(ERRCODE_DATATYPE_MISMATCH), + errmsg("cannot define not-null constraint with NOT ENFORCED on column \"%s\"", + strVal(linitial(constr->keys))), + errdetail("The column has an inherited ENFORCED not-null constraint.")); + inhcount++; old_notnulls = foreach_delete_current(old_notnulls, old); } @@ -3044,11 +3063,14 @@ AddRelationNotNullConstraints(Relation rel, List *constraints, nnnames); nnnames = lappend(nnnames, conname); - StoreRelNotNull(rel, conname, - attnum, true, true, - inhcount, constr->is_no_inherit); + Assert(constr->is_enforced || constr->skip_validation); + StoreRelNotNull(rel, conname, attnum, + constr->is_enforced, + !constr->skip_validation, + true, inhcount, constr->is_no_inherit); - nncols = lappend_int(nncols, attnum); + if (constr->is_enforced) + nncols = lappend_int(nncols, attnum); } /* @@ -3093,6 +3115,16 @@ AddRelationNotNullConstraints(Relation rel, List *constraints, conname = other->name; inhcount++; + + /* + * if a column inherit multiple not-null constraints, the + * enforced status should the same. + */ + if (other->is_enforced != cooked->is_enforced) + ereport(ERROR, + errcode(ERRCODE_DATATYPE_MISMATCH), + errmsg("cannot define not-null constraint on column \"%s\"", conname), + errdetail("The column inherited not-null constraints have conflict ENFORCED status.")); old_notnulls = list_delete_nth_cell(old_notnulls, restpos); } else @@ -3123,10 +3155,13 @@ AddRelationNotNullConstraints(Relation rel, List *constraints, nnnames = lappend(nnnames, conname); /* ignore the origin constraint's is_local and inhcount */ - StoreRelNotNull(rel, conname, cooked->attnum, true, + StoreRelNotNull(rel, conname, cooked->attnum, + cooked->is_enforced, + cooked->is_enforced ? true : false, false, inhcount, false); - nncols = lappend_int(nncols, cooked->attnum); + if (cooked->is_enforced) + nncols = lappend_int(nncols, cooked->attnum); } return nncols; diff --git a/src/backend/catalog/pg_constraint.c b/src/backend/catalog/pg_constraint.c index 6002fd0002f..a625813cd6f 100644 --- a/src/backend/catalog/pg_constraint.c +++ b/src/backend/catalog/pg_constraint.c @@ -100,9 +100,10 @@ CreateConstraintEntry(const char *constraintName, ObjectAddresses *addrs_auto; ObjectAddresses *addrs_normal; - /* Only CHECK or FOREIGN KEY constraint can be not enforced */ - Assert(isEnforced || constraintType == CONSTRAINT_CHECK || - constraintType == CONSTRAINT_FOREIGN); + /* Only CHECK, FOREIGN KEY, NOT NULL constraint can be not enforced */ + Assert(isEnforced || (constraintType == CONSTRAINT_CHECK || + constraintType == CONSTRAINT_FOREIGN || + constraintType == CONSTRAINT_NOTNULL)); /* NOT ENFORCED constraint must be NOT VALID */ Assert(isEnforced || !isValidated); @@ -580,8 +581,8 @@ ChooseConstraintName(const char *name1, const char *name2, } /* - * Find and return a copy of the pg_constraint tuple that implements a - * (possibly not valid) not-null constraint for the given column of the + * Find and return a copy of the pg_constraint tuple that implements a (possibly + * not valid or not enforced) not-null constraint for the given column of the * given relation. If no such constraint exists, return NULL. * * XXX This would be easier if we had pg_attribute.notnullconstr with the OID @@ -634,8 +635,8 @@ findNotNullConstraintAttnum(Oid relid, AttrNumber attnum) /* * Find and return a copy of the pg_constraint tuple that implements a - * (possibly not valid) not-null constraint for the given column of the - * given relation. + * (possibly not valid or not enforced) not-null constraint for the given column + * of the given relation. * If no such column or no such constraint exists, return NULL. */ HeapTuple @@ -738,8 +739,8 @@ extractNotNullColumn(HeapTuple constrTup) * it's already true; otherwise we increment coninhcount by 1. */ bool -AdjustNotNullInheritance(Oid relid, AttrNumber attnum, - bool is_local, bool is_no_inherit, bool is_notvalid) +AdjustNotNullInheritance(Oid relid, AttrNumber attnum, bool is_local, + bool is_no_inherit, bool is_notvalid, bool is_enforced) { HeapTuple tup; @@ -769,7 +770,7 @@ AdjustNotNullInheritance(Oid relid, AttrNumber attnum, * Throw an error if the existing constraint is NOT VALID and caller * wants a valid one. */ - if (!is_notvalid && !conform->convalidated) + if (!is_notvalid && !conform->convalidated && conform->conenforced) ereport(ERROR, errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE), errmsg("incompatible NOT VALID constraint \"%s\" on relation \"%s\"", @@ -777,6 +778,18 @@ AdjustNotNullInheritance(Oid relid, AttrNumber attnum, errhint("You might need to validate it using %s.", "ALTER TABLE ... VALIDATE CONSTRAINT")); + /* + * If the ENFORCED status we're asked for doesn't match what the + * existing constraint has, throw an error. + */ + if (is_enforced != conform->conenforced) + ereport(ERROR, + errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE), + errmsg("cannot change ENFORCED status of NOT NULL constraint \"%s\" on relation \"%s\"", + NameStr(conform->conname), get_rel_name(relid)), + errhint("You might need to drop the existing not enforced constraint using %s.", + "ALTER TABLE ... DROP CONSTRAINT")); + if (!is_local) { if (pg_add_s16_overflow(conform->coninhcount, 1, @@ -807,6 +820,7 @@ AdjustNotNullInheritance(Oid relid, AttrNumber attnum, * RelationGetNotNullConstraints * Return the list of not-null constraints for the given rel * + * The returned not-null constraints possibly not enforced! * Caller can request cooked constraints, or raw. * * This is seldom needed, so we just scan pg_constraint each time. @@ -853,7 +867,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->is_enforced = conForm->conenforced; cooked->skip_validation = !conForm->convalidated; cooked->is_local = true; cooked->inhcount = 0; @@ -873,7 +887,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->is_enforced = conForm->conenforced; constr->skip_validation = !conForm->convalidated; 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 ebe85337c28..c418e6cd1dd 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 YES except not-null constraints +F492 Optional table constraint enforcement YES 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 082a3575d62..733fe103f7d 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -2740,13 +2740,16 @@ MergeAttributes(List *columns, const List *supers, char relpersistence, inherited_defaults = cols_with_defaults = NIL; /* - * Request attnotnull on columns that have a not-null constraint + * Request attnotnull on columns that have an enforced not-null constraint * that's not marked NO INHERIT (even if not valid). */ nnconstrs = RelationGetNotNullConstraints(RelationGetRelid(relation), true, false); foreach_ptr(CookedConstraint, cc, nnconstrs) - nncols = bms_add_member(nncols, cc->attnum); + { + if (cc->is_enforced) + nncols = bms_add_member(nncols, cc->attnum); + } for (AttrNumber parent_attno = 1; parent_attno <= tupleDesc->natts; parent_attno++) @@ -7726,6 +7729,7 @@ add_column_collation_dependency(Oid relid, int32 attnum, Oid collid) * * Return the address of the modified column. If the column was already * nullable, InvalidObjectAddress is returned. + * This will drop the not enforced not-null constraint too. */ static ObjectAddress ATExecDropNotNull(Relation rel, const char *colName, bool recurse, @@ -7754,13 +7758,6 @@ ATExecDropNotNull(Relation rel, const char *colName, bool recurse, ObjectAddressSubSet(address, RelationRelationId, RelationGetRelid(rel), attnum); - /* If the column is already nullable there's nothing to do. */ - if (!attTup->attnotnull) - { - table_close(attr_rel, RowExclusiveLock); - return InvalidObjectAddress; - } - /* Prevent them from altering a system attribute */ if (attnum <= 0) ereport(ERROR, @@ -7799,8 +7796,17 @@ ATExecDropNotNull(Relation rel, const char *colName, bool recurse, */ conTup = findNotNullConstraintAttnum(RelationGetRelid(rel), attnum); if (conTup == NULL) - elog(ERROR, "cache lookup failed for not-null constraint on column \"%s\" of relation \"%s\"", - colName, RelationGetRelationName(rel)); + { + if (attTup->attnotnull) + elog(ERROR, "cache lookup failed for not-null constraint on column \"%s\" of relation \"%s\"", + colName, RelationGetRelationName(rel)); + else + { + /* If the column is already nullable there's nothing to do. */ + table_close(attr_rel, RowExclusiveLock); + return InvalidObjectAddress; + } + } /* The normal case: we have a pg_constraint row, remove it */ dropconstraint_internal(rel, conTup, DROP_RESTRICT, recurse, false, @@ -7935,7 +7941,7 @@ ATExecSetNotNull(List **wqueue, Relation rel, char *conName, char *colName, errmsg("cannot alter system column \"%s\"", colName))); - /* See if there's already a constraint */ + /* See if there's already a constraint. Note: it maybe not enforced! */ tuple = findNotNullConstraintAttnum(RelationGetRelid(rel), attnum); if (HeapTupleIsValid(tuple)) { @@ -7971,7 +7977,7 @@ ATExecSetNotNull(List **wqueue, Relation rel, char *conName, char *colName, conForm->conislocal = true; changed = true; } - else if (!conForm->convalidated) + else if (!conForm->convalidated && conForm->conenforced) { /* * Flip attnotnull and convalidated, and also validate the @@ -7981,6 +7987,13 @@ ATExecSetNotNull(List **wqueue, Relation rel, char *conName, char *colName, recurse, recursing, lockmode); } + if (!conForm->conenforced) + ereport(ERROR, + errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE), + errmsg("cannot validate NOT ENFORCED constraint \"%s\" on relation \"%s\"", + NameStr(conForm->conname), + RelationGetRelationName(rel))); + if (changed) { Relation constr_rel; @@ -9559,7 +9572,7 @@ verifyNotNullPKCompatible(HeapTuple tuple, const char *colname) "ALTER TABLE ... ALTER CONSTRAINT ... INHERIT")); /* an unvalidated constraint is no good */ - if (!conForm->convalidated) + if (!conForm->convalidated && conForm->conenforced) ereport(ERROR, errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE), errmsg("cannot create primary key on column \"%s\"", colname), @@ -9569,6 +9582,18 @@ verifyNotNullPKCompatible(HeapTuple tuple, const char *colname) get_rel_name(conForm->conrelid), "NOT VALID"), errhint("You might need to validate it using %s.", "ALTER TABLE ... VALIDATE CONSTRAINT")); + + /* a not enforced constraint is no good */ + if (!conForm->conenforced) + ereport(ERROR, + errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE), + errmsg("cannot create primary key on column \"%s\"", colname), + /*- translator: fourth %s is a constraint characteristic such as NOT ENFORCED */ + errdetail("The constraint \"%s\" on column \"%s\" of table \"%s\", marked %s, is incompatible with a primary key.", + NameStr(conForm->conname), colname, + get_rel_name(conForm->conrelid), "NOT ENFORCED"), + errhint("You might need to drop it first using %s.", + "ALTER TABLE ... DROP CONSTRAINT")); } /* @@ -9937,9 +9962,9 @@ ATAddCheckNNConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel, * If adding a valid not-null constraint, set the pg_attribute flag * and tell phase 3 to verify existing rows, if needed. For an * invalid constraint, just set attnotnull, without queueing - * verification. + * verification. For not enforced not-null, no need set attnotnull. */ - if (constr->contype == CONSTR_NOTNULL) + if (constr->contype == CONSTR_NOTNULL && ccon->is_enforced) set_attnotnull(wqueue, rel, ccon->attnum, !constr->skip_validation, !constr->skip_validation); @@ -12584,6 +12609,7 @@ ATExecAlterConstrInheritability(List **wqueue, ATAlterConstraint *cmdcon, AttrNumber colNum; char *colName; List *children; + bool notenforced; Assert(cmdcon->alterInheritability); @@ -12592,6 +12618,8 @@ ATExecAlterConstrInheritability(List **wqueue, ATAlterConstraint *cmdcon, /* The current implementation only works for NOT NULL constraints */ Assert(currcon->contype == CONSTRAINT_NOTNULL); + notenforced = !currcon->conenforced; + /* * If called to modify a constraint that's already in the desired state, * silently do nothing. @@ -12632,6 +12660,31 @@ ATExecAlterConstrInheritability(List **wqueue, ATAlterConstraint *cmdcon, CatalogTupleUpdate(conrel, &childtup->t_self, childtup); heap_freetuple(childtup); } + else if (notenforced) + { + /* + * We can't use ATExecSetNotNull here because it adds an enforced + * not-null constraint, but here we only want a non-enforced one. + */ + Relation childrel; + AlteredTableInfo *childtab; + Constraint *nnconstr; + + childrel = table_open(childoid, NoLock); + CheckAlterTableIsSafe(childrel); + + childtab = ATGetQueueEntry(wqueue, childrel); + + nnconstr = makeNotNullConstraint(makeString(colName)); + nnconstr->conname = pstrdup(NameStr(currcon->conname)); + nnconstr->is_enforced = false; + nnconstr->initially_valid = false; + nnconstr->skip_validation = true; + + ATAddCheckNNConstraint(wqueue, childtab, childrel, nnconstr, + true, true, false, lockmode); + table_close(childrel, NoLock); + } else { Relation childrel = table_open(childoid, NoLock); @@ -17505,9 +17558,28 @@ MergeAttributesIntoExisting(Relation child_rel, Relation parent_rel, bool ispart if (parent_att->attnotnull && !child_att->attnotnull) { HeapTuple contup; + HeapTuple childcontup; + childcontup = findNotNullConstraintAttnum(RelationGetRelid(child_rel), + child_att->attnum); contup = findNotNullConstraintAttnum(RelationGetRelid(parent_rel), parent_att->attnum); + if (HeapTupleIsValid(childcontup) && HeapTupleIsValid(contup)) + { + Form_pg_constraint child_con = (Form_pg_constraint) GETSTRUCT(childcontup); + Form_pg_constraint parent_con = (Form_pg_constraint) GETSTRUCT(contup); + + /* + * If the child constraint is "not enforced" then cannot + * merge with a enforced parent constraint + */ + 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))); + } + if (HeapTupleIsValid(contup) && !((Form_pg_constraint) GETSTRUCT(contup))->connoinherit) ereport(ERROR, @@ -17759,13 +17831,24 @@ MergeConstraintsIntoExisting(Relation child_rel, Relation parent_rel) if (!found) { if (parent_con->contype == CONSTRAINT_NOTNULL) - ereport(ERROR, - errcode(ERRCODE_DATATYPE_MISMATCH), - errmsg("column \"%s\" in child table \"%s\" must be marked NOT NULL", - get_attname(parent_relid, - extractNotNullColumn(parent_tuple), - false), - RelationGetRelationName(child_rel))); + { + if (parent_con->conenforced) + ereport(ERROR, + errcode(ERRCODE_DATATYPE_MISMATCH), + errmsg("column \"%s\" in child table \"%s\" must be marked NOT NULL", + get_attname(parent_relid, + extractNotNullColumn(parent_tuple), + false), + RelationGetRelationName(child_rel))); + else + ereport(ERROR, + errcode(ERRCODE_DATATYPE_MISMATCH), + errmsg("column \"%s\" in child table \"%s\" must be marked NOT NULL NOT ENFORCED", + get_attname(parent_relid, + extractNotNullColumn(parent_tuple), + false), + RelationGetRelationName(child_rel))); + } ereport(ERROR, (errcode(ERRCODE_DATATYPE_MISMATCH), diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index db43034b9db..c54131bd601 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -4258,7 +4258,7 @@ ConstraintElem: n->location = @1; n->keys = list_make1(makeString($3)); processCASbits($4, @4, "NOT NULL", - NULL, 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; diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c index afcf54169c3..b939980c068 100644 --- a/src/backend/parser/parse_utilcmd.c +++ b/src/backend/parser/parse_utilcmd.c @@ -317,6 +317,10 @@ transformCreateStmt(CreateStmt *stmt, const char *queryString) { char *colname = strVal(linitial(nn->keys)); + /* Do not set is_not_null to true for not enforced not-null constraint */ + if (!nn->is_enforced) + continue; + foreach_node(ColumnDef, cd, cxt.columns) { /* not our column? */ @@ -713,6 +717,9 @@ transformColumnDefinition(CreateStmtContext *cxt, ColumnDef *column) * disallow it here as well. Maybe AddRelationNotNullConstraints can be * improved someday, so that it doesn't complain, and then we can remove * the restriction for SERIAL and IDENTITY here as well. + * + * Note: The above explanation apply to NOT ENFORCED not-null constraint. + * disallow_noinherit_notnull treats NOT ENFORCED the same way as NO INHERIT. */ if (!disallow_noinherit_notnull) { @@ -773,6 +780,11 @@ transformColumnDefinition(CreateStmtContext *cxt, ColumnDef *column) errmsg("conflicting NO INHERIT declarations for not-null constraints on column \"%s\"", column->colname)); + if (disallow_noinherit_notnull && !constraint->is_enforced) + ereport(ERROR, + errcode(ERRCODE_SYNTAX_ERROR), + errmsg("conflicting NOT ENFORCED declarations for not-null constraints on column \"%s\"", + column->colname)); /* * If this is the first time we see this column being marked * not-null, add the constraint entry and keep track of it. @@ -792,6 +804,15 @@ transformColumnDefinition(CreateStmtContext *cxt, ColumnDef *column) constraint->keys = list_make1(makeString(column->colname)); notnull_constraint = constraint; + + /* + * NOT ENFORCED not-null constraint does not indicate data + * are all not-null, therefore can not set column + * pg_attribute.attnotnull to true. + */ + if (!constraint->is_enforced) + column->is_not_null = false; + cxt->nnconstraints = lappend(cxt->nnconstraints, constraint); } else if (notnull_constraint) @@ -1131,6 +1152,7 @@ transformTableLikeClause(CreateStmtContext *cxt, TableLikeClause *table_like_cla AclResult aclresult; char *comment; ParseCallbackState pcbstate; + List *lst = NIL; setup_parser_errposition_callback(&pcbstate, cxt->pstate, table_like_clause->relation->location); @@ -1272,33 +1294,41 @@ transformTableLikeClause(CreateStmtContext *cxt, TableLikeClause *table_like_cla * Reproduce not-null constraints, if any, by copying them. We do this * regardless of options given. */ - if (tupleDesc->constr && tupleDesc->constr->has_not_null) - { - List *lst; + lst = RelationGetNotNullConstraints(RelationGetRelid(relation), false, + true); + cxt->nnconstraints = list_concat(cxt->nnconstraints, lst); - lst = RelationGetNotNullConstraints(RelationGetRelid(relation), false, - true); - cxt->nnconstraints = list_concat(cxt->nnconstraints, lst); + /* + * When creating a new relation, marking the enforced not-null constraint as + * not valid doesn't make sense, so we treat it as valid. + */ + foreach_node(Constraint, nnconstr, lst) + { + if (nnconstr->is_enforced) + { + nnconstr->skip_validation = false; + nnconstr->initially_valid = true; + } + } - /* Copy comments on not-null constraints */ - if (table_like_clause->options & CREATE_TABLE_LIKE_COMMENTS) + /* Copy comments on not-null constraints */ + if (table_like_clause->options & CREATE_TABLE_LIKE_COMMENTS) + { + foreach_node(Constraint, nnconstr, lst) { - foreach_node(Constraint, nnconstr, lst) + if ((comment = GetComment(get_relation_constraint_oid(RelationGetRelid(relation), + nnconstr->conname, false), + ConstraintRelationId, + 0)) != NULL) { - if ((comment = GetComment(get_relation_constraint_oid(RelationGetRelid(relation), - nnconstr->conname, false), - ConstraintRelationId, - 0)) != NULL) - { - CommentStmt *stmt = makeNode(CommentStmt); + CommentStmt *stmt = makeNode(CommentStmt); - stmt->objtype = OBJECT_TABCONSTRAINT; - stmt->object = (Node *) list_make3(makeString(cxt->relation->schemaname), - makeString(cxt->relation->relname), - makeString(nnconstr->conname)); - stmt->comment = comment; - cxt->alist = lappend(cxt->alist, stmt); - } + stmt->objtype = OBJECT_TABCONSTRAINT; + stmt->object = (Node *) list_make3(makeString(cxt->relation->schemaname), + makeString(cxt->relation->relname), + makeString(nnconstr->conname)); + stmt->comment = comment; + cxt->alist = lappend(cxt->alist, stmt); } } } @@ -3992,7 +4022,8 @@ transformConstraintAttrs(CreateStmtContext *cxt, List *constraintList) case CONSTR_ATTR_ENFORCED: if (lastprimarycon == NULL || (lastprimarycon->contype != CONSTR_CHECK && - lastprimarycon->contype != CONSTR_FOREIGN)) + lastprimarycon->contype != CONSTR_FOREIGN && + lastprimarycon->contype != CONSTR_NOTNULL )) ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR), errmsg("misplaced ENFORCED clause"), @@ -4009,7 +4040,8 @@ transformConstraintAttrs(CreateStmtContext *cxt, List *constraintList) case CONSTR_ATTR_NOT_ENFORCED: if (lastprimarycon == NULL || (lastprimarycon->contype != CONSTR_CHECK && - lastprimarycon->contype != CONSTR_FOREIGN)) + lastprimarycon->contype != CONSTR_FOREIGN && + lastprimarycon->contype != CONSTR_NOTNULL)) ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR), errmsg("misplaced NOT ENFORCED clause"), diff --git a/src/backend/utils/cache/relcache.c b/src/backend/utils/cache/relcache.c index 6fe268a8eec..2333ef9b1fc 100644 --- a/src/backend/utils/cache/relcache.c +++ b/src/backend/utils/cache/relcache.c @@ -4632,7 +4632,7 @@ CheckNNConstraintFetch(Relation relation) */ if (conform->contype == CONSTRAINT_NOTNULL) { - if (!conform->convalidated) + if (!conform->convalidated && conform->conenforced) { AttrNumber attnum; diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c index 4aa793d7de7..543ed309b92 100644 --- a/src/bin/psql/describe.c +++ b/src/bin/psql/describe.c @@ -3108,7 +3108,14 @@ describeOneTableDetails(const char *schemaname, printfPQExpBuffer(&buf, "SELECT c.conname, a.attname, c.connoinherit,\n" " c.conislocal, c.coninhcount <> 0,\n" - " c.convalidated\n" + " c.convalidated,\n"); + + if (pset.sversion >= 190000) + appendPQExpBufferStr(&buf, "c.conenforced\n"); + else + appendPQExpBufferStr(&buf, "true as conenforced\n"); + + appendPQExpBuffer(&buf, "FROM pg_catalog.pg_constraint c JOIN\n" " pg_catalog.pg_attribute a ON\n" " (a.attrelid = c.conrelid AND a.attnum = c.conkey[1])\n" @@ -3132,15 +3139,20 @@ describeOneTableDetails(const char *schemaname, bool islocal = PQgetvalue(result, i, 3)[0] == 't'; bool inherited = PQgetvalue(result, i, 4)[0] == 't'; bool validated = PQgetvalue(result, i, 5)[0] == 't'; + bool enforced = PQgetvalue(result, i, 6)[0] == 't'; - printfPQExpBuffer(&buf, " \"%s\" NOT NULL \"%s\"%s%s", + printfPQExpBuffer(&buf, " \"%s\" NOT NULL \"%s\"%s", PQgetvalue(result, i, 0), PQgetvalue(result, i, 1), PQgetvalue(result, i, 2)[0] == 't' ? " NO INHERIT" : islocal && inherited ? _(" (local, inherited)") : - inherited ? _(" (inherited)") : "", - !validated ? " NOT VALID" : ""); + inherited ? _(" (inherited)") : ""); + + if (!enforced) + appendPQExpBufferStr(&buf, " NOT ENFORCED"); + else if (!validated) + appendPQExpBufferStr(&buf, " NOT VALID"); printTableAddFooter(&cont, buf.data); } diff --git a/src/include/catalog/heap.h b/src/include/catalog/heap.h index dbd339e9df4..5e7fe1d2f69 100644 --- a/src/include/catalog/heap.h +++ b/src/include/catalog/heap.h @@ -40,7 +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 is_enforced; /* is enforced? (for NOT NULL and 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 4afceb5c692..fc581701c61 100644 --- a/src/include/catalog/pg_constraint.h +++ b/src/include/catalog/pg_constraint.h @@ -263,8 +263,8 @@ extern HeapTuple findNotNullConstraintAttnum(Oid relid, AttrNumber attnum); extern HeapTuple findNotNullConstraint(Oid relid, const char *colname); extern HeapTuple findDomainNotNullConstraint(Oid typid); extern AttrNumber extractNotNullColumn(HeapTuple constrTup); -extern bool AdjustNotNullInheritance(Oid relid, AttrNumber attnum, - bool is_local, bool is_no_inherit, bool is_notvalid); +extern bool AdjustNotNullInheritance(Oid relid, AttrNumber attnum, bool is_local, + bool is_no_inherit, bool is_notvalid, bool is_enforced); extern List *RelationGetNotNullConstraints(Oid relid, bool cooked, bool include_noinh); diff --git a/src/test/regress/expected/constraints.out b/src/test/regress/expected/constraints.out index 3590d3274f0..2291c017605 100644 --- a/src/test/regress/expected/constraints.out +++ b/src/test/regress/expected/constraints.out @@ -961,33 +961,135 @@ drop table notnull_tbl2, notnull_tbl3, notnull_tbl4, notnull_tbl5, notnull_tbl6; -- error cases: create table notnull_tbl_fail (a serial constraint foo not null constraint bar not null); ERROR: conflicting not-null constraint names "foo" and "bar" +create table notnull_tbl_fail (a serial constraint foo not null constraint bar not null not enforced); +ERROR: conflicting NOT ENFORCED declarations for not-null constraints on column "a" create table notnull_tbl_fail (a serial constraint foo not null no inherit constraint foo not null); ERROR: conflicting NO INHERIT declarations for not-null constraints on column "a" +create table notnull_tbl_fail (a serial constraint foo not null not enforced); +ERROR: conflicting NOT ENFORCED declarations for not-null constraints on column "a" create table notnull_tbl_fail (a int constraint foo not null, constraint foo not null a no inherit); ERROR: conflicting NO INHERIT declaration for not-null constraint on column "a" +create table notnull_tbl_fail (a int constraint foo not null, constraint foo not null a not enforced); +ERROR: conflicting NOT ENFORCED declaration for not-null constraint on column "a" create table notnull_tbl_fail (a serial constraint foo not null, constraint bar not null a); ERROR: conflicting not-null constraint names "foo" and "bar" +create table notnull_tbl_fail (a serial constraint foo not null, constraint bar not null a not enforced); +ERROR: conflicting NOT ENFORCED declaration for not-null constraint on column "a" create table notnull_tbl_fail (a serial, constraint foo not null a, constraint bar not null a); ERROR: conflicting not-null constraint names "foo" and "bar" +create table notnull_tbl_fail (a serial, constraint foo not null a, constraint bar not null a not enforced); +ERROR: conflicting NOT ENFORCED declaration for not-null constraint on column "a" create table notnull_tbl_fail (a serial, constraint foo not null a no inherit); ERROR: conflicting NO INHERIT declaration for not-null constraint on column "a" +create table notnull_tbl_fail (a serial, constraint foo not null a not enforced); +ERROR: conflicting NOT ENFORCED declaration for not-null constraint on column "a" create table notnull_tbl_fail (a serial not null no inherit); ERROR: conflicting NO INHERIT declarations for not-null constraints on column "a" +create table notnull_tbl_fail (a serial not null not enforced); +ERROR: conflicting NOT ENFORCED declarations for not-null constraints on column "a" create table notnull_tbl_fail (like notnull_tbl1, constraint foo2 not null a); ERROR: conflicting not-null constraint names "foo" and "foo2" +create table notnull_tbl_fail (like notnull_tbl1, constraint foo2 not null a not enforced); +ERROR: conflicting NOT ENFORCED declaration for not-null constraint on column "a" create table notnull_tbl_fail (a int primary key constraint foo not null no inherit); ERROR: conflicting NO INHERIT declarations for not-null constraints on column "a" +create table notnull_tbl_fail (a int primary key constraint foo not null not enforced); +ERROR: conflicting NOT ENFORCED declarations for not-null constraints on column "a" create table notnull_tbl_fail (a int not null no inherit primary key); ERROR: conflicting NO INHERIT declarations for not-null constraints on column "a" create table notnull_tbl_fail (a int primary key, not null a no inherit); ERROR: conflicting NO INHERIT declaration for not-null constraint on column "a" +create table notnull_tbl_fail (a int primary key, not null a not enforced); +ERROR: conflicting NOT ENFORCED declaration for not-null constraint on column "a" create table notnull_tbl_fail (a int, primary key(a), not null a no inherit); ERROR: conflicting NO INHERIT declaration for not-null constraint on column "a" +create table notnull_tbl_fail (a int, primary key(a), not null a not enforced); +ERROR: conflicting NOT ENFORCED declaration for not-null constraint on column "a" create table notnull_tbl_fail (a int generated by default as identity, constraint foo not null a no inherit); ERROR: conflicting NO INHERIT declaration for not-null constraint on column "a" +create table notnull_tbl_fail (a int generated by default as identity, constraint foo not null a not enforced); +ERROR: conflicting NOT ENFORCED declaration for not-null constraint on column "a" create table notnull_tbl_fail (a int generated by default as identity not null no inherit); ERROR: conflicting NO INHERIT declarations for not-null constraints on column "a" +create table notnull_tbl_fail (a int generated by default as identity not null not enforced); +ERROR: conflicting NOT ENFORCED declarations for not-null constraints on column "a" +alter table notnull_tbl1 add column b int not null not enforced; --ok +alter table notnull_tbl1 alter column b add generated always as identity; +ERROR: column "b" of relation "notnull_tbl1" must be declared NOT NULL before identity can be added +alter table notnull_tbl1 add column c int not null not enforced, alter column c add generated always as identity; +ERROR: column "c" of relation "notnull_tbl1" must be declared NOT NULL before identity can be added +alter table notnull_tbl1 add column c int generated always as identity not null not enforced; +ERROR: conflicting NOT ENFORCED declarations for not-null constraints on column "c" +alter table notnull_tbl1 add column c serial not null not enforced; +ERROR: conflicting NOT ENFORCED declarations for not-null constraints on column "c" +alter table notnull_tbl1 add column c serial, add constraint nn not null c not enforced; +ERROR: cannot change ENFORCED status of NOT NULL constraint "notnull_tbl1_c_not_null" on relation "notnull_tbl1" +HINT: You might need to drop the existing not enforced constraint using ALTER TABLE ... DROP CONSTRAINT. drop table notnull_tbl1; +-- NOT NULL [NOT] ENFORCED +CREATE TABLE ne_nn_tbl (x int, CONSTRAINT nn NOT NULL x NOT ENFORCED, NOT NULL x ENFORCED); --error +ERROR: conflicting NOT ENFORCED declaration for not-null constraint on column "x" +CREATE TABLE ne_nn_tbl (x int, CONSTRAINT nn NOT NULL x NOT ENFORCED); +ALTER TABLE ne_nn_tbl ALTER CONSTRAINT nn ENFORCED; --error +ERROR: cannot alter enforceability of constraint "nn" of relation "ne_nn_tbl" +ALTER TABLE ne_nn_tbl ALTER CONSTRAINT nn NOT ENFORCED; --error +ERROR: cannot alter enforceability of constraint "nn" of relation "ne_nn_tbl" +ALTER TABLE ne_nn_tbl VALIDATE CONSTRAINT nn; --error +ERROR: cannot validate NOT ENFORCED constraint +SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conname = 'nn'; + pg_get_constraintdef +------------------------- + NOT NULL x NOT ENFORCED +(1 row) + +INSERT INTO ne_nn_tbl VALUES (NULL); --ok +ALTER TABLE ne_nn_tbl ADD CONSTRAINT nn_enforced1 NOT NULL x NOT ENFORCED NO INHERIT; --error +ERROR: cannot change NO INHERIT status of NOT NULL constraint "nn" on relation "ne_nn_tbl" +HINT: You might need to make the existing constraint inheritable using ALTER TABLE ... ALTER CONSTRAINT ... INHERIT. +ALTER TABLE ne_nn_tbl ADD CONSTRAINT nn_enforced1 NOT NULL x NOT VALID NOT ENFORCED; --no-op +ALTER TABLE ne_nn_tbl ADD CONSTRAINT nn_enforced1 NOT NULL x NOT ENFORCED; --no-op +ALTER TABLE ne_nn_tbl ADD CONSTRAINT nn_enforced1 NOT NULL x NOT VALID; --error +ERROR: cannot change ENFORCED status of NOT NULL constraint "nn" on relation "ne_nn_tbl" +HINT: You might need to drop the existing not enforced constraint using ALTER TABLE ... DROP CONSTRAINT. +ALTER TABLE ne_nn_tbl ADD CONSTRAINT nn_enforced NOT NULL x ENFORCED; --error +ERROR: cannot change ENFORCED status of NOT NULL constraint "nn" on relation "ne_nn_tbl" +HINT: You might need to drop the existing not enforced constraint using ALTER TABLE ... DROP CONSTRAINT. +ALTER TABLE ne_nn_tbl ADD CONSTRAINT nn_enforced NOT NULL x; --error, one column can only one not-null +ERROR: cannot change ENFORCED status of NOT NULL constraint "nn" on relation "ne_nn_tbl" +HINT: You might need to drop the existing not enforced constraint using ALTER TABLE ... DROP CONSTRAINT. +ALTER TABLE ne_nn_tbl ALTER COLUMN x SET NOT NULL; --error +ERROR: cannot validate NOT ENFORCED constraint "nn" on relation "ne_nn_tbl" +\d+ ne_nn_tbl + Table "public.ne_nn_tbl" + Column | Type | Collation | Nullable | Default | Storage | Stats target | Description +--------+---------+-----------+----------+---------+---------+--------------+------------- + x | integer | | | | plain | | +Not-null constraints: + "nn" NOT NULL "x" NOT ENFORCED + +TRUNCATE ne_nn_tbl; +--error, can not use not enforced not-null constaint for primary key +ALTER TABLE ne_nn_tbl ADD PRIMARY KEY(x); +ERROR: cannot create primary key on column "x" +DETAIL: The constraint "nn" on column "x" of table "ne_nn_tbl", marked NOT ENFORCED, is incompatible with a primary key. +HINT: You might need to drop it first using ALTER TABLE ... DROP CONSTRAINT. +ALTER TABLE ne_nn_tbl ADD column y int NOT NULL NOT ENFORCED ENFORCED; --error +ERROR: multiple ENFORCED/NOT ENFORCED clauses not allowed +LINE 1: ...E ne_nn_tbl ADD column y int NOT NULL NOT ENFORCED ENFORCED; + ^ +ALTER TABLE ne_nn_tbl ADD column x1 int NOT NULL NOT ENFORCED, ADD column y int NOT NULL ENFORCED; --ok +\d+ ne_nn_tbl + Table "public.ne_nn_tbl" + Column | Type | Collation | Nullable | Default | Storage | Stats target | Description +--------+---------+-----------+----------+---------+---------+--------------+------------- + x | integer | | | | plain | | + x1 | integer | | | | plain | | + y | integer | | not null | | plain | | +Not-null constraints: + "nn" NOT NULL "x" NOT ENFORCED + "ne_nn_tbl_x1_not_null" NOT NULL "x1" NOT ENFORCED + "ne_nn_tbl_y_not_null" NOT NULL "y" + -- NOT NULL NO INHERIT CREATE TABLE ATACC1 (a int, NOT NULL a NO INHERIT); CREATE TABLE ATACC2 () INHERITS (ATACC1); @@ -1668,6 +1770,66 @@ COMMENT ON CONSTRAINT constr_parent2_a_not_null ON constr_parent2 IS 'this const COMMENT ON CONSTRAINT constr_parent2_a_not_null ON constr_child2 IS 'this constraint is valid'; DEALLOCATE get_nnconstraint_info; -- end NOT NULL NOT VALID +-- Verify NOT NULL ENFORCED / ENFORCED with partition table. +PREPARE get_nnconstraint_info(regclass[]) AS +SELECT conrelid::regclass as relname, conname, convalidated, conislocal, coninhcount, conenforced +FROM pg_constraint +WHERE conrelid = ANY($1) +ORDER BY conrelid::regclass::text COLLATE "C", conname; +-- partitioned table have enforced not-null, then partitions can not have not enforced not-null +CREATE TABLE pp_nn (a int, b int, NOT NULL a) PARTITION BY LIST (a); +CREATE TABLE pp_nn_1(a int, b int, CONSTRAINT nn1 NOT NULL a NOT ENFORCED); +ALTER TABLE pp_nn ATTACH PARTITION pp_nn_1 FOR VALUES IN (NULL,5); --error +ERROR: constraint "nn1" conflicts with NOT ENFORCED constraint on child table "pp_nn_1" +DROP TABLE pp_nn, pp_nn_1; +CREATE TABLE notnull_tbl1 (a int, b int, CONSTRAINT nn0 NOT NULL a NOT ENFORCED) PARTITION BY LIST (a); +CREATE TABLE notnull_tbl1_1 PARTITION OF notnull_tbl1 FOR VALUES IN (1,2); +-- if partitioned table not-null is not enforced, then partitions can have enforced +-- or not enforced not-null +CREATE TABLE notnull_tbl1_2(a int, CONSTRAINT nn1 NOT NULL a, b int); +ALTER TABLE notnull_tbl1 ATTACH PARTITION notnull_tbl1_2 FOR VALUES IN (3,4); --ok +CREATE TABLE notnull_tbl1_3(a int, b int, CONSTRAINT nn2 NOT NULL a NOT ENFORCED); +ALTER TABLE notnull_tbl1 ATTACH PARTITION notnull_tbl1_3 FOR VALUES IN (NULL,5); +CREATE TABLE notnull_tbl1_4(a int, b int); +ALTER TABLE notnull_tbl1 ATTACH PARTITION notnull_tbl1_4 FOR VALUES IN (6); --error +ERROR: column "a" in child table "notnull_tbl1_4" must be marked NOT NULL NOT ENFORCED +EXECUTE get_nnconstraint_info('{notnull_tbl1, notnull_tbl1_1, notnull_tbl1_2, notnull_tbl1_3}'); + relname | conname | convalidated | conislocal | coninhcount | conenforced +----------------+---------+--------------+------------+-------------+------------- + notnull_tbl1 | nn0 | f | t | 0 | f + notnull_tbl1_1 | nn0 | f | f | 1 | f + notnull_tbl1_2 | nn1 | t | f | 1 | t + notnull_tbl1_3 | nn2 | f | f | 1 | f +(4 rows) + +ALTER TABLE notnull_tbl1 ALTER COLUMN a SET NOT NULL; --error, can not validate not-enforced +ERROR: cannot validate NOT ENFORCED constraint "nn0" on relation "notnull_tbl1" +ALTER TABLE notnull_tbl1 VALIDATE CONSTRAINT nn0; --error, can not validate not-enforced +ERROR: cannot validate NOT ENFORCED constraint +DROP TABLE notnull_tbl1, notnull_tbl1_4; +-- Create table with NOT NULL NOT ENFORCED constraint, for pg_upgrade. +CREATE TABLE nn_notenforced (a int, b int); +INSERT INTO nn_notenforced VALUES (NULL, 1), (NULL, 2), (300, 3); +ALTER TABLE nn_notenforced ADD CONSTRAINT nn NOT NULL a NOT ENFORCED; +EXECUTE get_nnconstraint_info('{nn_notenforced}'); + relname | conname | convalidated | conislocal | coninhcount | conenforced +----------------+---------+--------------+------------+-------------+------------- + nn_notenforced | nn | f | t | 0 | f +(1 row) + +-- Inherit test for pg_upgrade +CREATE TABLE notenforced_nn_parent (a int); +CREATE TABLE notenforced_nn_child () INHERITS (notenforced_nn_parent); +ALTER TABLE notenforced_nn_parent ADD CONSTRAINT nn NOT NULL a NOT ENFORCED; +EXECUTE get_nnconstraint_info('{notenforced_nn_parent, notenforced_nn_child}'); + relname | conname | convalidated | conislocal | coninhcount | conenforced +-----------------------+---------+--------------+------------+-------------+------------- + notenforced_nn_child | nn | f | f | 1 | f + notenforced_nn_parent | nn | f | t | 0 | f +(2 rows) + +DEALLOCATE get_nnconstraint_info; +--end of NOT NULL ENFORCED / ENFORCED with partition table. -- Comments -- Setup a low-level role to enforce non-superuser checks. CREATE ROLE regress_constraint_comments; diff --git a/src/test/regress/expected/create_table_like.out b/src/test/regress/expected/create_table_like.out index 29a779c2e90..993fd2a6b3b 100644 --- a/src/test/regress/expected/create_table_like.out +++ b/src/test/regress/expected/create_table_like.out @@ -317,6 +317,28 @@ Referenced by: TABLE "inhz" CONSTRAINT "inhz_x_fkey" FOREIGN KEY (x) REFERENCES inhz(xx) DROP TABLE inhz; +--not null not enforced constraint +CREATE TABLE not_enforced_nn (a text, constraint nn not null a not enforced); +COMMENT ON CONSTRAINT nn ON not_enforced_nn is 'not enforced not null constraint comment test'; +CREATE TABLE not_enforced_nn_copy(LIKE not_enforced_nn INCLUDING CONSTRAINTS INCLUDING COMMENTS); +\d+ not_enforced_nn_copy + Table "public.not_enforced_nn_copy" + Column | Type | Collation | Nullable | Default | Storage | Stats target | Description +--------+------+-----------+----------+---------+----------+--------------+------------- + a | text | | | | extended | | +Not-null constraints: + "nn" NOT NULL "a" NOT ENFORCED + +SELECT conname, description +FROM pg_description, pg_constraint c +WHERE classoid = 'pg_constraint'::regclass +AND objoid = c.oid AND c.conrelid = 'not_enforced_nn_copy'::regclass +ORDER BY conname COLLATE "C"; + conname | description +---------+----------------------------------------------- + nn | not enforced not null constraint comment test +(1 row) + -- including storage and comments CREATE TABLE ctlt1 (a text CHECK (length(a) > 2) ENFORCED PRIMARY KEY, b text CHECK (length(b) > 100) NOT ENFORCED); diff --git a/src/test/regress/expected/inherit.out b/src/test/regress/expected/inherit.out index 5b5055babdc..13008b71570 100644 --- a/src/test/regress/expected/inherit.out +++ b/src/test/regress/expected/inherit.out @@ -1439,6 +1439,113 @@ 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; -- +-- Similarly, check the merging of existing constraints; a parent not-null 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_nn not null); +create table p1_c1(f1 int constraint p1_c1_nn not null not enforced); +alter table p1_c1 inherit p1; --error +ERROR: constraint "p1_c1_nn" conflicts with NOT ENFORCED constraint on child table "p1_c1" +create table p1_c2(f1 int not null not enforced) inherits(p1); --error +NOTICE: merging column "f1" with inherited definition +ERROR: cannot define not-null constraint with NOT ENFORCED on column "f1" +DETAIL: The column has an inherited ENFORCED not-null constraint. +drop table if exists p1, p1_c1; +create table p1(f1 int constraint p1_a_nn not null not enforced); +create table p1_c1(f1 int constraint p1_c1_a_nn not null); +alter table p1_c1 inherit p1; --ok +create table p1_c3() inherits(p1, p1_c1); --error +NOTICE: merging multiple inherited definitions of column "f1" +ERROR: cannot define not-null constraint on column "p1_a_nn" +DETAIL: The column inherited not-null constraints have conflict ENFORCED status. +create table p1_c3(f1 int); +alter table p1_c3 inherit p1; --error +ERROR: column "f1" in child table "p1_c3" must be marked NOT NULL NOT ENFORCED +--error, can not merge conflict not-null constraint +create table p1_c4(f1 int not null not enforced) inherits(p1, p1_c1); +NOTICE: merging multiple inherited definitions of column "f1" +NOTICE: merging column "f1" with inherited definition +ERROR: cannot define not-null constraint with NOT ENFORCED on column "f1" +DETAIL: The column has an inherited ENFORCED not-null constraint. +create table p1_c4(f1 int not null) inherits(p1, p1_c1); --ok +NOTICE: merging multiple inherited definitions of column "f1" +NOTICE: merging column "f1" with inherited definition +create table p1_c5(f1 int) inherits(p1, p1_c1); --error +NOTICE: merging multiple inherited definitions of column "f1" +NOTICE: merging column "f1" with inherited definition +ERROR: cannot define not-null constraint on column "p1_a_nn" +DETAIL: The column inherited not-null constraints have conflict ENFORCED status. +drop table if exists p1, p1_c3 cascade; +NOTICE: drop cascades to 2 other objects +DETAIL: drop cascades to table p1_c1 +drop cascades to table p1_c4 +create table p1(f1 int); +create table p1_c1() inherits(p1); +alter table p1 add constraint p1_nn_1 not null f1 not enforced; +alter table p1_c1 add constraint p1_c1_nn_1 not null f1 enforced; --error, f1 already have not-null constraint +ERROR: cannot change ENFORCED status of NOT NULL constraint "p1_nn_1" on relation "p1_c1" +HINT: You might need to drop the existing not enforced constraint using ALTER TABLE ... DROP CONSTRAINT. +create table p1_nenn(f1 int constraint p1_nn not null) inherits(p1, p1_c1); --ok +NOTICE: merging multiple inherited definitions of column "f1" +NOTICE: merging column "f1" with inherited definition +\d+ p1_nenn + Table "public.p1_nenn" + Column | Type | Collation | Nullable | Default | Storage | Stats target | Description +--------+---------+-----------+----------+---------+---------+--------------+------------- + f1 | integer | | not null | | plain | | +Not-null constraints: + "p1_nn" NOT NULL "f1" (local, inherited) +Inherits: p1, + p1_c1 + +drop table p1_nenn; +-- not allowed: child is not enforced, parent have enforced +alter table p1 alter column f1 drop not null; +alter table p1_c1 add constraint nn not null f1 not enforced; +alter table p1 add constraint nn not null f1 enforced; --error +ERROR: cannot change ENFORCED status of NOT NULL constraint "nn" on relation "p1_c1" +HINT: You might need to drop the existing not enforced constraint using ALTER TABLE ... DROP CONSTRAINT. +alter table p1_c1 alter column f1 drop not null; +alter table p1_c1 add constraint nn not null f1 not valid enforced; +alter table p1 add constraint nn not null f1 not enforced; --error +ERROR: cannot change ENFORCED status of NOT NULL constraint "nn" on relation "p1_c1" +HINT: You might need to drop the existing not enforced constraint using ALTER TABLE ... DROP CONSTRAINT. +drop table p1 cascade; +NOTICE: drop cascades to table p1_c1 +-- Test INHERIT for not enforced constraint. +create table inh_nn1 (f1 int, constraint nn not null f1 no inherit not enforced); +create table inh_nn2 (f2 text, f3 int) inherits (inh_nn1); +create table inh_nn3 () inherits (inh_nn2); +create table inh_nn4 () inherits (inh_nn1); +alter table inh_nn2 add constraint nn not null f1 not enforced; +alter table inh_nn1 alter constraint nn inherit; --ok. +select conrelid::regclass, conname, contype, conenforced, convalidated, coninhcount, connoinherit, conislocal +from pg_constraint +where conrelid::regclass::text = ANY ('{inh_nn1, inh_nn2, inh_nn3, inh_nn4}') +order by conname, conrelid::regclass::text collate "C"; + conrelid | conname | contype | conenforced | convalidated | coninhcount | connoinherit | conislocal +----------+---------+---------+-------------+--------------+-------------+--------------+------------ + inh_nn1 | nn | n | f | f | 0 | f | t + inh_nn2 | nn | n | f | f | 1 | f | t + inh_nn3 | nn | n | f | f | 1 | f | f + inh_nn4 | nn | n | f | f | 1 | f | f +(4 rows) + +drop table inh_nn1 cascade; +NOTICE: drop cascades to 3 other objects +DETAIL: drop cascades to table inh_nn2 +drop cascades to table inh_nn3 +drop cascades to table inh_nn4 +create table inh_nn1 (f1 int, constraint nn not null f1 no inherit not enforced); +create table inh_nn2 (f2 text, f3 int) inherits (inh_nn1); +alter table inh_nn2 add constraint nn not null f1; +alter table inh_nn1 alter constraint nn inherit; --error +ERROR: cannot change ENFORCED status of NOT NULL constraint "nn" on relation "inh_nn2" +HINT: You might need to drop the existing not enforced constraint using ALTER TABLE ... DROP CONSTRAINT. +drop table inh_nn1 cascade; +NOTICE: drop cascades to table inh_nn2 +-- -- Test DROP behavior of multiply-defined CHECK constraints -- create table p1(f1 int constraint f1_pos CHECK (f1 > 0)); diff --git a/src/test/regress/sql/constraints.sql b/src/test/regress/sql/constraints.sql index 1f6dc8fd69f..2723c754774 100644 --- a/src/test/regress/sql/constraints.sql +++ b/src/test/regress/sql/constraints.sql @@ -663,22 +663,67 @@ drop table notnull_tbl2, notnull_tbl3, notnull_tbl4, notnull_tbl5, notnull_tbl6; -- error cases: create table notnull_tbl_fail (a serial constraint foo not null constraint bar not null); +create table notnull_tbl_fail (a serial constraint foo not null constraint bar not null not enforced); create table notnull_tbl_fail (a serial constraint foo not null no inherit constraint foo not null); +create table notnull_tbl_fail (a serial constraint foo not null not enforced); create table notnull_tbl_fail (a int constraint foo not null, constraint foo not null a no inherit); +create table notnull_tbl_fail (a int constraint foo not null, constraint foo not null a not enforced); create table notnull_tbl_fail (a serial constraint foo not null, constraint bar not null a); +create table notnull_tbl_fail (a serial constraint foo not null, constraint bar not null a not enforced); create table notnull_tbl_fail (a serial, constraint foo not null a, constraint bar not null a); +create table notnull_tbl_fail (a serial, constraint foo not null a, constraint bar not null a not enforced); create table notnull_tbl_fail (a serial, constraint foo not null a no inherit); +create table notnull_tbl_fail (a serial, constraint foo not null a not enforced); create table notnull_tbl_fail (a serial not null no inherit); +create table notnull_tbl_fail (a serial not null not enforced); create table notnull_tbl_fail (like notnull_tbl1, constraint foo2 not null a); +create table notnull_tbl_fail (like notnull_tbl1, constraint foo2 not null a not enforced); create table notnull_tbl_fail (a int primary key constraint foo not null no inherit); +create table notnull_tbl_fail (a int primary key constraint foo not null not enforced); create table notnull_tbl_fail (a int not null no inherit primary key); create table notnull_tbl_fail (a int primary key, not null a no inherit); +create table notnull_tbl_fail (a int primary key, not null a not enforced); create table notnull_tbl_fail (a int, primary key(a), not null a no inherit); +create table notnull_tbl_fail (a int, primary key(a), not null a not enforced); create table notnull_tbl_fail (a int generated by default as identity, constraint foo not null a no inherit); +create table notnull_tbl_fail (a int generated by default as identity, constraint foo not null a not enforced); create table notnull_tbl_fail (a int generated by default as identity not null no inherit); +create table notnull_tbl_fail (a int generated by default as identity not null not enforced); +alter table notnull_tbl1 add column b int not null not enforced; --ok +alter table notnull_tbl1 alter column b add generated always as identity; +alter table notnull_tbl1 add column c int not null not enforced, alter column c add generated always as identity; +alter table notnull_tbl1 add column c int generated always as identity not null not enforced; +alter table notnull_tbl1 add column c serial not null not enforced; +alter table notnull_tbl1 add column c serial, add constraint nn not null c not enforced; drop table notnull_tbl1; +-- NOT NULL [NOT] ENFORCED +CREATE TABLE ne_nn_tbl (x int, CONSTRAINT nn NOT NULL x NOT ENFORCED, NOT NULL x ENFORCED); --error +CREATE TABLE ne_nn_tbl (x int, CONSTRAINT nn NOT NULL x NOT ENFORCED); +ALTER TABLE ne_nn_tbl ALTER CONSTRAINT nn ENFORCED; --error +ALTER TABLE ne_nn_tbl ALTER CONSTRAINT nn NOT ENFORCED; --error +ALTER TABLE ne_nn_tbl VALIDATE CONSTRAINT nn; --error + +SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conname = 'nn'; +INSERT INTO ne_nn_tbl VALUES (NULL); --ok + +ALTER TABLE ne_nn_tbl ADD CONSTRAINT nn_enforced1 NOT NULL x NOT ENFORCED NO INHERIT; --error +ALTER TABLE ne_nn_tbl ADD CONSTRAINT nn_enforced1 NOT NULL x NOT VALID NOT ENFORCED; --no-op +ALTER TABLE ne_nn_tbl ADD CONSTRAINT nn_enforced1 NOT NULL x NOT ENFORCED; --no-op +ALTER TABLE ne_nn_tbl ADD CONSTRAINT nn_enforced1 NOT NULL x NOT VALID; --error +ALTER TABLE ne_nn_tbl ADD CONSTRAINT nn_enforced NOT NULL x ENFORCED; --error +ALTER TABLE ne_nn_tbl ADD CONSTRAINT nn_enforced NOT NULL x; --error, one column can only one not-null +ALTER TABLE ne_nn_tbl ALTER COLUMN x SET NOT NULL; --error +\d+ ne_nn_tbl + +TRUNCATE ne_nn_tbl; +--error, can not use not enforced not-null constaint for primary key +ALTER TABLE ne_nn_tbl ADD PRIMARY KEY(x); +ALTER TABLE ne_nn_tbl ADD column y int NOT NULL NOT ENFORCED ENFORCED; --error +ALTER TABLE ne_nn_tbl ADD column x1 int NOT NULL NOT ENFORCED, ADD column y int NOT NULL ENFORCED; --ok +\d+ ne_nn_tbl + -- NOT NULL NO INHERIT CREATE TABLE ATACC1 (a int, NOT NULL a NO INHERIT); CREATE TABLE ATACC2 () INHERITS (ATACC1); @@ -1008,6 +1053,49 @@ DEALLOCATE get_nnconstraint_info; -- end NOT NULL NOT VALID +-- Verify NOT NULL ENFORCED / ENFORCED with partition table. +PREPARE get_nnconstraint_info(regclass[]) AS +SELECT conrelid::regclass as relname, conname, convalidated, conislocal, coninhcount, conenforced +FROM pg_constraint +WHERE conrelid = ANY($1) +ORDER BY conrelid::regclass::text COLLATE "C", conname; + +-- partitioned table have enforced not-null, then partitions can not have not enforced not-null +CREATE TABLE pp_nn (a int, b int, NOT NULL a) PARTITION BY LIST (a); +CREATE TABLE pp_nn_1(a int, b int, CONSTRAINT nn1 NOT NULL a NOT ENFORCED); +ALTER TABLE pp_nn ATTACH PARTITION pp_nn_1 FOR VALUES IN (NULL,5); --error +DROP TABLE pp_nn, pp_nn_1; + +CREATE TABLE notnull_tbl1 (a int, b int, CONSTRAINT nn0 NOT NULL a NOT ENFORCED) PARTITION BY LIST (a); +CREATE TABLE notnull_tbl1_1 PARTITION OF notnull_tbl1 FOR VALUES IN (1,2); +-- if partitioned table not-null is not enforced, then partitions can have enforced +-- or not enforced not-null +CREATE TABLE notnull_tbl1_2(a int, CONSTRAINT nn1 NOT NULL a, b int); +ALTER TABLE notnull_tbl1 ATTACH PARTITION notnull_tbl1_2 FOR VALUES IN (3,4); --ok +CREATE TABLE notnull_tbl1_3(a int, b int, CONSTRAINT nn2 NOT NULL a NOT ENFORCED); +ALTER TABLE notnull_tbl1 ATTACH PARTITION notnull_tbl1_3 FOR VALUES IN (NULL,5); +CREATE TABLE notnull_tbl1_4(a int, b int); +ALTER TABLE notnull_tbl1 ATTACH PARTITION notnull_tbl1_4 FOR VALUES IN (6); --error + +EXECUTE get_nnconstraint_info('{notnull_tbl1, notnull_tbl1_1, notnull_tbl1_2, notnull_tbl1_3}'); +ALTER TABLE notnull_tbl1 ALTER COLUMN a SET NOT NULL; --error, can not validate not-enforced +ALTER TABLE notnull_tbl1 VALIDATE CONSTRAINT nn0; --error, can not validate not-enforced +DROP TABLE notnull_tbl1, notnull_tbl1_4; + +-- Create table with NOT NULL NOT ENFORCED constraint, for pg_upgrade. +CREATE TABLE nn_notenforced (a int, b int); +INSERT INTO nn_notenforced VALUES (NULL, 1), (NULL, 2), (300, 3); +ALTER TABLE nn_notenforced ADD CONSTRAINT nn NOT NULL a NOT ENFORCED; +EXECUTE get_nnconstraint_info('{nn_notenforced}'); + +-- Inherit test for pg_upgrade +CREATE TABLE notenforced_nn_parent (a int); +CREATE TABLE notenforced_nn_child () INHERITS (notenforced_nn_parent); +ALTER TABLE notenforced_nn_parent ADD CONSTRAINT nn NOT NULL a NOT ENFORCED; +EXECUTE get_nnconstraint_info('{notenforced_nn_parent, notenforced_nn_child}'); +DEALLOCATE get_nnconstraint_info; +--end of NOT NULL ENFORCED / ENFORCED with partition table. + -- Comments -- Setup a low-level role to enforce non-superuser checks. CREATE ROLE regress_constraint_comments; diff --git a/src/test/regress/sql/create_table_like.sql b/src/test/regress/sql/create_table_like.sql index bf8702116a7..d1a0613aa4d 100644 --- a/src/test/regress/sql/create_table_like.sql +++ b/src/test/regress/sql/create_table_like.sql @@ -127,6 +127,18 @@ CREATE TABLE inhz (x text REFERENCES inhz, LIKE inhx INCLUDING INDEXES); \d inhz DROP TABLE inhz; +--not null not enforced constraint +CREATE TABLE not_enforced_nn (a text, constraint nn not null a not enforced); +COMMENT ON CONSTRAINT nn ON not_enforced_nn is 'not enforced not null constraint comment test'; +CREATE TABLE not_enforced_nn_copy(LIKE not_enforced_nn INCLUDING CONSTRAINTS INCLUDING COMMENTS); +\d+ not_enforced_nn_copy + +SELECT conname, description +FROM pg_description, pg_constraint c +WHERE classoid = 'pg_constraint'::regclass +AND objoid = c.oid AND c.conrelid = 'not_enforced_nn_copy'::regclass +ORDER BY conname COLLATE "C"; + -- including storage and comments CREATE TABLE ctlt1 (a text CHECK (length(a) > 2) ENFORCED PRIMARY KEY, b text CHECK (length(b) > 100) NOT ENFORCED); diff --git a/src/test/regress/sql/inherit.sql b/src/test/regress/sql/inherit.sql index 699e8ac09c8..afe6d048d38 100644 --- a/src/test/regress/sql/inherit.sql +++ b/src/test/regress/sql/inherit.sql @@ -527,6 +527,68 @@ 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; + +-- +-- Similarly, check the merging of existing constraints; a parent not-null 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_nn not null); +create table p1_c1(f1 int constraint p1_c1_nn not null not enforced); +alter table p1_c1 inherit p1; --error +create table p1_c2(f1 int not null not enforced) inherits(p1); --error +drop table if exists p1, p1_c1; + +create table p1(f1 int constraint p1_a_nn not null not enforced); +create table p1_c1(f1 int constraint p1_c1_a_nn not null); +alter table p1_c1 inherit p1; --ok +create table p1_c3() inherits(p1, p1_c1); --error +create table p1_c3(f1 int); +alter table p1_c3 inherit p1; --error +--error, can not merge conflict not-null constraint +create table p1_c4(f1 int not null not enforced) inherits(p1, p1_c1); +create table p1_c4(f1 int not null) inherits(p1, p1_c1); --ok +create table p1_c5(f1 int) inherits(p1, p1_c1); --error +drop table if exists p1, p1_c3 cascade; + +create table p1(f1 int); +create table p1_c1() inherits(p1); +alter table p1 add constraint p1_nn_1 not null f1 not enforced; +alter table p1_c1 add constraint p1_c1_nn_1 not null f1 enforced; --error, f1 already have not-null constraint +create table p1_nenn(f1 int constraint p1_nn not null) inherits(p1, p1_c1); --ok +\d+ p1_nenn +drop table p1_nenn; + +-- not allowed: child is not enforced, parent have enforced +alter table p1 alter column f1 drop not null; +alter table p1_c1 add constraint nn not null f1 not enforced; +alter table p1 add constraint nn not null f1 enforced; --error + +alter table p1_c1 alter column f1 drop not null; +alter table p1_c1 add constraint nn not null f1 not valid enforced; +alter table p1 add constraint nn not null f1 not enforced; --error +drop table p1 cascade; + +-- Test INHERIT for not enforced constraint. +create table inh_nn1 (f1 int, constraint nn not null f1 no inherit not enforced); +create table inh_nn2 (f2 text, f3 int) inherits (inh_nn1); +create table inh_nn3 () inherits (inh_nn2); +create table inh_nn4 () inherits (inh_nn1); +alter table inh_nn2 add constraint nn not null f1 not enforced; +alter table inh_nn1 alter constraint nn inherit; --ok. + +select conrelid::regclass, conname, contype, conenforced, convalidated, coninhcount, connoinherit, conislocal +from pg_constraint +where conrelid::regclass::text = ANY ('{inh_nn1, inh_nn2, inh_nn3, inh_nn4}') +order by conname, conrelid::regclass::text collate "C"; + +drop table inh_nn1 cascade; +create table inh_nn1 (f1 int, constraint nn not null f1 no inherit not enforced); +create table inh_nn2 (f2 text, f3 int) inherits (inh_nn1); +alter table inh_nn2 add constraint nn not null f1; +alter table inh_nn1 alter constraint nn inherit; --error +drop table inh_nn1 cascade; + -- -- Test DROP behavior of multiply-defined CHECK constraints -- -- 2.34.1
