public inbox for [email protected]
help / color / mirror / Atom feedFrom: Rushabh Lathia <[email protected]>
To: Alvaro Herrera <[email protected]>
Cc: PostgreSQL Hackers <[email protected]>
Subject: Re: Support NOT VALID / VALIDATE constraint options for named NOT NULL constraints
Date: Tue, 11 Mar 2025 15:45:46 +0530
Message-ID: <CAGPqQf31ctDvcbJE7J3in++iCRbf7-Rv07F7dpnu-_GyLWFz+w@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <CAGPqQf2C+KyLkRQ3ToBx2BC-URG2LQDQ1LX0tu7_jvzTJ_caMg@mail.gmail.com>
<[email protected]>
On Mon, Mar 10, 2025 at 5:20 PM Alvaro Herrera <[email protected]>
wrote:
> On 2025-Mar-10, Rushabh Lathia wrote:
>
> > I adjusted the set_attnotnull() API and removed the added
> > queue_validation parameter. Rather, the function start using wqueue
> > input parameter as a check.
> > If wqueue is NULL, skip the queue_validation. Attaching patch here,
> > but not sure how clear it is, in term of understanding the API. Your
> > thoughts/inputs?
>
> Yeah, I think this makes sense, if it supports all the cases correctly.
> (If in your code you have any calls of set_attnotnull that pass a NULL
> wqueue during ALTER TABLE in order to avoid queueing a check, you had
> better have comments to explain this.)
>
Done.
>
> Kindly do not send patches standalone, because the CFbot doesn't
> understand that it needs to apply it on top of the three previous
> patches. You need to send all 4 patches every time. You can see the
> result here:
> https://commitfest.postgresql.org/patch/5554/
> If you click on the "need rebase!" label you'll see that it tried to
> apply patch 0004 to the top of the master branch, and that obviously
> failed. (FWIW if you click on the "summary" label you'll also see that
> the patch has been failing the CI tests since Feb 27.)
>
Sure, attaching the rebased patch here.
>
> > Looking further for AdjustNotNullInheritance() I don't see need of
> > rename this API as it's just adding new error check now for an
> > existing NOT NULL constraint.
>
> OK.
>
> > JFYI, I can reproduce the failure Ashutosh Bapat reported, while
> > running the pg_upgrade test through meson commands. I am
> > investigating that further.
>
> Ah good, thanks.
>
Somehow, I am now not able to reproduce after the clean build. Yesterday
I was able to reproduce, so I was happy, but again trying to analyze the
issue
when I start with the
Thanks,
Rushabh Lathia
www.EnterpriseDB.com
Attachments:
[application/octet-stream] 0002-Support-NOT-VALID-and-VALIDATE-CONSTRAINT-for-named-.patch (47.8K, ../CAGPqQf31ctDvcbJE7J3in++iCRbf7-Rv07F7dpnu-_GyLWFz+w@mail.gmail.com/3-0002-Support-NOT-VALID-and-VALIDATE-CONSTRAINT-for-named-.patch)
download | inline diff:
From aee75c8f0b5b7e1f5ccd83ab62a631611fab72f9 Mon Sep 17 00:00:00 2001
From: Rushabh Lathia <[email protected]>
Date: Mon, 10 Feb 2025 16:22:30 +0530
Subject: [PATCH 2/3] Support NOT VALID and VALIDATE CONSTRAINT for named NOT
NULL constraints.
Commit also add support for pg_dump to dump NOT VALID named NOT NULL
constraints.
---
src/backend/bootstrap/bootstrap.c | 4 +-
src/backend/catalog/heap.c | 2 +-
src/backend/catalog/pg_constraint.c | 31 ++-
src/backend/commands/tablecmds.c | 249 ++++++++++++++++++----
src/backend/executor/execMain.c | 3 +-
src/backend/parser/gram.y | 4 +-
src/bin/pg_dump/pg_dump.c | 169 +++++++++++++--
src/bin/pg_dump/pg_dump.h | 1 +
src/bin/pg_dump/t/002_pg_dump.pl | 17 ++
src/bin/psql/describe.c | 10 +-
src/include/catalog/pg_attribute.h | 1 +
src/include/catalog/pg_constraint.h | 3 +-
src/test/regress/expected/constraints.out | 153 +++++++++++++
src/test/regress/sql/constraints.sql | 85 ++++++++
14 files changed, 659 insertions(+), 73 deletions(-)
diff --git a/src/backend/bootstrap/bootstrap.c b/src/backend/bootstrap/bootstrap.c
index 1e95dc32f46..919972dc409 100644
--- a/src/backend/bootstrap/bootstrap.c
+++ b/src/backend/bootstrap/bootstrap.c
@@ -582,10 +582,8 @@ DefineAttr(char *name, char *type, int attnum, int nullness)
attrtypes[attnum]->atttypmod = -1;
attrtypes[attnum]->attislocal = true;
- /* set default to false */
attrtypes[attnum]->attnotnull = ATTRIBUTE_NOTNULL_FALSE;
-
if (nullness == BOOTCOL_NULL_FORCE_NOT_NULL)
{
attrtypes[attnum]->attnotnull = ATTRIBUTE_NOTNULL_TRUE;
@@ -699,7 +697,7 @@ InsertOneNull(int i)
{
elog(DEBUG4, "inserting column %d NULL", i);
Assert(i >= 0 && i < MAXATTR);
- if (TupleDescAttr(boot_reldesc->rd_att, i)->attnotnull == ATTRIBUTE_NOTNULL_TRUE)
+ if (TupleDescAttr(boot_reldesc->rd_att, i)->attnotnull != ATTRIBUTE_NOTNULL_FALSE)
elog(ERROR,
"NULL value specified for not-null column \"%s\" of relation \"%s\"",
NameStr(TupleDescAttr(boot_reldesc->rd_att, i)->attname),
diff --git a/src/backend/catalog/heap.c b/src/backend/catalog/heap.c
index 8c2d2cdcdfe..6458f78c216 100644
--- a/src/backend/catalog/heap.c
+++ b/src/backend/catalog/heap.c
@@ -2626,7 +2626,7 @@ AddRelationNewConstraints(Relation rel,
* to add another one; just adjust inheritance status as needed.
*/
if (AdjustNotNullInheritance(RelationGetRelid(rel), colnum,
- is_local, cdef->is_no_inherit))
+ cdef->conname, is_local, cdef->is_no_inherit))
continue;
/*
diff --git a/src/backend/catalog/pg_constraint.c b/src/backend/catalog/pg_constraint.c
index ac80652baf2..e14041b34ca 100644
--- a/src/backend/catalog/pg_constraint.c
+++ b/src/backend/catalog/pg_constraint.c
@@ -574,7 +574,7 @@ 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.
+ * 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
@@ -604,13 +604,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)
@@ -628,9 +626,9 @@ 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 the pg_constraint tuple that implements a
+ * 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)
@@ -726,10 +724,13 @@ extractNotNullColumn(HeapTuple constrTup)
* 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.
+ *
+ * Function add a check for existing INVALID not-null constraint on
+ * the table column.
*/
bool
AdjustNotNullInheritance(Oid relid, AttrNumber attnum,
- bool is_local, bool is_no_inherit)
+ const char *conname, bool is_local, bool is_no_inherit)
{
HeapTuple tup;
@@ -753,6 +754,18 @@ 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 an invalid NOT NULL constraint exists on the
+ * table column and an attempt is made to add another valid NOT NULL
+ * constraint.
+ */
+ if (is_local && !conform->convalidated && conname)
+ ereport(ERROR,
+ errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
+ errmsg("Invalid NOT NULL constraint \"%s\" exist on relation \"%s\"",
+ NameStr(conform->conname), get_rel_name(relid)),
+ errhint("Do VALIDATE CONSTRAINT"));
+
if (!is_local)
{
if (pg_add_s16_overflow(conform->coninhcount, 1,
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 3c81e48cac1..6a757c7c374 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -410,6 +410,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,
@@ -473,7 +476,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);
+ char newvalue, LOCKMODE lockmode);
static ObjectAddress ATExecSetNotNull(List **wqueue, Relation rel,
char *constrname, char *colName,
bool recurse, bool recursing,
@@ -710,6 +713,7 @@ static List *GetParentedForeignKeyRefs(Relation partition);
static void ATDetachCheckNoForeignKeyRefs(Relation partition);
static char GetAttributeCompression(Oid atttypid, const char *compression);
static char GetAttributeStorage(Oid atttypid, const char *storagemode);
+static bool check_for_invalid_notnull(Oid relid, const char *attname);
/* ----------------------------------------------------------------
@@ -1315,7 +1319,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, ATTRIBUTE_NOTNULL_TRUE, NoLock);
ObjectAddressSet(address, RelationRelationId, relationId);
@@ -6191,7 +6195,8 @@ ATRewriteTable(AlteredTableInfo *tab, Oid OIDNewHeap)
{
Form_pg_attribute attr = TupleDescAttr(newTupDesc, i);
- if (attr->attnotnull == ATTRIBUTE_NOTNULL_TRUE &&
+ if ((attr->attnotnull == ATTRIBUTE_NOTNULL_TRUE ||
+ attr->attnotnull == ATTRIBUTE_NOTNULL_INVALID) &&
!attr->attisdropped)
notnull_attrs = lappend_int(notnull_attrs, i);
}
@@ -7743,10 +7748,11 @@ ATExecDropNotNull(Relation rel, const char *colName, bool recurse,
*/
static void
set_attnotnull(List **wqueue, Relation rel, AttrNumber attnum,
- LOCKMODE lockmode)
+ char newvalue, LOCKMODE lockmode)
{
Form_pg_attribute attr;
+
CheckAlterTableIsSafe(rel);
/*
@@ -7757,7 +7763,7 @@ set_attnotnull(List **wqueue, Relation rel, AttrNumber attnum,
if (attr->attisdropped)
return;
- if (attr->attnotnull == ATTRIBUTE_NOTNULL_FALSE)
+ if (attr->attnotnull != newvalue)
{
Relation attr_rel;
HeapTuple tuple;
@@ -7770,15 +7776,17 @@ set_attnotnull(List **wqueue, Relation rel, AttrNumber attnum,
attnum, RelationGetRelid(rel));
attr = (Form_pg_attribute) GETSTRUCT(tuple);
- Assert(attr->attnotnull == ATTRIBUTE_NOTNULL_FALSE);
- attr->attnotnull = ATTRIBUTE_NOTNULL_TRUE;
+ attr->attnotnull = newvalue;
+
CatalogTupleUpdate(attr_rel, &tuple->t_self, tuple);
/*
- * If the nullness isn't already proven by validated constraints, have
- * ALTER TABLE phase 3 test for it.
+ * Queue later validation of this constraint, if necessary and
+ * requested by caller.
*/
- if (wqueue && !NotNullImpliedByRelConstraints(rel, attr))
+ if (wqueue &&
+ newvalue == ATTRIBUTE_NOTNULL_TRUE &&
+ !NotNullImpliedByRelConstraints(rel, attr))
{
AlteredTableInfo *tab;
@@ -7884,6 +7892,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, conForm->conname.data,
+ recurse, recursing, lockmode);
+ }
if (changed)
{
@@ -7946,8 +7963,12 @@ 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 request validation */
+ set_attnotnull(wqueue, rel, attnum,
+ constraint->skip_validation ?
+ ATTRIBUTE_NOTNULL_INVALID :
+ ATTRIBUTE_NOTNULL_TRUE,
+ lockmode);
/*
* Recurse to propagate the constraint to children that don't have one.
@@ -9387,12 +9408,19 @@ ATPrepAddPrimaryKey(List **wqueue, Relation rel, AlterTableCmd *cmd,
}
/* Insert not-null constraints in the queue for the PK columns */
- foreach(lc, pkconstr->keys)
+ foreach_node(String, colname, pkconstr->keys)
{
AlterTableCmd *newcmd;
Constraint *nnconstr;
- nnconstr = makeNotNullConstraint(lfirst(lc));
+ /* Verify that the not-null constraint has been validated */
+ if (check_for_invalid_notnull(RelationGetRelid(rel), strVal(colname)))
+ ereport(ERROR,
+ errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
+ errmsg("column \"%s\" of table \"%s\" is marked as NOT VALID NOT NULL constraint",
+ strVal(colname), RelationGetRelationName(rel)));
+
+ nnconstr = makeNotNullConstraint(colname);
newcmd = makeNode(AlterTableCmd);
newcmd->subtype = AT_AddConstraint;
@@ -9403,6 +9431,30 @@ ATPrepAddPrimaryKey(List **wqueue, Relation rel, AlterTableCmd *cmd,
}
}
+/*
+ * This function checks for any invalid NOT NULL constraint on the given
+ * relation and attribute name. It returns true if found, false otherwise.
+ */
+static bool
+check_for_invalid_notnull(Oid relid, const char *attname)
+{
+ HeapTuple tuple;
+ bool retval = false;
+
+ tuple = SearchSysCache2(ATTNAME,
+ ObjectIdGetDatum(relid),
+ CStringGetDatum(attname));
+ if (!HeapTupleIsValid(tuple))
+ return false;
+ if (!((Form_pg_attribute) GETSTRUCT(tuple))->attisdropped &&
+ ((Form_pg_attribute) GETSTRUCT(tuple))->attnotnull == ATTRIBUTE_NOTNULL_INVALID)
+ retval = true;
+
+ ReleaseSysCache(tuple);
+
+ return retval;
+}
+
/*
* ALTER TABLE ADD INDEX
*
@@ -9770,7 +9822,11 @@ ATAddCheckNNConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel,
* phase 3 to verify existing rows, if needed.
*/
if (constr->contype == CONSTR_NOTNULL)
- set_attnotnull(wqueue, rel, ccon->attnum, lockmode);
+ set_attnotnull(wqueue, rel, ccon->attnum,
+ ccon->skip_validation ?
+ ATTRIBUTE_NOTNULL_INVALID :
+ ATTRIBUTE_NOTNULL_TRUE,
+ lockmode);
ObjectAddressSet(address, ConstraintRelationId, ccon->conoid);
}
@@ -12371,10 +12427,11 @@ 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",
+ errmsg("constraint \"%s\" of relation \"%s\" is not a foreign key, not-null, or check constraint",
constrName, RelationGetRelationName(rel))));
if (!con->conenforced)
@@ -12393,6 +12450,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);
}
@@ -12522,9 +12584,7 @@ QueueCheckConstraintValidation(List **wqueue, Relation conrel, Relation rel,
AlteredTableInfo *tab;
HeapTuple copyTuple;
Form_pg_constraint copy_con;
-
List *children = NIL;
- ListCell *child;
NewConstraint *newcon;
Datum val;
char *conbin;
@@ -12533,24 +12593,19 @@ QueueCheckConstraintValidation(List **wqueue, Relation conrel, Relation rel,
Assert(con->contype == CONSTRAINT_CHECK);
/*
- * If we're recursing, the parent has already done this, so skip it. Also,
- * if the constraint is a NO INHERIT constraint, we shouldn't try to look
- * for it in the children.
- */
- if (!recursing && !con->connoinherit)
- children = find_all_inheritors(RelationGetRelid(rel),
- lockmode, NULL);
-
- /*
- * For CHECK constraints, we must ensure that we only mark the constraint
- * as validated on the parent if it's already validated on the children.
+ * For constraints that aren't NO INHERIT, we must ensure that we only
+ * mark the constraint as validated on the parent if it's already
+ * validated on the children.
+ *
+ * If we're recursing, the parent has already done this, so skip it.
*
* We recurse before validating on the parent, to reduce risk of
* deadlocks.
*/
- foreach(child, children)
+ if (!recursing && !con->connoinherit)
+ children = find_all_inheritors(RelationGetRelid(rel), lockmode, NULL);
+ foreach_oid(childoid, children)
{
- Oid childoid = lfirst_oid(child);
Relation childrel;
if (childoid == RelationGetRelid(rel))
@@ -12609,6 +12664,114 @@ QueueCheckConstraintValidation(List **wqueue, Relation conrel, Relation rel,
heap_freetuple(copyTuple);
}
+/*
+ * 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);
+
+ /*
+ * For constraints that aren't NO INHERIT, we must ensure that we only
+ * mark the constraint as validated on the parent if it's already
+ * validated on the children.
+ *
+ * If we're recursing, the parent has already done this, so skip it.
+ *
+ * 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);
+ }
+
+ 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, ©Tuple->t_self, copyTuple);
+
+ /*
+ * Also flip attnotnull. call function with wqueue as NULL to
+ * bypass validation, as it has already been performed.
+ */
+ set_attnotnull(NULL, rel, attnum, ATTRIBUTE_NOTNULL_TRUE, lockmode);
+
+ InvokeObjectPostAlterHook(ConstraintRelationId, con->oid, 0);
+
+ heap_freetuple(copyTuple);
+}
+
/*
* transformColumnNameList - transform list of column names
*
@@ -13480,7 +13643,7 @@ dropconstraint_internal(Relation rel, HeapTuple constraintTup, DropBehavior beha
RelationGetRelationName(rel)));
/* All good -- reset attnotnull if needed */
- if (attForm->attnotnull == ATTRIBUTE_NOTNULL_TRUE)
+ if (attForm->attnotnull != ATTRIBUTE_NOTNULL_FALSE)
{
attForm->attnotnull = ATTRIBUTE_NOTNULL_FALSE;
CatalogTupleUpdate(attrel, &atttup->t_self, atttup);
@@ -16816,12 +16979,20 @@ MergeAttributesIntoExisting(Relation child_rel, Relation parent_rel, bool ispart
contup = findNotNullConstraintAttnum(RelationGetRelid(parent_rel),
parent_att->attnum);
- if (HeapTupleIsValid(contup) &&
- !((Form_pg_constraint) GETSTRUCT(contup))->connoinherit)
- ereport(ERROR,
- errcode(ERRCODE_DATATYPE_MISMATCH),
- errmsg("column \"%s\" in child table \"%s\" must be marked NOT NULL",
- parent_attname, RelationGetRelationName(child_rel)));
+ if (HeapTupleIsValid(contup))
+ {
+ Form_pg_constraint childcon;
+
+ childcon = (Form_pg_constraint) GETSTRUCT(contup);
+ if (!childcon->connoinherit)
+ ereport(ERROR,
+ errcode(ERRCODE_DATATYPE_MISMATCH),
+ errmsg("column \"%s\" in child table \"%s\" must be marked NOT NULL",
+ parent_attname, RelationGetRelationName(child_rel)));
+
+ if (!childcon->convalidated)
+ elog(WARNING, "found an invalid constraint");
+ }
}
/*
diff --git a/src/backend/executor/execMain.c b/src/backend/executor/execMain.c
index d582231ab60..4b0446b00d8 100644
--- a/src/backend/executor/execMain.c
+++ b/src/backend/executor/execMain.c
@@ -2070,7 +2070,8 @@ ExecConstraints(ResultRelInfo *resultRelInfo,
{
Form_pg_attribute att = TupleDescAttr(tupdesc, attrChk - 1);
- if (att->attnotnull == ATTRIBUTE_NOTNULL_TRUE &&
+ if ((att->attnotnull == ATTRIBUTE_NOTNULL_TRUE ||
+ att->attnotnull == ATTRIBUTE_NOTNULL_INVALID) &&
slot_attisnull(slot, attrChk))
{
char *val_desc;
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 271ae26cbaf..715eb51300b 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -4224,9 +4224,9 @@ ConstraintElem:
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/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index c371570501a..1a946461219 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -8876,6 +8876,7 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables)
PQExpBuffer q = createPQExpBuffer();
PQExpBuffer tbloids = createPQExpBuffer();
PQExpBuffer checkoids = createPQExpBuffer();
+ PQExpBuffer invalidnotnulloids = createPQExpBuffer();
PGresult *res;
int ntups;
int curtblindx;
@@ -8892,6 +8893,7 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables)
int i_attlen;
int i_attalign;
int i_attislocal;
+ int i_notnull_valid;
int i_notnull_name;
int i_notnull_noinherit;
int i_notnull_islocal;
@@ -8988,11 +8990,13 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables)
*/
if (fout->remoteVersion >= 180000)
appendPQExpBufferStr(q,
+ "CASE WHEN a.attnotnull = 'i' THEN 'f' ELSE 't' END AS notnull_valid,\n"
"co.conname AS notnull_name,\n"
"co.connoinherit AS notnull_noinherit,\n"
"co.conislocal AS notnull_islocal,\n");
else
appendPQExpBufferStr(q,
+ "'t' AS notnull_valid,\n"
"CASE WHEN a.attnotnull THEN '' ELSE NULL END AS notnull_name,\n"
"false AS notnull_noinherit,\n"
"a.attislocal AS notnull_islocal,\n");
@@ -9067,6 +9071,7 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables)
i_attlen = PQfnumber(res, "attlen");
i_attalign = PQfnumber(res, "attalign");
i_attislocal = PQfnumber(res, "attislocal");
+ i_notnull_valid = PQfnumber(res, "notnull_valid");
i_notnull_name = PQfnumber(res, "notnull_name");
i_notnull_noinherit = PQfnumber(res, "notnull_noinherit");
i_notnull_islocal = PQfnumber(res, "notnull_islocal");
@@ -9086,6 +9091,7 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables)
* r is handled by the inner loop.
*/
curtblindx = -1;
+ appendPQExpBufferChar(invalidnotnulloids, '{');
for (int r = 0; r < ntups;)
{
Oid attrelid = atooid(PQgetvalue(res, r, i_attrelid));
@@ -9134,6 +9140,7 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables)
tbinfo->attcompression = (char *) pg_malloc(numatts * sizeof(char));
tbinfo->attfdwoptions = (char **) pg_malloc(numatts * sizeof(char *));
tbinfo->attmissingval = (char **) pg_malloc(numatts * sizeof(char *));
+ tbinfo->notnull_valid = (bool *) pg_malloc(numatts * sizeof(bool));
tbinfo->notnull_constrs = (char **) pg_malloc(numatts * sizeof(char *));
tbinfo->notnull_noinh = (bool *) pg_malloc(numatts * sizeof(bool));
tbinfo->notnull_islocal = (bool *) pg_malloc(numatts * sizeof(bool));
@@ -9160,12 +9167,29 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables)
tbinfo->attlen[j] = atoi(PQgetvalue(res, r, i_attlen));
tbinfo->attalign[j] = *(PQgetvalue(res, r, i_attalign));
tbinfo->attislocal[j] = (PQgetvalue(res, r, i_attislocal)[0] == 't');
+ tbinfo->notnull_valid[j] = (PQgetvalue(res, r, i_notnull_valid)[0] == 't');
- /* Handle not-null constraint name and flags */
- determineNotNullFlags(fout, res, r,
- tbinfo, j,
- i_notnull_name, i_notnull_noinherit,
- i_notnull_islocal);
+ /*
+ * Dump the invalid NOT NULL constraint like the Check constraints
+ */
+ if (tbinfo->notnull_valid[j])
+ /* Handle not-null constraint name and flags */
+ determineNotNullFlags(fout, res, r,
+ tbinfo, j,
+ i_notnull_name, i_notnull_noinherit,
+ i_notnull_islocal);
+ else if (!PQgetisnull(res, r, i_notnull_name))
+ {
+ /*
+ * Add the entry into invalidnotnull list so NOT NULL
+ * constraint get dump as separate constraints.
+ */
+ if (invalidnotnulloids->len > 1) /* do we have more than
+ * the '{'? */
+ appendPQExpBufferChar(invalidnotnulloids, ',');
+ appendPQExpBuffer(invalidnotnulloids, "%u", tbinfo->dobj.catId.oid);
+
+ }
tbinfo->attoptions[j] = pg_strdup(PQgetvalue(res, r, i_attoptions));
tbinfo->attcollation[j] = atooid(PQgetvalue(res, r, i_attcollation));
@@ -9187,6 +9211,7 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables)
}
PQclear(res);
+ appendPQExpBufferChar(invalidnotnulloids, '}');
/*
* Now get info about column defaults. This is skipped for a data-only
@@ -9450,6 +9475,128 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables)
PQclear(res);
}
+ /*
+ * Get info about table INVALID NOT NULL constraints. This is skipped for
+ * a data-only dump, as it is only needed for table schemas.
+ */
+ if (dopt->dumpSchema && invalidnotnulloids->len > 2)
+ {
+ ConstraintInfo *constrs;
+ int numConstrs;
+ int i_tableoid;
+ int i_oid;
+ int i_conrelid;
+ int i_conname;
+ int i_consrc;
+ int i_conislocal;
+ int i_convalidated;
+
+ pg_log_info("finding table 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(tbloid)\n"
+ "JOIN pg_catalog.pg_constraint c ON (src.tbloid = c.conrelid)\n"
+ "WHERE contype = 'n' AND convalidated = 'f'"
+ "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");
+ i_convalidated = PQfnumber(res, "convalidated");
+
+ /* 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);
+
+
+ tbinfo->checkexprs = constrs + j;
+
+ for (int c = 0; c < numcons; c++, j++)
+ {
+ bool validated = PQgetvalue(res, j, i_convalidated)[0] == 't';
+
+ 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');
+
+ /*
+ * An unvalidated constraint needs to be dumped separately, so
+ * that potentially-violating existing data is loaded before
+ * the constraint.
+ */
+ constrs[j].separate = !validated;
+
+ constrs[j].dobj.dump = tbinfo->dobj.dump;
+
+ /*
+ * Mark the constraint as needing to appear before the table
+ * --- this is so that any other dependencies of the
+ * constraint will be emitted before we try to create the
+ * table. If the constraint is to be dumped separately, it
+ * will be dumped after data is loaded anyway, so don't do it.
+ * (There's an automatic dependency in the opposite direction
+ * anyway, so don't need to add one manually here.)
+ */
+ if (!constrs[j].separate)
+ addObjectDependency(&tbinfo->dobj,
+ constrs[j].dobj.dumpId);
+
+ /*
+ * We will detect later whether the constraint must be split
+ * out from the table definition.
+ */
+ }
+ }
+ PQclear(res);
+ }
+
destroyPQExpBuffer(q);
destroyPQExpBuffer(tbloids);
destroyPQExpBuffer(checkoids);
@@ -16543,7 +16690,8 @@ dumpTableSchema(Archive *fout, const TableInfo *tbinfo)
* defined, or if binary upgrade. (In the latter case, we
* reset conislocal below.)
*/
- print_notnull = (tbinfo->notnull_constrs[j] != NULL &&
+ print_notnull = (tbinfo->notnull_valid[j] &&
+ tbinfo->notnull_constrs[j] != NULL &&
(tbinfo->notnull_islocal[j] ||
dopt->binary_upgrade ||
tbinfo->ispartition));
@@ -16605,11 +16753,6 @@ dumpTableSchema(Archive *fout, const TableInfo *tbinfo)
tbinfo->attrdefs[j]->adef_expr);
}
- print_notnull = (tbinfo->notnull_constrs[j] != NULL &&
- (tbinfo->notnull_islocal[j] ||
- dopt->binary_upgrade ||
- tbinfo->ispartition));
-
if (print_notnull)
{
if (tbinfo->notnull_constrs[j][0] == '\0')
@@ -17931,9 +18074,9 @@ 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/INVALID_NOTNULL constraint on a table */
/* Ignore if not to be dumped separately, or if it was inherited */
if (coninfo->separate && coninfo->conislocal)
diff --git a/src/bin/pg_dump/pg_dump.h b/src/bin/pg_dump/pg_dump.h
index bbdb30b5f54..90335d2bb0b 100644
--- a/src/bin/pg_dump/pg_dump.h
+++ b/src/bin/pg_dump/pg_dump.h
@@ -361,6 +361,7 @@ typedef struct _tableInfo
char *attcompression; /* per-attribute compression method */
char **attfdwoptions; /* per-attribute fdw options */
char **attmissingval; /* per attribute missing value */
+ bool *notnull_valid; /* NOT NULL status */
char **notnull_constrs; /* NOT NULL constraint names. If null,
* there isn't one on this column. If
* empty string, unnamed constraint
diff --git a/src/bin/pg_dump/t/002_pg_dump.pl b/src/bin/pg_dump/t/002_pg_dump.pl
index c7bffc1b045..5b5b756e512 100644
--- a/src/bin/pg_dump/t/002_pg_dump.pl
+++ b/src/bin/pg_dump/t/002_pg_dump.pl
@@ -1099,6 +1099,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 e6cf468ac9e..f48b24ed38c 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -2107,7 +2107,7 @@ describeOneTableDetails(const char *schemaname,
printTableAddCell(&cont, PQgetvalue(res, i, attcoll_col), false, false);
printTableAddCell(&cont,
- strcmp(PQgetvalue(res, i, attnotnull_col), "t") == 0 ? "not null" : "",
+ strcmp(PQgetvalue(res, i, attnotnull_col), "f") == 0 ? "" : "not null",
false, false);
identity = PQgetvalue(res, i, attidentity_col);
@@ -3114,7 +3114,7 @@ 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, 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"
@@ -3138,13 +3138,15 @@ describeOneTableDetails(const char *schemaname,
bool islocal = PQgetvalue(result, i, 3)[0] == 't';
bool inherited = PQgetvalue(result, i, 4)[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)") : "",
+ PQgetvalue(result, i, 5)[0] == 'f' ?
+ " NOT VALID " : "");
printTableAddFooter(&cont, buf.data);
}
diff --git a/src/include/catalog/pg_attribute.h b/src/include/catalog/pg_attribute.h
index b51a2670958..a01a0fa96d2 100644
--- a/src/include/catalog/pg_attribute.h
+++ b/src/include/catalog/pg_attribute.h
@@ -229,6 +229,7 @@ MAKE_SYSCACHE(ATTNUM, pg_attribute_relid_attnum_index, 128);
#define ATTRIBUTE_NOTNULL_TRUE 't'
#define ATTRIBUTE_NOTNULL_FALSE 'f'
+#define ATTRIBUTE_NOTNULL_INVALID 'i'
#endif /* EXPOSE_TO_CLIENT_CODE */
diff --git a/src/include/catalog/pg_constraint.h b/src/include/catalog/pg_constraint.h
index 6da164e7e4d..0e01ff1bbbd 100644
--- a/src/include/catalog/pg_constraint.h
+++ b/src/include/catalog/pg_constraint.h
@@ -264,7 +264,8 @@ extern HeapTuple findNotNullConstraint(Oid relid, const char *colname);
extern HeapTuple findDomainNotNullConstraint(Oid typid);
extern AttrNumber extractNotNullColumn(HeapTuple constrTup);
extern bool AdjustNotNullInheritance(Oid relid, AttrNumber attnum,
- bool is_local, bool is_no_inherit);
+ const char *conname, bool is_local,
+ bool is_no_inherit);
extern List *RelationGetNotNullConstraints(Oid relid, bool cooked,
bool include_noinh);
diff --git a/src/test/regress/expected/constraints.out b/src/test/regress/expected/constraints.out
index 4f39100fcdf..b52578bf8d3 100644
--- a/src/test/regress/expected/constraints.out
+++ b/src/test/regress/expected/constraints.out
@@ -896,6 +896,159 @@ Not-null constraints:
"foobar" NOT NULL "a"
DROP TABLE notnull_tbl1;
+-- verify NOT NULL VALID/NOT VALID
+CREATE TABLE notnull_tbl1 (a INTEGER, b INTEGER);
+INSERT INTO notnull_tbl1 VALUES (NULL, 1);
+INSERT INTO notnull_tbl1 VALUES (NULL, 2);
+INSERT INTO notnull_tbl1 VALUES (300, 3);
+-- Below statement should throw an error
+ALTER TABLE notnull_tbl1 ADD CONSTRAINT nn NOT NULL a;
+ERROR: column "a" of relation "notnull_tbl1" contains null values
+ALTER TABLE notnull_tbl1 ADD CONSTRAINT nn NOT NULL a NOT VALID;
+\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
+
+-- Try to insert new record with NULL, should throw an error
+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).
+-- SELECT NULL values for COLUMN a, should return 2 records.
+SELECT * FROM notnull_tbl1 WHERE a is NULL;
+ a | b
+---+---
+ | 1
+ | 2
+(2 rows)
+
+-- UPDATE the one of the NULL values
+UPDATE notnull_tbl1 SET a = 100 WHERE b = 1;
+-- DELETE the record (NULL, 2)
+DELETE FROM notnull_tbl1 WHERE b = 2;
+SELECT * FROM notnull_tbl1;
+ a | b
+-----+---
+ 300 | 3
+ 100 | 1
+(2 rows)
+
+-- Try to add primary key on table column marked as NOT VALID NOT NULL
+-- constraint. This should throw an error.
+ALTER TABLE notnull_tbl1 add primary key (a);
+ERROR: column "a" of table "notnull_tbl1" is marked as NOT VALID NOT NULL constraint
+-- INHERITS table having NOT VALID NOT NULL constraints.
+CREATE TABLE notnull_tbl1_child(a INTEGER, b INTEGER) INHERITS(notnull_tbl1);
+NOTICE: merging column "a" with inherited definition
+NOTICE: merging column "b" with inherited definition
+-- Child table NOT NULL constraints should be valid.
+SELECT conname, convalidated FROM pg_catalog.pg_constraint WHERE conrelid = 'notnull_tbl1_child'::regclass;
+ conname | convalidated
+---------+--------------
+ nn | t
+(1 row)
+
+DROP TABLE notnull_tbl1_child;
+ALTER TABLE notnull_tbl1 validate constraint nn;
+\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"
+
+DROP TABLE notnull_tbl1;
+-- Test the different Not null constraint name for parent and child table
+CREATE TABLE notnull_tbl1 (a int);
+ALTER TABLE notnull_tbl1 ADD CONSTRAINT nn_parent not null a not valid;
+CREATE TABLE notnull_chld (a int);
+ALTER TABLE notnull_chld ADD CONSTRAINT nn_child not null a not valid;
+ALTER TABLE notnull_chld INHERIT notnull_tbl1;
+-- This statement should validate not null constraint for parent as well as
+-- child.
+ALTER TABLE notnull_tbl1 VALIDATE CONSTRAINT nn_parent;
+SELECT conname, convalidated FROM pg_catalog.pg_constraint WHERE conrelid
+in ('notnull_tbl1'::regclass, 'notnull_chld'::regclass);
+ conname | convalidated
+-----------+--------------
+ nn_parent | t
+ nn_child | t
+(2 rows)
+
+DROP TABLE notnull_tbl1 CASCADE;
+NOTICE: drop cascades to table notnull_chld
+-- test to throw an error when trying to add another NOT NULL
+-- on the table column with INVALID NOT NULL constraint.
+CREATE TABLE notnull_tbl1 (a INTEGER);
+INSERT INTO notnull_tbl1 VALUES ( NULL );
+ALTER TABLE notnull_tbl1 ADD CONSTRAINT nn1 NOT NULL a not valid;
+-- Should throw an error
+ALTER TABLE notnull_tbl1 ADD CONSTRAINT nn NOT NULL a;
+ERROR: Invalid NOT NULL constraint "nn1" exist on relation "notnull_tbl1"
+HINT: Do VALIDATE CONSTRAINT
+-- This should throw an error while validating the NOT NULL
+-- constraint.
+ALTER TABLE notnull_tbl1 ALTER a SET NOT NULL;
+ERROR: column "a" of relation "notnull_tbl1" contains null values
+-- After deleting null values, SET NOT NULL should work
+DELETE FROM notnull_tbl1;
+ALTER TABLE notnull_tbl1 ALTER a SET NOT NULL;
+SELECT conname, convalidated FROM pg_catalog.pg_constraint WHERE conrelid
+in ('notnull_tbl1'::regclass);
+ conname | convalidated
+---------+--------------
+ nn1 | t
+(1 row)
+
+DROP TABLE notnull_tbl1;
+-- Test invalid not null on inheritance table.
+CREATE TABLE inh_parent ();
+CREATE TABLE inh_child (i int) INHERITS (inh_parent);
+CREATE TABLE inh_grandchild () INHERITS (inh_parent, inh_child);
+ALTER TABLE inh_parent ADD COLUMN i int;
+NOTICE: merging definition of column "i" for child "inh_child"
+NOTICE: merging definition of column "i" for child "inh_grandchild"
+ALTER TABLE inh_parent ADD CONSTRAINT nn NOT NULL i NOT VALID;
+ALTER TABLE inh_parent ALTER i SET NOT NULL;
+SELECT conrelid::regclass, conname, convalidated, coninhcount FROM pg_catalog.pg_constraint WHERE conrelid
+in ('inh_parent'::regclass, 'inh_child'::regclass, 'inh_grandchild'::regclass) ORDER BY 1;
+ conrelid | conname | convalidated | coninhcount
+----------------+---------+--------------+-------------
+ inh_parent | nn | t | 0
+ inh_child | nn | t | 1
+ inh_grandchild | nn | t | 2
+(3 rows)
+
+drop table inh_parent, inh_child, inh_grandchild;
+-- Create table with NOT NULL INVALID constraint, for pg_upgrade.
+CREATE TABLE notnull_tbl1_upg (a INTEGER, b INTEGER);
+INSERT INTO notnull_tbl1_upg VALUES (NULL, 1);
+INSERT INTO notnull_tbl1_upg VALUES (NULL, 2);
+INSERT INTO notnull_tbl1_upg VALUES (300, 3);
+ALTER TABLE notnull_tbl1_upg ADD CONSTRAINT nn NOT NULL a NOT VALID;
+-- Verify NOT NULL VALID/NOT VALID with partition table.
+CREATE TABLE notnull_tbl1 (a INTEGER, b INTEGER) PARTITION BY RANGE (a);
+ALTER TABLE notnull_tbl1 ADD CONSTRAINT notnull_con NOT NULL a NOT VALID;
+CREATE TABLE notnull_tbl1_1 PARTITION OF notnull_tbl1 FOR VALUES FROM (0) TO (10);
+CREATE TABLE notnull_tbl1_2 PARTITION OF notnull_tbl1 FOR VALUES FROM (20) TO (30);
+-- Parent table NOT NULL constraints will be marked as validated false, where
+-- for child table it will be true
+SELECT conrelid::regclass, conname, convalidated FROM pg_catalog.pg_constraint WHERE
+conrelid IN ('notnull_tbl1'::regclass, 'notnull_tbl1_1'::regclass, 'notnull_tbl1_2'::regclass);
+ conrelid | conname | convalidated
+----------------+-------------+--------------
+ notnull_tbl1 | notnull_con | f
+ notnull_tbl1_1 | notnull_con | t
+ notnull_tbl1_2 | notnull_con | t
+(3 rows)
+
+DROP TABLE notnull_tbl1, notnull_tbl1_1, notnull_tbl1_2;
-- Verify that constraint names and NO INHERIT are properly considered when
-- multiple constraint are specified, either explicitly or via SERIAL/PK/etc,
-- and that conflicting cases are rejected. Mind that table constraints
diff --git a/src/test/regress/sql/constraints.sql b/src/test/regress/sql/constraints.sql
index 21ce4177de4..7aff17e6764 100644
--- a/src/test/regress/sql/constraints.sql
+++ b/src/test/regress/sql/constraints.sql
@@ -640,6 +640,91 @@ ALTER TABLE notnull_tbl1 ADD CONSTRAINT foobar NOT NULL a;
\d+ notnull_tbl1
DROP TABLE notnull_tbl1;
+-- verify NOT NULL VALID/NOT VALID
+CREATE TABLE notnull_tbl1 (a INTEGER, b INTEGER);
+INSERT INTO notnull_tbl1 VALUES (NULL, 1);
+INSERT INTO notnull_tbl1 VALUES (NULL, 2);
+INSERT INTO notnull_tbl1 VALUES (300, 3);
+-- Below statement should throw an error
+ALTER TABLE notnull_tbl1 ADD CONSTRAINT nn NOT NULL a;
+ALTER TABLE notnull_tbl1 ADD CONSTRAINT nn NOT NULL a NOT VALID;
+\d+ notnull_tbl1
+-- Try to insert new record with NULL, should throw an error
+INSERT INTO notnull_tbl1 VALUES (NULL, 4);
+-- SELECT NULL values for COLUMN a, should return 2 records.
+SELECT * FROM notnull_tbl1 WHERE a is NULL;
+-- UPDATE the one of the NULL values
+UPDATE notnull_tbl1 SET a = 100 WHERE b = 1;
+-- DELETE the record (NULL, 2)
+DELETE FROM notnull_tbl1 WHERE b = 2;
+SELECT * FROM notnull_tbl1;
+-- Try to add primary key on table column marked as NOT VALID NOT NULL
+-- constraint. This should throw an error.
+ALTER TABLE notnull_tbl1 add primary key (a);
+-- INHERITS table having NOT VALID NOT NULL constraints.
+CREATE TABLE notnull_tbl1_child(a INTEGER, b INTEGER) INHERITS(notnull_tbl1);
+-- Child table NOT NULL constraints should be valid.
+SELECT conname, convalidated FROM pg_catalog.pg_constraint WHERE conrelid = 'notnull_tbl1_child'::regclass;
+DROP TABLE notnull_tbl1_child;
+ALTER TABLE notnull_tbl1 validate constraint nn;
+\d+ notnull_tbl1
+DROP TABLE notnull_tbl1;
+-- Test the different Not null constraint name for parent and child table
+CREATE TABLE notnull_tbl1 (a int);
+ALTER TABLE notnull_tbl1 ADD CONSTRAINT nn_parent not null a not valid;
+CREATE TABLE notnull_chld (a int);
+ALTER TABLE notnull_chld ADD CONSTRAINT nn_child not null a not valid;
+ALTER TABLE notnull_chld INHERIT notnull_tbl1;
+-- This statement should validate not null constraint for parent as well as
+-- child.
+ALTER TABLE notnull_tbl1 VALIDATE CONSTRAINT nn_parent;
+SELECT conname, convalidated FROM pg_catalog.pg_constraint WHERE conrelid
+in ('notnull_tbl1'::regclass, 'notnull_chld'::regclass);
+DROP TABLE notnull_tbl1 CASCADE;
+-- test to throw an error when trying to add another NOT NULL
+-- on the table column with INVALID NOT NULL constraint.
+CREATE TABLE notnull_tbl1 (a INTEGER);
+INSERT INTO notnull_tbl1 VALUES ( NULL );
+ALTER TABLE notnull_tbl1 ADD CONSTRAINT nn1 NOT NULL a not valid;
+-- Should throw an error
+ALTER TABLE notnull_tbl1 ADD CONSTRAINT nn NOT NULL a;
+-- This should throw an error while validating the NOT NULL
+-- constraint.
+ALTER TABLE notnull_tbl1 ALTER a SET NOT NULL;
+-- After deleting null values, SET NOT NULL should work
+DELETE FROM notnull_tbl1;
+ALTER TABLE notnull_tbl1 ALTER a SET NOT NULL;
+SELECT conname, convalidated FROM pg_catalog.pg_constraint WHERE conrelid
+in ('notnull_tbl1'::regclass);
+DROP TABLE notnull_tbl1;
+-- Test invalid not null on inheritance table.
+CREATE TABLE inh_parent ();
+CREATE TABLE inh_child (i int) INHERITS (inh_parent);
+CREATE TABLE inh_grandchild () INHERITS (inh_parent, inh_child);
+ALTER TABLE inh_parent ADD COLUMN i int;
+ALTER TABLE inh_parent ADD CONSTRAINT nn NOT NULL i NOT VALID;
+ALTER TABLE inh_parent ALTER i SET NOT NULL;
+SELECT conrelid::regclass, conname, convalidated, coninhcount FROM pg_catalog.pg_constraint WHERE conrelid
+in ('inh_parent'::regclass, 'inh_child'::regclass, 'inh_grandchild'::regclass) ORDER BY 1;
+drop table inh_parent, inh_child, inh_grandchild;
+-- Create table with NOT NULL INVALID constraint, for pg_upgrade.
+CREATE TABLE notnull_tbl1_upg (a INTEGER, b INTEGER);
+INSERT INTO notnull_tbl1_upg VALUES (NULL, 1);
+INSERT INTO notnull_tbl1_upg VALUES (NULL, 2);
+INSERT INTO notnull_tbl1_upg VALUES (300, 3);
+ALTER TABLE notnull_tbl1_upg ADD CONSTRAINT nn NOT NULL a NOT VALID;
+
+-- Verify NOT NULL VALID/NOT VALID with partition table.
+CREATE TABLE notnull_tbl1 (a INTEGER, b INTEGER) PARTITION BY RANGE (a);
+ALTER TABLE notnull_tbl1 ADD CONSTRAINT notnull_con NOT NULL a NOT VALID;
+CREATE TABLE notnull_tbl1_1 PARTITION OF notnull_tbl1 FOR VALUES FROM (0) TO (10);
+CREATE TABLE notnull_tbl1_2 PARTITION OF notnull_tbl1 FOR VALUES FROM (20) TO (30);
+-- Parent table NOT NULL constraints will be marked as validated false, where
+-- for child table it will be true
+SELECT conrelid::regclass, conname, convalidated FROM pg_catalog.pg_constraint WHERE
+conrelid IN ('notnull_tbl1'::regclass, 'notnull_tbl1_1'::regclass, 'notnull_tbl1_2'::regclass);
+DROP TABLE notnull_tbl1, notnull_tbl1_1, notnull_tbl1_2;
+
-- Verify that constraint names and NO INHERIT are properly considered when
-- multiple constraint are specified, either explicitly or via SERIAL/PK/etc,
-- and that conflicting cases are rejected. Mind that table constraints
--
2.43.0
[application/octet-stream] 0001-Convert-pg_attribut.attnotnull-to-char-type.patch (19.6K, ../CAGPqQf31ctDvcbJE7J3in++iCRbf7-Rv07F7dpnu-_GyLWFz+w@mail.gmail.com/4-0001-Convert-pg_attribut.attnotnull-to-char-type.patch)
download | inline diff:
From 8e909c049dabe9d6ed559e14f076714ad7a43d8f Mon Sep 17 00:00:00 2001
From: Rushabh Lathia <[email protected]>
Date: Mon, 10 Feb 2025 15:39:16 +0530
Subject: [PATCH 1/3] Convert pg_attribut.attnotnull to char type.
This commit change the pg_attribut.attnotnull to char type. Now
attnotnull holds three values, where attnotnull can be either
TRUE, FALSE and INVALID. INVALID is when name not null constrint
is NOT VALIDATED.
---
src/backend/access/common/tupdesc.c | 12 ++++----
src/backend/bootstrap/bootstrap.c | 13 ++++----
src/backend/catalog/heap.c | 16 +++++-----
src/backend/catalog/index.c | 2 +-
src/backend/catalog/indexing.c | 3 +-
src/backend/catalog/information_schema.sql | 4 +--
src/backend/commands/tablecmds.c | 35 ++++++++++++----------
src/backend/executor/execMain.c | 3 +-
src/backend/jit/llvm/llvmjit_deform.c | 10 ++++---
src/backend/optimizer/util/plancat.c | 5 ++--
src/include/access/tupdesc.h | 2 +-
src/include/catalog/pg_attribute.h | 5 +++-
12 files changed, 63 insertions(+), 47 deletions(-)
diff --git a/src/backend/access/common/tupdesc.c b/src/backend/access/common/tupdesc.c
index ed2195f14b2..58698812c0b 100644
--- a/src/backend/access/common/tupdesc.c
+++ b/src/backend/access/common/tupdesc.c
@@ -251,7 +251,7 @@ CreateTupleDescCopy(TupleDesc tupdesc)
{
Form_pg_attribute att = TupleDescAttr(desc, i);
- att->attnotnull = false;
+ att->attnotnull = ATTRIBUTE_NOTNULL_FALSE;
att->atthasdef = false;
att->atthasmissing = false;
att->attidentity = '\0';
@@ -297,7 +297,7 @@ CreateTupleDescTruncatedCopy(TupleDesc tupdesc, int natts)
{
Form_pg_attribute att = TupleDescAttr(desc, i);
- att->attnotnull = false;
+ att->attnotnull = ATTRIBUTE_NOTNULL_FALSE;
att->atthasdef = false;
att->atthasmissing = false;
att->attidentity = '\0';
@@ -417,7 +417,7 @@ TupleDescCopy(TupleDesc dst, TupleDesc src)
{
Form_pg_attribute att = TupleDescAttr(dst, i);
- att->attnotnull = false;
+ att->attnotnull = ATTRIBUTE_NOTNULL_FALSE;
att->atthasdef = false;
att->atthasmissing = false;
att->attidentity = '\0';
@@ -463,7 +463,7 @@ TupleDescCopyEntry(TupleDesc dst, AttrNumber dstAttno,
dstAtt->attnum = dstAttno;
/* since we're not copying constraints or defaults, clear these */
- dstAtt->attnotnull = false;
+ dstAtt->attnotnull = ATTRIBUTE_NOTNULL_FALSE;
dstAtt->atthasdef = false;
dstAtt->atthasmissing = false;
dstAtt->attidentity = '\0';
@@ -840,7 +840,7 @@ TupleDescInitEntry(TupleDesc desc,
att->attnum = attributeNumber;
att->attndims = attdim;
- att->attnotnull = false;
+ att->attnotnull = ATTRIBUTE_NOTNULL_FALSE;
att->atthasdef = false;
att->atthasmissing = false;
att->attidentity = '\0';
@@ -903,7 +903,7 @@ TupleDescInitBuiltinEntry(TupleDesc desc,
att->attnum = attributeNumber;
att->attndims = attdim;
- att->attnotnull = false;
+ att->attnotnull = ATTRIBUTE_NOTNULL_FALSE;
att->atthasdef = false;
att->atthasmissing = false;
att->attidentity = '\0';
diff --git a/src/backend/bootstrap/bootstrap.c b/src/backend/bootstrap/bootstrap.c
index 6db864892d0..1e95dc32f46 100644
--- a/src/backend/bootstrap/bootstrap.c
+++ b/src/backend/bootstrap/bootstrap.c
@@ -582,14 +582,17 @@ DefineAttr(char *name, char *type, int attnum, int nullness)
attrtypes[attnum]->atttypmod = -1;
attrtypes[attnum]->attislocal = true;
+ /* set default to false */
+ attrtypes[attnum]->attnotnull = ATTRIBUTE_NOTNULL_FALSE;
+
if (nullness == BOOTCOL_NULL_FORCE_NOT_NULL)
{
- attrtypes[attnum]->attnotnull = true;
+ attrtypes[attnum]->attnotnull = ATTRIBUTE_NOTNULL_TRUE;
}
else if (nullness == BOOTCOL_NULL_FORCE_NULL)
{
- attrtypes[attnum]->attnotnull = false;
+ attrtypes[attnum]->attnotnull = ATTRIBUTE_NOTNULL_FALSE;
}
else
{
@@ -608,11 +611,11 @@ DefineAttr(char *name, char *type, int attnum, int nullness)
for (i = 0; i < attnum; i++)
{
if (attrtypes[i]->attlen <= 0 ||
- !attrtypes[i]->attnotnull)
+ attrtypes[i]->attnotnull == ATTRIBUTE_NOTNULL_FALSE)
break;
}
if (i == attnum)
- attrtypes[attnum]->attnotnull = true;
+ attrtypes[attnum]->attnotnull = ATTRIBUTE_NOTNULL_TRUE;
}
}
}
@@ -696,7 +699,7 @@ InsertOneNull(int i)
{
elog(DEBUG4, "inserting column %d NULL", i);
Assert(i >= 0 && i < MAXATTR);
- if (TupleDescAttr(boot_reldesc->rd_att, i)->attnotnull)
+ if (TupleDescAttr(boot_reldesc->rd_att, i)->attnotnull == ATTRIBUTE_NOTNULL_TRUE)
elog(ERROR,
"NULL value specified for not-null column \"%s\" of relation \"%s\"",
NameStr(TupleDescAttr(boot_reldesc->rd_att, i)->attname),
diff --git a/src/backend/catalog/heap.c b/src/backend/catalog/heap.c
index bd3554c0bfd..8c2d2cdcdfe 100644
--- a/src/backend/catalog/heap.c
+++ b/src/backend/catalog/heap.c
@@ -150,7 +150,7 @@ static const FormData_pg_attribute a1 = {
.attbyval = false,
.attalign = TYPALIGN_SHORT,
.attstorage = TYPSTORAGE_PLAIN,
- .attnotnull = true,
+ .attnotnull = ATTRIBUTE_NOTNULL_TRUE,
.attislocal = true,
};
@@ -163,7 +163,7 @@ static const FormData_pg_attribute a2 = {
.attbyval = true,
.attalign = TYPALIGN_INT,
.attstorage = TYPSTORAGE_PLAIN,
- .attnotnull = true,
+ .attnotnull = ATTRIBUTE_NOTNULL_TRUE,
.attislocal = true,
};
@@ -176,7 +176,7 @@ static const FormData_pg_attribute a3 = {
.attbyval = true,
.attalign = TYPALIGN_INT,
.attstorage = TYPSTORAGE_PLAIN,
- .attnotnull = true,
+ .attnotnull = ATTRIBUTE_NOTNULL_TRUE,
.attislocal = true,
};
@@ -189,7 +189,7 @@ static const FormData_pg_attribute a4 = {
.attbyval = true,
.attalign = TYPALIGN_INT,
.attstorage = TYPSTORAGE_PLAIN,
- .attnotnull = true,
+ .attnotnull = ATTRIBUTE_NOTNULL_TRUE,
.attislocal = true,
};
@@ -202,7 +202,7 @@ static const FormData_pg_attribute a5 = {
.attbyval = true,
.attalign = TYPALIGN_INT,
.attstorage = TYPSTORAGE_PLAIN,
- .attnotnull = true,
+ .attnotnull = ATTRIBUTE_NOTNULL_TRUE,
.attislocal = true,
};
@@ -221,7 +221,7 @@ static const FormData_pg_attribute a6 = {
.attbyval = true,
.attalign = TYPALIGN_INT,
.attstorage = TYPSTORAGE_PLAIN,
- .attnotnull = true,
+ .attnotnull = ATTRIBUTE_NOTNULL_TRUE,
.attislocal = true,
};
@@ -752,7 +752,7 @@ InsertPgAttributeTuples(Relation pg_attribute_rel,
slot[slotCount]->tts_values[Anum_pg_attribute_attalign - 1] = CharGetDatum(attrs->attalign);
slot[slotCount]->tts_values[Anum_pg_attribute_attstorage - 1] = CharGetDatum(attrs->attstorage);
slot[slotCount]->tts_values[Anum_pg_attribute_attcompression - 1] = CharGetDatum(attrs->attcompression);
- slot[slotCount]->tts_values[Anum_pg_attribute_attnotnull - 1] = BoolGetDatum(attrs->attnotnull);
+ slot[slotCount]->tts_values[Anum_pg_attribute_attnotnull - 1] = CharGetDatum(attrs->attnotnull);
slot[slotCount]->tts_values[Anum_pg_attribute_atthasdef - 1] = BoolGetDatum(attrs->atthasdef);
slot[slotCount]->tts_values[Anum_pg_attribute_atthasmissing - 1] = BoolGetDatum(attrs->atthasmissing);
slot[slotCount]->tts_values[Anum_pg_attribute_attidentity - 1] = CharGetDatum(attrs->attidentity);
@@ -1713,7 +1713,7 @@ RemoveAttributeById(Oid relid, AttrNumber attnum)
attStruct->atttypid = InvalidOid;
/* Remove any not-null constraint the column may have */
- attStruct->attnotnull = false;
+ attStruct->attnotnull = ATTRIBUTE_NOTNULL_FALSE;
/* Unset this so no one tries to look up the generation expression */
attStruct->attgenerated = '\0';
diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c
index 739a92bdcc1..6b0c16bf59d 100644
--- a/src/backend/catalog/index.c
+++ b/src/backend/catalog/index.c
@@ -262,7 +262,7 @@ index_check_primary_key(Relation heapRel,
attnum, RelationGetRelid(heapRel));
attform = (Form_pg_attribute) GETSTRUCT(atttuple);
- if (!attform->attnotnull)
+ if (attform->attnotnull == ATTRIBUTE_NOTNULL_FALSE)
ereport(ERROR,
(errcode(ERRCODE_INVALID_TABLE_DEFINITION),
errmsg("primary key column \"%s\" is not marked NOT NULL",
diff --git a/src/backend/catalog/indexing.c b/src/backend/catalog/indexing.c
index 25c4b6bdc87..a2211d75566 100644
--- a/src/backend/catalog/indexing.c
+++ b/src/backend/catalog/indexing.c
@@ -207,7 +207,8 @@ CatalogTupleCheckConstraints(Relation heapRel, HeapTuple tup)
{
Form_pg_attribute thisatt = TupleDescAttr(tupdesc, attnum);
- Assert(!(thisatt->attnotnull && att_isnull(attnum, bp)));
+ Assert(!(thisatt->attnotnull == ATTRIBUTE_NOTNULL_TRUE &&
+ att_isnull(attnum, bp)));
}
}
}
diff --git a/src/backend/catalog/information_schema.sql b/src/backend/catalog/information_schema.sql
index a7bffca93d1..4f59bc46e74 100644
--- a/src/backend/catalog/information_schema.sql
+++ b/src/backend/catalog/information_schema.sql
@@ -292,7 +292,7 @@ CREATE VIEW attributes AS
CAST(a.attname AS sql_identifier) AS attribute_name,
CAST(a.attnum AS cardinal_number) AS ordinal_position,
CAST(pg_get_expr(ad.adbin, ad.adrelid) AS character_data) AS attribute_default,
- CAST(CASE WHEN a.attnotnull OR (t.typtype = 'd' AND t.typnotnull) THEN 'NO' ELSE 'YES' END
+ CAST(CASE WHEN a.attnotnull = 't' OR (t.typtype = 'd' AND t.typnotnull) THEN 'NO' ELSE 'YES' END
AS yes_or_no)
AS is_nullable, -- This column was apparently removed between SQL:2003 and SQL:2008.
@@ -671,7 +671,7 @@ CREATE VIEW columns AS
CAST(a.attname AS sql_identifier) AS column_name,
CAST(a.attnum AS cardinal_number) AS ordinal_position,
CAST(CASE WHEN a.attgenerated = '' THEN pg_get_expr(ad.adbin, ad.adrelid) END AS character_data) AS column_default,
- CAST(CASE WHEN a.attnotnull OR (t.typtype = 'd' AND t.typnotnull) THEN 'NO' ELSE 'YES' END
+ CAST(CASE WHEN a.attnotnull = 't' OR (t.typtype = 'd' AND t.typnotnull) THEN 'NO' ELSE 'YES' END
AS yes_or_no)
AS is_nullable,
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 18ff8956577..3c81e48cac1 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -1399,7 +1399,10 @@ BuildDescForRelation(const List *columns)
TupleDescInitEntryCollation(desc, attnum, attcollation);
/* Fill in additional stuff not handled by TupleDescInitEntry */
- att->attnotnull = entry->is_not_null;
+ if (entry->is_not_null)
+ att->attnotnull = ATTRIBUTE_NOTNULL_TRUE;
+ else
+ att->attnotnull = ATTRIBUTE_NOTNULL_FALSE;
att->attislocal = entry->is_local;
att->attinhcount = entry->inhcount;
att->attidentity = entry->identity;
@@ -6188,7 +6191,8 @@ ATRewriteTable(AlteredTableInfo *tab, Oid OIDNewHeap)
{
Form_pg_attribute attr = TupleDescAttr(newTupDesc, i);
- if (attr->attnotnull && !attr->attisdropped)
+ if (attr->attnotnull == ATTRIBUTE_NOTNULL_TRUE &&
+ !attr->attisdropped)
notnull_attrs = lappend_int(notnull_attrs, i);
}
if (notnull_attrs)
@@ -7669,7 +7673,7 @@ ATExecDropNotNull(Relation rel, const char *colName, bool recurse,
RelationGetRelid(rel), attnum);
/* If the column is already nullable there's nothing to do. */
- if (!attTup->attnotnull)
+ if (attTup->attnotnull == ATTRIBUTE_NOTNULL_FALSE)
{
table_close(attr_rel, RowExclusiveLock);
return InvalidObjectAddress;
@@ -7699,7 +7703,7 @@ ATExecDropNotNull(Relation rel, const char *colName, bool recurse,
AttrNumber parent_attnum;
parent_attnum = get_attnum(parentId, colName);
- if (TupleDescAttr(tupDesc, parent_attnum - 1)->attnotnull)
+ if (TupleDescAttr(tupDesc, parent_attnum - 1)->attnotnull == ATTRIBUTE_NOTNULL_TRUE)
ereport(ERROR,
(errcode(ERRCODE_INVALID_TABLE_DEFINITION),
errmsg("column \"%s\" is marked NOT NULL in parent table",
@@ -7753,7 +7757,7 @@ set_attnotnull(List **wqueue, Relation rel, AttrNumber attnum,
if (attr->attisdropped)
return;
- if (!attr->attnotnull)
+ if (attr->attnotnull == ATTRIBUTE_NOTNULL_FALSE)
{
Relation attr_rel;
HeapTuple tuple;
@@ -7766,8 +7770,8 @@ set_attnotnull(List **wqueue, Relation rel, AttrNumber attnum,
attnum, RelationGetRelid(rel));
attr = (Form_pg_attribute) GETSTRUCT(tuple);
- Assert(!attr->attnotnull);
- attr->attnotnull = true;
+ Assert(attr->attnotnull == ATTRIBUTE_NOTNULL_FALSE);
+ attr->attnotnull = ATTRIBUTE_NOTNULL_TRUE;
CatalogTupleUpdate(attr_rel, &tuple->t_self, tuple);
/*
@@ -8172,7 +8176,7 @@ ATExecAddIdentity(Relation rel, const char *colName,
* to an existing column that is not NOT NULL would create a state that
* cannot be reproduced without contortions.
*/
- if (!attTup->attnotnull)
+ if (attTup->attnotnull == ATTRIBUTE_NOTNULL_FALSE)
ereport(ERROR,
(errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
errmsg("column \"%s\" of relation \"%s\" must be declared NOT NULL before identity can be added",
@@ -9373,7 +9377,7 @@ ATPrepAddPrimaryKey(List **wqueue, Relation rel, AlterTableCmd *cmd,
elog(ERROR, "cache lookup failed for attribute %s of relation %u",
attname, childrelid);
attrForm = (Form_pg_attribute) GETSTRUCT(tup);
- if (!attrForm->attnotnull)
+ if (attrForm->attnotnull == ATTRIBUTE_NOTNULL_FALSE)
ereport(ERROR,
errmsg("column \"%s\" of table \"%s\" is not marked NOT NULL",
attname, get_rel_name(childrelid)));
@@ -13476,9 +13480,9 @@ dropconstraint_internal(Relation rel, HeapTuple constraintTup, DropBehavior beha
RelationGetRelationName(rel)));
/* All good -- reset attnotnull if needed */
- if (attForm->attnotnull)
+ if (attForm->attnotnull == ATTRIBUTE_NOTNULL_TRUE)
{
- attForm->attnotnull = false;
+ attForm->attnotnull = ATTRIBUTE_NOTNULL_FALSE;
CatalogTupleUpdate(attrel, &atttup->t_self, atttup);
}
@@ -16805,7 +16809,8 @@ MergeAttributesIntoExisting(Relation child_rel, Relation parent_rel, bool ispart
*
* Other constraints are checked elsewhere.
*/
- if (parent_att->attnotnull && !child_att->attnotnull)
+ if (parent_att->attnotnull == ATTRIBUTE_NOTNULL_TRUE &&
+ child_att->attnotnull == ATTRIBUTE_NOTNULL_FALSE)
{
HeapTuple contup;
@@ -17848,7 +17853,7 @@ ATExecReplicaIdentity(Relation rel, ReplicaIdentityStmt *stmt, LOCKMODE lockmode
RelationGetRelationName(indexRel), attno)));
attr = TupleDescAttr(rel->rd_att, attno - 1);
- if (!attr->attnotnull)
+ if (attr->attnotnull == ATTRIBUTE_NOTNULL_FALSE)
ereport(ERROR,
(errcode(ERRCODE_WRONG_OBJECT_TYPE),
errmsg("index \"%s\" cannot be used as replica identity because column \"%s\" is nullable",
@@ -19326,7 +19331,7 @@ PartConstraintImpliedByRelConstraint(Relation scanrel,
{
Form_pg_attribute att = TupleDescAttr(scanrel->rd_att, i - 1);
- if (att->attnotnull && !att->attisdropped)
+ if (att->attnotnull == ATTRIBUTE_NOTNULL_TRUE && !att->attisdropped)
{
NullTest *ntest = makeNode(NullTest);
@@ -21158,7 +21163,7 @@ verifyPartitionIndexNotNull(IndexInfo *iinfo, Relation partition)
Form_pg_attribute att = TupleDescAttr(RelationGetDescr(partition),
iinfo->ii_IndexAttrNumbers[i] - 1);
- if (!att->attnotnull)
+ if (att->attnotnull == ATTRIBUTE_NOTNULL_FALSE)
ereport(ERROR,
errcode(ERRCODE_INVALID_TABLE_DEFINITION),
errmsg("invalid primary key definition"),
diff --git a/src/backend/executor/execMain.c b/src/backend/executor/execMain.c
index 0493b7d5365..d582231ab60 100644
--- a/src/backend/executor/execMain.c
+++ b/src/backend/executor/execMain.c
@@ -2070,7 +2070,8 @@ ExecConstraints(ResultRelInfo *resultRelInfo,
{
Form_pg_attribute att = TupleDescAttr(tupdesc, attrChk - 1);
- if (att->attnotnull && slot_attisnull(slot, attrChk))
+ if (att->attnotnull == ATTRIBUTE_NOTNULL_TRUE &&
+ slot_attisnull(slot, attrChk))
{
char *val_desc;
Relation orig_rel = rel;
diff --git a/src/backend/jit/llvm/llvmjit_deform.c b/src/backend/jit/llvm/llvmjit_deform.c
index 5d169c7a40b..e6444bc2c91 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->attnotnull == ATTRIBUTE_NOTNULL_TRUE &&
!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->attnotnull == ATTRIBUTE_NOTNULL_FALSE)
{
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->attnotnull == ATTRIBUTE_NOTNULL_TRUE &&
+ 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->attnotnull == ATTRIBUTE_NOTNULL_TRUE &&
+ (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 71abb01f655..e322098cfb2 100644
--- a/src/backend/optimizer/util/plancat.c
+++ b/src/backend/optimizer/util/plancat.c
@@ -177,7 +177,7 @@ get_relation_info(PlannerInfo *root, Oid relationObjectId, bool inhparent,
{
CompactAttribute *attr = TupleDescCompactAttr(relation->rd_att, i);
- if (attr->attnotnull)
+ if (attr->attnotnull == ATTRIBUTE_NOTNULL_TRUE)
{
rel->notnullattnums = bms_add_member(rel->notnullattnums,
i + 1);
@@ -1355,7 +1355,8 @@ get_relation_constraints(PlannerInfo *root,
{
Form_pg_attribute att = TupleDescAttr(relation->rd_att, i - 1);
- if (att->attnotnull && !att->attisdropped)
+ if (att->attnotnull == ATTRIBUTE_NOTNULL_TRUE &&
+ !att->attisdropped)
{
NullTest *ntest = makeNode(NullTest);
diff --git a/src/include/access/tupdesc.h b/src/include/access/tupdesc.h
index 396eeb7a0bb..1eba67ca8e9 100644
--- a/src/include/access/tupdesc.h
+++ b/src/include/access/tupdesc.h
@@ -76,7 +76,7 @@ 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 attnotnull; /* as FormData_pg_attribute.attnotnull */
uint8 attalignby; /* alignment requirement in bytes */
} CompactAttribute;
diff --git a/src/include/catalog/pg_attribute.h b/src/include/catalog/pg_attribute.h
index deaa515fe53..b51a2670958 100644
--- a/src/include/catalog/pg_attribute.h
+++ b/src/include/catalog/pg_attribute.h
@@ -118,7 +118,7 @@ CATALOG(pg_attribute,1249,AttributeRelationId) BKI_BOOTSTRAP BKI_ROWTYPE_OID(75,
char attcompression BKI_DEFAULT('\0');
/* This flag represents the "NOT NULL" constraint */
- bool attnotnull;
+ char attnotnull;
/* Has DEFAULT value or not */
bool atthasdef BKI_DEFAULT(f);
@@ -227,6 +227,9 @@ MAKE_SYSCACHE(ATTNUM, pg_attribute_relid_attnum_index, 128);
#define ATTRIBUTE_GENERATED_STORED 's'
#define ATTRIBUTE_GENERATED_VIRTUAL 'v'
+#define ATTRIBUTE_NOTNULL_TRUE 't'
+#define ATTRIBUTE_NOTNULL_FALSE 'f'
+
#endif /* EXPOSE_TO_CLIENT_CODE */
#endif /* PG_ATTRIBUTE_H */
--
2.43.0
[application/octet-stream] 0003-Documentation-changes.patch (3.0K, ../CAGPqQf31ctDvcbJE7J3in++iCRbf7-Rv07F7dpnu-_GyLWFz+w@mail.gmail.com/5-0003-Documentation-changes.patch)
download | inline diff:
From b093a8a109fa7aec8caf792d9e3521fcebada86f Mon Sep 17 00:00:00 2001
From: Rushabh Lathia <[email protected]>
Date: Thu, 27 Feb 2025 15:22:56 +0530
Subject: [PATCH 3/3] Documentation changes
---
doc/src/sgml/catalogs.sgml | 5 +++--
doc/src/sgml/ref/alter_table.sgml | 4 ++--
src/bin/psql/tab-complete.in.c | 2 +-
3 files changed, 6 insertions(+), 5 deletions(-)
diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index fb050635551..d6066d7dba1 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -1257,10 +1257,11 @@
<row>
<entry role="catalog_table_entry"><para role="column_definition">
- <structfield>attnotnull</structfield> <type>bool</type>
+ <structfield>attnotnull</structfield> <type>char</type>
</para>
<para>
- This column has a not-null constraint.
+ The value 't' indicates that a not-null constraint exists for the
+ column, 'i' for an invalid constraint and 'f' for none.
</para></entry>
</row>
diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml
index dceb7a7593c..288cd3339f8 100644
--- a/doc/src/sgml/ref/alter_table.sgml
+++ b/doc/src/sgml/ref/alter_table.sgml
@@ -99,7 +99,7 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
<phrase>and <replaceable class="parameter">column_constraint</replaceable> is:</phrase>
[ CONSTRAINT <replaceable class="parameter">constraint_name</replaceable> ]
-{ NOT NULL [ NO INHERIT ] |
+{ NOT NULL [ NOT VALID ] [ NO INHERIT ] |
NULL |
CHECK ( <replaceable class="parameter">expression</replaceable> ) [ NO INHERIT ] |
DEFAULT <replaceable>default_expr</replaceable> |
@@ -458,7 +458,7 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
<para>
This form adds a new constraint to a table using the same constraint
syntax as <link linkend="sql-createtable"><command>CREATE TABLE</command></link>, plus the option <literal>NOT
- VALID</literal>, which is currently only allowed for foreign key
+ VALID</literal>, which is currently only allowed for foreign key, NOT NULL constraints
and CHECK constraints.
</para>
diff --git a/src/bin/psql/tab-complete.in.c b/src/bin/psql/tab-complete.in.c
index 8432be641ac..1c3295d949a 100644
--- a/src/bin/psql/tab-complete.in.c
+++ b/src/bin/psql/tab-complete.in.c
@@ -2728,7 +2728,7 @@ match_previous_words(int pattern_id,
COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_datatypes);
/* ALTER TABLE xxx ADD CONSTRAINT yyy */
else if (Matches("ALTER", "TABLE", MatchAny, "ADD", "CONSTRAINT", MatchAny))
- COMPLETE_WITH("CHECK", "UNIQUE", "PRIMARY KEY", "EXCLUDE", "FOREIGN KEY");
+ COMPLETE_WITH("CHECK", "UNIQUE", "PRIMARY KEY", "EXCLUDE", "FOREIGN KEY", "NOT NULL");
/* ALTER TABLE xxx ADD [CONSTRAINT yyy] (PRIMARY KEY|UNIQUE) */
else if (Matches("ALTER", "TABLE", MatchAny, "ADD", "PRIMARY", "KEY") ||
Matches("ALTER", "TABLE", MatchAny, "ADD", "UNIQUE") ||
--
2.43.0
view thread (44+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected]
Subject: Re: Support NOT VALID / VALIDATE constraint options for named NOT NULL constraints
In-Reply-To: <CAGPqQf31ctDvcbJE7J3in++iCRbf7-Rv07F7dpnu-_GyLWFz+w@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox