hi.
I played around with it.
current syntax, we don't need to deal with column constraint grammar.
like the following can fail directly:
create table t0(a int constraint nn not null a not valid);
we only support table constraint cases like:
alter table lp add constraint nc1 not null a not valid;
since CREATE TABLE with invalid constraint does not make sense, so
we can just issue a warning. like:
create table t0(a int, constraint nn not null a not valid);
WARNING: CREATE TABLE NOT NULL NOT VALID CONSTRAINT WILL SET TO VALID
the wording needs to change...
for not null not valid syntax, we only need to support:
ALTER TABLE ADD CONSTRAINT conname NOT NULL column_name NOT VALID
ALTER TABLE ADD NOT NULL column_name NOT VALID
ALTER TABLE VALIDATE CONSTRAINT conname
The attached is what I came up with:
--------------------------------------------------------------------
ALTER TABLE ADD CONSTRAINT conname NOT NULL column_name NOT VALID
will create an invalidated check constraint. like
ALTER TABLE ADD CONSTRAINT conname CHECK (column_name IS NOT NULL) NOT VALID
when you validate the not-null constraint (internally it's a check constraint)
it will drop the check constraint and install a not-null constraint
with the same name.
drop a check constraint, it will call RemoveConstraintById.
within RemoveConstraintById it will lock pg_constraint.conrelid in
AccessExclusiveLock mode,
which is not ideal, because
ALTER TABLE VALIDATE CONSTRAINT only needs ShareUpdateExclusiveLock.
so we have to find a way to release that AccessExclusiveLock.
because we have converted a not-null constraint to a check constraint,
we need to somehow distinguish this case,
so pg_constraint adds another column: coninternaltype.
(the naming is not good, i guess)
because we dropped a invalid check constraint, but the inherited
constraint cannot be dropped.
so this ALTER TABLE VALIDATE CONSTRAINT will not work for partitions,
but it will work for the root partitioned table. (same logic for table
inheritance).
----------------------------------
demo:
create table t(a int);
alter table t add constraint nc1 not null a not valid;
\d t
Table "public.t"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
a | integer | | |
Check constraints:
"nc1" CHECK (a IS NOT NULL) NOT VALID
insert into t default values;
ERROR: new row for relation "t" violates check constraint "nc1"
DETAIL: Failing row contains (null).
alter table t validate constraint nc1;
\d+ t
Table "public.t"
Column | Type | Collation | Nullable | Default | Storage |
Compression | Stats target | Description
--------+---------+-----------+----------+---------+---------+-------------+--------------+-------------
a | integer | | not null | | plain |
| |
Not-null constraints:
"nc1" NOT NULL "a"
Access method: heap
-------------------------------------------------------------------
some regress tests added.
need more polishing, but overall it works as the above described.
not sure if this idea is crazy or not,
what do you think?
From 5a66a8da2a8c5fef88d7730e127900e5b83e0cde Mon Sep 17 00:00:00 2001
From: jian he <jian.universal...@gmail.com>
Date: Sun, 16 Mar 2025 20:50:53 +0800
Subject: [PATCH v4 1/1] not null not valid implementation
---
src/backend/catalog/heap.c | 14 +-
src/backend/catalog/index.c | 1 +
src/backend/catalog/pg_constraint.c | 3 +
src/backend/commands/tablecmds.c | 168 +++++++++++++++++++++-
src/backend/commands/trigger.c | 1 +
src/backend/commands/typecmds.c | 2 +
src/backend/parser/gram.y | 5 +-
src/backend/parser/parse_utilcmd.c | 63 +++++++-
src/include/catalog/heap.h | 1 +
src/include/catalog/pg_constraint.h | 3 +
src/include/nodes/parsenodes.h | 1 +
src/test/regress/expected/constraints.out | 63 ++++++++
src/test/regress/sql/constraints.sql | 41 ++++++
13 files changed, 355 insertions(+), 11 deletions(-)
diff --git a/src/backend/catalog/heap.c b/src/backend/catalog/heap.c
index bd3554c0bfd..6dc0267bd5a 100644
--- a/src/backend/catalog/heap.c
+++ b/src/backend/catalog/heap.c
@@ -104,7 +104,7 @@ static ObjectAddress AddNewRelationType(const char *typeName,
static void RelationRemoveInheritance(Oid relid);
static Oid StoreRelCheck(Relation rel, const char *ccname, Node *expr,
bool is_enforced, bool is_validated, bool is_local,
- int16 inhcount, bool is_no_inherit, bool is_internal);
+ int16 inhcount, bool is_no_inherit, char coninternaltype, bool is_internal);
static void StoreConstraints(Relation rel, List *cooked_constraints,
bool is_internal);
static bool MergeWithExistingConstraint(Relation rel, const char *ccname, Node *expr,
@@ -2136,7 +2136,7 @@ SetAttrMissing(Oid relid, char *attname, char *value)
static Oid
StoreRelCheck(Relation rel, const char *ccname, Node *expr,
bool is_enforced, bool is_validated, bool is_local,
- int16 inhcount, bool is_no_inherit, bool is_internal)
+ int16 inhcount, bool is_no_inherit, char coninternaltype, bool is_internal)
{
char *ccbin;
List *varList;
@@ -2228,6 +2228,7 @@ StoreRelCheck(Relation rel, const char *ccname, Node *expr,
inhcount, /* coninhcount */
is_no_inherit, /* connoinherit */
false, /* conperiod */
+ coninternaltype, /* coninternaltype */
is_internal); /* internally constructed? */
pfree(ccbin);
@@ -2282,6 +2283,7 @@ StoreRelNotNull(Relation rel, const char *nnname, AttrNumber attnum,
inhcount,
is_no_inherit,
false,
+ '\0', /* coninternaltype */
false);
return constrOid;
}
@@ -2327,7 +2329,9 @@ StoreConstraints(Relation rel, List *cooked_constraints, bool is_internal)
StoreRelCheck(rel, con->name, con->expr,
con->is_enforced, !con->skip_validation,
con->is_local, con->inhcount,
- con->is_no_inherit, is_internal);
+ con->is_no_inherit,
+ con->coninternaltype,
+ is_internal);
numchecks++;
break;
@@ -2460,6 +2464,7 @@ AddRelationNewConstraints(Relation rel,
cooked->is_local = is_local;
cooked->inhcount = is_local ? 0 : 1;
cooked->is_no_inherit = false;
+ cooked->coninternaltype = '\0';
cookedConstraints = lappend(cookedConstraints, cooked);
}
@@ -2579,6 +2584,7 @@ AddRelationNewConstraints(Relation rel,
StoreRelCheck(rel, ccname, expr, cdef->is_enforced,
cdef->initially_valid, is_local,
is_local ? 0 : 1, cdef->is_no_inherit,
+ cdef->contype_internal,
is_internal);
numchecks++;
@@ -2594,6 +2600,7 @@ AddRelationNewConstraints(Relation rel,
cooked->is_local = is_local;
cooked->inhcount = is_local ? 0 : 1;
cooked->is_no_inherit = cdef->is_no_inherit;
+ cooked->coninternaltype = cdef->contype_internal;
cookedConstraints = lappend(cookedConstraints, cooked);
}
else if (cdef->contype == CONSTR_NOTNULL)
@@ -2670,6 +2677,7 @@ AddRelationNewConstraints(Relation rel,
nncooked->is_local = is_local;
nncooked->inhcount = inhcount;
nncooked->is_no_inherit = cdef->is_no_inherit;
+ nncooked->coninternaltype = '\0';
cookedConstraints = lappend(cookedConstraints, nncooked);
}
diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c
index 739a92bdcc1..e5bc679a850 100644
--- a/src/backend/catalog/index.c
+++ b/src/backend/catalog/index.c
@@ -1986,6 +1986,7 @@ index_constraint_create(Relation heapRelation,
inhcount,
noinherit,
is_without_overlaps,
+ '\0', /* coninternaltype */
is_internal);
/*
diff --git a/src/backend/catalog/pg_constraint.c b/src/backend/catalog/pg_constraint.c
index ac80652baf2..3f3a276e3aa 100644
--- a/src/backend/catalog/pg_constraint.c
+++ b/src/backend/catalog/pg_constraint.c
@@ -80,6 +80,7 @@ CreateConstraintEntry(const char *constraintName,
int16 conInhCount,
bool conNoInherit,
bool conPeriod,
+ char coninternaltype,
bool is_internal)
{
Relation conDesc;
@@ -202,6 +203,7 @@ CreateConstraintEntry(const char *constraintName,
values[Anum_pg_constraint_coninhcount - 1] = Int16GetDatum(conInhCount);
values[Anum_pg_constraint_connoinherit - 1] = BoolGetDatum(conNoInherit);
values[Anum_pg_constraint_conperiod - 1] = BoolGetDatum(conPeriod);
+ values[Anum_pg_constraint_coninternaltype - 1] = CharGetDatum(coninternaltype);
if (conkeyArray)
values[Anum_pg_constraint_conkey - 1] = PointerGetDatum(conkeyArray);
@@ -834,6 +836,7 @@ RelationGetNotNullConstraints(Oid relid, bool cooked, bool include_noinh)
cooked->is_local = true;
cooked->inhcount = 0;
cooked->is_no_inherit = conForm->connoinherit;
+ cooked->coninternaltype = '\0';
notnulls = lappend(notnulls, cooked);
}
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 129c97fdf28..b3df5b913a5 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -604,6 +604,9 @@ static void GetForeignKeyCheckTriggers(Relation trigrel,
static void ATExecDropConstraint(Relation rel, const char *constrName,
DropBehavior behavior, bool recurse,
bool missing_ok, LOCKMODE lockmode);
+static void DropConstraintByOid(Relation rel, Oid constroid,
+ DropBehavior behavior, bool recurse,
+ bool missing_ok, LOCKMODE lockmode);
static ObjectAddress dropconstraint_internal(Relation rel,
HeapTuple constraintTup, DropBehavior behavior,
bool recurse, bool recursing,
@@ -991,6 +994,7 @@ DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId,
cooked->is_local = true; /* not used for defaults */
cooked->inhcount = 0; /* ditto */
cooked->is_no_inherit = false;
+ cooked->coninternaltype = '\0';
cookedDefaults = lappend(cookedDefaults, cooked);
}
}
@@ -10581,6 +10585,7 @@ addFkConstraint(addFkConstraintSides fkside,
coninhcount, /* inhcount */
connoinherit, /* conNoInherit */
with_period, /* conPeriod */
+ '\0', /* coninternaltype */
is_internal); /* is_internal */
ObjectAddressSet(address, ConstraintRelationId, constrOid);
@@ -12337,6 +12342,9 @@ ATExecValidateConstraint(List **wqueue, Relation rel, char *constrName,
HeapTuple tuple;
Form_pg_constraint con;
ObjectAddress address;
+ Oid constroid = InvalidOid;
+ AttrNumber attnum = -1;
+ char *conname = NULL;
conrel = table_open(ConstraintRelationId, RowExclusiveLock);
@@ -12367,7 +12375,8 @@ ATExecValidateConstraint(List **wqueue, Relation rel, char *constrName,
con = (Form_pg_constraint) GETSTRUCT(tuple);
if (con->contype != CONSTRAINT_FOREIGN &&
- con->contype != CONSTRAINT_CHECK)
+ con->contype != CONSTRAINT_CHECK &&
+ con->contype != CONSTRAINT_NOTNULL)
ereport(ERROR,
(errcode(ERRCODE_WRONG_OBJECT_TYPE),
errmsg("constraint \"%s\" of relation \"%s\" is not a foreign key or check constraint",
@@ -12383,14 +12392,30 @@ ATExecValidateConstraint(List **wqueue, Relation rel, char *constrName,
if (con->contype == CONSTRAINT_FOREIGN)
{
QueueFKConstraintValidation(wqueue, conrel, rel, tuple, lockmode);
+ ObjectAddressSet(address, ConstraintRelationId, con->oid);
}
- else if (con->contype == CONSTRAINT_CHECK)
+ else if (con->contype == CONSTRAINT_CHECK && con->coninternaltype == '\0')
{
QueueCheckConstraintValidation(wqueue, conrel, rel, constrName,
tuple, recurse, recursing, lockmode);
+ ObjectAddressSet(address, ConstraintRelationId, con->oid);
+ }
+ else if (con->contype == CONSTRAINT_CHECK && con->coninternaltype == CONSTRAINT_NOTNULL)
+ {
+ Datum adatum;
+ ArrayType *arr;
+ adatum = SysCacheGetAttrNotNull(CONSTROID, tuple,
+ Anum_pg_constraint_conkey);
+ arr = DatumGetArrayTypeP(adatum); /* ensure not toasted */
+ if (ARR_NDIM(arr) != 1 ||
+ ARR_DIMS(arr)[0] != 1 ||
+ ARR_HASNULL(arr) ||
+ ARR_ELEMTYPE(arr) != INT2OID)
+ elog(ERROR, "confkey is not a 1-D smallint array");
+ memcpy(&attnum, ARR_DATA_PTR(arr), 1 * sizeof(int16));
+ constroid = con->oid;
+ conname = pstrdup(NameStr(con->conname));
}
-
- ObjectAddressSet(address, ConstraintRelationId, con->oid);
}
else
address = InvalidObjectAddress; /* already validated */
@@ -12399,6 +12424,80 @@ ATExecValidateConstraint(List **wqueue, Relation rel, char *constrName,
table_close(conrel, RowExclusiveLock);
+ if (OidIsValid(constroid))
+ {
+ AlteredTableInfo *tab;
+ Constraint *n;
+ String *val;
+ List *children;
+
+ if (rel->rd_rel->relispartition)
+ {
+ char *relname;
+ List *ancestors = NIL;
+ Oid rootrelid = InvalidOid;
+
+ ancestors = get_partition_ancestors(RelationGetRelid(rel));
+ Assert(ancestors != NIL);
+
+ rootrelid = llast_oid(ancestors);
+ list_free(ancestors);
+
+ relname = get_rel_name(rootrelid);
+ ereport(ERROR,
+ errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot validate not-null constraint from partition \"%s\"",
+ RelationGetRelationName(rel)),
+ errhint("You might need validate not-null constraint through root partition \"%s\"", relname));
+ }
+ else if (has_superclass(RelationGetRelid(rel)))
+ {
+ ereport(ERROR,
+ errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot validate not-null constraint on inheritance children \"%s\"",
+ RelationGetRelationName(rel)));
+ }
+
+ children = find_all_inheritors(RelationGetRelid(rel), lockmode, NULL);
+ DropConstraintByOid(rel, constroid,
+ DROP_CASCADE, true,
+ false, lockmode);
+
+ tab = ATGetQueueEntry(wqueue, rel);
+ n = makeNode(Constraint);
+ n->contype = CONSTR_NOTNULL;
+ n->conname = conname;
+ n->location = -1;
+ n->is_no_inherit = false;
+ n->raw_expr = NULL;
+ n->is_enforced = true;
+ n->initially_valid = true;
+ n->skip_validation = false;
+ val = makeString(get_attname(RelationGetRelid(rel), attnum,
+ false));
+ n->keys = list_make1(val);
+ ATAddCheckNNConstraint(wqueue, tab, rel, n,
+ recurse, recursing, false, lockmode);
+
+ /*
+ * DropConstraintByOid will call RemoveConstraintById.
+ * Inside RemoveConstraintById, the relation to which this constraint belongs
+ * will be locked in AccessExclusiveLock mode.
+ *
+ * ATAddCheckNNConstraint will install not-null constraint for rel and it's children.
+ * ALTER TABLE VALIDATE CONSTRAINT generally take ShareUpdateExclusiveLock lock.
+ * removing the logical equivalent CHECK constraint will not impact the data integrity.
+ * thus here release the AccessExclusiveLock on rel and its children.
+ */
+ foreach_oid(childrelid, children)
+ UnlockRelationOid(childrelid, AccessExclusiveLock);
+
+ if (CheckRelationLockedByMe(rel, AccessExclusiveLock, true))
+ elog(ERROR, "relation \"%s\" was lock in AccessExclusiveLock", RelationGetRelationName(rel));
+
+ address = InvalidObjectAddress;
+ }
+
return address;
}
@@ -13298,6 +13397,67 @@ createForeignKeyCheckTriggers(Oid myRelOid, Oid refRelOid,
parentUpdTrigger, false);
}
+
+
+/*
+ * DROP CONSTRAINT by Oid
+ *
+ * Like DROP COLUMN, we can't use the normal ALTER TABLE recursion mechanism.
+ * The constroid is the OID of the constraint to be dropped. If the constraint
+ * has any inherited child constraints, it will recursively drop them too.
+ */
+static void
+DropConstraintByOid(Relation rel, Oid constroid,
+ DropBehavior behavior, bool recurse,
+ bool missing_ok, LOCKMODE lockmode)
+{
+ Relation conrel;
+ SysScanDesc scan;
+ ScanKeyData skey[1];
+ HeapTuple tuple;
+ bool found = false;
+ char *constrName = NULL;
+ Form_pg_constraint con;
+
+ conrel = table_open(ConstraintRelationId, RowExclusiveLock);
+ /*
+ * Find and drop the target constraint
+ */
+ ScanKeyInit(&skey[0],
+ Anum_pg_constraint_oid,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(constroid));
+ scan = systable_beginscan(conrel, ConstraintOidIndexId,
+ true, NULL, 1, skey);
+
+ /* There can be at most one matching row */
+ if (HeapTupleIsValid(tuple = systable_getnext(scan)))
+ {
+ con = (Form_pg_constraint) GETSTRUCT(tuple);
+ constrName = pstrdup(NameStr(con->conname));
+ dropconstraint_internal(rel, tuple, behavior, recurse, false,
+ missing_ok, lockmode);
+ found = true;
+ }
+
+ systable_endscan(scan);
+
+ if (!found)
+ {
+ if (!missing_ok)
+ ereport(ERROR,
+ errcode(ERRCODE_UNDEFINED_OBJECT),
+ errmsg("constraint \"%s\" of relation \"%s\" does not exist",
+ constrName, RelationGetRelationName(rel)));
+ else
+ ereport(NOTICE,
+ errmsg("constraint \"%s\" of relation \"%s\" does not exist, skipping",
+ constrName, RelationGetRelationName(rel)));
+ }
+
+ table_close(conrel, RowExclusiveLock);
+}
+
/*
* ALTER TABLE DROP CONSTRAINT
*
diff --git a/src/backend/commands/trigger.c b/src/backend/commands/trigger.c
index c9f61130c69..e99b91d3840 100644
--- a/src/backend/commands/trigger.c
+++ b/src/backend/commands/trigger.c
@@ -839,6 +839,7 @@ CreateTriggerFiringOn(CreateTrigStmt *stmt, const char *queryString,
0, /* inhcount */
true, /* noinherit */
false, /* conperiod */
+ '\0', /* coninternaltype */
isInternal); /* is_internal */
}
diff --git a/src/backend/commands/typecmds.c b/src/backend/commands/typecmds.c
index 3cb3ca1cca1..457d47b7ba4 100644
--- a/src/backend/commands/typecmds.c
+++ b/src/backend/commands/typecmds.c
@@ -3606,6 +3606,7 @@ domainAddCheckConstraint(Oid domainOid, Oid domainNamespace, Oid baseTypeOid,
0, /* inhcount */
false, /* connoinherit */
false, /* conperiod */
+ '\0', /* coninternaltype */
false); /* is_internal */
if (constrAddr)
ObjectAddressSet(*constrAddr, ConstraintRelationId, ccoid);
@@ -3714,6 +3715,7 @@ domainAddNotNullConstraint(Oid domainOid, Oid domainNamespace, Oid baseTypeOid,
0, /* inhcount */
false, /* connoinherit */
false, /* conperiod */
+ '\0', /* coninternaltype */
false); /* is_internal */
if (constrAddr)
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 271ae26cbaf..0af53b6fbf3 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -4222,11 +4222,10 @@ ConstraintElem:
n->contype = CONSTR_NOTNULL;
n->location = @1;
n->keys = list_make1(makeString($3));
- /* no NOT VALID support yet */
processCASbits($4, @4, "NOT NULL",
- NULL, NULL, NULL, NULL,
+ NULL, NULL, NULL, &n->skip_validation,
&n->is_no_inherit, yyscanner);
- n->initially_valid = true;
+ n->initially_valid = !n->skip_validation;
$$ = (Node *) n;
}
| UNIQUE opt_unique_null_treatment '(' columnList opt_without_overlaps ')' opt_c_include opt_definition OptConsTableSpace
diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c
index abbe1bb45a3..d5fb9b70c9b 100644
--- a/src/backend/parser/parse_utilcmd.c
+++ b/src/backend/parser/parse_utilcmd.c
@@ -1090,7 +1090,68 @@ transformTableConstraint(CreateStmtContext *cxt, Constraint *constraint)
errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("not-null constraints on partitioned tables cannot be NO INHERIT"));
- cxt->nnconstraints = lappend(cxt->nnconstraints, constraint);
+ if (constraint->skip_validation && (strcmp(cxt->stmtType, "ALTER TABLE") == 0))
+ {
+ Constraint *constr;
+ NullTest *nnulltest;
+ AttrNumber colnum;
+ TupleDesc tupdesc;
+ Form_pg_attribute attr;
+
+ constr = makeNode(Constraint);
+ constr->contype = CONSTR_CHECK;
+ if (constraint->conname)
+ constr->conname = pstrdup(constraint->conname);
+ constr->deferrable = false;
+ constr->initdeferred = false;
+ constr->is_enforced = true;
+ constr->skip_validation = true;
+ constr->initially_valid = false;
+ constr->is_no_inherit = false;
+ constr->nulls_not_distinct = false;
+ constr->location = constraint->location;
+ constr->contype_internal = CONSTRAINT_NOTNULL;
+
+ colnum = get_attnum(RelationGetRelid(cxt->rel), strVal(linitial(constraint->keys)));
+ if (colnum == InvalidAttrNumber)
+ ereport(ERROR,
+ errcode(ERRCODE_UNDEFINED_COLUMN),
+ errmsg("column \"%s\" of relation \"%s\" does not exist",
+ strVal(linitial(constraint->keys)), RelationGetRelationName(cxt->rel)));
+ if (colnum < InvalidAttrNumber)
+ ereport(ERROR,
+ errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot add not-null constraint on system column \"%s\"",
+ strVal(linitial(constraint->keys))));
+
+ tupdesc = RelationGetDescr(cxt->rel);
+ attr = TupleDescAttr(tupdesc, colnum - 1);
+
+ nnulltest = makeNode(NullTest);
+ nnulltest->arg = (Expr *) makeVar(1,
+ attr->attnum,
+ attr->atttypid,
+ attr->atttypmod,
+ attr->attcollation,
+ 0);
+
+ nnulltest->nulltesttype = IS_NOT_NULL;
+ nnulltest->argisrow = false;
+ nnulltest->location = constraint->location;
+ constr->cooked_expr = nodeToString(nnulltest);
+
+ cxt->ckconstraints = lappend(cxt->ckconstraints, constr);
+ }
+ else if (constraint->skip_validation && (strcmp(cxt->stmtType, "CREATE TABLE") == 0))
+ {
+ constraint->skip_validation = false;
+ constraint->initially_valid = true;
+ ereport(WARNING,
+ errmsg("CREATE TABLE NOT NULL NOT VALID CONSTRAINT WILL SET TO VALID"));
+ cxt->nnconstraints = lappend(cxt->nnconstraints, constraint);
+ }
+ else
+ cxt->nnconstraints = lappend(cxt->nnconstraints, constraint);
break;
case CONSTR_FOREIGN:
diff --git a/src/include/catalog/heap.h b/src/include/catalog/heap.h
index dbd339e9df4..70b61b12464 100644
--- a/src/include/catalog/heap.h
+++ b/src/include/catalog/heap.h
@@ -42,6 +42,7 @@ typedef struct CookedConstraint
Node *expr; /* transformed default or check expr */
bool is_enforced; /* is enforced? (only for CHECK) */
bool skip_validation; /* skip validation? (only for CHECK) */
+ char coninternaltype; /* (only for NOT NULL) */
bool is_local; /* constraint has local (non-inherited) def */
int16 inhcount; /* number of times constraint is inherited */
bool is_no_inherit; /* constraint has local def and cannot be
diff --git a/src/include/catalog/pg_constraint.h b/src/include/catalog/pg_constraint.h
index 6da164e7e4d..5740a277d8f 100644
--- a/src/include/catalog/pg_constraint.h
+++ b/src/include/catalog/pg_constraint.h
@@ -114,6 +114,8 @@ CATALOG(pg_constraint,2606,ConstraintRelationId)
*/
bool conperiod;
+ char coninternaltype; /* constraint internal type; see codes below */
+
#ifdef CATALOG_VARLEN /* variable-length fields start here */
/*
@@ -250,6 +252,7 @@ extern Oid CreateConstraintEntry(const char *constraintName,
int16 conInhCount,
bool conNoInherit,
bool conPeriod,
+ char coninternaltype,
bool is_internal);
extern bool ConstraintNameIsUsed(ConstraintCategory conCat, Oid objId,
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 23c9e3c5abf..01a72589084 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -2824,6 +2824,7 @@ typedef struct Constraint
* untransformed parse tree */
char *cooked_expr; /* CHECK or DEFAULT expression, as
* nodeToString representation */
+ char contype_internal; /**/
char generated_when; /* ALWAYS or BY DEFAULT */
char generated_kind; /* STORED or VIRTUAL */
bool nulls_not_distinct; /* null treatment for UNIQUE constraints */
diff --git a/src/test/regress/expected/constraints.out b/src/test/regress/expected/constraints.out
index 4f39100fcdf..cdba544bb8e 100644
--- a/src/test/regress/expected/constraints.out
+++ b/src/test/regress/expected/constraints.out
@@ -1392,3 +1392,66 @@ DROP TABLE constraint_comments_tbl;
DROP DOMAIN constraint_comments_dom;
DROP ROLE regress_constraint_comments;
DROP ROLE regress_constraint_comments_noaccess;
+-- test cases for not null not valid and validate it.
+create table t0_ok(a int, constraint nn not null a not valid);
+WARNING: CREATE TABLE NOT NULL NOT VALID CONSTRAINT WILL SET TO VALID
+create table t_notnull_notvalid(a int);
+insert into t_notnull_notvalid values(null);
+alter table t_notnull_notvalid add constraint nc1 not null a not valid;
+\d t_notnull_notvalid
+ Table "public.t_notnull_notvalid"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+---------
+ a | integer | | |
+Check constraints:
+ "nc1" CHECK (a IS NOT NULL) NOT VALID
+
+alter table t_notnull_notvalid validate constraint nc1; --fail.
+ERROR: column "a" of relation "t_notnull_notvalid" contains null values
+truncate t_notnull_notvalid;
+insert into t_notnull_notvalid values(1);
+alter table t_notnull_notvalid validate constraint nc1; --ok
+\d+ t_notnull_notvalid
+ Table "public.t_notnull_notvalid"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+---------+--------------+-------------
+ a | integer | | not null | | plain | |
+Not-null constraints:
+ "nc1" NOT NULL "a"
+
+create table lp (a int) partition by list (a);
+create table lp_default partition of lp default;
+create table lp_ef partition of lp for values in (1,2,3,4);
+create table lp_g partition of lp for values in (5,6,7,8);
+create table lp_null partition of lp for values in (null);
+alter table lp add constraint nc1 not null a not valid;
+insert into lp select g from generate_Series(2, 9) g;
+insert into lp_null select NULL; --fail
+ERROR: new row for relation "lp_null" violates check constraint "nc1"
+DETAIL: Failing row contains (null).
+alter table lp_default validate constraint nc1; --fail
+ERROR: cannot validate not-null constraint from partition "lp_default"
+HINT: You might need validate not-null constraint through root partition "lp"
+alter table lp_g validate constraint nc1; --fail
+ERROR: cannot validate not-null constraint from partition "lp_g"
+HINT: You might need validate not-null constraint through root partition "lp"
+alter table lp validate constraint nc1; --ok.
+alter table lp validate constraint nc1; --ok.
+\d lp
+ Partitioned table "public.lp"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+---------
+ a | integer | | not null |
+Partition key: LIST (a)
+Number of partitions: 4 (Use \d+ to list them.)
+
+\d lp_default
+ Table "public.lp_default"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+---------
+ a | integer | | not null |
+Partition of: lp DEFAULT
+
+drop table t0_ok;
+drop table lp;
+drop table t_notnull_notvalid;
diff --git a/src/test/regress/sql/constraints.sql b/src/test/regress/sql/constraints.sql
index 21ce4177de4..06c3895e745 100644
--- a/src/test/regress/sql/constraints.sql
+++ b/src/test/regress/sql/constraints.sql
@@ -836,3 +836,44 @@ DROP DOMAIN constraint_comments_dom;
DROP ROLE regress_constraint_comments;
DROP ROLE regress_constraint_comments_noaccess;
+
+
+-- test cases for not null not valid and validate it.
+create table t0_ok(a int, constraint nn not null a not valid);
+
+create table t_notnull_notvalid(a int);
+insert into t_notnull_notvalid values(null);
+alter table t_notnull_notvalid add constraint nc1 not null a not valid;
+\d t_notnull_notvalid
+alter table t_notnull_notvalid validate constraint nc1; --fail.
+
+truncate t_notnull_notvalid;
+insert into t_notnull_notvalid values(1);
+alter table t_notnull_notvalid validate constraint nc1; --ok
+\d+ t_notnull_notvalid
+
+
+create table lp (a int) partition by list (a);
+create table lp_default partition of lp default;
+create table lp_ef partition of lp for values in (1,2,3,4);
+create table lp_g partition of lp for values in (5,6,7,8);
+create table lp_null partition of lp for values in (null);
+alter table lp add constraint nc1 not null a not valid;
+insert into lp select g from generate_Series(2, 9) g;
+
+insert into lp_null select NULL; --fail
+alter table lp_default validate constraint nc1; --fail
+alter table lp_g validate constraint nc1; --fail
+
+alter table lp validate constraint nc1; --ok.
+alter table lp validate constraint nc1; --ok.
+
+\d lp
+\d lp_default
+
+
+
+drop table t0_ok;
+drop table lp;
+drop table t_notnull_notvalid;
+
--
2.34.1
