On Thu, Sep 4, 2025 at 8:00 PM Álvaro Herrera <[email protected]> wrote:
>
> On 2025-Sep-04, jian he wrote:
>
> > @@ -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
>
> Hmmm, are you sure about this? I think if a table has two parents, one
> with enforced and the other with not enforced constraint, then it's okay
> to get them combined resulting in one enforced constraint.
>
changed accordingly.
When a column can inherit multiple not-null constraints. If one is not enforced,
another one is enforced then we will install an enforced one.
> > @@ -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"));
>
> I think the hint here should suggest to make the existing constraint as
> enforced, rather than drop it.
>
The hint also changed.
+ /*
+ * If the ENFORCED status we're asked for doesn't match what the
+ * existing constraint has, 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."));
+ }
>
> > + 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.
> > + */
>
> Umm, wouldn't it make more sense to modify ATExecSetNotNull() so that it
> does what we want? This seems hackish.
>
modified ATExecSetNotNull for ATExecAlterConstrInheritability usage.
now ATExecSetNotNull is
ATExecSetNotNull(List **wqueue, Relation rel, char *conName, char *colName,
bool recurse, bool recursing, bool
is_enforced, LOCKMODE lockmode)
new patch attached with the pg_dump TAP tests.
currently NOT VALID NOT NULL dumped
constraint separately, NOT NULL NOT ENFORCED constraints can also be dumped
separately.
CREATE TABLE tx3 (x int not null not enforced);
can be dumped as:
CREATE TABLE public.tx3 (x integer);
ALTER TABLE public.tx3 ADD CONSTRAINT tx3_x_not_null NOT NULL x NOT ENFORCED;
---------------
note: currently not enforced check constraint is dumped separately.
CREATE TABLE tx2 (x int check (x > 1) not enforced);
will be dumped as
CREATE TABLE public.tx2 (x integer);
ALTER TABLE public.tx2
ADD CONSTRAINT tx2_x_check CHECK ((x > 1)) NOT ENFORCED;
From e34fd68c4acf925fb241ce8d842ef66b91ad4795 Mon Sep 17 00:00:00 2001
From: jian he <[email protected]>
Date: Wed, 24 Sep 2025 14:00:11 +0800
Subject: [PATCH v2 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/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 | 62 +++++--
src/backend/catalog/pg_constraint.c | 50 ++++--
src/backend/catalog/sql_features.txt | 2 +-
src/backend/commands/tablecmds.c | 121 ++++++++++---
src/backend/parser/gram.y | 2 +-
src/backend/parser/parse_utilcmd.c | 80 ++++++---
src/backend/utils/cache/relcache.c | 4 +-
src/bin/pg_dump/pg_dump.h | 1 +
src/bin/pg_dump/t/002_pg_dump.pl | 31 ++++
src/bin/psql/describe.c | 20 ++-
src/include/access/tupdesc.h | 2 +-
src/include/catalog/heap.h | 2 +-
src/include/catalog/pg_constraint.h | 4 +-
src/test/regress/expected/constraints.out | 163 ++++++++++++++++++
.../regress/expected/create_table_like.out | 22 +++
src/test/regress/expected/inherit.out | 130 ++++++++++++++
src/test/regress/sql/constraints.sql | 90 ++++++++++
src/test/regress/sql/create_table_like.sql | 12 ++
src/test/regress/sql/inherit.sql | 70 ++++++++
22 files changed, 782 insertions(+), 96 deletions(-)
diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index e9095bedf21..d1efb96fe0a 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -1260,7 +1260,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 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..1eccbbb5f03 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, 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,17 @@ AddRelationNotNullConstraints(Relation rel, List *constraints,
conname = other->name;
inhcount++;
+
+ /*
+ * A column can inherit multiple not-null constraints. If one is
+ * not enforced, another one is enforced then we will install an
+ * 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
@@ -3123,10 +3156,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..fe8a9ba7912 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
@@ -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).
*
@@ -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,26 @@ 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)
+ {
+ 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,
@@ -807,6 +828,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 +875,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 +895,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 3be2e051d32..b1416d55b68 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -506,6 +506,7 @@ 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,
+ bool is_enforced,
LOCKMODE lockmode);
static bool NotNullImpliedByRelConstraints(Relation rel, Form_pg_attribute attr);
static bool ConstraintImpliedByRelConstraint(Relation scanrel,
@@ -2744,13 +2745,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++)
@@ -5407,7 +5411,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, true, lockmode);
break;
case AT_SetExpression:
address = ATExecSetExpression(tab, rel, cmd->name, cmd->def, lockmode);
@@ -7730,6 +7734,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,
@@ -7758,13 +7763,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,
@@ -7803,8 +7801,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,
@@ -7901,10 +7908,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.
+ *
+ * If is_enforced is false, the added NOT NULL constraint is 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, bool is_enforced, LOCKMODE lockmode)
{
HeapTuple tuple;
AttrNumber attnum;
@@ -7939,7 +7950,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))
{
@@ -7956,6 +7967,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
@@ -7975,7 +7993,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
@@ -8036,6 +8054,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),
@@ -8047,7 +8068,8 @@ ATExecSetNotNull(List **wqueue, Relation rel, char *conName, char *colName,
RelationGetRelid(rel), attnum);
/* Mark pg_attribute.attnotnull for the column and queue validation */
- set_attnotnull(wqueue, rel, attnum, true, true);
+ if (ccon->is_enforced)
+ set_attnotnull(wqueue, rel, attnum, true, true);
/*
* Recurse to propagate the constraint to children that don't have one.
@@ -8066,7 +8088,7 @@ ATExecSetNotNull(List **wqueue, Relation rel, char *conName, char *colName,
CommandCounterIncrement();
ATExecSetNotNull(wqueue, childrel, conName, colName,
- recurse, true, lockmode);
+ recurse, true, is_enforced, lockmode);
table_close(childrel, NoLock);
}
}
@@ -9563,7 +9585,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),
@@ -9573,6 +9595,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."));
}
/*
@@ -9941,9 +9974,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 to 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);
@@ -12641,7 +12674,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,
+ currcon->conenforced,
+ lockmode);
if (OidIsValid(addr.objectId))
CommandCounterIncrement();
table_close(childrel, NoLock);
@@ -17509,9 +17544,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 an 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,
@@ -17763,13 +17817,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 9fd48acb1f8..ac779bb4d31 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -4259,7 +4259,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 e96b38a59d5..aad8f292ed9 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);
}
}
}
@@ -3991,7 +4021,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"),
@@ -4008,7 +4039,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..1b1ba4d4e5f 100644
--- a/src/backend/utils/cache/relcache.c
+++ b/src/backend/utils/cache/relcache.c
@@ -4623,7 +4623,7 @@ CheckNNConstraintFetch(Relation relation)
bool isnull;
/*
- * If this is a not-null constraint, then only look at it if it's
+ * 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
@@ -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/pg_dump/pg_dump.h b/src/bin/pg_dump/pg_dump.h
index bcc94ff07cc..0fbe0a42e65 100644
--- a/src/bin/pg_dump/pg_dump.h
+++ b/src/bin/pg_dump/pg_dump.h
@@ -375,6 +375,7 @@ typedef struct _tableInfo
char **notnull_comment; /* comment thereof */
bool *notnull_invalid; /* true for NOT NULL NOT VALID */
bool *notnull_noinh; /* NOT NULL is NO INHERIT */
+ bool *notnull_enforced; /* NOT NULL is ENFORCED */
bool *notnull_islocal; /* true if NOT NULL has local definition */
struct _attrDefInfo **attrdefs; /* DEFAULT expressions */
struct _constraintInfo *checkexprs; /* CHECK constraints */
diff --git a/src/bin/pg_dump/t/002_pg_dump.pl b/src/bin/pg_dump/t/002_pg_dump.pl
index fc5b9b52f80..375dd9b344f 100644
--- a/src/bin/pg_dump/t/002_pg_dump.pl
+++ b/src/bin/pg_dump/t/002_pg_dump.pl
@@ -1226,6 +1226,37 @@ 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/^
+ \QALTER TABLE dump_test.test_table_nn0\E \n^\s+
+ \QADD CONSTRAINT nn NOT NULL col1 NOT ENFORCED;\E
+ /xm,
+ like => {
+ %full_runs, %dump_test_schema_runs, section_post_data => 1,
+ },
+ unlike => {
+ exclude_dump_test_schema => 1,
+ only_dump_measurement => 1,
+ },
+ },
+
+ # This constraint is invalid (not enforced) therefore it goes in SECTION_POST_DATA
+ '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_post_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 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/access/tupdesc.h b/src/include/access/tupdesc.h
index a25b94ba423..a810159d4a3 100644
--- a/src/include/access/tupdesc.h
+++ b/src/include/access/tupdesc.h
@@ -42,7 +42,7 @@ 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 */
+ bool has_not_null; /* any enforced not-null, including not valid ones */
bool has_generated_stored;
bool has_generated_virtual;
} TupleConstr;
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..fdae98c58f3 100644
--- a/src/test/regress/expected/constraints.out
+++ b/src/test/regress/expected/constraints.out
@@ -961,33 +961,136 @@ 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 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.
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);
+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 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
+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 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_enforced 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_enforced NOT NULL x; --error, one column can only one not-null
+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 | |
+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 ensure the existing constraint is enforced.
+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 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 | |
+ x1 | integer | | | | plain | |
+ y | integer | | not null | | plain | |
+Not-null constraints:
+ "nn" NOT NULL "x" NO INHERIT 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 +1771,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 d3c35c14847..36afb096fde 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..4c555efd55d 100644
--- a/src/test/regress/expected/inherit.out
+++ b/src/test/regress/expected/inherit.out
@@ -1439,6 +1439,136 @@ 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); --ok
+NOTICE: merging column "f1" with inherited definition
+select conenforced from pg_constraint where conrelid::regclass::text = ANY ('{p1_c2}') and contype = 'n';
+ conenforced
+-------------
+ f
+(1 row)
+
+drop table if exists p1, p1_c1, p1_c2;
+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; --it's ok for parent not-null is not enforced while child is enforced
+create table p1_c2() inherits(p1, p1_c1); --merged multiple not-null constraints produce an enforced one
+NOTICE: merging multiple inherited definitions of column "f1"
+\d+ p1_c2
+ Table "public.p1_c2"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+---------+--------------+-------------
+ f1 | integer | | not null | | plain | |
+Not-null constraints:
+ "p1_a_nn" NOT NULL "f1" (inherited)
+Inherits: p1,
+ p1_c1
+
+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
+create table p1_c4(f1 int not null not enforced) inherits(p1, p1_c1); --error, parent have enforced not-null
+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_c4(f1 int not null) inherits(p1, p1_c1);
+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);
+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, p1_c1, p1_c2, p1_c4, p1_c5}') and contype = 'n'
+order by conname, conrelid::regclass::text collate "C";
+ conrelid | conname | conenforced | convalidated | coninhcount
+----------+-------------------+-------------+--------------+-------------
+ p1 | p1_a_nn | f | f | 0
+ p1_c2 | p1_a_nn | t | t | 2
+ p1_c5 | p1_a_nn | t | t | 2
+ p1_c1 | p1_c1_a_nn | t | t | 1
+ p1_c4 | p1_c4_f1_not_null | t | t | 2
+(5 rows)
+
+drop table if exists p1 cascade;
+NOTICE: drop cascades to 4 other objects
+DETAIL: drop cascades to table p1_c1
+drop cascades to table p1_c2
+drop cascades to table p1_c4
+drop cascades to table p1_c5
+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, column f1 already have not-enforced not-null
+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.
+-- 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_x not null f1 not enforced;
+alter table p1 add constraint nn 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 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 table p1_c1
+-- 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;
+select conrelid::regclass, conname, conkey[1], conenforced, convalidated, coninhcount, connoinherit, conislocal
+from pg_constraint
+where conrelid::regclass::text = ANY ('{inh_nn1, inh_nn2, inh_nn3, inh_nn4}')
+and contype = 'n'
+order by conname, conrelid::regclass::text collate "C";
+ 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;
+--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 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..27333168150 100644
--- a/src/test/regress/sql/constraints.sql
+++ b/src/test/regress/sql/constraints.sql
@@ -663,22 +663,69 @@ 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);
+
+SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conname = 'nn';
+INSERT INTO ne_nn_tbl VALUES (NULL); --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 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 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);
@@ -1008,6 +1055,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 93389b57dbf..59cf66edd5d 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..63f3d65cf12 100644
--- a/src/test/regress/sql/inherit.sql
+++ b/src/test/regress/sql/inherit.sql
@@ -527,6 +527,76 @@ 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); --ok
+select conenforced from pg_constraint where conrelid::regclass::text = ANY ('{p1_c2}') and contype = 'n';
+drop table if exists p1, p1_c1, p1_c2;
+
+
+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; --it's ok for parent not-null is not enforced while child is enforced
+create table p1_c2() inherits(p1, p1_c1); --merged multiple not-null constraints produce an enforced one
+\d+ p1_c2
+create table p1_c3(f1 int);
+alter table p1_c3 inherit p1; --error
+create table p1_c4(f1 int not null not enforced) inherits(p1, p1_c1); --error, parent have enforced not-null
+--merged multiple not-null constraints produce an enforced one, below two will be success.
+create table p1_c4(f1 int not null) inherits(p1, p1_c1);
+create table p1_c5(f1 int) inherits(p1, p1_c1);
+select conrelid::regclass, conname, conenforced, convalidated, coninhcount
+from pg_constraint
+where conrelid::regclass::text = ANY ('{p1, p1_c1, p1_c2, p1_c4, p1_c5}') and contype = 'n'
+order by conname, conrelid::regclass::text collate "C";
+drop table if exists p1 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, column f1 already have not-enforced not-null
+
+-- 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_x 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_v not null f1 not valid enforced;
+alter table p1 add constraint nn not null f1 not enforced; --error
+drop table p1 cascade;
+
+
+-- 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;
+
+select conrelid::regclass, conname, conkey[1], conenforced, convalidated, coninhcount, connoinherit, conislocal
+from pg_constraint
+where conrelid::regclass::text = ANY ('{inh_nn1, inh_nn2, inh_nn3, inh_nn4}')
+and contype = 'n'
+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);
+create table inh_nn2 (f2 text, f3 int) inherits (inh_nn1);
+alter table inh_nn2 add constraint nn2 not null f1 not enforced;
+--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
--
--
2.34.1
