On Mon, Feb 23, 2026 at 11:50 PM Álvaro Herrera <[email protected]> wrote:
>
> But what is NOT VALID NOT ENFORCED? Unless I'm confused, a constraint
> can be in one of three cases,
>
> 1. the normal one (valid and enforced), which is dumped together with
> the table,
> 2. the NOT ENFORCED one, for which there's no validity marking (these
> are all in practice invalid, but we don't spell that out because it's
> pointless to do so), which I argue should also be dumped together
> with the table,
> 3. NOT VALID but enforced, which is dumped separately, because trying to
> dump it together with the table just won't work.
>
> I understand that you want to treat 2 the same as 3, because it saves a
> dozen of pg_dump lines of code. I think it's more user friendly to
> treat 2 the same as 1, and that the dozen lines of code are worth it.
>
Hi.
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....
--
jian
https://www.enterprisedb.com/
From e5b51db39c1b11166ec9ffd3e352357e663e49a0 Mon Sep 17 00:00:00 2001
From: jian he <[email protected]>
Date: Tue, 3 Mar 2026 16:02:19 +0800
Subject: [PATCH v4 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, 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, raise an error.
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 | 67 +++++--
src/backend/catalog/pg_constraint.c | 49 ++++--
src/backend/catalog/sql_features.txt | 2 +-
src/backend/commands/tablecmds.c | 129 ++++++++++----
src/backend/parser/gram.y | 2 +-
src/backend/parser/parse_utilcmd.c | 87 ++++++---
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 | 32 ++++
src/bin/psql/describe.c | 20 ++-
src/include/access/tupdesc.h | 2 +-
src/include/catalog/heap.h | 2 +-
src/include/catalog/pg_constraint.h | 3 +-
src/test/regress/expected/constraints.out | 165 ++++++++++++++++++
.../regress/expected/create_table_like.out | 22 +++
src/test/regress/expected/inherit.out | 126 +++++++++++++
src/test/regress/sql/constraints.sql | 90 ++++++++++
src/test/regress/sql/create_table_like.sql | 12 ++
src/test/regress/sql/inherit.sql | 70 ++++++++
23 files changed, 831 insertions(+), 108 deletions(-)
diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index e7067c84ece..0cba925e68b 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -1263,7 +1263,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 aab2c6eb19f..3e9bc4fb53a 100644
--- a/doc/src/sgml/ref/alter_table.sgml
+++ b/doc/src/sgml/ref/alter_table.sgml
@@ -1688,8 +1688,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 982532fe725..947b107e216 100644
--- a/doc/src/sgml/ref/create_table.sgml
+++ b/doc/src/sgml/ref/create_table.sgml
@@ -1426,8 +1426,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 5748aa9a1a9..4d54c300034 100644
--- a/src/backend/catalog/heap.c
+++ b/src/backend/catalog/heap.c
@@ -2253,7 +2253,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;
@@ -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,17 +2627,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;
/*
@@ -2668,7 +2670,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;
@@ -2676,7 +2679,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;
@@ -2952,7 +2955,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);)
{
@@ -2968,6 +2971,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.
@@ -3013,6 +3022,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);
}
@@ -3047,11 +3067,15 @@ 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);
+ StoreRelNotNull(rel, conname, attnum,
+ !constr->skip_validation,
+ true, inhcount, constr->is_no_inherit,
+ constr->is_enforced);
+
+ if (constr->is_enforced)
+ nncols = lappend_int(nncols, attnum);
}
/*
@@ -3096,6 +3120,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 constraint.
+ */
+ 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
@@ -3126,10 +3162,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 ? true : false,
+ 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 3a8ad201607..73620edf04e 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 b04b0dbd2a0..8d03fce97fa 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -506,7 +506,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);
@@ -2759,13 +2760,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++)
@@ -5433,7 +5437,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);
@@ -7776,6 +7780,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,
@@ -7804,13 +7810,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,
@@ -7849,8 +7848,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
+ {
+ /* Skip if no NOT NULL constraint exists (including NOT ENFORCED). */
+ 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,
@@ -7947,10 +7955,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;
@@ -7985,7 +7997,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))
{
@@ -8002,6 +8014,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
@@ -8021,7 +8040,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
@@ -8082,6 +8101,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),
@@ -8093,7 +8115,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.
@@ -8112,7 +8135,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);
}
}
@@ -9621,7 +9644,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),
@@ -9631,6 +9654,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."));
}
/*
@@ -10000,9 +10034,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,
!constr->skip_validation);
@@ -12700,7 +12734,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);
@@ -17568,9 +17604,31 @@ 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);
+
+ Assert(parent_con->conenforced);
+ 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,
@@ -17822,13 +17880,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 c567252acc4..cda99ca22c2 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -4339,7 +4339,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 cc244c49e9e..a1bef78e875 100644
--- a/src/backend/parser/parse_utilcmd.c
+++ b/src/backend/parser/parse_utilcmd.c
@@ -320,6 +320,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? */
@@ -600,6 +604,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);
@@ -700,6 +705,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... */
@@ -716,8 +722,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)
{
@@ -726,6 +736,7 @@ transformColumnDefinition(CreateStmtContext *cxt, ColumnDef *column)
case CONSTR_IDENTITY:
case CONSTR_PRIMARY:
disallow_noinherit_notnull = true;
+ disallow_notenforced_notnull = true;
break;
default:
break;
@@ -776,6 +787,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.
@@ -795,6 +812,15 @@ transformColumnDefinition(CreateStmtContext *cxt, ColumnDef *column)
constraint->keys = list_make1(makeString(column->colname));
notnull_constraint = constraint;
+
+ /*
+ * 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;
+
cxt->nnconstraints = lappend(cxt->nnconstraints, constraint);
}
else if (notnull_constraint)
@@ -1134,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);
@@ -1275,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);
}
}
}
@@ -4310,7 +4345,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"),
@@ -4327,7 +4363,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 6b634c9fff1..b9a72a892b3 100644
--- a/src/backend/utils/cache/relcache.c
+++ b/src/backend/utils/cache/relcache.c
@@ -4618,8 +4618,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
@@ -4627,7 +4627,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 6df79067db5..f3262da75d5 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);
@@ -9264,6 +9265,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;
@@ -9355,12 +9357,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
@@ -9374,9 +9377,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");
@@ -9391,6 +9394,11 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables)
" ELSE false\n"
"END AS notnull_islocal,\n");
+ if (fout->remoteVersion >= 190000)
+ 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");
@@ -9478,6 +9486,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");
@@ -9550,6 +9559,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;
@@ -9584,6 +9594,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) ?
@@ -10032,10 +10043,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 >= 190000)
+ 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
@@ -17377,6 +17394,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 */
@@ -17424,6 +17444,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 6deceef23f3..79426b761a1 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 ! */
bool *notnull_noinh; /* NOT NULL is NO INHERIT */
bool *notnull_islocal; /* true if NOT NULL has local definition */
+ bool *notnull_enforced; /* true if 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 f15bd06adcc..aa3c05fa41d 100644
--- a/src/bin/pg_dump/t/002_pg_dump.pl
+++ b/src/bin/pg_dump/t/002_pg_dump.pl
@@ -968,6 +968,38 @@ 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 col1 NOT ENFORCED)\E$
+ /xm,
+ like => {
+ %full_runs, %dump_test_schema_runs, section_pre_data => 1,
+ },
+ unlike => {
+ exclude_dump_test_schema => 1,
+ only_dump_measurement => 1,
+ binary_upgrade => 1,
+ },
+ },
+
+ # This constraint is valid therefore it goes in SECTION_PRE_DATA
+ 'COMMENT ON CONSTRAINT ON test_table_chld2' => {
+ 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 ab13c90ed33..6174de7b3bd 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -3142,7 +3142,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"
@@ -3166,15 +3173,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 d46cdbf7a3c..abc877e9856 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 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 a6fa9cacb72..324b88411c1 100644
--- a/src/test/regress/expected/constraints.out
+++ b/src/test/regress/expected/constraints.out
@@ -969,33 +969,139 @@ 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 notnull_tbl1_c_not_null 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; -- 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 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 | |
+Not-null constraints:
+ "nn" NOT NULL "x" NOT ENFORCED
+
+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 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);
@@ -1680,6 +1786,65 @@ 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
+-- Create table with NOT NULL NOT ENFORCED constraint, for pg_upgrade.
+CREATE TABLE nn_notenforced (a int, b int, CONSTRAINT nn NOT NULL a NOT ENFORCED);
+INSERT INTO nn_notenforced VALUES (NULL, 1);
+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_child 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 | t | 0 | f
+(1 row)
+
+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 d3c35c14847..013ff7984ea 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 0490a746555..d822b2dcaf0 100644
--- a/src/test/regress/expected/inherit.out
+++ b/src/test/regress/expected/inherit.out
@@ -1442,6 +1442,132 @@ 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 = 'p1_c2'::regclass and contype = 'n';
+ conenforced
+-------------
+ f
+(1 row)
+
+drop table if exists p1, p1_c1, p1_c2;
+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_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_c1" to enforced
+HINT: You might need to ensure the existing constraint is enforced.
+-- not allowed: child is not enforced, parent is 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
+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; -- it's ok for 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);
+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 | 2
+ 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)
+
+-- nn_enforced_tbl1 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;
+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 b7f6efdd814..73b10cb178a 100644
--- a/src/test/regress/sql/constraints.sql
+++ b/src/test/regress/sql/constraints.sql
@@ -668,22 +668,71 @@ 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 notnull_tbl1_c_not_null 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; -- 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 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 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);
@@ -1015,6 +1064,47 @@ 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
+
+-- Create table with NOT NULL NOT ENFORCED constraint, for pg_upgrade.
+CREATE TABLE nn_notenforced (a int, b int, CONSTRAINT nn NOT NULL a NOT ENFORCED);
+INSERT INTO nn_notenforced VALUES (NULL, 1);
+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_child ADD CONSTRAINT nn NOT NULL a NOT ENFORCED;
+EXECUTE get_nnconstraint_info('{notenforced_nn_parent, notenforced_nn_child}');
+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 93389b57dbf..d0d860045c2 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..4d2bad8d190 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 = 'p1_c2'::regclass and contype = 'n';
+drop table if exists p1, p1_c1, p1_c2;
+
+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_nn_1 not null f1 enforced; -- error, column f1 already have not enforced
+
+-- not allowed: child is not enforced, parent is 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;
+
+
+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; -- it's ok for 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);
+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";
+-- nn_enforced_tbl1 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;
+
+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
