aboutsummaryrefslogtreecommitdiff
path: root/src
diff options
context:
space:
mode:
Diffstat (limited to 'src')
-rw-r--r--src/backend/access/common/tupdesc.c38
-rw-r--r--src/backend/catalog/heap.c9
-rw-r--r--src/backend/catalog/pg_constraint.c46
-rw-r--r--src/backend/commands/tablecmds.c271
-rw-r--r--src/backend/jit/llvm/llvmjit_deform.c10
-rw-r--r--src/backend/optimizer/util/plancat.c16
-rw-r--r--src/backend/parser/gram.y5
-rw-r--r--src/backend/utils/cache/relcache.c73
-rw-r--r--src/bin/pg_dump/pg_dump.c190
-rw-r--r--src/bin/pg_dump/pg_dump.h2
-rw-r--r--src/bin/pg_dump/t/002_pg_dump.pl21
-rw-r--r--src/bin/psql/describe.c9
-rw-r--r--src/include/access/tupdesc.h10
-rw-r--r--src/include/catalog/pg_attribute.h4
-rw-r--r--src/include/catalog/pg_constraint.h2
-rw-r--r--src/test/regress/expected/alter_table.out69
-rw-r--r--src/test/regress/expected/constraints.out252
-rw-r--r--src/test/regress/sql/alter_table.sql14
-rw-r--r--src/test/regress/sql/constraints.sql169
19 files changed, 1103 insertions, 107 deletions
diff --git a/src/backend/access/common/tupdesc.c b/src/backend/access/common/tupdesc.c
index ed2195f14b2..ffd0c78f905 100644
--- a/src/backend/access/common/tupdesc.c
+++ b/src/backend/access/common/tupdesc.c
@@ -22,6 +22,7 @@
#include "access/htup_details.h"
#include "access/toast_compression.h"
#include "access/tupdesc_details.h"
+#include "catalog/catalog.h"
#include "catalog/pg_collation.h"
#include "catalog/pg_type.h"
#include "common/hashfn.h"
@@ -74,7 +75,16 @@ populate_compact_attribute_internal(Form_pg_attribute src,
dst->atthasmissing = src->atthasmissing;
dst->attisdropped = src->attisdropped;
dst->attgenerated = (src->attgenerated != '\0');
- dst->attnotnull = src->attnotnull;
+
+ /*
+ * Assign nullability status for this column. Assuming that a constraint
+ * exists, at this point we don't know if a not-null constraint is valid,
+ * so we assign UNKNOWN unless the table is a catalog, in which case we
+ * know it's valid.
+ */
+ dst->attnullability = !src->attnotnull ? ATTNULLABLE_UNRESTRICTED :
+ IsCatalogRelationOid(src->attrelid) ? ATTNULLABLE_VALID :
+ ATTNULLABLE_UNKNOWN;
switch (src->attalign)
{
@@ -144,9 +154,10 @@ verify_compact_attribute(TupleDesc tupdesc, int attnum)
/*
* Make the attcacheoff match since it's been reset to -1 by
- * populate_compact_attribute_internal.
+ * populate_compact_attribute_internal. Same with attnullability.
*/
tmp.attcacheoff = cattr->attcacheoff;
+ tmp.attnullability = cattr->attnullability;
/* Check the freshly populated CompactAttribute matches the TupleDesc's */
Assert(memcmp(&tmp, cattr, sizeof(CompactAttribute)) == 0);
@@ -333,8 +344,13 @@ CreateTupleDescCopyConstr(TupleDesc tupdesc)
desc->natts * sizeof(FormData_pg_attribute));
for (i = 0; i < desc->natts; i++)
+ {
populate_compact_attribute(desc, i);
+ TupleDescCompactAttr(desc, i)->attnullability =
+ TupleDescCompactAttr(tupdesc, i)->attnullability;
+ }
+
/* Copy the TupleConstr data structure, if any */
if (constr)
{
@@ -613,6 +629,24 @@ equalTupleDescs(TupleDesc tupdesc1, TupleDesc tupdesc2)
return false;
if (attr1->attnotnull != attr2->attnotnull)
return false;
+
+ /*
+ * When the column has a not-null constraint, we also need to consider
+ * its validity aspect, which only manifests in CompactAttribute->
+ * attnullability, so verify that.
+ */
+ if (attr1->attnotnull)
+ {
+ CompactAttribute *cattr1 = TupleDescCompactAttr(tupdesc1, i);
+ CompactAttribute *cattr2 = TupleDescCompactAttr(tupdesc2, i);
+
+ Assert(cattr1->attnullability != ATTNULLABLE_UNKNOWN);
+ Assert((cattr1->attnullability == ATTNULLABLE_UNKNOWN) ==
+ (cattr2->attnullability == ATTNULLABLE_UNKNOWN));
+
+ if (cattr1->attnullability != cattr2->attnullability)
+ return false;
+ }
if (attr1->atthasdef != attr2->atthasdef)
return false;
if (attr1->attidentity != attr2->attidentity)
diff --git a/src/backend/catalog/heap.c b/src/backend/catalog/heap.c
index b807ab66668..fbaed5359ad 100644
--- a/src/backend/catalog/heap.c
+++ b/src/backend/catalog/heap.c
@@ -2616,12 +2616,17 @@ AddRelationNewConstraints(Relation rel,
errmsg("cannot add not-null constraint on system column \"%s\"",
strVal(linitial(cdef->keys))));
+ Assert(cdef->initially_valid != cdef->skip_validation);
+
/*
* If the column already has a not-null constraint, we don't want
- * to add another one; just adjust inheritance status as needed.
+ * to add another one; adjust inheritance status as needed. This
+ * also checks whether the existing constraint matches the
+ * requested validity.
*/
if (AdjustNotNullInheritance(RelationGetRelid(rel), colnum,
- is_local, cdef->is_no_inherit))
+ is_local, cdef->is_no_inherit,
+ cdef->skip_validation))
continue;
/*
diff --git a/src/backend/catalog/pg_constraint.c b/src/backend/catalog/pg_constraint.c
index b97960d2766..2f73085961b 100644
--- a/src/backend/catalog/pg_constraint.c
+++ b/src/backend/catalog/pg_constraint.c
@@ -576,8 +576,8 @@ ChooseConstraintName(const char *name1, const char *name2,
/*
* Find and return a copy of the pg_constraint tuple that implements a
- * validated not-null constraint for the given column of the given relation.
- * If no such constraint exists, return NULL.
+ * (possibly not valid) 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
* of the constraint that implements the not-null constraint for that column.
@@ -606,13 +606,11 @@ findNotNullConstraintAttnum(Oid relid, AttrNumber attnum)
AttrNumber conkey;
/*
- * We're looking for a NOTNULL constraint that's marked validated,
- * with the column we're looking for as the sole element in conkey.
+ * We're looking for a NOTNULL constraint with the column we're
+ * looking for as the sole element in conkey.
*/
if (con->contype != CONSTRAINT_NOTNULL)
continue;
- if (!con->convalidated)
- continue;
conkey = extractNotNullColumn(conTup);
if (conkey != attnum)
@@ -630,9 +628,10 @@ findNotNullConstraintAttnum(Oid relid, AttrNumber attnum)
}
/*
- * Find and return the pg_constraint tuple that implements a validated
- * not-null constraint for the given column of the given relation. If
- * no such column or no such constraint exists, return NULL.
+ * 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.
+ * If no such column or no such constraint exists, return NULL.
*/
HeapTuple
findNotNullConstraint(Oid relid, const char *colname)
@@ -723,15 +722,19 @@ 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. Otherwise, we adjust
- * conislocal/coninhcount and return true.
- * In the latter case, if is_local is true we flip conislocal true, or do
- * nothing if it's already true; otherwise we increment coninhcount by 1.
+ * 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 everything checks out, we adjust conislocal/coninhcount and return
+ * true. If is_local is true we flip conislocal true, or do nothing if
+ * it's already true; otherwise we increment coninhcount by 1.
*/
bool
AdjustNotNullInheritance(Oid relid, AttrNumber attnum,
- bool is_local, bool is_no_inherit)
+ bool is_local, bool is_no_inherit, bool is_notvalid)
{
HeapTuple tup;
@@ -755,6 +758,17 @@ AdjustNotNullInheritance(Oid relid, AttrNumber attnum,
errmsg("cannot change NO INHERIT status of NOT NULL constraint \"%s\" on relation \"%s\"",
NameStr(conform->conname), get_rel_name(relid)));
+ /*
+ * Throw an error if the existing constraint is NOT VALID and caller
+ * wants a valid one.
+ */
+ if (!is_notvalid && !conform->convalidated)
+ ereport(ERROR,
+ errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
+ errmsg("incompatible NOT VALID constraint \"%s\" on relation \"%s\"",
+ NameStr(conform->conname), get_rel_name(relid)),
+ errhint("You will need to use ALTER TABLE ... VALIDATE CONSTRAINT to validate it."));
+
if (!is_local)
{
if (pg_add_s16_overflow(conform->coninhcount, 1,
@@ -832,7 +846,7 @@ RelationGetNotNullConstraints(Oid relid, bool cooked, bool include_noinh)
cooked->attnum = colnum;
cooked->expr = NULL;
cooked->is_enforced = true;
- cooked->skip_validation = false;
+ cooked->skip_validation = !conForm->convalidated;
cooked->is_local = true;
cooked->inhcount = 0;
cooked->is_no_inherit = conForm->connoinherit;
@@ -852,7 +866,7 @@ RelationGetNotNullConstraints(Oid relid, bool cooked, bool include_noinh)
constr->keys = list_make1(makeString(get_attname(relid, colnum,
false)));
constr->is_enforced = true;
- constr->skip_validation = false;
+ constr->skip_validation = !conForm->convalidated;
constr->initially_valid = true;
constr->is_no_inherit = conForm->connoinherit;
notnulls = lappend(notnulls, constr);
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 4397123398e..686f1850cab 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -435,6 +435,9 @@ static void QueueFKConstraintValidation(List **wqueue, Relation conrel, Relation
static void QueueCheckConstraintValidation(List **wqueue, Relation conrel, Relation rel,
char *constrName, HeapTuple contuple,
bool recurse, bool recursing, LOCKMODE lockmode);
+static void QueueNNConstraintValidation(List **wqueue, Relation conrel, Relation rel,
+ HeapTuple contuple, bool recurse, bool recursing,
+ LOCKMODE lockmode);
static int transformColumnNameList(Oid relId, List *colList,
int16 *attnums, Oid *atttypids, Oid *attcollids);
static int transformFkeyGetPrimaryKey(Relation pkrel, Oid *indexOid,
@@ -498,7 +501,7 @@ static void add_column_collation_dependency(Oid relid, int32 attnum, Oid collid)
static ObjectAddress ATExecDropNotNull(Relation rel, const char *colName, bool recurse,
LOCKMODE lockmode);
static void set_attnotnull(List **wqueue, Relation rel, AttrNumber attnum,
- LOCKMODE lockmode);
+ bool is_valid, bool queue_validation);
static ObjectAddress ATExecSetNotNull(List **wqueue, Relation rel,
char *constrname, char *colName,
bool recurse, bool recursing,
@@ -1340,7 +1343,7 @@ DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId,
nncols = AddRelationNotNullConstraints(rel, stmt->nnconstraints,
old_notnulls);
foreach_int(attrnum, nncols)
- set_attnotnull(NULL, rel, attrnum, NoLock);
+ set_attnotnull(NULL, rel, attrnum, true, false);
ObjectAddressSet(address, RelationRelationId, relationId);
@@ -2738,7 +2741,7 @@ MergeAttributes(List *columns, const List *supers, char relpersistence,
/*
* Request attnotnull on columns that have a not-null constraint
- * that's not marked NO INHERIT.
+ * that's not marked NO INHERIT (even if not valid).
*/
nnconstrs = RelationGetNotNullConstraints(RelationGetRelid(relation),
true, false);
@@ -6207,24 +6210,28 @@ ATRewriteTable(AlteredTableInfo *tab, Oid OIDNewHeap)
{
/*
* If we are rebuilding the tuples OR if we added any new but not
- * verified not-null constraints, check all not-null constraints. This
- * is a bit of overkill but it minimizes risk of bugs.
+ * verified not-null constraints, check all *valid* not-null
+ * constraints. This is a bit of overkill but it minimizes risk of
+ * bugs.
*
- * notnull_attrs does *not* collect attribute numbers for not-null
- * constraints over virtual generated columns; instead, they are
- * collected in notnull_virtual_attrs.
+ * notnull_attrs does *not* collect attribute numbers for valid
+ * not-null constraints over virtual generated columns; instead, they
+ * are collected in notnull_virtual_attrs for verification elsewhere.
*/
for (i = 0; i < newTupDesc->natts; i++)
{
- Form_pg_attribute attr = TupleDescAttr(newTupDesc, i);
+ CompactAttribute *attr = TupleDescCompactAttr(newTupDesc, i);
- if (attr->attnotnull && !attr->attisdropped)
+ if (attr->attnullability == ATTNULLABLE_VALID &&
+ !attr->attisdropped)
{
- if (attr->attgenerated != ATTRIBUTE_GENERATED_VIRTUAL)
- notnull_attrs = lappend_int(notnull_attrs, attr->attnum);
+ Form_pg_attribute wholeatt = TupleDescAttr(newTupDesc, i);
+
+ if (wholeatt->attgenerated != ATTRIBUTE_GENERATED_VIRTUAL)
+ notnull_attrs = lappend_int(notnull_attrs, wholeatt->attnum);
else
notnull_virtual_attrs = lappend_int(notnull_virtual_attrs,
- attr->attnum);
+ wholeatt->attnum);
}
}
if (notnull_attrs || notnull_virtual_attrs)
@@ -7809,18 +7816,23 @@ ATExecDropNotNull(Relation rel, const char *colName, bool recurse,
}
/*
- * Helper to set pg_attribute.attnotnull if it isn't set, and to tell phase 3
- * to verify it.
+ * set_attnotnull
+ * Helper to update/validate the pg_attribute status of a not-null
+ * constraint
*
- * When called to alter an existing table, 'wqueue' must be given so that we
- * can queue a check that existing tuples pass the constraint. When called
- * from table creation, 'wqueue' should be passed as NULL.
+ * pg_attribute.attnotnull is set true, if it isn't already.
+ * If queue_validation is true, also set up wqueue to validate the constraint.
+ * wqueue may be given as NULL when validation is not needed (e.g., on table
+ * creation).
*/
static void
set_attnotnull(List **wqueue, Relation rel, AttrNumber attnum,
- LOCKMODE lockmode)
+ bool is_valid, bool queue_validation)
{
Form_pg_attribute attr;
+ CompactAttribute *thisatt;
+
+ Assert(!queue_validation || wqueue);
CheckAlterTableIsSafe(rel);
@@ -7844,8 +7856,11 @@ set_attnotnull(List **wqueue, Relation rel, AttrNumber attnum,
elog(ERROR, "cache lookup failed for attribute %d of relation %u",
attnum, RelationGetRelid(rel));
+ thisatt = TupleDescCompactAttr(RelationGetDescr(rel), attnum - 1);
+ thisatt->attnullability = ATTNULLABLE_VALID;
+
attr = (Form_pg_attribute) GETSTRUCT(tuple);
- Assert(!attr->attnotnull);
+
attr->attnotnull = true;
CatalogTupleUpdate(attr_rel, &tuple->t_self, tuple);
@@ -7853,7 +7868,8 @@ set_attnotnull(List **wqueue, Relation rel, AttrNumber attnum,
* If the nullness isn't already proven by validated constraints, have
* ALTER TABLE phase 3 test for it.
*/
- if (wqueue && !NotNullImpliedByRelConstraints(rel, attr))
+ if (queue_validation && wqueue &&
+ !NotNullImpliedByRelConstraints(rel, attr))
{
AlteredTableInfo *tab;
@@ -7866,6 +7882,10 @@ set_attnotnull(List **wqueue, Relation rel, AttrNumber attnum,
table_close(attr_rel, RowExclusiveLock);
heap_freetuple(tuple);
}
+ else
+ {
+ CacheInvalidateRelcache(rel);
+ }
}
/*
@@ -7951,6 +7971,15 @@ ATExecSetNotNull(List **wqueue, Relation rel, char *conName, char *colName,
conForm->conislocal = true;
changed = true;
}
+ else if (!conForm->convalidated)
+ {
+ /*
+ * Flip attnotnull and convalidated, and also validate the
+ * constraint.
+ */
+ return ATExecValidateConstraint(wqueue, rel, NameStr(conForm->conname),
+ recurse, recursing, lockmode);
+ }
if (changed)
{
@@ -8013,8 +8042,8 @@ ATExecSetNotNull(List **wqueue, Relation rel, char *conName, char *colName,
InvokeObjectPostAlterHook(RelationRelationId,
RelationGetRelid(rel), attnum);
- /* Mark pg_attribute.attnotnull for the column */
- set_attnotnull(wqueue, rel, attnum, lockmode);
+ /* Mark pg_attribute.attnotnull for the column and queue validation */
+ set_attnotnull(wqueue, rel, attnum, true, true);
/*
* Recurse to propagate the constraint to children that don't have one.
@@ -9417,7 +9446,6 @@ ATPrepAddPrimaryKey(List **wqueue, Relation rel, AlterTableCmd *cmd,
bool recurse, LOCKMODE lockmode,
AlterTableUtilityContext *context)
{
- ListCell *lc;
Constraint *pkconstr;
pkconstr = castNode(Constraint, cmd->def);
@@ -9436,33 +9464,73 @@ ATPrepAddPrimaryKey(List **wqueue, Relation rel, AlterTableCmd *cmd,
lockmode);
foreach_oid(childrelid, children)
{
- foreach(lc, pkconstr->keys)
+ foreach_node(String, attname, pkconstr->keys)
{
HeapTuple tup;
Form_pg_attribute attrForm;
- char *attname = strVal(lfirst(lc));
- tup = SearchSysCacheAttName(childrelid, attname);
+ tup = SearchSysCacheAttName(childrelid, strVal(attname));
if (!tup)
elog(ERROR, "cache lookup failed for attribute %s of relation %u",
- attname, childrelid);
+ strVal(attname), childrelid);
attrForm = (Form_pg_attribute) GETSTRUCT(tup);
if (!attrForm->attnotnull)
ereport(ERROR,
errmsg("column \"%s\" of table \"%s\" is not marked NOT NULL",
- attname, get_rel_name(childrelid)));
+ strVal(attname), get_rel_name(childrelid)));
ReleaseSysCache(tup);
}
}
}
- /* Insert not-null constraints in the queue for the PK columns */
- foreach(lc, pkconstr->keys)
+ /* Verify that columns are not-null, or request that they be made so */
+ foreach_node(String, column, pkconstr->keys)
{
AlterTableCmd *newcmd;
Constraint *nnconstr;
+ HeapTuple tuple;
+
+ /*
+ * First check if a suitable constraint exists. If it does, we don't
+ * need to request another one. We do need to bail out if it's not
+ * valid, though.
+ */
+ tuple = findNotNullConstraint(RelationGetRelid(rel), strVal(column));
+ if (tuple != NULL)
+ {
+ Form_pg_constraint conForm = (Form_pg_constraint) GETSTRUCT(tuple);
+
+ /* a NO INHERIT constraint is no good */
+ if (conForm->connoinherit)
+ ereport(ERROR,
+ errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
+ errmsg("cannot create primary key on column \"%s\"",
+ strVal(column)),
+ /*- translator: third %s is a constraint characteristic such as NOT VALID */
+ errdetail("The constraint \"%s\" on column \"%s\", marked %s, is incompatible with a primary key.",
+ NameStr(conForm->conname), strVal(column), "NO INHERIT"),
+ errhint("You will need to make it inheritable using %s.",
+ "ALTER TABLE ... ALTER CONSTRAINT ... INHERIT"));
+
+ /* an unvalidated constraint is no good */
+ if (!conForm->convalidated)
+ ereport(ERROR,
+ errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
+ errmsg("cannot create primary key on column \"%s\"",
+ strVal(column)),
+ /*- translator: third %s is a constraint characteristic such as NOT VALID */
+ errdetail("The constraint \"%s\" on column \"%s\", marked %s, is incompatible with a primary key.",
+ NameStr(conForm->conname), strVal(column), "NOT VALID"),
+ errhint("You will need to validate it using %s.",
+ "ALTER TABLE ... VALIDATE CONSTRAINT"));
+
+ /* All good with this one; don't request another */
+ heap_freetuple(tuple);
+ continue;
+ }
- nnconstr = makeNotNullConstraint(lfirst(lc));
+ /* This column is not already not-null, so add it to the queue */
+ nnconstr = makeNotNullConstraint(column);
newcmd = makeNode(AlterTableCmd);
newcmd->subtype = AT_AddConstraint;
@@ -9836,11 +9904,15 @@ ATAddCheckNNConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel,
constr->conname = ccon->name;
/*
- * If adding a not-null constraint, set the pg_attribute flag and tell
- * phase 3 to verify existing rows, if needed.
+ * 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.
*/
if (constr->contype == CONSTR_NOTNULL)
- set_attnotnull(wqueue, rel, ccon->attnum, lockmode);
+ set_attnotnull(wqueue, rel, ccon->attnum,
+ !constr->skip_validation,
+ !constr->skip_validation);
ObjectAddressSet(address, ConstraintRelationId, ccon->conoid);
}
@@ -12811,11 +12883,12 @@ 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",
- constrName, RelationGetRelationName(rel))));
+ errcode(ERRCODE_WRONG_OBJECT_TYPE),
+ errmsg("constraint \"%s\" of relation \"%s\" is not a foreign key, check, or not-null constraint",
+ constrName, RelationGetRelationName(rel)));
if (!con->conenforced)
ereport(ERROR,
@@ -12833,6 +12906,11 @@ ATExecValidateConstraint(List **wqueue, Relation rel, char *constrName,
QueueCheckConstraintValidation(wqueue, conrel, rel, constrName,
tuple, recurse, recursing, lockmode);
}
+ else if (con->contype == CONSTRAINT_NOTNULL)
+ {
+ QueueNNConstraintValidation(wqueue, conrel, rel,
+ tuple, recurse, recursing, lockmode);
+ }
ObjectAddressSet(address, ConstraintRelationId, con->oid);
}
@@ -13050,6 +13128,109 @@ QueueCheckConstraintValidation(List **wqueue, Relation conrel, Relation rel,
}
/*
+ * QueueNNConstraintValidation
+ *
+ * Add an entry to the wqueue to validate the given not-null constraint in
+ * Phase 3 and update the convalidated field in the pg_constraint catalog for
+ * the specified relation and all its inheriting children.
+ */
+static void
+QueueNNConstraintValidation(List **wqueue, Relation conrel, Relation rel,
+ HeapTuple contuple, bool recurse, bool recursing,
+ LOCKMODE lockmode)
+{
+ Form_pg_constraint con;
+ AlteredTableInfo *tab;
+ HeapTuple copyTuple;
+ Form_pg_constraint copy_con;
+ List *children = NIL;
+ AttrNumber attnum;
+ char *colname;
+
+ con = (Form_pg_constraint) GETSTRUCT(contuple);
+ Assert(con->contype == CONSTRAINT_NOTNULL);
+
+ attnum = extractNotNullColumn(contuple);
+
+ /*
+ * If we're recursing, we've already done this for parent, so skip it.
+ * Also, if the constraint is a NO INHERIT constraint, we shouldn't try to
+ * look for it in the children.
+ *
+ * We recurse before validating on the parent, to reduce risk of
+ * deadlocks.
+ */
+ if (!recursing && !con->connoinherit)
+ children = find_all_inheritors(RelationGetRelid(rel), lockmode, NULL);
+
+ colname = get_attname(RelationGetRelid(rel), attnum, false);
+ foreach_oid(childoid, children)
+ {
+ Relation childrel;
+ HeapTuple contup;
+ Form_pg_constraint childcon;
+ char *conname;
+
+ if (childoid == RelationGetRelid(rel))
+ continue;
+
+ /*
+ * If we are told not to recurse, there had better not be any child
+ * tables, because we can't mark the constraint on the parent valid
+ * unless it is valid for all child tables.
+ */
+ if (!recurse)
+ ereport(ERROR,
+ errcode(ERRCODE_INVALID_TABLE_DEFINITION),
+ errmsg("constraint must be validated on child tables too"));
+
+ /*
+ * The column on child might have a different attnum, so search by
+ * column name.
+ */
+ contup = findNotNullConstraint(childoid, colname);
+ if (!contup)
+ elog(ERROR, "cache lookup failed for not-null constraint on column \"%s\" of relation \"%s\"",
+ colname, get_rel_name(childoid));
+ childcon = (Form_pg_constraint) GETSTRUCT(contup);
+ if (childcon->convalidated)
+ continue;
+
+ /* find_all_inheritors already got lock */
+ childrel = table_open(childoid, NoLock);
+ conname = pstrdup(NameStr(childcon->conname));
+
+ /* XXX improve ATExecValidateConstraint API to avoid double search */
+ ATExecValidateConstraint(wqueue, childrel, conname,
+ false, true, lockmode);
+ table_close(childrel, NoLock);
+ }
+
+ /* Set attnotnull appropriately without queueing another validation */
+ set_attnotnull(NULL, rel, attnum, true, false);
+
+ tab = ATGetQueueEntry(wqueue, rel);
+ tab->verify_new_notnull = true;
+
+ /*
+ * Invalidate relcache so that others see the new validated constraint.
+ */
+ CacheInvalidateRelcache(rel);
+
+ /*
+ * Now update the catalogs, while we have the door open.
+ */
+ copyTuple = heap_copytuple(contuple);
+ copy_con = (Form_pg_constraint) GETSTRUCT(copyTuple);
+ copy_con->convalidated = true;
+ CatalogTupleUpdate(conrel, &copyTuple->t_self, copyTuple);
+
+ InvokeObjectPostAlterHook(ConstraintRelationId, con->oid, 0);
+
+ heap_freetuple(copyTuple);
+}
+
+/*
* transformColumnNameList - transform list of column names
*
* Lookup each name and return its attnum and, optionally, type and collation
@@ -19770,17 +19951,19 @@ PartConstraintImpliedByRelConstraint(Relation scanrel,
for (i = 1; i <= natts; i++)
{
- Form_pg_attribute att = TupleDescAttr(scanrel->rd_att, i - 1);
+ CompactAttribute *att = TupleDescCompactAttr(scanrel->rd_att, i - 1);
- if (att->attnotnull && !att->attisdropped)
+ /* invalid not-null constraint must be ignored here */
+ if (att->attnullability == ATTNULLABLE_VALID && !att->attisdropped)
{
+ Form_pg_attribute wholeatt = TupleDescAttr(scanrel->rd_att, i - 1);
NullTest *ntest = makeNode(NullTest);
ntest->arg = (Expr *) makeVar(1,
i,
- att->atttypid,
- att->atttypmod,
- att->attcollation,
+ wholeatt->atttypid,
+ wholeatt->atttypmod,
+ wholeatt->attcollation,
0);
ntest->nulltesttype = IS_NOT_NULL;
diff --git a/src/backend/jit/llvm/llvmjit_deform.c b/src/backend/jit/llvm/llvmjit_deform.c
index 5d169c7a40b..c562edd094b 100644
--- a/src/backend/jit/llvm/llvmjit_deform.c
+++ b/src/backend/jit/llvm/llvmjit_deform.c
@@ -123,7 +123,7 @@ slot_compile_deform(LLVMJitContext *context, TupleDesc desc,
* combination of attisdropped && attnotnull combination shouldn't
* exist.
*/
- if (att->attnotnull &&
+ if (att->attnullability == ATTNULLABLE_VALID &&
!att->atthasmissing &&
!att->attisdropped)
guaranteed_column_number = attnum;
@@ -438,7 +438,7 @@ slot_compile_deform(LLVMJitContext *context, TupleDesc desc,
* into account, because if they're present the heaptuple's natts
* would have indicated that a slot_getmissingattrs() is needed.
*/
- if (!att->attnotnull)
+ if (att->attnullability != ATTNULLABLE_VALID)
{
LLVMBasicBlockRef b_ifnotnull;
LLVMBasicBlockRef b_ifnull;
@@ -604,7 +604,8 @@ slot_compile_deform(LLVMJitContext *context, TupleDesc desc,
known_alignment = -1;
attguaranteedalign = false;
}
- else if (att->attnotnull && attguaranteedalign && known_alignment >= 0)
+ else if (att->attnullability == ATTNULLABLE_VALID &&
+ attguaranteedalign && known_alignment >= 0)
{
/*
* If the offset to the column was previously known, a NOT NULL &
@@ -614,7 +615,8 @@ slot_compile_deform(LLVMJitContext *context, TupleDesc desc,
Assert(att->attlen > 0);
known_alignment += att->attlen;
}
- else if (att->attnotnull && (att->attlen % alignto) == 0)
+ else if (att->attnullability == ATTNULLABLE_VALID &&
+ (att->attlen % alignto) == 0)
{
/*
* After a NOT NULL fixed-width column with a length that is a
diff --git a/src/backend/optimizer/util/plancat.c b/src/backend/optimizer/util/plancat.c
index 67d879be8b8..59233b64730 100644
--- a/src/backend/optimizer/util/plancat.c
+++ b/src/backend/optimizer/util/plancat.c
@@ -177,7 +177,9 @@ get_relation_info(PlannerInfo *root, Oid relationObjectId, bool inhparent,
{
CompactAttribute *attr = TupleDescCompactAttr(relation->rd_att, i);
- if (attr->attnotnull)
+ Assert(attr->attnullability != ATTNULLABLE_UNKNOWN);
+
+ if (attr->attnullability == ATTNULLABLE_VALID)
{
rel->notnullattnums = bms_add_member(rel->notnullattnums,
i + 1);
@@ -1251,6 +1253,7 @@ get_relation_data_width(Oid relid, int32 *attr_widths)
* get_relation_constraints
*
* Retrieve the applicable constraint expressions of the given relation.
+ * Only constraints that have been validated are considered.
*
* Returns a List (possibly empty) of constraint expressions. Each one
* has been canonicalized, and its Vars are changed to have the varno
@@ -1351,17 +1354,18 @@ get_relation_constraints(PlannerInfo *root,
for (i = 1; i <= natts; i++)
{
- Form_pg_attribute att = TupleDescAttr(relation->rd_att, i - 1);
+ CompactAttribute *att = TupleDescCompactAttr(relation->rd_att, i - 1);
- if (att->attnotnull && !att->attisdropped)
+ if (att->attnullability == ATTNULLABLE_VALID && !att->attisdropped)
{
+ Form_pg_attribute wholeatt = TupleDescAttr(relation->rd_att, i - 1);
NullTest *ntest = makeNode(NullTest);
ntest->arg = (Expr *) makeVar(varno,
i,
- att->atttypid,
- att->atttypmod,
- att->attcollation,
+ wholeatt->atttypid,
+ wholeatt->atttypmod,
+ wholeatt->attcollation,
0);
ntest->nulltesttype = IS_NOT_NULL;
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index f1156e2fca3..3c4268b271a 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -4217,11 +4217,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/utils/cache/relcache.c b/src/backend/utils/cache/relcache.c
index 18a14ae186e..2905ae86a20 100644
--- a/src/backend/utils/cache/relcache.c
+++ b/src/backend/utils/cache/relcache.c
@@ -307,7 +307,7 @@ static TupleDesc GetPgClassDescriptor(void);
static TupleDesc GetPgIndexDescriptor(void);
static void AttrDefaultFetch(Relation relation, int ndef);
static int AttrDefaultCmp(const void *a, const void *b);
-static void CheckConstraintFetch(Relation relation);
+static void CheckNNConstraintFetch(Relation relation);
static int CheckConstraintCmp(const void *a, const void *b);
static void InitIndexAmRoutine(Relation relation);
static void IndexSupportInitialize(oidvector *indclass,
@@ -684,6 +684,8 @@ RelationBuildTupleDesc(Relation relation)
attrmiss ||
relation->rd_rel->relchecks > 0)
{
+ bool is_catalog = IsCatalogRelation(relation);
+
relation->rd_att->constr = constr;
if (ndef > 0) /* DEFAULTs */
@@ -693,9 +695,33 @@ RelationBuildTupleDesc(Relation relation)
constr->missing = attrmiss;
- if (relation->rd_rel->relchecks > 0) /* CHECKs */
- CheckConstraintFetch(relation);
- else
+ /* CHECK and NOT NULLs */
+ if (relation->rd_rel->relchecks > 0 ||
+ (!is_catalog && constr->has_not_null))
+ CheckNNConstraintFetch(relation);
+
+ /*
+ * Any not-null constraint that wasn't marked invalid by
+ * CheckNNConstraintFetch must necessarily be valid; make it so in the
+ * CompactAttribute array.
+ */
+ if (!is_catalog)
+ {
+ for (int i = 0; i < relation->rd_rel->relnatts; i++)
+ {
+ CompactAttribute *attr;
+
+ attr = TupleDescCompactAttr(relation->rd_att, i);
+
+ if (attr->attnullability == ATTNULLABLE_UNKNOWN)
+ attr->attnullability = ATTNULLABLE_VALID;
+ else
+ Assert(attr->attnullability == ATTNULLABLE_INVALID ||
+ attr->attnullability == ATTNULLABLE_UNRESTRICTED);
+ }
+ }
+
+ if (relation->rd_rel->relchecks == 0)
constr->num_check = 0;
}
else
@@ -3575,6 +3601,14 @@ RelationBuildLocalRelation(const char *relname,
datt->attnotnull = satt->attnotnull;
has_not_null |= satt->attnotnull;
populate_compact_attribute(rel->rd_att, i);
+
+ if (satt->attnotnull)
+ {
+ CompactAttribute *scatt = TupleDescCompactAttr(tupDesc, i);
+ CompactAttribute *dcatt = TupleDescCompactAttr(rel->rd_att, i);
+
+ dcatt->attnullability = scatt->attnullability;
+ }
}
if (has_not_null)
@@ -4533,13 +4567,14 @@ AttrDefaultCmp(const void *a, const void *b)
}
/*
- * Load any check constraints for the relation.
+ * Load any check constraints for the relation, and update not-null validity
+ * of invalid constraints.
*
* As with defaults, if we don't find the expected number of them, just warn
* here. The executor should throw an error if an INSERT/UPDATE is attempted.
*/
static void
-CheckConstraintFetch(Relation relation)
+CheckNNConstraintFetch(Relation relation)
{
ConstrCheck *check;
int ncheck = relation->rd_rel->relchecks;
@@ -4570,7 +4605,31 @@ CheckConstraintFetch(Relation relation)
Datum val;
bool isnull;
- /* We want check constraints only */
+ /*
+ * 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.
+ * 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
+ * majority of cases.
+ */
+ if (conform->contype == CONSTRAINT_NOTNULL)
+ {
+ if (!conform->convalidated)
+ {
+ AttrNumber attnum;
+
+ attnum = extractNotNullColumn(htup);
+ Assert(relation->rd_att->compact_attrs[attnum - 1].attnullability ==
+ ATTNULLABLE_UNKNOWN);
+ relation->rd_att->compact_attrs[attnum - 1].attnullability =
+ ATTNULLABLE_INVALID;
+ }
+
+ continue;
+ }
+
+ /* For what follows, consider check constraints only */
if (conform->contype != CONSTRAINT_CHECK)
continue;
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index 8e6364d32d7..25264f8c9fb 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -350,8 +350,10 @@ static void buildMatViewRefreshDependencies(Archive *fout);
static void getTableDataFKConstraints(void);
static void determineNotNullFlags(Archive *fout, PGresult *res, int r,
TableInfo *tbinfo, int j,
- int i_notnull_name, int i_notnull_noinherit,
- int i_notnull_islocal);
+ int i_notnull_name, int i_notnull_invalidoid,
+ int i_notnull_noinherit,
+ int i_notnull_islocal,
+ PQExpBuffer *invalidnotnulloids);
static char *format_function_arguments(const FuncInfo *finfo, const char *funcargs,
bool is_agg);
static char *format_function_signature(Archive *fout,
@@ -8984,6 +8986,7 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables)
PQExpBuffer q = createPQExpBuffer();
PQExpBuffer tbloids = createPQExpBuffer();
PQExpBuffer checkoids = createPQExpBuffer();
+ PQExpBuffer invalidnotnulloids = NULL;
PGresult *res;
int ntups;
int curtblindx;
@@ -9003,6 +9006,7 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables)
int i_notnull_name;
int i_notnull_noinherit;
int i_notnull_islocal;
+ int i_notnull_invalidoid;
int i_attoptions;
int i_attcollation;
int i_attcompression;
@@ -9089,6 +9093,10 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables)
* 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
+ * is invalid, and NULL otherwise.
+ *
* We track in notnull_islocal whether the constraint was defined directly
* in this table or via an ancestor, for binary upgrade. flagInhAttrs
* might modify this later for servers older than 18; it's also in charge
@@ -9097,11 +9105,14 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables)
if (fout->remoteVersion >= 180000)
appendPQExpBufferStr(q,
"co.conname AS notnull_name,\n"
+ "CASE WHEN NOT co.convalidated THEN co.oid "
+ "ELSE NULL END AS notnull_invalidoid,\n"
"co.connoinherit AS notnull_noinherit,\n"
"co.conislocal AS notnull_islocal,\n");
else
appendPQExpBufferStr(q,
"CASE WHEN a.attnotnull THEN '' ELSE NULL END AS notnull_name,\n"
+ "NULL AS notnull_invalidoid,\n"
"false AS notnull_noinherit,\n"
"a.attislocal AS notnull_islocal,\n");
@@ -9176,6 +9187,7 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables)
i_attalign = PQfnumber(res, "attalign");
i_attislocal = PQfnumber(res, "attislocal");
i_notnull_name = PQfnumber(res, "notnull_name");
+ i_notnull_invalidoid = PQfnumber(res, "notnull_invalidoid");
i_notnull_noinherit = PQfnumber(res, "notnull_noinherit");
i_notnull_islocal = PQfnumber(res, "notnull_islocal");
i_attoptions = PQfnumber(res, "attoptions");
@@ -9272,8 +9284,11 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables)
/* Handle not-null constraint name and flags */
determineNotNullFlags(fout, res, r,
tbinfo, j,
- i_notnull_name, i_notnull_noinherit,
- i_notnull_islocal);
+ i_notnull_name,
+ i_notnull_invalidoid,
+ i_notnull_noinherit,
+ i_notnull_islocal,
+ &invalidnotnulloids);
tbinfo->attoptions[j] = pg_strdup(PQgetvalue(res, r, i_attoptions));
tbinfo->attcollation[j] = atooid(PQgetvalue(res, r, i_attcollation));
@@ -9294,6 +9309,10 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables)
}
}
+ /* If invalidnotnulloids has any data, finalize it */
+ if (invalidnotnulloids != NULL)
+ appendPQExpBufferChar(invalidnotnulloids, '}');
+
PQclear(res);
/*
@@ -9427,6 +9446,103 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables)
}
/*
+ * Get info about NOT NULL NOT VALID constraints. This is skipped for a
+ * data-only dump, as it is only needed for table schemas.
+ */
+ if (dopt->dumpSchema && invalidnotnulloids)
+ {
+ ConstraintInfo *constrs;
+ int numConstrs;
+ int i_tableoid;
+ int i_oid;
+ int i_conrelid;
+ int i_conname;
+ int i_consrc;
+ int i_conislocal;
+
+ pg_log_info("finding invalid not null constraints");
+
+ resetPQExpBuffer(q);
+ appendPQExpBuffer(q,
+ "SELECT c.tableoid, c.oid, conrelid, conname, "
+ "pg_catalog.pg_get_constraintdef(c.oid) AS consrc, "
+ "conislocal, convalidated "
+ "FROM unnest('%s'::pg_catalog.oid[]) AS src(conoid)\n"
+ "JOIN pg_catalog.pg_constraint c ON (src.conoid = c.oid)\n"
+ "ORDER BY c.conrelid, c.conname",
+ invalidnotnulloids->data);
+
+ res = ExecuteSqlQuery(fout, q->data, PGRES_TUPLES_OK);
+
+ numConstrs = PQntuples(res);
+ constrs = (ConstraintInfo *) pg_malloc(numConstrs * sizeof(ConstraintInfo));
+
+ i_tableoid = PQfnumber(res, "tableoid");
+ i_oid = PQfnumber(res, "oid");
+ i_conrelid = PQfnumber(res, "conrelid");
+ i_conname = PQfnumber(res, "conname");
+ i_consrc = PQfnumber(res, "consrc");
+ i_conislocal = PQfnumber(res, "conislocal");
+
+ /* As above, this loop iterates once per table, not once per row */
+ curtblindx = -1;
+ for (int j = 0; j < numConstrs;)
+ {
+ Oid conrelid = atooid(PQgetvalue(res, j, i_conrelid));
+ TableInfo *tbinfo = NULL;
+ int numcons;
+
+ /* Count rows for this table */
+ for (numcons = 1; numcons < numConstrs - j; numcons++)
+ if (atooid(PQgetvalue(res, j + numcons, i_conrelid)) != conrelid)
+ break;
+
+ /*
+ * Locate the associated TableInfo; we rely on tblinfo[] being in
+ * OID order.
+ */
+ while (++curtblindx < numTables)
+ {
+ tbinfo = &tblinfo[curtblindx];
+ if (tbinfo->dobj.catId.oid == conrelid)
+ break;
+ }
+ if (curtblindx >= numTables)
+ pg_fatal("unrecognized table OID %u", conrelid);
+
+ for (int c = 0; c < numcons; c++, j++)
+ {
+ constrs[j].dobj.objType = DO_CONSTRAINT;
+ constrs[j].dobj.catId.tableoid = atooid(PQgetvalue(res, j, i_tableoid));
+ constrs[j].dobj.catId.oid = atooid(PQgetvalue(res, j, i_oid));
+ AssignDumpId(&constrs[j].dobj);
+ constrs[j].dobj.name = pg_strdup(PQgetvalue(res, j, i_conname));
+ constrs[j].dobj.namespace = tbinfo->dobj.namespace;
+ constrs[j].contable = tbinfo;
+ constrs[j].condomain = NULL;
+ constrs[j].contype = 'n';
+ constrs[j].condef = pg_strdup(PQgetvalue(res, j, i_consrc));
+ constrs[j].confrelid = InvalidOid;
+ constrs[j].conindex = 0;
+ constrs[j].condeferrable = false;
+ constrs[j].condeferred = false;
+ constrs[j].conislocal = (PQgetvalue(res, j, i_conislocal)[0] == 't');
+
+ /*
+ * All invalid not-null constraints must be dumped separately,
+ * because CREATE TABLE would not create them as invalid, and
+ * also because they must be created after potentially
+ * violating data has been loaded.
+ */
+ constrs[j].separate = true;
+
+ constrs[j].dobj.dump = tbinfo->dobj.dump;
+ }
+ }
+ PQclear(res);
+ }
+
+ /*
* Get info about table CHECK constraints. This is skipped for a
* data-only dump, as it is only needed for table schemas.
*/
@@ -9570,18 +9686,23 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables)
*
* Result row 'r' is for tbinfo's attribute 'j'.
*
- * There are three possibilities:
+ * There are four possibilities:
* 1) the column has no not-null constraints. In that case, ->notnull_constrs
* (the constraint name) remains NULL.
* 2) The column has a constraint with no name (this is the case when
* constraints come from pre-18 servers). In this case, ->notnull_constrs
* is set to the empty string; dumpTableSchema will print just "NOT NULL".
- * 3) The column has a constraint with a known name; in that case
+ * 3) The column has an invalid not-null constraint. This must be treated
+ * as a separate object (because it must be created after the table data
+ * is loaded). So we add its OID to invalidnotnulloids for processing
+ * elsewhere and do nothing further with it here. We distinguish this
+ * case because the "notnull_invalidoid" column has been set to a non-NULL
+ * value, which is the constraint OID. Valid constraints have a null OID.
+ * 4) The column has a constraint with a known name; in that case
* notnull_constrs carries that name and dumpTableSchema will print
* "CONSTRAINT the_name NOT NULL". However, if the name is the default
* (table_column_not_null), there's no need to print that name in the dump,
- * so notnull_constrs is set to the empty string and it behaves as the case
- * above.
+ * so notnull_constrs is set to the empty string and it behaves as case 2.
*
* In a child table that inherits from a parent already containing NOT NULL
* constraints and the columns in the child don't have their own NOT NULL
@@ -9593,20 +9714,54 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables)
* Any of these constraints might have the NO INHERIT bit. If so we set
* ->notnull_noinh and NO INHERIT will be printed by dumpTableSchema.
*
- * In case 3 above, the name comparison is a bit of a hack; it actually fails
+ * In case 4 above, the name comparison is a bit of a hack; it actually fails
* to do the right thing in all but the trivial case. However, the downside
* of getting it wrong is simply that the name is printed rather than
* suppressed, so it's not a big deal.
+ *
+ * invalidnotnulloids is expected to be given as NULL; if any invalid not-null
+ * constraints are found, it is initialized and filled with the array of
+ * OIDs of such constraints, for later processing.
*/
static void
determineNotNullFlags(Archive *fout, PGresult *res, int r,
TableInfo *tbinfo, int j,
- int i_notnull_name, int i_notnull_noinherit,
- int i_notnull_islocal)
+ int i_notnull_name,
+ int i_notnull_invalidoid,
+ int i_notnull_noinherit,
+ int i_notnull_islocal,
+ PQExpBuffer *invalidnotnulloids)
{
DumpOptions *dopt = fout->dopt;
/*
+ * If this not-null constraint is not valid, list its OID in
+ * invalidnotnulloids and do nothing further. It'll be processed
+ * elsewhere later.
+ *
+ * Because invalid not-null constraints are rare, we don't want to malloc
+ * invalidnotnulloids until we're sure we're going it need it, which
+ * happens here.
+ */
+ if (!PQgetisnull(res, r, i_notnull_invalidoid))
+ {
+ char *constroid = PQgetvalue(res, r, i_notnull_invalidoid);
+
+ if (*invalidnotnulloids == NULL)
+ {
+ *invalidnotnulloids = createPQExpBuffer();
+ appendPQExpBufferChar(*invalidnotnulloids, '{');
+ appendPQExpBuffer(*invalidnotnulloids, "%s", constroid);
+ }
+ else
+ appendPQExpBuffer(*invalidnotnulloids, ",%s", constroid);
+
+ /* nothing else to do */
+ tbinfo->notnull_constrs[j] = NULL;
+ return;
+ }
+
+ /*
* notnull_noinh is straight from the query result. notnull_islocal also,
* though flagInhAttrs may change that one later in versions < 18.
*/
@@ -18152,13 +18307,20 @@ dumpConstraint(Archive *fout, const ConstraintInfo *coninfo)
.createStmt = q->data,
.dropStmt = delq->data));
}
- else if (coninfo->contype == 'c' && tbinfo)
+ else if ((coninfo->contype == 'c' || coninfo->contype == 'n') && tbinfo)
{
- /* CHECK constraint on a table */
+ /* CHECK or invalid not-null constraint on a table */
/* Ignore if not to be dumped separately, or if it was inherited */
if (coninfo->separate && coninfo->conislocal)
{
+ const char *keyword;
+
+ if (coninfo->contype == 'c')
+ keyword = "CHECK CONSTRAINT";
+ else
+ keyword = "CONSTRAINT";
+
/* not ONLY since we want it to propagate to children */
appendPQExpBuffer(q, "ALTER %sTABLE %s\n", foreign,
fmtQualifiedDumpable(tbinfo));
@@ -18178,7 +18340,7 @@ dumpConstraint(Archive *fout, const ConstraintInfo *coninfo)
ARCHIVE_OPTS(.tag = tag,
.namespace = tbinfo->dobj.namespace->dobj.name,
.owner = tbinfo->rolname,
- .description = "CHECK CONSTRAINT",
+ .description = keyword,
.section = SECTION_POST_DATA,
.createStmt = q->data,
.dropStmt = delq->data));
diff --git a/src/bin/pg_dump/pg_dump.h b/src/bin/pg_dump/pg_dump.h
index e6f0f86a459..b426b5e4736 100644
--- a/src/bin/pg_dump/pg_dump.h
+++ b/src/bin/pg_dump/pg_dump.h
@@ -498,6 +498,8 @@ typedef struct _evttriggerInfo
* use a different objType for foreign key constraints, to make it easier
* to sort them the way we want.
*
+ * Not-null constraints don't need this, unless they are NOT VALID.
+ *
* Note: condeferrable and condeferred are currently only valid for
* unique/primary-key constraints. Otherwise that info is in condef.
*/
diff --git a/src/bin/pg_dump/t/002_pg_dump.pl b/src/bin/pg_dump/t/002_pg_dump.pl
index 576326daec7..6c03eca8e50 100644
--- a/src/bin/pg_dump/t/002_pg_dump.pl
+++ b/src/bin/pg_dump/t/002_pg_dump.pl
@@ -744,8 +744,8 @@ my %pgdump_runs = (
schema_only_with_statistics => {
dump_cmd => [
'pg_dump', '--no-sync',
- "--file=$tempdir/schema_only_with_statistics.sql", '--schema-only',
- '--with-statistics', 'postgres',
+ "--file=$tempdir/schema_only_with_statistics.sql",
+ '--schema-only', '--with-statistics', 'postgres',
],
},
no_schema => {
@@ -1118,6 +1118,23 @@ my %tests = (
},
},
+ 'CONSTRAINT NOT NULL / INVALID' => {
+ create_sql => 'CREATE TABLE dump_test.test_table_nn (
+ col1 int);
+ ALTER TABLE dump_test.test_table_nn ADD CONSTRAINT nn NOT NULL col1 NOT VALID;',
+ regexp => qr/^
+ \QALTER TABLE dump_test.test_table_nn\E \n^\s+
+ \QADD CONSTRAINT nn NOT NULL col1 NOT VALID;\E
+ /xm,
+ like => {
+ %full_runs, %dump_test_schema_runs, section_post_data => 1,
+ },
+ unlike => {
+ exclude_dump_test_schema => 1,
+ only_dump_measurement => 1,
+ },
+ },
+
'CONSTRAINT PRIMARY KEY / WITHOUT OVERLAPS' => {
create_sql => 'CREATE TABLE dump_test.test_table_tpk (
col1 int4range,
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index 8970677ac64..1d08268393e 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -3106,7 +3106,8 @@ describeOneTableDetails(const char *schemaname,
{
printfPQExpBuffer(&buf,
"SELECT c.conname, a.attname, c.connoinherit,\n"
- " c.conislocal, c.coninhcount <> 0\n"
+ " c.conislocal, c.coninhcount <> 0,\n"
+ " c.convalidated\n"
"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"
@@ -3129,14 +3130,16 @@ 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';
- printfPQExpBuffer(&buf, " \"%s\" NOT NULL \"%s\"%s",
+ printfPQExpBuffer(&buf, " \"%s\" NOT NULL \"%s\"%s%s",
PQgetvalue(result, i, 0),
PQgetvalue(result, i, 1),
PQgetvalue(result, i, 2)[0] == 't' ?
" NO INHERIT" :
islocal && inherited ? _(" (local, inherited)") :
- inherited ? _(" (inherited)") : "");
+ inherited ? _(" (inherited)") : "",
+ !validated ? " NOT VALID" : "");
printTableAddFooter(&cont, buf.data);
}
diff --git a/src/include/access/tupdesc.h b/src/include/access/tupdesc.h
index 396eeb7a0bb..a25b94ba423 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;
+ bool has_not_null; /* any not-null, including not valid ones */
bool has_generated_stored;
bool has_generated_virtual;
} TupleConstr;
@@ -76,10 +76,16 @@ typedef struct CompactAttribute
bool atthasmissing; /* as FormData_pg_attribute.atthasmissing */
bool attisdropped; /* as FormData_pg_attribute.attisdropped */
bool attgenerated; /* FormData_pg_attribute.attgenerated != '\0' */
- bool attnotnull; /* as FormData_pg_attribute.attnotnull */
+ char attnullability; /* status of not-null constraint, see below */
uint8 attalignby; /* alignment requirement in bytes */
} CompactAttribute;
+/* Valid values for CompactAttribute->attnullability */
+#define ATTNULLABLE_UNRESTRICTED 'f' /* No constraint exists */
+#define ATTNULLABLE_UNKNOWN 'u' /* constraint exists, validity unknown */
+#define ATTNULLABLE_VALID 'v' /* valid constraint exists */
+#define ATTNULLABLE_INVALID 'i' /* constraint exists, marked invalid */
+
/*
* This struct is passed around within the backend to describe the structure
* of tuples. For tuples coming from on-disk relations, the information is
diff --git a/src/include/catalog/pg_attribute.h b/src/include/catalog/pg_attribute.h
index deaa515fe53..c612aed620f 100644
--- a/src/include/catalog/pg_attribute.h
+++ b/src/include/catalog/pg_attribute.h
@@ -117,7 +117,9 @@ CATALOG(pg_attribute,1249,AttributeRelationId) BKI_BOOTSTRAP BKI_ROWTYPE_OID(75,
*/
char attcompression BKI_DEFAULT('\0');
- /* This flag represents the "NOT NULL" constraint */
+ /*
+ * Whether a (possibly invalid) not-null constraint exists for the column
+ */
bool attnotnull;
/* Has DEFAULT value or not */
diff --git a/src/include/catalog/pg_constraint.h b/src/include/catalog/pg_constraint.h
index 6da164e7e4d..4afceb5c692 100644
--- a/src/include/catalog/pg_constraint.h
+++ b/src/include/catalog/pg_constraint.h
@@ -264,7 +264,7 @@ extern HeapTuple findNotNullConstraint(Oid relid, const char *colname);
extern HeapTuple findDomainNotNullConstraint(Oid typid);
extern AttrNumber extractNotNullColumn(HeapTuple constrTup);
extern bool AdjustNotNullInheritance(Oid relid, AttrNumber attnum,
- bool is_local, bool is_no_inherit);
+ bool is_local, bool is_no_inherit, bool is_notvalid);
extern List *RelationGetNotNullConstraints(Oid relid, bool cooked,
bool include_noinh);
diff --git a/src/test/regress/expected/alter_table.out b/src/test/regress/expected/alter_table.out
index 362f38856d2..8a44321034b 100644
--- a/src/test/regress/expected/alter_table.out
+++ b/src/test/regress/expected/alter_table.out
@@ -1202,6 +1202,75 @@ alter table atacc1 alter test_a drop not null, alter test_b drop not null;
alter table atacc1 add constraint atacc1_constr_b_valid check(test_b is not null);
alter table atacc1 alter test_b set not null, alter test_a set not null;
drop table atacc1;
+-- not null not valid with partitions
+CREATE TABLE atnnparted (id int, col1 int) PARTITION BY LIST (id);
+ALTER TABLE atnnparted ADD CONSTRAINT dummy_constr NOT NULL id NOT VALID;
+CREATE TABLE atnnpart1 (col1 int, id int);
+ALTER TABLE atnnpart1 ADD CONSTRAINT another_constr NOT NULL id;
+ALTER TABLE atnnpart1 ADD PRIMARY KEY (id);
+ALTER TABLE atnnparted ATTACH PARTITION atnnpart1 FOR VALUES IN ('1');
+\d+ atnnpart*
+ Table "public.atnnpart1"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+---------+--------------+-------------
+ col1 | integer | | | | plain | |
+ id | integer | | not null | | plain | |
+Partition of: atnnparted FOR VALUES IN (1)
+Partition constraint: ((id IS NOT NULL) AND (id = 1))
+Indexes:
+ "atnnpart1_pkey" PRIMARY KEY, btree (id)
+Not-null constraints:
+ "another_constr" NOT NULL "id" (inherited)
+
+ Index "public.atnnpart1_pkey"
+ Column | Type | Key? | Definition | Storage | Stats target
+--------+---------+------+------------+---------+--------------
+ id | integer | yes | id | plain |
+primary key, btree, for table "public.atnnpart1"
+
+ Partitioned table "public.atnnparted"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+---------+--------------+-------------
+ id | integer | | not null | | plain | |
+ col1 | integer | | | | plain | |
+Partition key: LIST (id)
+Not-null constraints:
+ "dummy_constr" NOT NULL "id" NOT VALID
+Partitions: atnnpart1 FOR VALUES IN (1)
+
+BEGIN;
+ALTER TABLE atnnparted VALIDATE CONSTRAINT dummy_constr;
+\d+ atnnpart*
+ Table "public.atnnpart1"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+---------+--------------+-------------
+ col1 | integer | | | | plain | |
+ id | integer | | not null | | plain | |
+Partition of: atnnparted FOR VALUES IN (1)
+Partition constraint: ((id IS NOT NULL) AND (id = 1))
+Indexes:
+ "atnnpart1_pkey" PRIMARY KEY, btree (id)
+Not-null constraints:
+ "another_constr" NOT NULL "id" (inherited)
+
+ Index "public.atnnpart1_pkey"
+ Column | Type | Key? | Definition | Storage | Stats target
+--------+---------+------+------------+---------+--------------
+ id | integer | yes | id | plain |
+primary key, btree, for table "public.atnnpart1"
+
+ Partitioned table "public.atnnparted"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+---------+--------------+-------------
+ id | integer | | not null | | plain | |
+ col1 | integer | | | | plain | |
+Partition key: LIST (id)
+Not-null constraints:
+ "dummy_constr" NOT NULL "id"
+Partitions: atnnpart1 FOR VALUES IN (1)
+
+ROLLBACK;
+-- leave a table in this state for the pg_upgrade test
-- test inheritance
create table parent (a int);
create table child (b varchar(255)) inherits (parent);
diff --git a/src/test/regress/expected/constraints.out b/src/test/regress/expected/constraints.out
index a719d2f74e9..1346134e23c 100644
--- a/src/test/regress/expected/constraints.out
+++ b/src/test/regress/expected/constraints.out
@@ -1229,6 +1229,13 @@ alter table cnn_uq add unique using index cnn_uq_idx;
Indexes:
"cnn_uq_idx" UNIQUE CONSTRAINT, btree (a)
+-- can't create a primary key on a noinherit not-null
+create table cnn_pk (a int not null no inherit);
+alter table cnn_pk add primary key (a);
+ERROR: cannot create primary key on column "a"
+DETAIL: The constraint "cnn_pk_a_not_null" on column "a", marked NO INHERIT, is incompatible with a primary key.
+HINT: You will need to make it inheritable using ALTER TABLE ... ALTER CONSTRAINT ... INHERIT.
+drop table cnn_pk;
-- Ensure partitions are scanned for null values when adding a PK
create table cnn2_parted(a int) partition by list (a);
create table cnn_part1 partition of cnn2_parted for values in (1, null);
@@ -1355,6 +1362,251 @@ Not-null constraints:
"ann" NOT NULL "a"
"bnn" NOT NULL "b"
+-- NOT NULL NOT VALID
+PREPARE get_nnconstraint_info(regclass[]) AS
+SELECT conrelid::regclass as tabname, conname, convalidated, conislocal, coninhcount
+FROM pg_constraint
+WHERE conrelid = ANY($1)
+ORDER BY conrelid::regclass::text, conname;
+CREATE TABLE notnull_tbl1 (a int, b int);
+INSERT INTO notnull_tbl1 VALUES (NULL, 1), (300, 3);
+ALTER TABLE notnull_tbl1 ADD CONSTRAINT nn NOT NULL a; -- error
+ERROR: column "a" of relation "notnull_tbl1" contains null values
+ALTER TABLE notnull_tbl1 ADD CONSTRAINT nn NOT NULL a NOT VALID; -- ok
+-- even an invalid not-null forbids new nulls
+INSERT INTO notnull_tbl1 VALUES (NULL, 4);
+ERROR: null value in column "a" of relation "notnull_tbl1" violates not-null constraint
+DETAIL: Failing row contains (null, 4).
+\d+ notnull_tbl1
+ Table "public.notnull_tbl1"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+---------+--------------+-------------
+ a | integer | | not null | | plain | |
+ b | integer | | | | plain | |
+Not-null constraints:
+ "nn" NOT NULL "a" NOT VALID
+
+-- If we have an invalid constraint, we can't have another
+ALTER TABLE notnull_tbl1 ADD CONSTRAINT nn1 NOT NULL a NOT VALID NO INHERIT;
+ERROR: cannot change NO INHERIT status of NOT NULL constraint "nn" on relation "notnull_tbl1"
+ALTER TABLE notnull_tbl1 ADD CONSTRAINT nn NOT NULL a;
+ERROR: incompatible NOT VALID constraint "nn" on relation "notnull_tbl1"
+HINT: You will need to use ALTER TABLE ... VALIDATE CONSTRAINT to validate it.
+-- cannot add primary key on a column with an invalid not-null
+ALTER TABLE notnull_tbl1 ADD PRIMARY KEY (a);
+ERROR: cannot create primary key on column "a"
+DETAIL: The constraint "nn" on column "a", marked NOT VALID, is incompatible with a primary key.
+HINT: You will need to validate it using ALTER TABLE ... VALIDATE CONSTRAINT.
+-- ALTER column SET NOT NULL validates an invalid constraint (but this fails
+-- because of rows with null values)
+ALTER TABLE notnull_tbl1 ALTER a SET NOT NULL;
+ERROR: column "a" of relation "notnull_tbl1" contains null values
+\d+ notnull_tbl1
+ Table "public.notnull_tbl1"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+---------+--------------+-------------
+ a | integer | | not null | | plain | |
+ b | integer | | | | plain | |
+Not-null constraints:
+ "nn" NOT NULL "a" NOT VALID
+
+-- Creating a derived table using LIKE gets the constraint, but it's valid
+CREATE TABLE notnull_tbl1_copy (LIKE notnull_tbl1);
+EXECUTE get_nnconstraint_info('{notnull_tbl1_copy}');
+ tabname | conname | convalidated | conislocal | coninhcount
+-------------------+---------+--------------+------------+-------------
+ notnull_tbl1_copy | nn | t | t | 0
+(1 row)
+
+-- An inheritance child table gets the constraint, but it's valid
+CREATE TABLE notnull_tbl1_child (a int, b int) INHERITS (notnull_tbl1);
+NOTICE: merging column "a" with inherited definition
+NOTICE: merging column "b" with inherited definition
+EXECUTE get_nnconstraint_info('{notnull_tbl1_child, notnull_tbl1}');
+ tabname | conname | convalidated | conislocal | coninhcount
+--------------------+---------+--------------+------------+-------------
+ notnull_tbl1 | nn | f | t | 0
+ notnull_tbl1_child | nn | t | f | 1
+(2 rows)
+
+-- Also try inheritance added after table creation
+CREATE TABLE notnull_tbl1_child2 (c int, b int, a int);
+ALTER TABLE notnull_tbl1_child2 INHERIT notnull_tbl1; -- nope
+ERROR: column "a" in child table "notnull_tbl1_child2" must be marked NOT NULL
+ALTER TABLE notnull_tbl1_child2 ADD NOT NULL a NOT VALID;
+ALTER TABLE notnull_tbl1_child2 INHERIT notnull_tbl1;
+EXECUTE get_nnconstraint_info('{notnull_tbl1_child2}');
+ tabname | conname | convalidated | conislocal | coninhcount
+---------------------+--------------------------------+--------------+------------+-------------
+ notnull_tbl1_child2 | notnull_tbl1_child2_a_not_null | f | t | 1
+(1 row)
+
+--table rewrite won't validate invalid constraint
+ALTER TABLE notnull_tbl1 ADD column d float8 default random();
+-- VALIDATE CONSTRAINT scans the table
+ALTER TABLE notnull_tbl1 VALIDATE CONSTRAINT nn; -- error, nulls exist
+ERROR: column "a" of relation "notnull_tbl1" contains null values
+UPDATE notnull_tbl1 SET a = 100 WHERE b = 1;
+ALTER TABLE notnull_tbl1 VALIDATE CONSTRAINT nn; -- now ok
+EXECUTE get_nnconstraint_info('{notnull_tbl1}');
+ tabname | conname | convalidated | conislocal | coninhcount
+--------------+---------+--------------+------------+-------------
+ notnull_tbl1 | nn | t | t | 0
+(1 row)
+
+--- now we can add primary key
+ALTER TABLE notnull_tbl1 ADD PRIMARY KEY (a);
+DROP TABLE notnull_tbl1, notnull_tbl1_child, notnull_tbl1_child2;
+-- dropping an invalid constraint is possible
+CREATE TABLE notnull_tbl1 (a int, b int);
+ALTER TABLE notnull_tbl1 ADD NOT NULL a NOT VALID,
+ ADD NOT NULL b NOT VALID;
+ALTER TABLE notnull_tbl1 ALTER a DROP NOT NULL;
+ALTER TABLE notnull_tbl1 DROP CONSTRAINT notnull_tbl1_b_not_null;
+DROP TABLE notnull_tbl1;
+-- ALTER .. NO INHERIT works for invalid constraints
+CREATE TABLE notnull_tbl1 (a int);
+CREATE TABLE notnull_tbl1_chld () INHERITS (notnull_tbl1);
+ALTER TABLE notnull_tbl1 ADD CONSTRAINT nntbl1_a NOT NULL a NOT VALID;
+ALTER TABLE notnull_tbl1 ALTER CONSTRAINT nntbl1_a NO INHERIT;
+-- DROP CONSTRAINT recurses correctly on invalid constraints
+ALTER TABLE notnull_tbl1 ALTER CONSTRAINT nntbl1_a INHERIT;
+ALTER TABLE notnull_tbl1 DROP CONSTRAINT nntbl1_a;
+DROP TABLE notnull_tbl1, notnull_tbl1_chld;
+-- if a parent has a valid not null constraint then a child table cannot
+-- have an invalid one
+CREATE TABLE notnull_tbl1 (a int);
+ALTER TABLE notnull_tbl1 ADD CONSTRAINT nn_parent NOT NULL a not valid;
+CREATE TABLE notnull_chld0 (a int, CONSTRAINT nn_chld0 NOT NULL a);
+ALTER TABLE notnull_tbl1 INHERIT notnull_chld0; --error
+ERROR: constraint "nn_parent" conflicts with NOT VALID constraint on child table "notnull_tbl1"
+ALTER TABLE notnull_chld0 DROP CONSTRAINT nn_chld0;
+ALTER TABLE notnull_chld0 ADD CONSTRAINT nn_chld0 NOT NULL a not valid;
+ALTER TABLE notnull_tbl1 INHERIT notnull_chld0; --now ok
+-- parents and child not-null will all be validated.
+ALTER TABLE notnull_tbl1 VALIDATE CONSTRAINT nn_parent;
+EXECUTE get_nnconstraint_info('{notnull_tbl1, notnull_chld0}');
+ tabname | conname | convalidated | conislocal | coninhcount
+---------------+-----------+--------------+------------+-------------
+ notnull_chld0 | nn_chld0 | f | t | 0
+ notnull_tbl1 | nn_parent | t | t | 1
+(2 rows)
+
+DROP TABLE notnull_tbl1, notnull_chld0;
+-- Test invalid not null on inheritance table.
+CREATE TABLE notnull_inhparent (i int);
+CREATE TABLE notnull_inhchild (i int) INHERITS (notnull_inhparent);
+NOTICE: merging column "i" with inherited definition
+CREATE TABLE notnull_inhgrand () INHERITS (notnull_inhparent, notnull_inhchild);
+NOTICE: merging multiple inherited definitions of column "i"
+ALTER TABLE notnull_inhparent ADD CONSTRAINT nn NOT NULL i NOT VALID;
+ALTER TABLE notnull_inhchild ADD CONSTRAINT nn1 NOT NULL i; -- error
+ERROR: incompatible NOT VALID constraint "nn" on relation "notnull_inhchild"
+HINT: You will need to use ALTER TABLE ... VALIDATE CONSTRAINT to validate it.
+EXECUTE get_nnconstraint_info('{notnull_inhparent, notnull_inhchild, notnull_inhgrand}');
+ tabname | conname | convalidated | conislocal | coninhcount
+-------------------+---------+--------------+------------+-------------
+ notnull_inhchild | nn | f | f | 1
+ notnull_inhgrand | nn | f | f | 2
+ notnull_inhparent | nn | f | t | 0
+(3 rows)
+
+ALTER TABLE notnull_inhparent ALTER i SET NOT NULL; -- ok
+EXECUTE get_nnconstraint_info('{notnull_inhparent, notnull_inhchild, notnull_inhgrand}');
+ tabname | conname | convalidated | conislocal | coninhcount
+-------------------+---------+--------------+------------+-------------
+ notnull_inhchild | nn | t | f | 1
+ notnull_inhgrand | nn | t | f | 2
+ notnull_inhparent | nn | t | t | 0
+(3 rows)
+
+DROP TABLE notnull_inhparent, notnull_inhchild, notnull_inhgrand;
+-- Verify NOT NULL VALID/NOT VALID with partition table.
+DROP TABLE notnull_tbl1;
+ERROR: table "notnull_tbl1" does not exist
+CREATE TABLE notnull_tbl1 (a int, b int) PARTITION BY LIST (a);
+ALTER TABLE notnull_tbl1 ADD CONSTRAINT notnull_con NOT NULL a NOT VALID; --ok
+CREATE TABLE notnull_tbl1_1 PARTITION OF notnull_tbl1 FOR VALUES IN (1,2);
+CREATE TABLE notnull_tbl1_2(a int, CONSTRAINT nn2 NOT NULL a, b int);
+ALTER TABLE notnull_tbl1 ATTACH PARTITION notnull_tbl1_2 FOR VALUES IN (3,4);
+CREATE TABLE notnull_tbl1_3(a int, b int);
+INSERT INTO notnull_tbl1_3 values(NULL,1);
+ALTER TABLE notnull_tbl1_3 add CONSTRAINT nn3 NOT NULL a NOT VALID;
+ALTER TABLE notnull_tbl1 ATTACH PARTITION notnull_tbl1_3 FOR VALUES IN (NULL,5);
+EXECUTE get_nnconstraint_info('{notnull_tbl1, notnull_tbl1_1, notnull_tbl1_2, notnull_tbl1_3}');
+ tabname | conname | convalidated | conislocal | coninhcount
+----------------+-------------+--------------+------------+-------------
+ notnull_tbl1 | notnull_con | f | t | 0
+ notnull_tbl1_1 | notnull_con | t | f | 1
+ notnull_tbl1_2 | nn2 | t | f | 1
+ notnull_tbl1_3 | nn3 | f | f | 1
+(4 rows)
+
+ALTER TABLE notnull_tbl1 ALTER COLUMN a SET NOT NULL; --error, notnull_tbl1_3 have null values
+ERROR: column "a" of relation "notnull_tbl1_3" contains null values
+ALTER TABLE notnull_tbl1_3 VALIDATE CONSTRAINT nn3; --error
+ERROR: column "a" of relation "notnull_tbl1_3" contains null values
+TRUNCATE notnull_tbl1;
+ALTER TABLE notnull_tbl1 ALTER COLUMN a SET NOT NULL; --OK
+EXECUTE get_nnconstraint_info('{notnull_tbl1, notnull_tbl1_1, notnull_tbl1_2, notnull_tbl1_3}');
+ tabname | conname | convalidated | conislocal | coninhcount
+----------------+-------------+--------------+------------+-------------
+ notnull_tbl1 | notnull_con | t | t | 0
+ notnull_tbl1_1 | notnull_con | t | f | 1
+ notnull_tbl1_2 | nn2 | t | f | 1
+ notnull_tbl1_3 | nn3 | t | f | 1
+(4 rows)
+
+DROP TABLE notnull_tbl1;
+-- partitioned table have not-null, then the partitions can not be NOT NULL NOT VALID.
+CREATE TABLE pp_nn (a int, b int, NOT NULL a) PARTITION BY LIST (a);
+CREATE TABLE pp_nn_1(a int, b int);
+ALTER TABLE pp_nn_1 ADD CONSTRAINT nn1 NOT NULL a NOT VALID;
+ALTER TABLE pp_nn ATTACH PARTITION pp_nn_1 FOR VALUES IN (NULL,5); --error
+ERROR: constraint "nn1" conflicts with NOT VALID constraint on child table "pp_nn_1"
+ALTER TABLE pp_nn_1 VALIDATE CONSTRAINT nn1;
+ALTER TABLE pp_nn ATTACH PARTITION pp_nn_1 FOR VALUES IN (NULL,5); --ok
+DROP TABLE pp_nn;
+-- Create table with NOT NULL INVALID constraint, for pg_upgrade.
+CREATE TABLE notnull_tbl1_upg (a int, b int);
+INSERT INTO notnull_tbl1_upg VALUES (NULL, 1), (NULL, 2), (300, 3);
+ALTER TABLE notnull_tbl1_upg ADD CONSTRAINT nn NOT NULL a NOT VALID;
+-- Inherit test for pg_upgrade
+CREATE TABLE notnull_parent_upg (a int);
+CREATE TABLE notnull_child_upg () INHERITS (notnull_parent_upg);
+ALTER TABLE notnull_child_upg ADD CONSTRAINT nn NOT NULL a;
+ALTER TABLE notnull_parent_upg ADD CONSTRAINT nn NOT NULL a NOT VALID;
+SELECT conrelid::regclass, contype, convalidated, conislocal
+FROM pg_catalog.pg_constraint
+WHERE conrelid in ('notnull_parent_upg'::regclass, 'notnull_child_upg'::regclass)
+ORDER BY 1;
+ conrelid | contype | convalidated | conislocal
+--------------------+---------+--------------+------------
+ notnull_parent_upg | n | f | t
+ notnull_child_upg | n | t | t
+(2 rows)
+
+-- Partition table test, for pg_upgrade
+CREATE TABLE notnull_part1_upg (a int, b int) PARTITION BY LIST (a);
+ALTER TABLE notnull_part1_upg ADD CONSTRAINT notnull_con NOT NULL a NOT VALID; --ok
+CREATE TABLE notnull_part1_1_upg PARTITION OF notnull_part1_upg FOR VALUES IN (1,2);
+CREATE TABLE notnull_part1_2_upg (a int, CONSTRAINT nn2 NOT NULL a, b int);
+ALTER TABLE notnull_part1_upg ATTACH PARTITION notnull_part1_2_upg FOR VALUES IN (3,4);
+CREATE TABLE notnull_part1_3_upg (a int, b int);
+INSERT INTO notnull_part1_3_upg values(NULL,1);
+ALTER TABLE notnull_part1_3_upg add CONSTRAINT nn3 NOT NULL a NOT VALID;
+ALTER TABLE notnull_part1_upg ATTACH PARTITION notnull_part1_3_upg FOR VALUES IN (NULL,5);
+EXECUTE get_nnconstraint_info('{notnull_part1_upg, notnull_part1_1_upg, notnull_part1_2_upg, notnull_part1_3_upg}');
+ tabname | conname | convalidated | conislocal | coninhcount
+---------------------+-------------+--------------+------------+-------------
+ notnull_part1_1_upg | notnull_con | t | f | 1
+ notnull_part1_2_upg | nn2 | t | f | 1
+ notnull_part1_3_upg | nn3 | f | f | 1
+ notnull_part1_upg | notnull_con | f | t | 0
+(4 rows)
+
+DEALLOCATE get_nnconstraint_info;
+-- end NOT NULL NOT VALID
-- Comments
-- Setup a low-level role to enforce non-superuser checks.
CREATE ROLE regress_constraint_comments;
diff --git a/src/test/regress/sql/alter_table.sql b/src/test/regress/sql/alter_table.sql
index 84e93ef575e..8432e8e3d54 100644
--- a/src/test/regress/sql/alter_table.sql
+++ b/src/test/regress/sql/alter_table.sql
@@ -910,6 +910,20 @@ alter table atacc1 add constraint atacc1_constr_b_valid check(test_b is not null
alter table atacc1 alter test_b set not null, alter test_a set not null;
drop table atacc1;
+-- not null not valid with partitions
+CREATE TABLE atnnparted (id int, col1 int) PARTITION BY LIST (id);
+ALTER TABLE atnnparted ADD CONSTRAINT dummy_constr NOT NULL id NOT VALID;
+CREATE TABLE atnnpart1 (col1 int, id int);
+ALTER TABLE atnnpart1 ADD CONSTRAINT another_constr NOT NULL id;
+ALTER TABLE atnnpart1 ADD PRIMARY KEY (id);
+ALTER TABLE atnnparted ATTACH PARTITION atnnpart1 FOR VALUES IN ('1');
+\d+ atnnpart*
+BEGIN;
+ALTER TABLE atnnparted VALIDATE CONSTRAINT dummy_constr;
+\d+ atnnpart*
+ROLLBACK;
+-- leave a table in this state for the pg_upgrade test
+
-- test inheritance
create table parent (a int);
create table child (b varchar(255)) inherits (parent);
diff --git a/src/test/regress/sql/constraints.sql b/src/test/regress/sql/constraints.sql
index 21ce4177de4..0f2fe3e6e2d 100644
--- a/src/test/regress/sql/constraints.sql
+++ b/src/test/regress/sql/constraints.sql
@@ -761,6 +761,11 @@ create unique index cnn_uq_idx on cnn_uq (a);
alter table cnn_uq add unique using index cnn_uq_idx;
\d+ cnn_uq
+-- can't create a primary key on a noinherit not-null
+create table cnn_pk (a int not null no inherit);
+alter table cnn_pk add primary key (a);
+drop table cnn_pk;
+
-- Ensure partitions are scanned for null values when adding a PK
create table cnn2_parted(a int) partition by list (a);
create table cnn_part1 partition of cnn2_parted for values in (1, null);
@@ -801,6 +806,170 @@ ALTER TABLE ONLY notnull_tbl6 DROP CONSTRAINT ann;
ALTER TABLE ONLY notnull_tbl6 ALTER b DROP NOT NULL;
\d+ notnull_tbl6_1
+
+-- NOT NULL NOT VALID
+PREPARE get_nnconstraint_info(regclass[]) AS
+SELECT conrelid::regclass as tabname, conname, convalidated, conislocal, coninhcount
+FROM pg_constraint
+WHERE conrelid = ANY($1)
+ORDER BY conrelid::regclass::text, conname;
+
+CREATE TABLE notnull_tbl1 (a int, b int);
+INSERT INTO notnull_tbl1 VALUES (NULL, 1), (300, 3);
+ALTER TABLE notnull_tbl1 ADD CONSTRAINT nn NOT NULL a; -- error
+ALTER TABLE notnull_tbl1 ADD CONSTRAINT nn NOT NULL a NOT VALID; -- ok
+-- even an invalid not-null forbids new nulls
+INSERT INTO notnull_tbl1 VALUES (NULL, 4);
+\d+ notnull_tbl1
+
+-- If we have an invalid constraint, we can't have another
+ALTER TABLE notnull_tbl1 ADD CONSTRAINT nn1 NOT NULL a NOT VALID NO INHERIT;
+ALTER TABLE notnull_tbl1 ADD CONSTRAINT nn NOT NULL a;
+
+-- cannot add primary key on a column with an invalid not-null
+ALTER TABLE notnull_tbl1 ADD PRIMARY KEY (a);
+
+-- ALTER column SET NOT NULL validates an invalid constraint (but this fails
+-- because of rows with null values)
+ALTER TABLE notnull_tbl1 ALTER a SET NOT NULL;
+\d+ notnull_tbl1
+
+-- Creating a derived table using LIKE gets the constraint, but it's valid
+CREATE TABLE notnull_tbl1_copy (LIKE notnull_tbl1);
+EXECUTE get_nnconstraint_info('{notnull_tbl1_copy}');
+
+-- An inheritance child table gets the constraint, but it's valid
+CREATE TABLE notnull_tbl1_child (a int, b int) INHERITS (notnull_tbl1);
+EXECUTE get_nnconstraint_info('{notnull_tbl1_child, notnull_tbl1}');
+
+-- Also try inheritance added after table creation
+CREATE TABLE notnull_tbl1_child2 (c int, b int, a int);
+ALTER TABLE notnull_tbl1_child2 INHERIT notnull_tbl1; -- nope
+ALTER TABLE notnull_tbl1_child2 ADD NOT NULL a NOT VALID;
+ALTER TABLE notnull_tbl1_child2 INHERIT notnull_tbl1;
+EXECUTE get_nnconstraint_info('{notnull_tbl1_child2}');
+
+--table rewrite won't validate invalid constraint
+ALTER TABLE notnull_tbl1 ADD column d float8 default random();
+
+-- VALIDATE CONSTRAINT scans the table
+ALTER TABLE notnull_tbl1 VALIDATE CONSTRAINT nn; -- error, nulls exist
+UPDATE notnull_tbl1 SET a = 100 WHERE b = 1;
+ALTER TABLE notnull_tbl1 VALIDATE CONSTRAINT nn; -- now ok
+EXECUTE get_nnconstraint_info('{notnull_tbl1}');
+
+--- now we can add primary key
+ALTER TABLE notnull_tbl1 ADD PRIMARY KEY (a);
+DROP TABLE notnull_tbl1, notnull_tbl1_child, notnull_tbl1_child2;
+
+-- dropping an invalid constraint is possible
+CREATE TABLE notnull_tbl1 (a int, b int);
+ALTER TABLE notnull_tbl1 ADD NOT NULL a NOT VALID,
+ ADD NOT NULL b NOT VALID;
+ALTER TABLE notnull_tbl1 ALTER a DROP NOT NULL;
+ALTER TABLE notnull_tbl1 DROP CONSTRAINT notnull_tbl1_b_not_null;
+DROP TABLE notnull_tbl1;
+
+-- ALTER .. NO INHERIT works for invalid constraints
+CREATE TABLE notnull_tbl1 (a int);
+CREATE TABLE notnull_tbl1_chld () INHERITS (notnull_tbl1);
+ALTER TABLE notnull_tbl1 ADD CONSTRAINT nntbl1_a NOT NULL a NOT VALID;
+ALTER TABLE notnull_tbl1 ALTER CONSTRAINT nntbl1_a NO INHERIT;
+
+-- DROP CONSTRAINT recurses correctly on invalid constraints
+ALTER TABLE notnull_tbl1 ALTER CONSTRAINT nntbl1_a INHERIT;
+ALTER TABLE notnull_tbl1 DROP CONSTRAINT nntbl1_a;
+DROP TABLE notnull_tbl1, notnull_tbl1_chld;
+
+-- if a parent has a valid not null constraint then a child table cannot
+-- have an invalid one
+CREATE TABLE notnull_tbl1 (a int);
+ALTER TABLE notnull_tbl1 ADD CONSTRAINT nn_parent NOT NULL a not valid;
+CREATE TABLE notnull_chld0 (a int, CONSTRAINT nn_chld0 NOT NULL a);
+ALTER TABLE notnull_tbl1 INHERIT notnull_chld0; --error
+
+ALTER TABLE notnull_chld0 DROP CONSTRAINT nn_chld0;
+ALTER TABLE notnull_chld0 ADD CONSTRAINT nn_chld0 NOT NULL a not valid;
+ALTER TABLE notnull_tbl1 INHERIT notnull_chld0; --now ok
+
+-- parents and child not-null will all be validated.
+ALTER TABLE notnull_tbl1 VALIDATE CONSTRAINT nn_parent;
+EXECUTE get_nnconstraint_info('{notnull_tbl1, notnull_chld0}');
+DROP TABLE notnull_tbl1, notnull_chld0;
+
+-- Test invalid not null on inheritance table.
+CREATE TABLE notnull_inhparent (i int);
+CREATE TABLE notnull_inhchild (i int) INHERITS (notnull_inhparent);
+CREATE TABLE notnull_inhgrand () INHERITS (notnull_inhparent, notnull_inhchild);
+ALTER TABLE notnull_inhparent ADD CONSTRAINT nn NOT NULL i NOT VALID;
+ALTER TABLE notnull_inhchild ADD CONSTRAINT nn1 NOT NULL i; -- error
+EXECUTE get_nnconstraint_info('{notnull_inhparent, notnull_inhchild, notnull_inhgrand}');
+ALTER TABLE notnull_inhparent ALTER i SET NOT NULL; -- ok
+EXECUTE get_nnconstraint_info('{notnull_inhparent, notnull_inhchild, notnull_inhgrand}');
+DROP TABLE notnull_inhparent, notnull_inhchild, notnull_inhgrand;
+
+-- Verify NOT NULL VALID/NOT VALID with partition table.
+DROP TABLE notnull_tbl1;
+CREATE TABLE notnull_tbl1 (a int, b int) PARTITION BY LIST (a);
+ALTER TABLE notnull_tbl1 ADD CONSTRAINT notnull_con NOT NULL a NOT VALID; --ok
+CREATE TABLE notnull_tbl1_1 PARTITION OF notnull_tbl1 FOR VALUES IN (1,2);
+CREATE TABLE notnull_tbl1_2(a int, CONSTRAINT nn2 NOT NULL a, b int);
+ALTER TABLE notnull_tbl1 ATTACH PARTITION notnull_tbl1_2 FOR VALUES IN (3,4);
+
+CREATE TABLE notnull_tbl1_3(a int, b int);
+INSERT INTO notnull_tbl1_3 values(NULL,1);
+ALTER TABLE notnull_tbl1_3 add CONSTRAINT nn3 NOT NULL a NOT VALID;
+ALTER TABLE notnull_tbl1 ATTACH PARTITION notnull_tbl1_3 FOR VALUES IN (NULL,5);
+
+EXECUTE get_nnconstraint_info('{notnull_tbl1, notnull_tbl1_1, notnull_tbl1_2, notnull_tbl1_3}');
+ALTER TABLE notnull_tbl1 ALTER COLUMN a SET NOT NULL; --error, notnull_tbl1_3 have null values
+ALTER TABLE notnull_tbl1_3 VALIDATE CONSTRAINT nn3; --error
+
+TRUNCATE notnull_tbl1;
+ALTER TABLE notnull_tbl1 ALTER COLUMN a SET NOT NULL; --OK
+
+EXECUTE get_nnconstraint_info('{notnull_tbl1, notnull_tbl1_1, notnull_tbl1_2, notnull_tbl1_3}');
+DROP TABLE notnull_tbl1;
+
+-- partitioned table have not-null, then the partitions can not be NOT NULL NOT VALID.
+CREATE TABLE pp_nn (a int, b int, NOT NULL a) PARTITION BY LIST (a);
+CREATE TABLE pp_nn_1(a int, b int);
+ALTER TABLE pp_nn_1 ADD CONSTRAINT nn1 NOT NULL a NOT VALID;
+ALTER TABLE pp_nn ATTACH PARTITION pp_nn_1 FOR VALUES IN (NULL,5); --error
+ALTER TABLE pp_nn_1 VALIDATE CONSTRAINT nn1;
+ALTER TABLE pp_nn ATTACH PARTITION pp_nn_1 FOR VALUES IN (NULL,5); --ok
+DROP TABLE pp_nn;
+
+-- Create table with NOT NULL INVALID constraint, for pg_upgrade.
+CREATE TABLE notnull_tbl1_upg (a int, b int);
+INSERT INTO notnull_tbl1_upg VALUES (NULL, 1), (NULL, 2), (300, 3);
+ALTER TABLE notnull_tbl1_upg ADD CONSTRAINT nn NOT NULL a NOT VALID;
+-- Inherit test for pg_upgrade
+CREATE TABLE notnull_parent_upg (a int);
+CREATE TABLE notnull_child_upg () INHERITS (notnull_parent_upg);
+ALTER TABLE notnull_child_upg ADD CONSTRAINT nn NOT NULL a;
+ALTER TABLE notnull_parent_upg ADD CONSTRAINT nn NOT NULL a NOT VALID;
+SELECT conrelid::regclass, contype, convalidated, conislocal
+FROM pg_catalog.pg_constraint
+WHERE conrelid in ('notnull_parent_upg'::regclass, 'notnull_child_upg'::regclass)
+ORDER BY 1;
+
+-- Partition table test, for pg_upgrade
+CREATE TABLE notnull_part1_upg (a int, b int) PARTITION BY LIST (a);
+ALTER TABLE notnull_part1_upg ADD CONSTRAINT notnull_con NOT NULL a NOT VALID; --ok
+CREATE TABLE notnull_part1_1_upg PARTITION OF notnull_part1_upg FOR VALUES IN (1,2);
+CREATE TABLE notnull_part1_2_upg (a int, CONSTRAINT nn2 NOT NULL a, b int);
+ALTER TABLE notnull_part1_upg ATTACH PARTITION notnull_part1_2_upg FOR VALUES IN (3,4);
+CREATE TABLE notnull_part1_3_upg (a int, b int);
+INSERT INTO notnull_part1_3_upg values(NULL,1);
+ALTER TABLE notnull_part1_3_upg add CONSTRAINT nn3 NOT NULL a NOT VALID;
+ALTER TABLE notnull_part1_upg ATTACH PARTITION notnull_part1_3_upg FOR VALUES IN (NULL,5);
+EXECUTE get_nnconstraint_info('{notnull_part1_upg, notnull_part1_1_upg, notnull_part1_2_upg, notnull_part1_3_upg}');
+DEALLOCATE get_nnconstraint_info;
+
+-- end NOT NULL NOT VALID
+
+
-- Comments
-- Setup a low-level role to enforce non-superuser checks.
CREATE ROLE regress_constraint_comments;
generated by cgit v1.2.3 (git 2.25.1) at 2025-05-31 07:50:58 +0000