On Tue, Mar 3, 2026 at 4:03 PM jian he <[email protected]> wrote: > > It turns out that the change for NOT ENFORCED NOT NULL is pg_dump.c is > not complicated. > > CREATE TABLE ne_nn_tbl (x int, CONSTRAINT nn NOT NULL x NOT ENFORCED); > Now the pg_dump output is > > CREATE TABLE public.ne_nn_tbl ( > x integer CONSTRAINT nn NOT NULL NOT ENFORCED > ); > > but the 002_pg_dump.pl is failing.... >
Hi. v5 is attached. Now the 002_pg_dump.pl is written by Claude. Claude also found that v4 failed completely for ALTER TABLE MERGE/SPLIT PARTITIONS. I am aware of the complexity of https://git.postgresql.org/cgit/postgresql.git/commit/?id=0cd17fdd3c000f6e64e79da0fea5e65dc9f562df However, we currently do not support altering a NOT NULL constraint's enforceability, so we are fine for now. But I hope the new tests in src/test/regress/sql/inherit.sql cover all the corner cases.
From c6bd722a51be1b6fbc005f356ef828f97ad9d27b Mon Sep 17 00:00:00 2001 From: jian he <[email protected]> Date: Thu, 6 Aug 2026 12:43:49 +0800 Subject: [PATCH v5 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: * 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: command ALTER TABLE ALTER COLUMN SET NOT NULL will be error out, cannot validate not enforced not-null constraint. ALTER TABLE ADD NOT NULL: error out, cannot 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, we install the enforced one. commitfest: https://commitfest.postgresql.org/patch/6029 reference: https://git.postgresql.org/cgit/postgresql.git/commit/?id=a379061a22a8fdf421e1a457cc6af8503def6252 discussion: https://postgr.es/m/CACJufxFbH1_9BDow=4nmsdbflsoakigd5hxo6bouwjzayhb...@mail.gmail.com --- doc/src/sgml/catalogs.sgml | 2 +- doc/src/sgml/ref/alter_table.sgml | 4 +- doc/src/sgml/ref/create_table.sgml | 4 +- src/backend/catalog/heap.c | 68 +++++-- src/backend/catalog/pg_constraint.c | 49 +++-- src/backend/catalog/sql_features.txt | 2 +- src/backend/commands/tablecmds.c | 175 +++++++++++------ src/backend/parser/gram.y | 3 +- src/backend/parser/parse_utilcmd.c | 92 ++++++--- src/backend/utils/cache/relcache.c | 6 +- src/bin/pg_dump/pg_dump.c | 39 +++- src/bin/pg_dump/pg_dump.h | 4 +- src/bin/pg_dump/t/002_pg_dump.pl | 33 ++++ src/bin/psql/describe.c | 20 +- src/include/access/tupdesc.h | 7 +- src/include/catalog/heap.h | 2 +- src/include/catalog/pg_constraint.h | 3 +- src/test/regress/expected/constraints.out | 160 +++++++++++++++ .../regress/expected/create_table_like.out | 24 ++- src/test/regress/expected/inherit.out | 184 ++++++++++++++++++ src/test/regress/expected/partition_merge.out | 3 + src/test/regress/expected/partition_split.out | 3 + src/test/regress/sql/constraints.sql | 82 ++++++++ src/test/regress/sql/create_table_like.sql | 17 +- src/test/regress/sql/inherit.sql | 92 +++++++++ src/test/regress/sql/partition_merge.sql | 1 + src/test/regress/sql/partition_split.sql | 1 + 27 files changed, 945 insertions(+), 135 deletions(-) diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml index 6066c4784f4..48f34d8b26e 100644 --- a/doc/src/sgml/catalogs.sgml +++ b/doc/src/sgml/catalogs.sgml @@ -1288,7 +1288,7 @@ <structfield>attnotnull</structfield> <type>bool</type> </para> <para> - This column has a (possibly invalid) not-null constraint. + This column has an enforced (possibly invalid) not-null constraint. </para></entry> </row> diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml index ff7071bef5b..657ee42b701 100644 --- a/doc/src/sgml/ref/alter_table.sgml +++ b/doc/src/sgml/ref/alter_table.sgml @@ -1740,8 +1740,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 e342585c7f0..230f35bb2dc 100644 --- a/doc/src/sgml/ref/create_table.sgml +++ b/doc/src/sgml/ref/create_table.sgml @@ -1446,8 +1446,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 88087654de9..31df2eb8b3b 100644 --- a/src/backend/catalog/heap.c +++ b/src/backend/catalog/heap.c @@ -2273,7 +2273,7 @@ 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_no_inherit, bool is_enforced) { Oid constrOid; @@ -2285,7 +2285,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), @@ -2647,17 +2647,19 @@ 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, enforceability. */ if (AdjustNotNullInheritance(RelationGetRelid(rel), colnum, cdef->conname, is_local, cdef->is_no_inherit, - cdef->skip_validation)) + cdef->skip_validation, + cdef->is_enforced)) continue; /* @@ -2688,7 +2690,8 @@ AddRelationNewConstraints(Relation rel, cdef->initially_valid, is_local, inhcount, - cdef->is_no_inherit); + cdef->is_no_inherit, + cdef->is_enforced); nncooked = palloc_object(CookedConstraint); nncooked->contype = CONSTR_NOTNULL; @@ -2696,7 +2699,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; @@ -2972,7 +2975,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);) { @@ -2988,6 +2991,12 @@ 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. @@ -3033,6 +3042,17 @@ AddRelationNotNullConstraints(Relation rel, List *constraints, strVal(linitial(constr->keys))), errdetail("The column has an inherited not-null constraint."))); + /* + * If we get an 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); } @@ -3067,11 +3087,16 @@ 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); - nncols = lappend_int(nncols, attnum); + /* CREATE TABLE will treat the NOT VALID not-null constraint as VALID */ + StoreRelNotNull(rel, conname, attnum, + constr->is_enforced, + true, inhcount, constr->is_no_inherit, + constr->is_enforced); + + if (constr->is_enforced) + nncols = lappend_int(nncols, attnum); } /* @@ -3116,6 +3141,18 @@ AddRelationNotNullConstraints(Relation rel, List *constraints, conname = other->name; inhcount++; + + /* + * A column may inherit multiple NOT NULL constraints. If one + * is marked NOT ENFORCED while another is ENFORCED, we + * install the enforced one. + */ + if (other->is_enforced != cooked->is_enforced) + { + cooked->is_enforced = true; + cooked->skip_validation = false; + } + old_notnulls = list_delete_nth_cell(old_notnulls, restpos); } else @@ -3146,10 +3183,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, - false, inhcount, false); + StoreRelNotNull(rel, conname, cooked->attnum, + cooked->is_enforced, + false, inhcount, false, + cooked->is_enforced); - 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 b12765ae691..9fd2a5fe2fc 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); + /* 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 @@ -728,8 +729,8 @@ extractNotNullColumn(HeapTuple constrTup) * If no not-null constraint is found for the column, return false. * Caller can create one. * - * If a constraint exists but the connoinherit flag is not what the caller - * wants, throw an error about the incompatibility. If the desired + * If a constraint exists but the connoinherit, conenforced flag is not what the + * caller wants, throw an error about the incompatibility. If the desired * constraint is valid but the existing constraint is not valid, also * throw an error about that (the opposite case is acceptable). If * the proposed constraint has a different name, also throw an error. @@ -740,7 +741,8 @@ extractNotNullColumn(HeapTuple constrTup) */ bool AdjustNotNullInheritance(Oid relid, AttrNumber attnum, const char *new_conname, - bool is_local, bool is_no_inherit, bool is_notvalid) + bool is_local, bool is_no_inherit, bool is_notvalid, + bool is_enforced) { HeapTuple tup; @@ -770,7 +772,7 @@ AdjustNotNullInheritance(Oid relid, AttrNumber attnum, const char *new_conname, * 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\"", @@ -794,6 +796,26 @@ AdjustNotNullInheritance(Oid relid, AttrNumber attnum, const char *new_conname, errdetail("A not-null constraint named \"%s\" already exists for this column.", NameStr(conform->conname))); + /* + * If the ENFORCED status we're asked for doesn't match what the + * existing constraint has, then throw an error. + */ + if (is_enforced != conform->conenforced) + { + if (is_enforced) + ereport(ERROR, + errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE), + errmsg("cannot change not enforced NOT NULL constraint \"%s\" on relation \"%s\" to enforced", + NameStr(conform->conname), get_rel_name(relid)), + errhint("You might need to ensure the existing constraint is enforced.")); + else + ereport(ERROR, + errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE), + errmsg("cannot change enforced NOT NULL constraint \"%s\" on relation \"%s\" to not enforced", + NameStr(conform->conname), get_rel_name(relid)), + errhint("You might need to ensure the existing constraint is not enforced.")); + } + if (!is_local) { if (pg_add_s16_overflow(conform->coninhcount, 1, @@ -824,6 +846,7 @@ AdjustNotNullInheritance(Oid relid, AttrNumber attnum, const char *new_conname, * 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. @@ -870,7 +893,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; @@ -890,7 +913,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 = conForm->convalidated; constr->is_no_inherit = conForm->connoinherit; diff --git a/src/backend/catalog/sql_features.txt b/src/backend/catalog/sql_features.txt index 55f073e2262..28118cd89e9 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 2fa534413ea..ce829d6442b 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -551,7 +551,8 @@ static void set_attnotnull(List **wqueue, Relation rel, AttrNumber attnum, static ObjectAddress ATExecSetNotNull(List **wqueue, Relation rel, char *conName, char *colName, bool recurse, bool recursing, - LOCKMODE lockmode); + LOCKMODE lockmode, + bool is_enforced); static bool NotNullImpliedByRelConstraints(Relation rel, Form_pg_attribute attr); static bool ConstraintImpliedByRelConstraint(Relation scanrel, List *testConstraint, List *provenConstraint); @@ -2830,13 +2831,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 - * that's not marked NO INHERIT (even if not valid). + * 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++) @@ -5517,7 +5521,7 @@ ATExecCmd(List **wqueue, AlteredTableInfo *tab, break; case AT_SetNotNull: /* ALTER COLUMN SET NOT NULL */ address = ATExecSetNotNull(wqueue, rel, NULL, cmd->name, - cmd->recurse, false, lockmode); + cmd->recurse, false, lockmode, true); break; case AT_SetExpression: address = ATExecSetExpression(tab, rel, cmd->name, cmd->def, lockmode); @@ -7878,6 +7882,8 @@ 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, @@ -7906,11 +7912,22 @@ 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) + /* + * Find the constraint that makes this column NOT NULL, and drop it. + * dropconstraint_internal() resets attnotnull. + */ + conTup = findNotNullConstraintAttnum(RelationGetRelid(rel), attnum); + if (conTup == NULL) { - table_close(attr_rel, RowExclusiveLock); - return InvalidObjectAddress; + if (attTup->attnotnull) + elog(ERROR, "cache lookup failed for not-null constraint on column \"%s\" of relation \"%s\"", + colName, RelationGetRelationName(rel)); + else + { + /* Skip if no NOT NULL constraint exists (including NOT ENFORCED). */ + table_close(attr_rel, RowExclusiveLock); + return InvalidObjectAddress; + } } /* Prevent them from altering a system attribute */ @@ -7945,15 +7962,6 @@ ATExecDropNotNull(Relation rel, const char *colName, bool recurse, table_close(parent, AccessShareLock); } - /* - * Find the constraint that makes this column NOT NULL, and drop it. - * dropconstraint_internal() resets attnotnull. - */ - 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)); - /* The normal case: we have a pg_constraint row, remove it */ dropconstraint_internal(rel, conTup, DROP_RESTRICT, recurse, false, false, lockmode); @@ -8045,10 +8053,14 @@ set_attnotnull(List **wqueue, Relation rel, AttrNumber attnum, * * We must recurse to child tables during execution, rather than using * ALTER TABLE's normal prep-time recursion. + * + * When the is_enforced flag is false, the newly added NOT NULL constraint will + * be not enforced. This supports potential future syntax such as + * ALTER TABLE ALTER COLUMN SET NOT NULL NOT ENFORCED. */ static ObjectAddress ATExecSetNotNull(List **wqueue, Relation rel, char *conName, char *colName, - bool recurse, bool recursing, LOCKMODE lockmode) + bool recurse, bool recursing, LOCKMODE lockmode, bool is_enforced) { HeapTuple tuple; AttrNumber attnum; @@ -8083,7 +8095,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. It maybe not enforced! */ tuple = findNotNullConstraintAttnum(RelationGetRelid(rel), attnum); if (HeapTupleIsValid(tuple)) { @@ -8100,6 +8112,13 @@ ATExecSetNotNull(List **wqueue, Relation rel, char *conName, char *colName, NameStr(conForm->conname), RelationGetRelationName(rel))); + if (is_enforced && !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 we find an appropriate constraint, we're almost done, but just * need to change some properties on it: if we're recursing, increment @@ -8119,7 +8138,7 @@ ATExecSetNotNull(List **wqueue, Relation rel, char *conName, char *colName, conForm->conislocal = true; changed = true; } - else if (!conForm->convalidated) + else if (is_enforced && !conForm->convalidated && conForm->conenforced) { /* * Flip attnotnull and convalidated, and also validate the @@ -8180,6 +8199,9 @@ ATExecSetNotNull(List **wqueue, Relation rel, char *conName, char *colName, constraint = makeNotNullConstraint(makeString(colName)); constraint->is_no_inherit = is_no_inherit; constraint->conname = conName; + constraint->is_enforced = is_enforced; + constraint->initially_valid = is_enforced; + constraint->skip_validation = !is_enforced; /* and do it */ cooked = AddRelationNewConstraints(rel, NIL, list_make1(constraint), @@ -8188,7 +8210,8 @@ ATExecSetNotNull(List **wqueue, Relation rel, char *conName, char *colName, ObjectAddressSet(address, ConstraintRelationId, ccon->conoid); /* Mark pg_attribute.attnotnull for the column and queue validation */ - set_attnotnull(wqueue, rel, attnum, true); + if (ccon->is_enforced) + set_attnotnull(wqueue, rel, attnum, true); InvokeObjectPostAlterHook(RelationRelationId, RelationGetRelid(rel), attnum); @@ -8210,7 +8233,7 @@ ATExecSetNotNull(List **wqueue, Relation rel, char *conName, char *colName, CommandCounterIncrement(); ATExecSetNotNull(wqueue, childrel, conName, colName, - recurse, true, lockmode); + recurse, true, lockmode, is_enforced); table_close(childrel, NoLock); } } @@ -9721,7 +9744,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), @@ -9731,6 +9754,17 @@ 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 ensure the existing constraint is enforced.")); } /* @@ -10100,9 +10134,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. No need to set attnotnull for not enforced not-null. */ - if (constr->contype == CONSTR_NOTNULL) + if (constr->contype == CONSTR_NOTNULL && ccon->is_enforced) set_attnotnull(wqueue, rel, ccon->attnum, !constr->skip_validation); @@ -13197,7 +13231,9 @@ ATExecAlterConstrInheritability(List **wqueue, ATAlterConstraint *cmdcon, Relation childrel = table_open(childoid, NoLock); addr = ATExecSetNotNull(wqueue, childrel, NameStr(currcon->conname), - colName, true, true, lockmode); + colName, true, true, + lockmode, + currcon->conenforced); if (OidIsValid(addr.objectId)) CommandCounterIncrement(); table_close(childrel, NoLock); @@ -18241,9 +18277,29 @@ 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); + + Assert(!child_con->conenforced); + + /* + * An unenforced NOT NULL constraint on the child cannot + * be merged with an enforced constraint on the parent. + */ + 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, @@ -18495,13 +18551,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), @@ -23164,29 +23231,9 @@ createTableConstraints(List **wqueue, AlteredTableInfo *tab, /* Don't need the cookedConstraints anymore. */ list_free_deep(cookedConstraints); - - /* Reproduce not-null constraints. */ - if (constr->has_not_null) - { - List *nnconstraints; - - /* - * The "include_noinh" argument is false because a partitioned table - * can't have NO INHERIT constraint. - */ - nnconstraints = RelationGetNotNullConstraints(RelationGetRelid(parent_rel), - false, false); - - Assert(list_length(nnconstraints) > 0); - - /* - * We already set pg_attribute.attnotnull in createPartitionTable. No - * need call set_attnotnull again. - */ - AddRelationNewConstraints(newRel, NIL, nnconstraints, false, true, false, NULL); - } } + /* * createPartitionTable: * @@ -23211,6 +23258,7 @@ createPartitionTable(List **wqueue, RangeVar *newPartName, Oid namespaceId; AlteredTableInfo *new_partrel_tab; Form_pg_class parent_relform = parent_rel->rd_rel; + List *nnconstraints = NIL; /* If the existing rel is temp, it must belong to this session. */ if (RELATION_IS_OTHER_TEMP(parent_rel)) @@ -23334,6 +23382,23 @@ createPartitionTable(List **wqueue, RangeVar *newPartName, /* Create constraints, default values, and generated values. */ createTableConstraints(wqueue, new_partrel_tab, parent_rel, newRel); + /* + * create not-null constraints for newRel based on parent_rel. + * + * The "include_noinh" argument is false because a partitioned table can't + * have NO INHERIT constraint. + */ + nnconstraints = RelationGetNotNullConstraints(RelationGetRelid(parent_rel), + false, + false); + + /* + * We already set pg_attribute.attnotnull in createPartitionTable. No need + * call set_attnotnull again. + */ + AddRelationNewConstraints(newRel, NIL, nnconstraints, + false, true, false, NULL); + /* * Need to call CommandCounterIncrement, so a fresh relcache entry has * newly installed constraint info. diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index 17035fb4d15..f015acf78a1 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -4406,7 +4406,8 @@ 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 ccf6ee55310..c3533fd2f4e 100644 --- a/src/backend/parser/parse_utilcmd.c +++ b/src/backend/parser/parse_utilcmd.c @@ -315,6 +315,10 @@ transformCreateStmt(CreateStmt *stmt, const char *queryString) { char *colname = strVal(linitial(nn->keys)); + /* Don't set is_not_null to true for not enforced not-null */ + if (!nn->is_enforced) + continue; + foreach_node(ColumnDef, cd, cxt.columns) { /* not our column? */ @@ -595,6 +599,7 @@ transformColumnDefinition(CreateStmtContext *cxt, ColumnDef *column) bool saw_generated; bool need_notnull = false; bool disallow_noinherit_notnull = false; + bool disallow_notenforced_notnull = false; Constraint *notnull_constraint = NULL; cxt->columns = lappend(cxt->columns, column); @@ -695,6 +700,7 @@ transformColumnDefinition(CreateStmtContext *cxt, ColumnDef *column) /* have a not-null constraint added later */ need_notnull = true; disallow_noinherit_notnull = true; + disallow_notenforced_notnull = true; } /* Process column constraints, if any... */ @@ -711,8 +717,12 @@ 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. + * + * Also check if the added NOT NULL constraint is prohibited from being + * NOT ENFORCED. This restriction applies to PRIMARY KEY, IDENTITY, and + * SERIAL columns. */ - if (!disallow_noinherit_notnull) + if (!disallow_noinherit_notnull || !disallow_notenforced_notnull) { foreach_node(Constraint, constraint, column->constraints) { @@ -721,6 +731,7 @@ transformColumnDefinition(CreateStmtContext *cxt, ColumnDef *column) case CONSTR_IDENTITY: case CONSTR_PRIMARY: disallow_noinherit_notnull = true; + disallow_notenforced_notnull = true; break; default: break; @@ -771,6 +782,12 @@ transformColumnDefinition(CreateStmtContext *cxt, ColumnDef *column) errmsg("conflicting NO INHERIT declarations for not-null constraints on column \"%s\"", column->colname)); + if (disallow_notenforced_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. @@ -988,6 +1005,20 @@ transformColumnDefinition(CreateStmtContext *cxt, ColumnDef *column) constraint->location))); } + + foreach_node(Constraint, constraint, column->constraints) + { + if (constraint->contype != CONSTR_NOTNULL) + continue; + + /* + * NOT ENFORCED not-null does not indicate data are all not-null, + * therefore cannot set the column's pg_attribute.attnotnull to true. + */ + if (!constraint->is_enforced) + column->is_not_null = false; + } + /* * If we need a not-null constraint for PRIMARY KEY, SERIAL or IDENTITY, * and one was not explicitly specified, add one now. @@ -1129,6 +1160,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); @@ -1270,33 +1302,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 not-null constraint as not + * valid doesn't make sense, so we treat it as valid unconditionally. + */ + 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); } } } @@ -4332,7 +4372,8 @@ transformConstraintAttrs(ParseState *pstate, 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"), @@ -4349,7 +4390,8 @@ transformConstraintAttrs(ParseState *pstate, 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 19c4ff6e75e..fb13edc9f85 100644 --- a/src/backend/utils/cache/relcache.c +++ b/src/backend/utils/cache/relcache.c @@ -4635,8 +4635,8 @@ CheckNNConstraintFetch(Relation relation) bool isnull; /* - * If this is a not-null constraint, then only look at it if it's - * invalid, and if so, mark the TupleDesc entry as known invalid. + * If this is an enforced not-null constraint, then only look at it if + * it's invalid, and if so, mark the TupleDesc entry as known invalid. * Otherwise move on. We'll mark any remaining columns that are still * in UNKNOWN state as known valid later. This allows us not to have * to extract the attnum from this constraint tuple in the vast @@ -4644,7 +4644,7 @@ CheckNNConstraintFetch(Relation relation) */ if (conform->contype == CONSTRAINT_NOTNULL) { - if (!conform->convalidated) + if (!conform->convalidated && conform->conenforced) { AttrNumber attnum; diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c index 4948e6d80c7..1c5e4d83d2c 100644 --- a/src/bin/pg_dump/pg_dump.c +++ b/src/bin/pg_dump/pg_dump.c @@ -361,6 +361,7 @@ static void determineNotNullFlags(Archive *fout, PGresult *res, int r, int i_notnull_invalidoid, int i_notnull_noinherit, int i_notnull_islocal, + int i_notnull_enforced, PQExpBuffer *invalidnotnulloids); static char *format_function_arguments(const FuncInfo *finfo, const char *funcargs, bool is_agg); @@ -9209,6 +9210,7 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables) int i_notnull_comment; int i_notnull_noinherit; int i_notnull_islocal; + int i_notnull_enforced; int i_notnull_invalidoid; int i_attoptions; int i_attcollation; @@ -9300,12 +9302,13 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables) /* * Find out any NOT NULL markings for each column. In 18 and up we read - * pg_constraint to obtain the constraint name, and for valid constraints - * also pg_description to obtain its comment. notnull_noinherit is set - * according to the NO INHERIT property. For versions prior to 18, we - * store an empty string as the name when a constraint is marked as - * attnotnull (this cues dumpTableSchema to print the NOT NULL clause - * without a name); also, such cases are never NO INHERIT. + * pg_constraint to obtain the constraint name, and for valid and not + * enforced constraints also pg_description to obtain its comment. + * notnull_noinherit is set according to the NO INHERIT property. For + * versions prior to 18, we store an empty string as the name when a + * constraint is marked as attnotnull (this cues dumpTableSchema to print + * the NOT NULL clause without a name); also, such cases are never NO + * INHERIT. * * For invalid constraints, we need to store their OIDs for processing * elsewhere, so we bring the pg_constraint.oid value when the constraint @@ -9319,9 +9322,9 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables) if (fout->remoteVersion >= 180000) appendPQExpBufferStr(q, "co.conname AS notnull_name,\n" - "CASE WHEN co.convalidated THEN pt.description" + "CASE WHEN (NOT co.conenforced OR co.convalidated) THEN pt.description" " ELSE NULL END AS notnull_comment,\n" - "CASE WHEN NOT co.convalidated THEN co.oid " + "CASE WHEN (NOT co.convalidated AND co.conenforced) THEN co.oid " "ELSE NULL END AS notnull_invalidoid,\n" "co.connoinherit AS notnull_noinherit,\n" "co.conislocal AS notnull_islocal,\n"); @@ -9336,6 +9339,11 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables) " ELSE false\n" "END AS notnull_islocal,\n"); + if (fout->remoteVersion >= 200000) + appendPQExpBufferStr(q, "co.conenforced AS notnull_enforced,\n"); + else + appendPQExpBufferStr(q, "true AS notnull_enforced,\n"); + if (fout->remoteVersion >= 140000) appendPQExpBufferStr(q, "a.attcompression AS attcompression,\n"); @@ -9419,6 +9427,7 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables) i_notnull_invalidoid = PQfnumber(res, "notnull_invalidoid"); i_notnull_noinherit = PQfnumber(res, "notnull_noinherit"); i_notnull_islocal = PQfnumber(res, "notnull_islocal"); + i_notnull_enforced = PQfnumber(res, "notnull_enforced"); i_attoptions = PQfnumber(res, "attoptions"); i_attcollation = PQfnumber(res, "attcollation"); i_attcompression = PQfnumber(res, "attcompression"); @@ -9491,6 +9500,7 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables) tbinfo->notnull_invalid = pg_malloc_array(bool, numatts); tbinfo->notnull_noinh = pg_malloc_array(bool, numatts); tbinfo->notnull_islocal = pg_malloc_array(bool, numatts); + tbinfo->notnull_enforced = pg_malloc_array(bool, numatts); tbinfo->attrdefs = pg_malloc_array(AttrDefInfo *, numatts); hasdefaults = false; @@ -9525,6 +9535,7 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables) i_notnull_invalidoid, i_notnull_noinherit, i_notnull_islocal, + i_notnull_enforced, &invalidnotnulloids); tbinfo->notnull_comment[j] = PQgetisnull(res, r, i_notnull_comment) ? @@ -9973,10 +9984,16 @@ determineNotNullFlags(Archive *fout, PGresult *res, int r, int i_notnull_invalidoid, int i_notnull_noinherit, int i_notnull_islocal, + int i_notnull_enforced, PQExpBuffer *invalidnotnulloids) { DumpOptions *dopt = fout->dopt; + if (fout->remoteVersion >= 200000) + tbinfo->notnull_enforced[j] = PQgetvalue(res, r, i_notnull_enforced)[0] == 't'; + else + tbinfo->notnull_enforced[j] = true; + /* * If this not-null constraint is not valid, list its OID in * invalidnotnulloids and do nothing further. It'll be processed @@ -17351,6 +17368,9 @@ dumpTableSchema(Archive *fout, const TableInfo *tbinfo) if (tbinfo->notnull_noinh[j]) appendPQExpBufferStr(q, " NO INHERIT"); + + if (!tbinfo->notnull_enforced[j]) + appendPQExpBufferStr(q, " NOT ENFORCED"); } /* Add collation if not default for the type */ @@ -17398,6 +17418,9 @@ dumpTableSchema(Archive *fout, const TableInfo *tbinfo) if (tbinfo->notnull_noinh[j]) appendPQExpBufferStr(q, " NO INHERIT"); + + if (!tbinfo->notnull_enforced[j]) + appendPQExpBufferStr(q, " NOT ENFORCED"); } } diff --git a/src/bin/pg_dump/pg_dump.h b/src/bin/pg_dump/pg_dump.h index 5a6726d8b12..3a09c6020d8 100644 --- a/src/bin/pg_dump/pg_dump.h +++ b/src/bin/pg_dump/pg_dump.h @@ -373,9 +373,11 @@ typedef struct _tableInfo * empty string, unnamed constraint * (pre-v17) */ char **notnull_comment; /* comment thereof */ - bool *notnull_invalid; /* true for NOT NULL NOT VALID */ + bool *notnull_invalid; /* true for NOT NULL NOT VALID. Note It's + * false for NOT NULL NOT ENFORCED too. */ bool *notnull_noinh; /* NOT NULL is NO INHERIT */ bool *notnull_islocal; /* true if NOT NULL has local definition */ + bool *notnull_enforced; /* false for NOT NULL NOT ENFORCED */ struct _attrDefInfo **attrdefs; /* DEFAULT expressions */ struct _constraintInfo *checkexprs; /* CHECK constraints */ struct _relStatsInfo *stats; /* only set for matviews */ diff --git a/src/bin/pg_dump/t/002_pg_dump.pl b/src/bin/pg_dump/t/002_pg_dump.pl index 9258948b583..1bdbdd477f4 100644 --- a/src/bin/pg_dump/t/002_pg_dump.pl +++ b/src/bin/pg_dump/t/002_pg_dump.pl @@ -1018,6 +1018,39 @@ my %tests = ( }, }, + 'CONSTRAINT NOT NULL NOT ENFORCED' => { + create_sql => 'CREATE TABLE dump_test.test_table_nn0 ( + col1 int, CONSTRAINT nn NOT NULL col1 NOT ENFORCED); + COMMENT ON CONSTRAINT nn ON dump_test.test_table_nn0 IS \'nn comment is not enfoced\';', + regexp => qr/^ + \QCREATE TABLE dump_test.test_table_nn0 (\E\n + \s+\Qcol1 integer CONSTRAINT nn NOT NULL NOT ENFORCED\E\n + \); + /xm, + like => { + %full_runs, %dump_test_schema_runs, section_pre_data => 1, + }, + unlike => { + exclude_dump_test_schema => 1, + only_dump_measurement => 1, + }, + }, + + # The not-null constraint is dumped as part of CREATE TABLE, which is in + # SECTION_PRE_DATA, so its comment goes there too + 'COMMENT ON CONSTRAINT ON test_table_nn0' => { + regexp => qr/^ + \QCOMMENT ON CONSTRAINT nn ON dump_test.test_table_nn0 IS\E + /xm, + like => { + %full_runs, %dump_test_schema_runs, section_pre_data => 1, + }, + unlike => { + exclude_dump_test_schema => 1, + only_dump_measurement => 1, + }, + }, + 'CONSTRAINT NOT NULL / NOT VALID' => { create_sql => 'CREATE TABLE dump_test.test_table_nn ( col1 int); diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c index ad9c8affb4f..69effb7fc26 100644 --- a/src/bin/psql/describe.c +++ b/src/bin/psql/describe.c @@ -3187,7 +3187,14 @@ describeOneTableDetails(const char *schemaname, appendPQExpBuffer(&buf, "SELECT c.conname, a.attname, c.connoinherit,\n" " c.conislocal, c.coninhcount <> 0,\n" - " c.convalidated\n" + " c.convalidated, "); + + if (pset.sversion >= 200000) + 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" @@ -3211,15 +3218,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/access/tupdesc.h b/src/include/access/tupdesc.h index d26287271e9..48db97134a5 100644 --- a/src/include/access/tupdesc.h +++ b/src/include/access/tupdesc.h @@ -42,7 +42,12 @@ typedef struct TupleConstr struct AttrMissing *missing; /* missing attributes values, NULL if none */ uint16 num_defval; uint16 num_check; - bool has_not_null; /* any not-null, including not valid ones */ + + /* + * any enforced not-null, including not valid ones. Note this does not + * include not enforced not-null. + */ + bool has_not_null; bool has_generated_stored; bool has_generated_virtual; } TupleConstr; diff --git a/src/include/catalog/heap.h b/src/include/catalog/heap.h index 6c9ac812aa0..344d4bc5390 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 1b7fedf1750..a60c0d5b59f 100644 --- a/src/include/catalog/pg_constraint.h +++ b/src/include/catalog/pg_constraint.h @@ -268,7 +268,8 @@ 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, const char *new_conname, - bool is_local, bool is_no_inherit, bool is_notvalid); + 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 83f97f684d5..8ccc5f7fcd6 100644 --- a/src/test/regress/expected/constraints.out +++ b/src/test/regress/expected/constraints.out @@ -1082,7 +1082,127 @@ create table notnull_tbl_fail (a int generated by default as identity, constrain ERROR: conflicting NO INHERIT 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 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 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 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 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 not enforced); +ERROR: conflicting NOT ENFORCED declaration for not-null constraint 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 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 not enforced); +ERROR: conflicting NOT ENFORCED declarations for not-null constraints 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 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 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 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 constraint bar not null not enforced); +ERROR: conflicting NOT ENFORCED declarations for not-null constraints on column "a" +alter table notnull_tbl1 add column c serial, add constraint notnull_tbl1_c_not_null not null c not enforced; -- error +ERROR: cannot change enforced NOT NULL constraint "notnull_tbl1_c_not_null" on relation "notnull_tbl1" to not enforced +HINT: You might need to ensure the existing constraint is not enforced. +alter table notnull_tbl1 add column c serial not null not enforced; -- error +ERROR: conflicting NOT ENFORCED declarations for not-null constraints on column "c" +alter table notnull_tbl1 add column c int generated always as identity not null not enforced; -- error +ERROR: conflicting NOT ENFORCED declarations for not-null constraints on column "c" +alter table notnull_tbl1 add column c int not null not enforced not null not enforced, alter column c add generated always as identity; -- error +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 not null not enforced enforced; -- error +ERROR: multiple ENFORCED/NOT ENFORCED clauses not allowed +LINE 1: ...otnull_tbl1 add column c int not null not enforced enforced; + ^ +alter table notnull_tbl1 add column c int not null not enforced null enforced; -- error +ERROR: misplaced ENFORCED clause +LINE 1: ...l_tbl1 add column c int not null not enforced null enforced; + ^ +alter table notnull_tbl1 add column c int not null not enforced null; -- error +ERROR: conflicting NULL/NOT NULL declarations for column "c" of table "notnull_tbl1" +LINE 1: ...le notnull_tbl1 add column c int not null not enforced null; + ^ 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, b int, CONSTRAINT nnb NOT NULL b NOT VALID); +SELECT conname, conrelid::regclass, convalidated, conenforced, pg_get_constraintdef(oid) +FROM pg_constraint +WHERE conrelid = 'ne_nn_tbl'::regclass; + conname | conrelid | convalidated | conenforced | pg_get_constraintdef +---------+-----------+--------------+-------------+------------------------- + nn | ne_nn_tbl | f | f | NOT NULL x NOT ENFORCED + nnb | ne_nn_tbl | t | t | NOT NULL b +(2 rows) + +INSERT INTO ne_nn_tbl VALUES (NULL, 1); -- ok +ALTER TABLE ne_nn_tbl ALTER CONSTRAINT nn ENFORCED; -- error +ERROR: cannot alter enforceability of constraint "nn" of relation "ne_nn_tbl" +HINT: Only foreign key and check constraints can change enforceability. +ALTER TABLE ne_nn_tbl ALTER CONSTRAINT nn NOT ENFORCED; -- error +ERROR: cannot alter enforceability of constraint "nn" of relation "ne_nn_tbl" +HINT: Only foreign key and check constraints can change enforceability. +ALTER TABLE ne_nn_tbl VALIDATE CONSTRAINT nn; -- error +ERROR: cannot validate NOT ENFORCED constraint +ALTER TABLE ne_nn_tbl ADD CONSTRAINT nn_enforced1 NOT NULL x NOT ENFORCED; -- error +ERROR: cannot create not-null constraint "nn_enforced1" on column "x" of table "ne_nn_tbl" +DETAIL: A not-null constraint named "nn" already exists for this column. +ALTER TABLE ne_nn_tbl ADD CONSTRAINT nn NOT NULL x NOT ENFORCED; -- no-op +ALTER TABLE ne_nn_tbl ADD CONSTRAINT nn NOT NULL x NOT VALID NOT ENFORCED; -- no-op, because NOT ENFORCED imply NOT VALID. +ALTER TABLE ne_nn_tbl ADD CONSTRAINT nn 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 NOT NULL x NOT VALID; -- error, because NOT VALID alone imply ENFORCED +ERROR: cannot change not enforced NOT NULL constraint "nn" on relation "ne_nn_tbl" to enforced +HINT: You might need to ensure the existing constraint is enforced. +ALTER TABLE ne_nn_tbl ADD CONSTRAINT nn NOT NULL x ENFORCED; -- error +ERROR: cannot change not enforced NOT NULL constraint "nn" on relation "ne_nn_tbl" to enforced +HINT: You might need to ensure the existing constraint is enforced. +ALTER TABLE ne_nn_tbl ADD CONSTRAINT nn NOT NULL x; -- error +ERROR: cannot change not enforced NOT NULL constraint "nn" on relation "ne_nn_tbl" to enforced +HINT: You might need to ensure the existing constraint is enforced. +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 | | + b | integer | | not null | | plain | | +Not-null constraints: + "nn" NOT NULL "x" NOT ENFORCED + "nnb" NOT NULL "b" + +TRUNCATE ne_nn_tbl; +-- error, cannot 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 ensure the existing constraint is enforced. +ALTER TABLE ne_nn_tbl ALTER CONSTRAINT nn NO INHERIT; -- ok +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 | | + b | integer | | not null | | plain | | + x1 | integer | | | | plain | | + y | integer | | not null | | plain | | +Not-null constraints: + "nn" NOT NULL "x" NO INHERIT NOT ENFORCED + "nnb" NOT NULL "b" + "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); @@ -1791,6 +1911,46 @@ 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 NOT ENFORCED +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 cannot 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 nn_enforced_tbl1 (a int, b int, CONSTRAINT nn0 NOT NULL a NOT ENFORCED) PARTITION BY LIST (a); +CREATE TABLE nn_enforced_tbl1_1 PARTITION OF nn_enforced_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 nn_enforced_tbl1_2(a int, CONSTRAINT nn1 NOT NULL a, b int); +ALTER TABLE nn_enforced_tbl1 ATTACH PARTITION nn_enforced_tbl1_2 FOR VALUES IN (3,4); -- ok +CREATE TABLE nn_enforced_tbl1_3(a int, b int, CONSTRAINT nn2 NOT NULL a NOT ENFORCED); +ALTER TABLE nn_enforced_tbl1 ATTACH PARTITION nn_enforced_tbl1_3 FOR VALUES IN (NULL,5); +CREATE TABLE nn_enforced_tbl1_4(a int, b int); +ALTER TABLE nn_enforced_tbl1 ATTACH PARTITION nn_enforced_tbl1_4 FOR VALUES IN (6); -- error +ERROR: column "a" in child table "nn_enforced_tbl1_4" must be marked NOT NULL NOT ENFORCED +DROP TABLE nn_enforced_tbl1_4; +EXECUTE get_nnconstraint_info('{nn_enforced_tbl1, nn_enforced_tbl1_1, nn_enforced_tbl1_2, nn_enforced_tbl1_3}'); + relname | conname | convalidated | conislocal | coninhcount | conenforced +--------------------+---------+--------------+------------+-------------+------------- + nn_enforced_tbl1 | nn0 | f | t | 0 | f + nn_enforced_tbl1_1 | nn0 | f | f | 1 | f + nn_enforced_tbl1_2 | nn1 | t | f | 1 | t + nn_enforced_tbl1_3 | nn2 | f | f | 1 | f +(4 rows) + +ALTER TABLE nn_enforced_tbl1 ALTER COLUMN a SET NOT NULL; -- error, cannot validate not-enforced +ERROR: cannot validate NOT ENFORCED constraint "nn0" on relation "nn_enforced_tbl1" +ALTER TABLE nn_enforced_tbl1 VALIDATE CONSTRAINT nn0; -- error, cannot validate not-enforced +ERROR: cannot validate NOT ENFORCED constraint +-- nn_enforced_tbl1 is used for pg_upgrade tests +DEALLOCATE get_nnconstraint_info; +-- end NOT NULL NOT ENFORCED -- 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 a23735b5fb4..62f3b102316 100644 --- a/src/test/regress/expected/create_table_like.out +++ b/src/test/regress/expected/create_table_like.out @@ -279,9 +279,31 @@ CREATE TABLE test_like_6c (LIKE test_like_6 INCLUDING ALL); Statistics objects: "public.test_like_6c_expr_stat" ON (a || b) FROM test_like_6c +-- not null not enforced constraint +ALTER TABLE test_like_6c ADD COLUMN x INT, ADD CONSTRAINT nn_notenforced NOT NULL x NOT ENFORCED; +COMMENT ON CONSTRAINT nn_notenforced ON test_like_6c is 'not null not enforced constraint comment test'; +CREATE TABLE test_like_6c_copy(LIKE test_like_6c INCLUDING CONSTRAINTS INCLUDING COMMENTS); +SELECT pg_get_constraintdef(oid) FROM pg_constraint +WHERE conrelid = 'test_like_6c_copy'::regclass +AND conname = 'nn_notenforced'; + pg_get_constraintdef +------------------------- + NOT NULL x NOT ENFORCED +(1 row) + +SELECT conname, description +FROM pg_description, pg_constraint c +WHERE classoid = 'pg_constraint'::regclass +AND objoid = c.oid AND c.conrelid = 'test_like_6c_copy'::regclass +ORDER BY conname COLLATE "C"; + conname | description +----------------+----------------------------------------------- + nn_notenforced | not null not enforced constraint comment test +(1 row) + DROP TABLE test_like_4, test_like_4a, test_like_4b, test_like_4c, test_like_4d; DROP TABLE test_like_5, test_like_5x, test_like_5c; -DROP TABLE test_like_6, test_like_6c; +DROP TABLE test_like_6, test_like_6c, test_like_6c_copy; CREATE TABLE inhg (x text, LIKE inhx INCLUDING INDEXES, y text); /* copies indexes */ INSERT INTO inhg VALUES (5, 10); INSERT INTO inhg VALUES (20, 10); -- should fail diff --git a/src/test/regress/expected/inherit.out b/src/test/regress/expected/inherit.out index 0136aa53c96..2b08a4b91eb 100644 --- a/src/test/regress/expected/inherit.out +++ b/src/test/regress/expected/inherit.out @@ -1616,6 +1616,190 @@ 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. +create table p1_c2(f1 int not null not enforced) inherits(p1_c1, p1); -- error +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_c2(f1 int not null not enforced) inherits(p1_c1); -- ok. produce not enforced +NOTICE: merging column "f1" with inherited definition +create table p1_c3(f1 int not null enforced) inherits(p1_c1, p1); -- ok. produce enforced +NOTICE: merging multiple inherited definitions of column "f1" +NOTICE: merging column "f1" with inherited definition +create table p1_c4(f1 int ) inherits(p1_c1, p1, p1_c2, p1_c3); -- ok, produce enforced +NOTICE: merging multiple inherited definitions of column "f1" +NOTICE: merging multiple inherited definitions of column "f1" +NOTICE: merging multiple inherited definitions of column "f1" +NOTICE: merging column "f1" with inherited definition +select conrelid::regclass::text, coninhcount, conenforced +from pg_constraint +where conrelid::regclass::text in ('p1', 'p1_c1', 'p1_c2', 'p1_c3', 'p1_c4') and contype = 'n'; + conrelid | coninhcount | conenforced +----------+-------------+------------- + p1 | 0 | t + p1_c1 | 0 | f + p1_c2 | 1 | f + p1_c3 | 2 | t + p1_c4 | 4 | t +(5 rows) + +drop table if exists p1, p1_c1, p1_c2, p1_c3, p1_c4 cascade; +create table p1(f1 int); +create table p1_c1() inherits(p1); +create table p1_c2() inherits(p1, p1_c1); +NOTICE: merging multiple inherited definitions of column "f1" +alter table p1 add constraint p1_nn_1 not null f1 not enforced; +alter table p1 alter column f1 drop not null; -- all not enforced not-null constraint will be droped +select 1 as expect_zero_row +from pg_constraint +where conrelid::regclass::text in ('p1', 'p1_c1', 'p1_c2', 'p1_c3', 'p1_c4') and contype = 'n'; + expect_zero_row +----------------- +(0 rows) + +alter table p1 add constraint p1_nn_1 not null f1 not enforced; +alter table p1 add constraint p1_nn_1x not null f1 not enforced; -- error, already exists +ERROR: cannot create not-null constraint "p1_nn_1x" on column "f1" of table "p1" +DETAIL: A not-null constraint named "p1_nn_1" already exists for this column. +alter table p1 add constraint p1_nn_1 not null f1 enforced; -- error, column f1 already have not enforced +ERROR: cannot change not enforced NOT NULL constraint "p1_nn_1" on relation "p1" to enforced +HINT: You might need to ensure the existing constraint is enforced. +alter table p1 add constraint p1_nn_1 not null f1 not enforced; +alter table p1_c1 add constraint p1_nn_1 not null f1 enforced; -- error +ERROR: cannot change not enforced NOT NULL constraint "p1_nn_1" on relation "p1_c1" to enforced +HINT: You might need to ensure the existing constraint is enforced. +alter table p1_c1 add constraint p1_nn_1 not null f1 not enforced; -- ok +-- not allowed: child is not enforced, parent is enforced +alter table p1 alter column f1 drop not null; +alter table p1_c1 alter column f1 drop not null; +alter table p1_c1 add constraint nn_x not null f1 not enforced; +alter table p1 add constraint nn_x not null f1 enforced; -- error +ERROR: cannot change not enforced NOT NULL constraint "nn_x" on relation "p1_c1" to enforced +HINT: You might need to ensure the existing constraint is enforced. +alter table p1_c1 alter column f1 drop not null; +alter table p1_c1 add constraint nn_v not null f1 not valid enforced; +alter table p1 add constraint nn_v not null f1 not enforced; -- error +ERROR: cannot change enforced NOT NULL constraint "nn_v" on relation "p1_c1" to not enforced +HINT: You might need to ensure the existing constraint is not enforced. +drop table p1 cascade; +NOTICE: drop cascades to 2 other objects +DETAIL: drop cascades to table p1_c1 +drop cascades to table p1_c2 +create table p1_nn(f1 int constraint p1_nn_a_nn not null not enforced); +create table p1_nn_c3(f1 int); +alter table p1_nn_c3 inherit p1_nn; -- error, because p1_nn_c3 does not have not-null constraint +ERROR: column "f1" in child table "p1_nn_c3" must be marked NOT NULL NOT ENFORCED +drop table p1_nn_c3; +create table p1_nn_c1(f1 int constraint p1_nn_c1_a_nn not null); +alter table p1_nn_c1 inherit p1_nn; -- ok: parent not-null is not enforced while child is enforced +create table p1_nn_c2() inherits(p1_nn, p1_nn_c1); -- merged multiple not-null constraints produce an enforced one +NOTICE: merging multiple inherited definitions of column "f1" +\d+ p1_nn_c2 + Table "public.p1_nn_c2" + Column | Type | Collation | Nullable | Default | Storage | Stats target | Description +--------+---------+-----------+----------+---------+---------+--------------+------------- + f1 | integer | | not null | | plain | | +Not-null constraints: + "p1_nn_a_nn" NOT NULL "f1" (inherited) +Inherits: + p1_nn + p1_nn_c1 + +create table p1_nn_c4(f1 int not null not enforced) inherits(p1_nn, p1_nn_c1); -- error, parent (p1_nn_c1) have enforced +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. +-- merged multiple not-null constraints produce an enforced one, below two will be success. +create table p1_nn_c4(f1 int not null) inherits(p1_nn, p1_nn_c1); +NOTICE: merging multiple inherited definitions of column "f1" +NOTICE: merging column "f1" with inherited definition +create table p1_nn_c5(f1 int) inherits(p1_nn, p1_nn_c1, p1_nn_c4); +NOTICE: merging multiple inherited definitions of column "f1" +NOTICE: merging multiple inherited definitions of column "f1" +NOTICE: merging column "f1" with inherited definition +select conrelid::regclass, conname, conenforced, convalidated, coninhcount +from pg_constraint +where conrelid::regclass::text = ANY ('{p1_nn, p1_nn_c1, p1_nn_c2, p1_nn_c4, p1_nn_c5}') and contype = 'n' +order by conname, conrelid::regclass::text collate "C"; + conrelid | conname | conenforced | convalidated | coninhcount +----------+----------------------+-------------+--------------+------------- + p1_nn | p1_nn_a_nn | f | f | 0 + p1_nn_c2 | p1_nn_a_nn | t | t | 2 + p1_nn_c5 | p1_nn_a_nn | t | t | 3 + p1_nn_c1 | p1_nn_c1_a_nn | t | t | 1 + p1_nn_c4 | p1_nn_c4_f1_not_null | t | t | 2 +(5 rows) + +-- p1_nn etc is used for pg_upgrade tests, so don't drop it +-- Test ALTER CONSTRAINT INHERIT for not enforced not null +create table inh_nn1 (f1 int, constraint nn not null f1 not enforced no inherit); +create table inh_nn2 (f2 text, f3 int) inherits (inh_nn1); +create table inh_nn3 (f1 int) inherits (inh_nn1, inh_nn2); +NOTICE: merging multiple inherited definitions of column "f1" +NOTICE: merging column "f1" with inherited definition +create table inh_nn4 (f1 int) inherits (inh_nn1, inh_nn2, inh_nn3); +NOTICE: merging multiple inherited definitions of column "f1" +NOTICE: merging multiple inherited definitions of column "f1" +NOTICE: merging multiple inherited definitions of column "f2" +NOTICE: merging multiple inherited definitions of column "f3" +NOTICE: merging column "f1" with inherited definition +alter table inh_nn2 add constraint nn2 not null f1; +alter table inh_nn1 alter constraint nn inherit; +create view constraint_info AS +select conrelid::regclass, conname, conkey[1], conenforced, convalidated, coninhcount, connoinherit, conislocal +from pg_constraint +where conrelid::regclass::text IN ('inh_nn1', 'inh_nn2', 'inh_nn3', 'inh_nn4') +and contype = 'n' +order by conname, conrelid::regclass::text collate "C"; +select * from constraint_info; + conrelid | conname | conkey | conenforced | convalidated | coninhcount | connoinherit | conislocal +----------+---------+--------+-------------+--------------+-------------+--------------+------------ + inh_nn1 | nn | 1 | f | f | 0 | f | t + inh_nn2 | nn2 | 1 | t | t | 1 | f | t + inh_nn3 | nn2 | 1 | t | t | 2 | f | f + inh_nn4 | nn2 | 1 | t | t | 3 | 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); +create table inh_nn2 (f2 text, f3 int) inherits (inh_nn1); +alter table inh_nn2 add constraint nn2 not null f1 not enforced; +create table inh_nn3 (f1 int) inherits (inh_nn1, inh_nn2); +NOTICE: merging multiple inherited definitions of column "f1" +NOTICE: merging column "f1" with inherited definition +select * from constraint_info; + conrelid | conname | conkey | conenforced | convalidated | coninhcount | connoinherit | conislocal +----------+---------+--------+-------------+--------------+-------------+--------------+------------ + inh_nn1 | nn | 1 | t | t | 0 | t | t + inh_nn2 | nn2 | 1 | f | f | 0 | f | t + inh_nn3 | nn2 | 1 | f | f | 1 | f | f +(3 rows) + +-- error, parent not-null is enforcecd, child not-null cannot be not enforced +alter table inh_nn1 alter constraint nn inherit; +ERROR: cannot validate NOT ENFORCED constraint "nn2" on relation "inh_nn2" +drop table inh_nn1 cascade; +NOTICE: drop cascades to 2 other objects +DETAIL: drop cascades to table inh_nn2 +drop cascades to table inh_nn3 +-- -- 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/expected/partition_merge.out b/src/test/regress/expected/partition_merge.out index ccda2b5843b..a2d44e6d216 100644 --- a/src/test/regress/expected/partition_merge.out +++ b/src/test/regress/expected/partition_merge.out @@ -904,6 +904,7 @@ ALTER TABLE t ADD CONSTRAINT t_b_check CHECK (b > 0); ALTER TABLE t ADD CONSTRAINT t_b_check1 CHECK (b > 0) NOT ENFORCED; ALTER TABLE t ADD CONSTRAINT t_b_check2 CHECK (b > 0) NOT VALID; ALTER TABLE t ADD CONSTRAINT t_b_nn NOT NULL b NOT VALID; +ALTER TABLE t ADD CONSTRAINT t_d_nn NOT NULL d NOT ENFORCED; INSERT INTO tp_0_1(i, t, b) VALUES(0, DEFAULT, 1); INSERT INTO tp_1_2(i, t, b) VALUES(1, DEFAULT, 2); CREATE OR REPLACE FUNCTION trigger_function() RETURNS trigger LANGUAGE 'plpgsql' AS @@ -938,6 +939,7 @@ Statistics objects: Not-null constraints: "tp_0_1_i_not_null" NOT NULL "i" (inherited) "t_b_nn" NOT NULL "b" (inherited) NOT VALID + "t_d_nn" NOT NULL "d" (inherited) NOT ENFORCED Triggers: t_before_insert_row_trigger BEFORE INSERT ON tp_0_1 FOR EACH ROW EXECUTE FUNCTION trigger_function('t'), ON TABLE t tp_0_1_before_insert_row_trigger BEFORE INSERT ON tp_0_1 FOR EACH ROW EXECUTE FUNCTION trigger_function('tp_0_1') @@ -960,6 +962,7 @@ Check constraints: Not-null constraints: "t_i_not_null" NOT NULL "i" (inherited) "t_b_nn" NOT NULL "b" (inherited) NOT VALID + "t_d_nn" NOT NULL "d" (inherited) NOT ENFORCED Triggers: t_before_insert_row_trigger BEFORE INSERT ON tp_0_1 FOR EACH ROW EXECUTE FUNCTION trigger_function('t'), ON TABLE t diff --git a/src/test/regress/expected/partition_split.out b/src/test/regress/expected/partition_split.out index 8e245563801..d4916a1835d 100644 --- a/src/test/regress/expected/partition_split.out +++ b/src/test/regress/expected/partition_split.out @@ -1556,6 +1556,7 @@ ALTER TABLE t ADD CONSTRAINT t_b_check CHECK (b > 0); ALTER TABLE t ADD CONSTRAINT t_b_check1 CHECK (b > 0) NOT ENFORCED; ALTER TABLE t ADD CONSTRAINT t_b_check2 CHECK (b > 0) NOT VALID; ALTER TABLE t ADD CONSTRAINT t_b_nn NOT NULL b NOT VALID; +ALTER TABLE t ADD CONSTRAINT t_t_nn NOT NULL d NOT ENFORCED; INSERT INTO tp_x(i, t, b) VALUES(0, DEFAULT, 1); INSERT INTO tp_x(i, t, b) VALUES(1, DEFAULT, 2); CREATE OR REPLACE FUNCTION trigger_function() RETURNS trigger LANGUAGE 'plpgsql' AS @@ -1588,6 +1589,7 @@ Statistics objects: Not-null constraints: "tp_x_i_not_null" NOT NULL "i" (inherited) "t_b_nn" NOT NULL "b" (inherited) NOT VALID + "t_t_nn" NOT NULL "d" (inherited) NOT ENFORCED Triggers: t_before_insert_row_trigger BEFORE INSERT ON tp_x FOR EACH ROW EXECUTE FUNCTION trigger_function('t'), ON TABLE t tp_x_before_insert_row_trigger BEFORE INSERT ON tp_x FOR EACH ROW EXECUTE FUNCTION trigger_function('tp_x') @@ -1612,6 +1614,7 @@ Check constraints: Not-null constraints: "t_i_not_null" NOT NULL "i" (inherited) "t_b_nn" NOT NULL "b" (inherited) NOT VALID + "t_t_nn" NOT NULL "d" (inherited) NOT ENFORCED Triggers: t_before_insert_row_trigger BEFORE INSERT ON tp_x FOR EACH ROW EXECUTE FUNCTION trigger_function('t'), ON TABLE t diff --git a/src/test/regress/sql/constraints.sql b/src/test/regress/sql/constraints.sql index 9705962eb9f..33e9e8a8ece 100644 --- a/src/test/regress/sql/constraints.sql +++ b/src/test/regress/sql/constraints.sql @@ -734,9 +734,59 @@ create table notnull_tbl_fail (a int primary key, not null a no inherit); create table notnull_tbl_fail (a int, primary key(a), not null a no inherit); 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 not null no inherit); +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 not enforced); +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 not enforced); +create table notnull_tbl_fail (a serial, constraint foo not null a not enforced); +create table notnull_tbl_fail (a serial not null not enforced); +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 not enforced); +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 not enforced); +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 not enforced); +create table notnull_tbl_fail (a serial constraint foo not null constraint bar not null not enforced); + +alter table notnull_tbl1 add column c serial, add constraint notnull_tbl1_c_not_null not null c not enforced; -- error +alter table notnull_tbl1 add column c serial not null not enforced; -- error +alter table notnull_tbl1 add column c int generated always as identity not null not enforced; -- error +alter table notnull_tbl1 add column c int not null not enforced not null not enforced, alter column c add generated always as identity; -- error +alter table notnull_tbl1 add column c int not null not enforced enforced; -- error +alter table notnull_tbl1 add column c int not null not enforced null enforced; -- error +alter table notnull_tbl1 add column c int not null not enforced null; -- error 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, b int, CONSTRAINT nnb NOT NULL b NOT VALID); +SELECT conname, conrelid::regclass, convalidated, conenforced, pg_get_constraintdef(oid) +FROM pg_constraint +WHERE conrelid = 'ne_nn_tbl'::regclass; + +INSERT INTO ne_nn_tbl VALUES (NULL, 1); -- ok +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 + +ALTER TABLE ne_nn_tbl ADD CONSTRAINT nn_enforced1 NOT NULL x NOT ENFORCED; -- error +ALTER TABLE ne_nn_tbl ADD CONSTRAINT nn NOT NULL x NOT ENFORCED; -- no-op +ALTER TABLE ne_nn_tbl ADD CONSTRAINT nn NOT NULL x NOT VALID NOT ENFORCED; -- no-op, because NOT ENFORCED imply NOT VALID. +ALTER TABLE ne_nn_tbl ADD CONSTRAINT nn NOT NULL x NOT ENFORCED NO INHERIT; -- error +ALTER TABLE ne_nn_tbl ADD CONSTRAINT nn NOT NULL x NOT VALID; -- error, because NOT VALID alone imply ENFORCED +ALTER TABLE ne_nn_tbl ADD CONSTRAINT nn NOT NULL x ENFORCED; -- error +ALTER TABLE ne_nn_tbl ADD CONSTRAINT nn NOT NULL x; -- error +ALTER TABLE ne_nn_tbl ALTER COLUMN x SET NOT NULL; -- error +\d+ ne_nn_tbl + +TRUNCATE ne_nn_tbl; +-- error, cannot use not enforced not-null constaint for primary key +ALTER TABLE ne_nn_tbl ADD PRIMARY KEY(x); +ALTER TABLE ne_nn_tbl ALTER CONSTRAINT nn NO INHERIT; -- ok +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); @@ -1071,6 +1121,38 @@ DEALLOCATE get_nnconstraint_info; -- end NOT NULL NOT VALID +-- Verify NOT NULL ENFORCED NOT ENFORCED +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 cannot 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 nn_enforced_tbl1 (a int, b int, CONSTRAINT nn0 NOT NULL a NOT ENFORCED) PARTITION BY LIST (a); +CREATE TABLE nn_enforced_tbl1_1 PARTITION OF nn_enforced_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 nn_enforced_tbl1_2(a int, CONSTRAINT nn1 NOT NULL a, b int); +ALTER TABLE nn_enforced_tbl1 ATTACH PARTITION nn_enforced_tbl1_2 FOR VALUES IN (3,4); -- ok +CREATE TABLE nn_enforced_tbl1_3(a int, b int, CONSTRAINT nn2 NOT NULL a NOT ENFORCED); +ALTER TABLE nn_enforced_tbl1 ATTACH PARTITION nn_enforced_tbl1_3 FOR VALUES IN (NULL,5); +CREATE TABLE nn_enforced_tbl1_4(a int, b int); +ALTER TABLE nn_enforced_tbl1 ATTACH PARTITION nn_enforced_tbl1_4 FOR VALUES IN (6); -- error +DROP TABLE nn_enforced_tbl1_4; + +EXECUTE get_nnconstraint_info('{nn_enforced_tbl1, nn_enforced_tbl1_1, nn_enforced_tbl1_2, nn_enforced_tbl1_3}'); +ALTER TABLE nn_enforced_tbl1 ALTER COLUMN a SET NOT NULL; -- error, cannot validate not-enforced +ALTER TABLE nn_enforced_tbl1 VALIDATE CONSTRAINT nn0; -- error, cannot validate not-enforced +-- nn_enforced_tbl1 is used for pg_upgrade tests + +DEALLOCATE get_nnconstraint_info; +-- end NOT NULL NOT ENFORCED -- Comments -- Setup a low-level role to enforce non-superuser checks. diff --git a/src/test/regress/sql/create_table_like.sql b/src/test/regress/sql/create_table_like.sql index d52a93ef131..d2813cef5e3 100644 --- a/src/test/regress/sql/create_table_like.sql +++ b/src/test/regress/sql/create_table_like.sql @@ -102,9 +102,24 @@ ALTER TABLE test_like_6 DROP COLUMN c; CREATE TABLE test_like_6c (LIKE test_like_6 INCLUDING ALL); \d+ test_like_6c +-- not null not enforced constraint +ALTER TABLE test_like_6c ADD COLUMN x INT, ADD CONSTRAINT nn_notenforced NOT NULL x NOT ENFORCED; +COMMENT ON CONSTRAINT nn_notenforced ON test_like_6c is 'not null not enforced constraint comment test'; +CREATE TABLE test_like_6c_copy(LIKE test_like_6c INCLUDING CONSTRAINTS INCLUDING COMMENTS); + +SELECT pg_get_constraintdef(oid) FROM pg_constraint +WHERE conrelid = 'test_like_6c_copy'::regclass +AND conname = 'nn_notenforced'; + +SELECT conname, description +FROM pg_description, pg_constraint c +WHERE classoid = 'pg_constraint'::regclass +AND objoid = c.oid AND c.conrelid = 'test_like_6c_copy'::regclass +ORDER BY conname COLLATE "C"; + DROP TABLE test_like_4, test_like_4a, test_like_4b, test_like_4c, test_like_4d; DROP TABLE test_like_5, test_like_5x, test_like_5c; -DROP TABLE test_like_6, test_like_6c; +DROP TABLE test_like_6, test_like_6c, test_like_6c_copy; CREATE TABLE inhg (x text, LIKE inhx INCLUDING INDEXES, y text); /* copies indexes */ INSERT INTO inhg VALUES (5, 10); diff --git a/src/test/regress/sql/inherit.sql b/src/test/regress/sql/inherit.sql index 072fca13c13..6d4aa6b758d 100644 --- a/src/test/regress/sql/inherit.sql +++ b/src/test/regress/sql/inherit.sql @@ -622,6 +622,98 @@ 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 +create table p1_c2(f1 int not null not enforced) inherits(p1_c1, p1); -- error +create table p1_c2(f1 int not null not enforced) inherits(p1_c1); -- ok. produce not enforced +create table p1_c3(f1 int not null enforced) inherits(p1_c1, p1); -- ok. produce enforced +create table p1_c4(f1 int ) inherits(p1_c1, p1, p1_c2, p1_c3); -- ok, produce enforced +select conrelid::regclass::text, coninhcount, conenforced +from pg_constraint +where conrelid::regclass::text in ('p1', 'p1_c1', 'p1_c2', 'p1_c3', 'p1_c4') and contype = 'n'; +drop table if exists p1, p1_c1, p1_c2, p1_c3, p1_c4 cascade; + +create table p1(f1 int); +create table p1_c1() inherits(p1); +create table p1_c2() inherits(p1, p1_c1); + +alter table p1 add constraint p1_nn_1 not null f1 not enforced; +alter table p1 alter column f1 drop not null; -- all not enforced not-null constraint will be droped +select 1 as expect_zero_row +from pg_constraint +where conrelid::regclass::text in ('p1', 'p1_c1', 'p1_c2', 'p1_c3', 'p1_c4') and contype = 'n'; +alter table p1 add constraint p1_nn_1 not null f1 not enforced; +alter table p1 add constraint p1_nn_1x not null f1 not enforced; -- error, already exists +alter table p1 add constraint p1_nn_1 not null f1 enforced; -- error, column f1 already have not enforced +alter table p1 add constraint p1_nn_1 not null f1 not enforced; +alter table p1_c1 add constraint p1_nn_1 not null f1 enforced; -- error +alter table p1_c1 add constraint p1_nn_1 not null f1 not enforced; -- ok + +-- not allowed: child is not enforced, parent is enforced +alter table p1 alter column f1 drop not null; +alter table p1_c1 alter column f1 drop not null; +alter table p1_c1 add constraint nn_x not null f1 not enforced; +alter table p1 add constraint nn_x not null f1 enforced; -- error + +alter table p1_c1 alter column f1 drop not null; +alter table p1_c1 add constraint nn_v not null f1 not valid enforced; +alter table p1 add constraint nn_v not null f1 not enforced; -- error +drop table p1 cascade; + +create table p1_nn(f1 int constraint p1_nn_a_nn not null not enforced); +create table p1_nn_c3(f1 int); +alter table p1_nn_c3 inherit p1_nn; -- error, because p1_nn_c3 does not have not-null constraint +drop table p1_nn_c3; +create table p1_nn_c1(f1 int constraint p1_nn_c1_a_nn not null); +alter table p1_nn_c1 inherit p1_nn; -- ok: parent not-null is not enforced while child is enforced +create table p1_nn_c2() inherits(p1_nn, p1_nn_c1); -- merged multiple not-null constraints produce an enforced one +\d+ p1_nn_c2 + +create table p1_nn_c4(f1 int not null not enforced) inherits(p1_nn, p1_nn_c1); -- error, parent (p1_nn_c1) have enforced +-- merged multiple not-null constraints produce an enforced one, below two will be success. +create table p1_nn_c4(f1 int not null) inherits(p1_nn, p1_nn_c1); +create table p1_nn_c5(f1 int) inherits(p1_nn, p1_nn_c1, p1_nn_c4); + +select conrelid::regclass, conname, conenforced, convalidated, coninhcount +from pg_constraint +where conrelid::regclass::text = ANY ('{p1_nn, p1_nn_c1, p1_nn_c2, p1_nn_c4, p1_nn_c5}') and contype = 'n' +order by conname, conrelid::regclass::text collate "C"; +-- p1_nn etc is used for pg_upgrade tests, so don't drop it + +-- Test ALTER CONSTRAINT INHERIT for not enforced not null +create table inh_nn1 (f1 int, constraint nn not null f1 not enforced no inherit); +create table inh_nn2 (f2 text, f3 int) inherits (inh_nn1); +create table inh_nn3 (f1 int) inherits (inh_nn1, inh_nn2); +create table inh_nn4 (f1 int) inherits (inh_nn1, inh_nn2, inh_nn3); +alter table inh_nn2 add constraint nn2 not null f1; +alter table inh_nn1 alter constraint nn inherit; + +create view constraint_info AS +select conrelid::regclass, conname, conkey[1], conenforced, convalidated, coninhcount, connoinherit, conislocal +from pg_constraint +where conrelid::regclass::text IN ('inh_nn1', 'inh_nn2', 'inh_nn3', 'inh_nn4') +and contype = 'n' +order by conname, conrelid::regclass::text collate "C"; + +select * from constraint_info; +drop table inh_nn1 cascade; +create table inh_nn1 (f1 int, constraint nn not null f1 no inherit); +create table inh_nn2 (f2 text, f3 int) inherits (inh_nn1); +alter table inh_nn2 add constraint nn2 not null f1 not enforced; +create table inh_nn3 (f1 int) inherits (inh_nn1, inh_nn2); +select * from constraint_info; + +-- error, parent not-null is enforcecd, child not-null cannot be not enforced +alter table inh_nn1 alter constraint nn inherit; +drop table inh_nn1 cascade; + -- -- Test DROP behavior of multiply-defined CHECK constraints -- diff --git a/src/test/regress/sql/partition_merge.sql b/src/test/regress/sql/partition_merge.sql index 80dc365b0ce..44d2d0f01b8 100644 --- a/src/test/regress/sql/partition_merge.sql +++ b/src/test/regress/sql/partition_merge.sql @@ -669,6 +669,7 @@ ALTER TABLE t ADD CONSTRAINT t_b_check CHECK (b > 0); ALTER TABLE t ADD CONSTRAINT t_b_check1 CHECK (b > 0) NOT ENFORCED; ALTER TABLE t ADD CONSTRAINT t_b_check2 CHECK (b > 0) NOT VALID; ALTER TABLE t ADD CONSTRAINT t_b_nn NOT NULL b NOT VALID; +ALTER TABLE t ADD CONSTRAINT t_d_nn NOT NULL d NOT ENFORCED; INSERT INTO tp_0_1(i, t, b) VALUES(0, DEFAULT, 1); INSERT INTO tp_1_2(i, t, b) VALUES(1, DEFAULT, 2); diff --git a/src/test/regress/sql/partition_split.sql b/src/test/regress/sql/partition_split.sql index ffd15e7f969..a03969eb940 100644 --- a/src/test/regress/sql/partition_split.sql +++ b/src/test/regress/sql/partition_split.sql @@ -1133,6 +1133,7 @@ ALTER TABLE t ADD CONSTRAINT t_b_check CHECK (b > 0); ALTER TABLE t ADD CONSTRAINT t_b_check1 CHECK (b > 0) NOT ENFORCED; ALTER TABLE t ADD CONSTRAINT t_b_check2 CHECK (b > 0) NOT VALID; ALTER TABLE t ADD CONSTRAINT t_b_nn NOT NULL b NOT VALID; +ALTER TABLE t ADD CONSTRAINT t_t_nn NOT NULL d NOT ENFORCED; INSERT INTO tp_x(i, t, b) VALUES(0, DEFAULT, 1); INSERT INTO tp_x(i, t, b) VALUES(1, DEFAULT, 2); -- 2.34.1
