hi.
you may like the attached. it's based on your idea: attnotnullvalid.
I came across a case, not sure if it's a bug.
CREATE TABLE ttchk (a INTEGER);
ALTER TABLE ttchk ADD CONSTRAINT cc check (a is NOT NULL) NOT VALID;
CREATE TABLE ttchk_child(a INTEGER) INHERITS(ttchk);
ttchk_child's constraint cc will default to valid,
but pg_dump && pg_restore will make ttchk_child's constraint invalid.
since it's an existing behavior, so not-null constraint will align with it.
--------------------------------------------------------------------
-----the following text is copied from the commit message------------
NOT NULL NOT VALID
* TODO: In doc/src/sgml/ref/alter_table.sgml, under the
<title>Compatibility</title> section,
clarify how the "NOT NULL NOT VALID" syntax conforms with the standard.
* TODO: Should CREATE TABLE LIKE copy an existing invalid not-null
constraint to the new table,
and if so, the new table's not-null will be marked as valid.
description entry of pg_attribute.attnotnullvalid:
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>attnotnullvalid</structfield> <type>bool</type>
+ </para>
+ <para>
+ The not-null constraint validity status of the column.
+ If true, it means this column has a valid not-null constraint,
+ false means this column doesn't have a not-null constraint or
has an unvalidated one.
+ If <structfield>attnotnull</structfield> is false, this must be false.
</para></entry>
* attnotnull means that a not-null constraint exists; it doesn't imply anything
regarding the constraint being valid or not.
attnotnullvalid will indicate whether the constraint is valid; this column can
only be true if attnotnull is already true.
attnotnullvalid only added to FormData_pg_attribute, didn't add to
CompactAttribute.
mainly because invalid not-null is not being commonly used.
TupleDesc->TupleConstr->has_not_null now also represents invalid not-null
constraint.
* For table in pg_catalog schema, if that column attnotnull attribute is true,
then attnotnullvalid attribute is also true. Similarly, if
attnotnull is false,
then attnotnullvalid is false. I added an SQL check at the end of
src/test/regress/sql/constraints.sql (not sure it's necessary)
* CREATE TABLE specifying not valid not-null constraint will be set to valid,
a warning is issued within function transformCreateStmt.
that means InsertPgAttributeTuples can not insert attribute
that is (attnotnull && !attnotnullvalid).
I added an Assert in InsertPgAttributeTuples.
(also added to other places, to demo i didn't mess something, maybe
it's necessary).
* table rewrite won't validate invalid not-null constraint, that is aligned
with check constraint.
* attnotnullvalid mainly changed in these two places:
1. ATAddCheckNNConstraint, if you specified "NOT NULL NOT VALID", it
will change
it from false to false, but will set attnotnull to true.
2. QueueNNConstraintValidation, subroutine of ATExecValidateConstraint.
when validing an not valid not-null constraint, toggle it from
false to true,
also set attnotnull to true.
* A partitioned table can have an invalid NOT NULL constraint while its
partitions have a valid one, but not the other way around.
but pg_dump/pg_restore may not preserve the constraint name properly, but
that's fine for not-null constraint, i think.
* regular table invalid not null constraint pg_dump also works fine.
From fc4bf954772d25dfbf60774429d875f78e4fd69e Mon Sep 17 00:00:00 2001
From: jian he <jian.universal...@gmail.com>
Date: Mon, 24 Mar 2025 09:21:10 +0800
Subject: [PATCH v5 1/1] NOT NULL NOT VALID
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
* TODO: In doc/src/sgml/ref/alter_table.sgml, under the <title>Compatibility</title> section,
clarify how the "NOT NULL NOT VALID" syntax conforms with the standard.
* TODO: Should CREATE TABLE LIKE copy an existing invalid not-null constraint to the new table,
and if so, the new table's not-null will be marked as valid.
* attnotnull means that a not-null constraint exists; it doesn't imply anything
regarding the constraint being valid or not.
attnotnullvalid will indicate whether the constraint is valid; this column can
only be true if attnotnull is already true.
attnotnullvalid only added to FormData_pg_attribute, didn't add to CompactAttribute.
mainly because invalid not-null is not being commonly used.
TupleDesc->TupleConstr->has_not_null now also represents invalid not-null
constraint.
* For table in pg_catalog schema, if that column attnotnull attribute is true,
then attnotnullvalid attribute is also true. Similarly, if attnotnull is false,
then attnotnullvalid is false. I added an SQL check at the end of
src/test/regress/sql/constraints.sql (not sure it's necessary)
* CREATE TABLE specifying not valid not-null constraint will be set to valid,
a warning is issued within function transformCreateStmt.
that means InsertPgAttributeTuples can not insert attribute
that is (attnotnull && !attnotnullvalid).
I added an Assert in InsertPgAttributeTuples.
* table rewrite won't validate invalid not-null constraint, that is aligned
with check constraint.
* attnotnullvalid, two places toggled it from true to false.
1. ATAddCheckNNConstraint, if you specified "NOT NULL NOT VALID", it will change
it from false to false, but will set attnotnull to true.
2. QueueNNConstraintValidation, subroutine of ATExecValidateConstraint.
when validing an not valid not-null constraint, toggle it from false to true,
also set attnotnull to true.
* A partitioned table can have an invalid NOT NULL constraint while its
partitions have a valid one, but not the other way around.
but pg_dump/pg_restore may not preserve the constraint name properly, but
that's fine for not-null constraint.
* regular table invalid not null constraint pg_dump also works fine.
discussion: https://postgr.es/m/CAGPqQf0KitkNack4F5CFkFi-9Dqvp29Ro=EpcWt=4_hs-rt...@mail.gmail.com
---
contrib/postgres_fdw/postgres_fdw.c | 1 +
doc/src/sgml/catalogs.sgml | 14 +-
doc/src/sgml/ref/alter_table.sgml | 8 +-
src/backend/access/common/tupdesc.c | 8 +
src/backend/bootstrap/bootstrap.c | 9 +
src/backend/catalog/genbki.pl | 2 +
src/backend/catalog/heap.c | 20 +-
src/backend/catalog/pg_constraint.c | 57 +++++-
src/backend/commands/tablecmds.c | 222 ++++++++++++++++++++--
src/backend/executor/execMain.c | 1 +
src/backend/optimizer/util/plancat.c | 6 +-
src/backend/parser/gram.y | 4 +-
src/backend/parser/parse_utilcmd.c | 16 +-
src/backend/utils/cache/catcache.c | 1 +
src/backend/utils/cache/relcache.c | 6 +-
src/bin/pg_dump/pg_dump.c | 157 ++++++++++++++-
src/bin/pg_dump/pg_dump.h | 3 +-
src/bin/psql/describe.c | 9 +-
src/include/access/tupdesc.h | 2 +-
src/include/catalog/pg_attribute.h | 3 +
src/include/catalog/pg_constraint.h | 10 +-
src/test/regress/expected/constraints.out | 183 ++++++++++++++++++
src/test/regress/sql/constraints.sql | 125 ++++++++++++
23 files changed, 810 insertions(+), 57 deletions(-)
diff --git a/contrib/postgres_fdw/postgres_fdw.c b/contrib/postgres_fdw/postgres_fdw.c
index 6beae0fa37f..92dd1afd47f 100644
--- a/contrib/postgres_fdw/postgres_fdw.c
+++ b/contrib/postgres_fdw/postgres_fdw.c
@@ -5572,6 +5572,7 @@ postgresImportForeignSchema(ImportForeignSchemaStmt *stmt, Oid serverOid)
" LEFT JOIN pg_attribute a ON "
" attrelid = c.oid AND attnum > 0 "
" AND NOT attisdropped "
+ " AND attnotnullvalid "
" LEFT JOIN pg_attrdef ad ON "
" adrelid = c.oid AND adnum = attnum ");
diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index fb050635551..4f42c5bdc32 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -1260,7 +1260,19 @@
<structfield>attnotnull</structfield> <type>bool</type>
</para>
<para>
- This column has a not-null constraint.
+ This column has a possibly unvalidated not-null constraint
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>attnotnullvalid</structfield> <type>bool</type>
+ </para>
+ <para>
+ The not-null constraint validity status of the column.
+ If true, it means this column has a valid not-null constraint,
+ false means this column doesn't have a not-null constraint or has an unvalidated one.
+ If <structfield>attnotnull</structfield> is false, this must be false.
</para></entry>
</row>
diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml
index 4f15b89a98f..9a2c0f5bfa0 100644
--- a/doc/src/sgml/ref/alter_table.sgml
+++ b/doc/src/sgml/ref/alter_table.sgml
@@ -243,6 +243,8 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
entire table; however, if a valid <literal>CHECK</literal> constraint is
found which proves no <literal>NULL</literal> can exist, then the
table scan is skipped.
+ If a column has a invalid not-null constraint, <literal>SET NOT NULL</literal>
+ will change it to a validated not-null constraint.
</para>
<para>
@@ -458,8 +460,8 @@ 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
- and CHECK constraints.
+ VALID</literal>, which is currently only allowed for foreign key,
+ CHECK constraints and not-null constraints.
</para>
<para>
@@ -586,7 +588,7 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
<term><literal>VALIDATE CONSTRAINT</literal></term>
<listitem>
<para>
- This form validates a foreign key or check constraint that was
+ This form validates a foreign key or check constraint or not-null constraint that was
previously created as <literal>NOT VALID</literal>, by scanning the
table to ensure there are no rows for which the constraint is not
satisfied. Nothing happens if the constraint is already marked valid.
diff --git a/src/backend/access/common/tupdesc.c b/src/backend/access/common/tupdesc.c
index ed2195f14b2..86cb23d58ab 100644
--- a/src/backend/access/common/tupdesc.c
+++ b/src/backend/access/common/tupdesc.c
@@ -252,6 +252,7 @@ CreateTupleDescCopy(TupleDesc tupdesc)
Form_pg_attribute att = TupleDescAttr(desc, i);
att->attnotnull = false;
+ att->attnotnullvalid = false;
att->atthasdef = false;
att->atthasmissing = false;
att->attidentity = '\0';
@@ -298,6 +299,7 @@ CreateTupleDescTruncatedCopy(TupleDesc tupdesc, int natts)
Form_pg_attribute att = TupleDescAttr(desc, i);
att->attnotnull = false;
+ att->attnotnullvalid = false;
att->atthasdef = false;
att->atthasmissing = false;
att->attidentity = '\0';
@@ -418,6 +420,7 @@ TupleDescCopy(TupleDesc dst, TupleDesc src)
Form_pg_attribute att = TupleDescAttr(dst, i);
att->attnotnull = false;
+ att->attnotnullvalid = false;
att->atthasdef = false;
att->atthasmissing = false;
att->attidentity = '\0';
@@ -464,6 +467,7 @@ TupleDescCopyEntry(TupleDesc dst, AttrNumber dstAttno,
/* since we're not copying constraints or defaults, clear these */
dstAtt->attnotnull = false;
+ dstAtt->attnotnullvalid = false;
dstAtt->atthasdef = false;
dstAtt->atthasmissing = false;
dstAtt->attidentity = '\0';
@@ -613,6 +617,8 @@ equalTupleDescs(TupleDesc tupdesc1, TupleDesc tupdesc2)
return false;
if (attr1->attnotnull != attr2->attnotnull)
return false;
+ if (attr1->attnotnullvalid != attr2->attnotnullvalid)
+ return false;
if (attr1->atthasdef != attr2->atthasdef)
return false;
if (attr1->attidentity != attr2->attidentity)
@@ -841,6 +847,7 @@ TupleDescInitEntry(TupleDesc desc,
att->attndims = attdim;
att->attnotnull = false;
+ att->attnotnullvalid = false;
att->atthasdef = false;
att->atthasmissing = false;
att->attidentity = '\0';
@@ -904,6 +911,7 @@ TupleDescInitBuiltinEntry(TupleDesc desc,
att->attndims = attdim;
att->attnotnull = false;
+ att->attnotnullvalid = 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..b18dc1d5ee6 100644
--- a/src/backend/bootstrap/bootstrap.c
+++ b/src/backend/bootstrap/bootstrap.c
@@ -615,6 +615,15 @@ DefineAttr(char *name, char *type, int attnum, int nullness)
attrtypes[attnum]->attnotnull = true;
}
}
+
+ /*
+ * If a system catalog column is attnotnull then attnotnullvalid is true,
+ * otherwise false.
+ */
+ if (attrtypes[attnum]->attnotnull == true)
+ attrtypes[attnum]->attnotnullvalid = true;
+ else
+ attrtypes[attnum]->attnotnullvalid = false;
}
diff --git a/src/backend/catalog/genbki.pl b/src/backend/catalog/genbki.pl
index df3231fcd41..b8651a9d865 100644
--- a/src/backend/catalog/genbki.pl
+++ b/src/backend/catalog/genbki.pl
@@ -986,6 +986,8 @@ sub morph_row_for_pgattr
$row->{attnotnull} = 'f';
}
+ $row->{attnotnullvalid} = $row->{attnotnull} eq 't' ? 't' : 'f';
+
Catalog::AddDefaultValues($row, $pgattr_schema, 'pg_attribute');
return;
}
diff --git a/src/backend/catalog/heap.c b/src/backend/catalog/heap.c
index bd3554c0bfd..bed22db755a 100644
--- a/src/backend/catalog/heap.c
+++ b/src/backend/catalog/heap.c
@@ -151,6 +151,7 @@ static const FormData_pg_attribute a1 = {
.attalign = TYPALIGN_SHORT,
.attstorage = TYPSTORAGE_PLAIN,
.attnotnull = true,
+ .attnotnullvalid = true,
.attislocal = true,
};
@@ -164,6 +165,7 @@ static const FormData_pg_attribute a2 = {
.attalign = TYPALIGN_INT,
.attstorage = TYPSTORAGE_PLAIN,
.attnotnull = true,
+ .attnotnullvalid = true,
.attislocal = true,
};
@@ -177,6 +179,7 @@ static const FormData_pg_attribute a3 = {
.attalign = TYPALIGN_INT,
.attstorage = TYPSTORAGE_PLAIN,
.attnotnull = true,
+ .attnotnullvalid = true,
.attislocal = true,
};
@@ -190,6 +193,7 @@ static const FormData_pg_attribute a4 = {
.attalign = TYPALIGN_INT,
.attstorage = TYPSTORAGE_PLAIN,
.attnotnull = true,
+ .attnotnullvalid = true,
.attislocal = true,
};
@@ -203,6 +207,7 @@ static const FormData_pg_attribute a5 = {
.attalign = TYPALIGN_INT,
.attstorage = TYPSTORAGE_PLAIN,
.attnotnull = true,
+ .attnotnullvalid = true,
.attislocal = true,
};
@@ -222,6 +227,7 @@ static const FormData_pg_attribute a6 = {
.attalign = TYPALIGN_INT,
.attstorage = TYPSTORAGE_PLAIN,
.attnotnull = true,
+ .attnotnullvalid = true,
.attislocal = true,
};
@@ -753,6 +759,11 @@ InsertPgAttributeTuples(Relation pg_attribute_rel,
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_attnotnullvalid - 1] = BoolGetDatum(attrs->attnotnullvalid);
+ if (!attrs->attnotnull)
+ Assert(!attrs->attnotnullvalid);
+ else
+ Assert(attrs->attnotnullvalid);
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);
@@ -2621,12 +2632,17 @@ AddRelationNewConstraints(Relation rel,
errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("not-null constraints are not supported on virtual generated columns"));
+ if (cdef->initially_valid)
+ Assert(!cdef->skip_validation);
+ else
+ Assert(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.
*/
- if (AdjustNotNullInheritance(RelationGetRelid(rel), colnum,
- is_local, cdef->is_no_inherit))
+ if (AdjustNotNullInheritance(rel, colnum,
+ 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 ac80652baf2..5005ea7e6f1 100644
--- a/src/backend/catalog/pg_constraint.c
+++ b/src/backend/catalog/pg_constraint.c
@@ -576,13 +576,14 @@ 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.
+ * if include_invalid is true, it may return an invalid not-null tuple.
*
* 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.
* I'm not sure it's worth the catalog bloat and de-normalization, however.
*/
HeapTuple
-findNotNullConstraintAttnum(Oid relid, AttrNumber attnum)
+findNotNullConstraintAttnum(Oid relid, AttrNumber attnum, bool include_invalid)
{
Relation pg_constraint;
HeapTuple conTup,
@@ -609,7 +610,7 @@ findNotNullConstraintAttnum(Oid relid, AttrNumber attnum)
*/
if (con->contype != CONSTRAINT_NOTNULL)
continue;
- if (!con->convalidated)
+ if (!con->convalidated && !include_invalid)
continue;
conkey = extractNotNullColumn(conTup);
@@ -631,9 +632,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.
+ * if include_invalid is true, it may return an invalid not-null tuple.
*/
HeapTuple
-findNotNullConstraint(Oid relid, const char *colname)
+findNotNullConstraint(Oid relid, const char *colname, bool include_invalid)
{
AttrNumber attnum;
@@ -641,7 +643,7 @@ findNotNullConstraint(Oid relid, const char *colname)
if (attnum <= InvalidAttrNumber)
return NULL;
- return findNotNullConstraintAttnum(relid, attnum);
+ return findNotNullConstraintAttnum(relid, attnum, include_invalid);
}
/*
@@ -728,12 +730,13 @@ extractNotNullColumn(HeapTuple constrTup)
* 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)
+AdjustNotNullInheritance(Relation rel, AttrNumber attnum,
+ bool is_local, bool is_no_inherit, bool is_notvalid)
{
HeapTuple tup;
+ Oid relid = RelationGetRelid(rel);
- tup = findNotNullConstraintAttnum(relid, attnum);
+ tup = findNotNullConstraintAttnum(relid, attnum, true);
if (HeapTupleIsValid(tup))
{
Relation pg_constraint;
@@ -753,6 +756,36 @@ 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 we want change the NOT NULL constraint from NOT
+ * VALID to VALID.
+ */
+ if (!is_notvalid && !conform->convalidated)
+ ereport(ERROR,
+ errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
+ errmsg("cannot change invalid constraint \"%s\" on relation \"%s\" to valid",
+ NameStr(conform->conname), get_rel_name(relid)),
+ errhint("You may need to use ALTER TABLE VALIDATE CONSTRAINT to validate constraint \"%s\"",
+ NameStr(conform->conname)));
+
+ /* VALID TO NOT VALID seems no usage, so just issue a warning */
+ if (is_notvalid && conform->convalidated)
+ {
+ if (is_local)
+ ereport(WARNING,
+ errmsg("NOT NULL constraint \"%s\" on relation \"%s\" is already valid",
+ NameStr(conform->conname), get_rel_name(relid)));
+ else if (rel->rd_rel->relispartition)
+ {
+ /*
+ * In case of partitions, an inherited not null constraint
+ * is never considered local. See MergeConstraintsIntoExisting also.
+ */
+ conform->conislocal = false;
+ changed =true;
+ }
+ }
+
if (!is_local)
{
if (pg_add_s16_overflow(conform->coninhcount, 1,
@@ -788,9 +821,10 @@ AdjustNotNullInheritance(Oid relid, AttrNumber attnum,
* This is seldom needed, so we just scan pg_constraint each time.
*
* 'include_noinh' determines whether to include NO INHERIT constraints or not.
+ * 'include_notvalid' determines whether to include NO VALID constraints or not.
*/
List *
-RelationGetNotNullConstraints(Oid relid, bool cooked, bool include_noinh)
+RelationGetNotNullConstraints(Oid relid, bool cooked, bool include_noinh, bool include_notvalid)
{
List *notnulls = NIL;
Relation constrRel;
@@ -816,6 +850,9 @@ RelationGetNotNullConstraints(Oid relid, bool cooked, bool include_noinh)
if (conForm->connoinherit && !include_noinh)
continue;
+ if (!conForm->convalidated && !include_notvalid)
+ continue;
+
colnum = extractNotNullColumn(htup);
if (cooked)
@@ -830,7 +867,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;
@@ -850,7 +887,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 1202544ebd0..fc77e15793e 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,
@@ -474,6 +477,7 @@ static ObjectAddress ATExecDropNotNull(Relation rel, const char *colName, bool r
LOCKMODE lockmode);
static void set_attnotnull(List **wqueue, Relation rel, AttrNumber attnum,
LOCKMODE lockmode);
+static void set_attnotnullinvalid(Relation rel, AttrNumber attnum, bool is_valid);
static ObjectAddress ATExecSetNotNull(List **wqueue, Relation rel,
char *constrname, char *colName,
bool recurse, bool recursing,
@@ -1400,6 +1404,12 @@ BuildDescForRelation(const List *columns)
/* Fill in additional stuff not handled by TupleDescInitEntry */
att->attnotnull = entry->is_not_null;
+
+ if (att->attnotnull)
+ att->attnotnullvalid = true;
+ else
+ att->attnotnullvalid = false;
+
att->attislocal = entry->is_local;
att->attinhcount = entry->inhcount;
att->attidentity = entry->identity;
@@ -2713,10 +2723,10 @@ 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. but we will include NOT VALID
*/
nnconstrs = RelationGetNotNullConstraints(RelationGetRelid(relation),
- true, false);
+ true, false, true);
foreach_ptr(CookedConstraint, cc, nnconstrs)
nncols = bms_add_member(nncols, cc->attnum);
@@ -6183,12 +6193,14 @@ 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.
+ * But we don't need check invalid not-null constraint! this is aligned
+ * with check constraint behavior.
*/
for (i = 0; i < newTupDesc->natts; i++)
{
Form_pg_attribute attr = TupleDescAttr(newTupDesc, i);
- if (attr->attnotnull && !attr->attisdropped)
+ if (attr->attnotnull && attr->attnotnullvalid && !attr->attisdropped)
notnull_attrs = lappend_int(notnull_attrs, attr->attnum);
}
if (notnull_attrs)
@@ -7669,6 +7681,7 @@ ATExecDropNotNull(Relation rel, const char *colName, bool recurse,
/* If the column is already nullable there's nothing to do. */
if (!attTup->attnotnull)
{
+ // Assert(!attTup->attnotnullvalid);
table_close(attr_rel, RowExclusiveLock);
return InvalidObjectAddress;
}
@@ -7709,7 +7722,7 @@ ATExecDropNotNull(Relation rel, const char *colName, bool recurse,
* Find the constraint that makes this column NOT NULL, and drop it.
* dropconstraint_internal() resets attnotnull.
*/
- conTup = findNotNullConstraintAttnum(RelationGetRelid(rel), attnum);
+ conTup = findNotNullConstraintAttnum(RelationGetRelid(rel), attnum, false);
if (conTup == NULL)
elog(ERROR, "cache lookup failed for not-null constraint on column \"%s\" of relation \"%s\"",
colName, RelationGetRelationName(rel));
@@ -7766,6 +7779,7 @@ set_attnotnull(List **wqueue, Relation rel, AttrNumber attnum,
attr = (Form_pg_attribute) GETSTRUCT(tuple);
Assert(!attr->attnotnull);
attr->attnotnull = true;
+ attr->attnotnullvalid = true;
CatalogTupleUpdate(attr_rel, &tuple->t_self, tuple);
/*
@@ -7787,6 +7801,42 @@ set_attnotnull(List **wqueue, Relation rel, AttrNumber attnum,
}
}
+/*
+ * currently we have two commands can change attnotnullvalid, attnotnull status:
+ * ALTER TABLE VALIDATE CONSTRAINT, ALTER TABLE ADD NOT NULL NOT VALID. In both
+ * case, we need set attnotnull to true, set attnotnullvalid based on "is_valid".
+*/
+static void
+set_attnotnullinvalid(Relation rel, AttrNumber attnum, bool is_valid)
+{
+ Form_pg_attribute attr;
+ Relation attr_rel;
+ HeapTuple tuple;
+
+ CheckAlterTableIsSafe(rel);
+
+ attr = TupleDescAttr(RelationGetDescr(rel), attnum - 1);
+ if (attr->attisdropped)
+ return;
+
+ attr_rel = table_open(AttributeRelationId, RowExclusiveLock);
+
+ tuple = SearchSysCacheCopyAttNum(RelationGetRelid(rel), attnum);
+ if (!HeapTupleIsValid(tuple))
+ elog(ERROR, "cache lookup failed for attribute %d of relation %u",
+ attnum, RelationGetRelid(rel));
+
+ attr = (Form_pg_attribute) GETSTRUCT(tuple);
+ attr->attnotnullvalid = is_valid;
+ attr->attnotnull = true;
+
+ CatalogTupleUpdate(attr_rel, &tuple->t_self, tuple);
+ CommandCounterIncrement();
+
+ table_close(attr_rel, RowExclusiveLock);
+ heap_freetuple(tuple);
+}
+
/*
* ALTER TABLE ALTER COLUMN SET NOT NULL
*
@@ -7843,7 +7893,7 @@ ATExecSetNotNull(List **wqueue, Relation rel, char *conName, char *colName,
colName, RelationGetRelationName(rel))));
/* See if there's already a constraint */
- tuple = findNotNullConstraintAttnum(RelationGetRelid(rel), attnum);
+ tuple = findNotNullConstraintAttnum(RelationGetRelid(rel), attnum, true);
if (HeapTupleIsValid(tuple))
{
Form_pg_constraint conForm = (Form_pg_constraint) GETSTRUCT(tuple);
@@ -7878,6 +7928,16 @@ 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)
{
@@ -9385,6 +9445,24 @@ ATPrepAddPrimaryKey(List **wqueue, Relation rel, AlterTableCmd *cmd,
{
AlterTableCmd *newcmd;
Constraint *nnconstr;
+ HeapTuple tuple;
+
+ tuple = findNotNullConstraint(RelationGetRelid(rel), strVal(lfirst(lc)), true);
+ if (tuple != NULL)
+ {
+ Form_pg_constraint conForm = (Form_pg_constraint) GETSTRUCT(tuple);
+ if (!conForm->convalidated)
+ ereport(ERROR,
+ errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
+ errmsg("primary key require an validated NOT NULL constraint"),
+ errdetail("NOT NULL constraint \"%s\" on Column \"%s\" of table \"%s\" is not validated",
+ NameStr(conForm->conname),
+ strVal(lfirst(lc)),
+ RelationGetRelationName(rel)),
+ errhint("You may try ALTER TABLE VALIDATE CONSTRAINT to validate it \"%s\"",
+ NameStr(conForm->conname)));
+ heap_freetuple(tuple);
+ }
nnconstr = makeNotNullConstraint(lfirst(lc));
@@ -9760,11 +9838,19 @@ ATAddCheckNNConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel,
constr->conname = ccon->name;
/*
- * If adding a not-null constraint, set the pg_attribute flag and tell
+ * If adding a valid not-null constraint, set the pg_attribute flag and tell
* phase 3 to verify existing rows, if needed.
+ * However if we are adding a invalid not-null constraint, then we only
+ * need set the pg_attribute.attnotnullvalid to false, phase 3 don't need to
+ * verify existing rows.
*/
if (constr->contype == CONSTR_NOTNULL)
- set_attnotnull(wqueue, rel, ccon->attnum, lockmode);
+ {
+ if (!constr->skip_validation)
+ set_attnotnull(wqueue, rel, ccon->attnum, lockmode);
+ else
+ set_attnotnullinvalid(rel, ccon->attnum, false);
+ }
ObjectAddressSet(address, ConstraintRelationId, ccon->conoid);
}
@@ -12174,7 +12260,7 @@ ATExecAlterConstraintInternal(List **wqueue, ATAlterConstraint *cmdcon,
HeapTuple childtup;
Form_pg_constraint childcon;
- childtup = findNotNullConstraint(childoid, colName);
+ childtup = findNotNullConstraint(childoid, colName, false);
if (!childtup)
elog(ERROR, "cache lookup failed for not-null constraint on column \"%s\" of relation %u",
colName, childoid);
@@ -12365,10 +12451,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 or check constraint or not-null constraint",
constrName, RelationGetRelationName(rel))));
if (!con->conenforced)
@@ -12387,6 +12474,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);
}
@@ -12603,6 +12695,106 @@ 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);
+
+ /*
+ * 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, true);
+ 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_attnotnullinvalid(rel, attnum, true);
+
+ 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);
+
+ InvokeObjectPostAlterHook(ConstraintRelationId, con->oid, 0);
+
+ heap_freetuple(copyTuple);
+}
+
/*
* transformColumnNameList - transform list of column names
*
@@ -13473,10 +13665,11 @@ dropconstraint_internal(Relation rel, HeapTuple constraintTup, DropBehavior beha
false),
RelationGetRelationName(rel)));
- /* All good -- reset attnotnull if needed */
+ /* All good -- reset attnotnull and attnotnullvalid if needed */
if (attForm->attnotnull)
{
attForm->attnotnull = false;
+ attForm->attnotnullvalid = false;
CatalogTupleUpdate(attrel, &atttup->t_self, atttup);
}
@@ -13547,7 +13740,7 @@ dropconstraint_internal(Relation rel, HeapTuple constraintTup, DropBehavior beha
*/
if (con->contype == CONSTRAINT_NOTNULL)
{
- tuple = findNotNullConstraint(childrelid, colname);
+ tuple = findNotNullConstraint(childrelid, colname, false);
if (!HeapTupleIsValid(tuple))
elog(ERROR, "cache lookup failed for not-null constraint on column \"%s\" of relation %u",
colname, RelationGetRelid(childrel));
@@ -16812,7 +17005,7 @@ MergeAttributesIntoExisting(Relation child_rel, Relation parent_rel, bool ispart
HeapTuple contup;
contup = findNotNullConstraintAttnum(RelationGetRelid(parent_rel),
- parent_att->attnum);
+ parent_att->attnum, true);
if (HeapTupleIsValid(contup) &&
!((Form_pg_constraint) GETSTRUCT(contup))->connoinherit)
ereport(ERROR,
@@ -19328,7 +19521,8 @@ PartConstraintImpliedByRelConstraint(Relation scanrel,
{
Form_pg_attribute att = TupleDescAttr(scanrel->rd_att, i - 1);
- if (att->attnotnull && !att->attisdropped)
+ /* invalid not-null constraint must be ignored */
+ if (att->attnotnull && att->attnotnullvalid && !att->attisdropped)
{
NullTest *ntest = makeNode(NullTest);
diff --git a/src/backend/executor/execMain.c b/src/backend/executor/execMain.c
index e9bd98c7738..3be2e4f4639 100644
--- a/src/backend/executor/execMain.c
+++ b/src/backend/executor/execMain.c
@@ -2070,6 +2070,7 @@ ExecConstraints(ResultRelInfo *resultRelInfo,
{
Form_pg_attribute att = TupleDescAttr(tupdesc, attrChk - 1);
+ /* not valid not-null constraint also checked */
if (att->attnotnull && slot_attisnull(slot, attrChk))
{
char *val_desc;
diff --git a/src/backend/optimizer/util/plancat.c b/src/backend/optimizer/util/plancat.c
index 0489ad36644..c8790264c8d 100644
--- a/src/backend/optimizer/util/plancat.c
+++ b/src/backend/optimizer/util/plancat.c
@@ -175,9 +175,9 @@ get_relation_info(PlannerInfo *root, Oid relationObjectId, bool inhparent,
{
for (int i = 0; i < relation->rd_att->natts; i++)
{
- CompactAttribute *attr = TupleDescCompactAttr(relation->rd_att, i);
+ Form_pg_attribute attr = TupleDescAttr(relation->rd_att, i);
- if (attr->attnotnull)
+ if (attr->attnotnull && attr->attnotnullvalid)
{
rel->notnullattnums = bms_add_member(rel->notnullattnums,
i + 1);
@@ -1355,7 +1355,7 @@ get_relation_constraints(PlannerInfo *root,
{
Form_pg_attribute att = TupleDescAttr(relation->rd_att, i - 1);
- if (att->attnotnull && !att->attisdropped)
+ if (att->attnotnull && att->attnotnullvalid && !att->attisdropped)
{
NullTest *ntest = makeNode(NullTest);
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/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c
index 896a7f2c59b..aaaee388f5d 100644
--- a/src/backend/parser/parse_utilcmd.c
+++ b/src/backend/parser/parse_utilcmd.c
@@ -329,6 +329,20 @@ transformCreateStmt(CreateStmt *stmt, const char *queryString)
cd->is_not_null = true;
break;
}
+ if (!cxt.isforeign && !nn->initially_valid)
+ {
+ nn->initially_valid = true;
+ nn->skip_validation = false;
+
+ /*
+ * not-null constraint created via CREATE TABLE will always be
+ * valid. since there is no data there while CREATE TABLE, make it
+ * invalid does not make sense
+ */
+ ereport(WARNING,
+ errcode(ERRCODE_WARNING),
+ errmsg("Ignoring NOT VALID flag for NOT NULL constraint on column \"%s\"", colname));
+ }
}
/*
@@ -1291,7 +1305,7 @@ transformTableLikeClause(CreateStmtContext *cxt, TableLikeClause *table_like_cla
List *lst;
lst = RelationGetNotNullConstraints(RelationGetRelid(relation), false,
- true);
+ true, false);
cxt->nnconstraints = list_concat(cxt->nnconstraints, lst);
}
diff --git a/src/backend/utils/cache/catcache.c b/src/backend/utils/cache/catcache.c
index 9ad7681f155..70c11529b90 100644
--- a/src/backend/utils/cache/catcache.c
+++ b/src/backend/utils/cache/catcache.c
@@ -1142,6 +1142,7 @@ CatalogCacheInitializeCache(CatCache *cache)
keytype = attr->atttypid;
/* cache key columns should always be NOT NULL */
Assert(attr->attnotnull);
+ Assert(attr->attnotnullvalid);
}
else
{
diff --git a/src/backend/utils/cache/relcache.c b/src/backend/utils/cache/relcache.c
index 9f54a9e72b7..84fbabd5344 100644
--- a/src/backend/utils/cache/relcache.c
+++ b/src/backend/utils/cache/relcache.c
@@ -590,7 +590,10 @@ RelationBuildTupleDesc(Relation relation)
populate_compact_attribute(relation->rd_att, attnum - 1);
- /* Update constraint/default info */
+ /*
+ * Update constraint/default info
+ * has_not_null also include invalid not-null constraint
+ */
if (attp->attnotnull)
constr->has_not_null = true;
if (attp->attgenerated == ATTRIBUTE_GENERATED_STORED)
@@ -3573,6 +3576,7 @@ RelationBuildLocalRelation(const char *relname,
datt->attidentity = satt->attidentity;
datt->attgenerated = satt->attgenerated;
datt->attnotnull = satt->attnotnull;
+ datt->attnotnullvalid = satt->attnotnullvalid;
has_not_null |= satt->attnotnull;
populate_compact_attribute(rel->rd_att, i);
}
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index 428ed2d60fc..42755bdfd7d 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -8884,6 +8884,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;
@@ -8903,6 +8904,7 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables)
int i_notnull_name;
int i_notnull_noinherit;
int i_notnull_islocal;
+ int i_notnull_validated;
int i_attoptions;
int i_attcollation;
int i_attcompression;
@@ -8998,12 +9000,14 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables)
appendPQExpBufferStr(q,
"co.conname AS notnull_name,\n"
"co.connoinherit AS notnull_noinherit,\n"
- "co.conislocal AS notnull_islocal,\n");
+ "co.conislocal AS notnull_islocal,\n"
+ "co.convalidated as notnull_validated,\n");
else
appendPQExpBufferStr(q,
"CASE WHEN a.attnotnull THEN '' ELSE NULL END AS notnull_name,\n"
"false AS notnull_noinherit,\n"
- "a.attislocal AS notnull_islocal,\n");
+ "a.attislocal AS notnull_islocal,\n"
+ "true as notnull_validated\n");
if (fout->remoteVersion >= 140000)
appendPQExpBufferStr(q,
@@ -9078,6 +9082,7 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables)
i_notnull_name = PQfnumber(res, "notnull_name");
i_notnull_noinherit = PQfnumber(res, "notnull_noinherit");
i_notnull_islocal = PQfnumber(res, "notnull_islocal");
+ i_notnull_validated = PQfnumber(res, "notnull_validated");
i_attoptions = PQfnumber(res, "attoptions");
i_attcollation = PQfnumber(res, "attcollation");
i_attcompression = PQfnumber(res, "attcompression");
@@ -9094,6 +9099,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));
@@ -9145,6 +9151,7 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables)
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));
+ tbinfo->notnull_validated = (bool *) pg_malloc(numatts * sizeof(bool));
tbinfo->attrdefs = (AttrDefInfo **) pg_malloc(numatts * sizeof(AttrDefInfo *));
hasdefaults = false;
@@ -9168,12 +9175,32 @@ 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_validated[j] = (PQgetvalue(res, r, i_notnull_validated)[0] == 't');
- /* Handle not-null constraint name and flags */
- determineNotNullFlags(fout, res, r,
- tbinfo, j,
- i_notnull_name, i_notnull_noinherit,
- i_notnull_islocal);
+ if (tbinfo->notnull_validated[j])
+ {
+ /* Handle not-null constraint name and flags */
+ determineNotNullFlags(fout, res, r,
+ tbinfo, j,
+ i_notnull_name, i_notnull_noinherit,
+ i_notnull_islocal);
+ }
+ else
+ {
+ tbinfo->notnull_constrs[j] = NULL;
+
+ /*
+ * if column notnull_validated is false, it means either column
+ * don't have not-null constraint at all, or the existing one is
+ * invalid. here we onlu dump the invalid not-null.
+ */
+ if (!PQgetisnull(res, r, i_notnull_name))
+ {
+ if (invalidnotnulloids->len > 1)
+ 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));
@@ -9193,6 +9220,7 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables)
appendPQExpBuffer(tbloids, "%u", tbinfo->dobj.catId.oid);
}
}
+ appendPQExpBufferChar(invalidnotnulloids, '}');
PQclear(res);
@@ -9326,6 +9354,112 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables)
PQclear(res);
}
+ /*
+ * Get info about table 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->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 NOT convalidated "
+ "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';
+
+ Assert(!validated);
+ 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');
+
+ /*
+ * invalid not-null constraint must dumped separately as an
+ * ALTER TABLE ADD CONSTRAINT entry. because CREATE TABLE can
+ * not create invalid not null constraint. it's also required
+ * for potentially-violating existing data is loaded before the
+ * constraint.
+ */
+ constrs[j].separate = !validated;
+
+ 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.
@@ -16620,6 +16754,9 @@ dumpTableSchema(Archive *fout, const TableInfo *tbinfo)
if (print_notnull)
{
+ /* column constraint should all be validated not-null */
+ Assert(tbinfo->notnull_validated[j]);
+
if (tbinfo->notnull_constrs[j][0] == '\0')
appendPQExpBufferStr(q, " NOT NULL");
else
@@ -17939,9 +18076,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 and invalid not-null constraint on a table */
/* Ignore if not to be dumped separately, or if it was inherited */
if (coninfo->separate && coninfo->conislocal)
@@ -17965,7 +18102,7 @@ dumpConstraint(Archive *fout, const ConstraintInfo *coninfo)
ARCHIVE_OPTS(.tag = tag,
.namespace = tbinfo->dobj.namespace->dobj.name,
.owner = tbinfo->rolname,
- .description = "CHECK CONSTRAINT",
+ .description = coninfo->contype == 'c' ? "CHECK CONSTRAINT" : "CONSTRAINT",
.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 bbdb30b5f54..c2d8c3e601a 100644
--- a/src/bin/pg_dump/pg_dump.h
+++ b/src/bin/pg_dump/pg_dump.h
@@ -367,8 +367,9 @@ typedef struct _tableInfo
* (pre-v17) */
bool *notnull_noinh; /* NOT NULL is NO INHERIT */
bool *notnull_islocal; /* true if NOT NULL has local definition */
+ bool *notnull_validated; /* true if NOT NULL is validated */
struct _attrDefInfo **attrdefs; /* DEFAULT expressions */
- struct _constraintInfo *checkexprs; /* CHECK constraints */
+ struct _constraintInfo *checkexprs; /* CHECK constraints XXX: also include invalid not-null constraint */
bool needs_override; /* has GENERATED ALWAYS AS IDENTITY */
char *amname; /* relation access method */
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index e6cf468ac9e..09f8f92a59c 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -3114,7 +3114,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"
@@ -3137,14 +3138,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 ? " NO VALID": "");
printTableAddFooter(&cont, buf.data);
}
diff --git a/src/include/access/tupdesc.h b/src/include/access/tupdesc.h
index 396eeb7a0bb..55418350505 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; /* this includes invalid not-null !!! */
bool has_generated_stored;
bool has_generated_virtual;
} TupleConstr;
diff --git a/src/include/catalog/pg_attribute.h b/src/include/catalog/pg_attribute.h
index deaa515fe53..398b6e324c8 100644
--- a/src/include/catalog/pg_attribute.h
+++ b/src/include/catalog/pg_attribute.h
@@ -120,6 +120,9 @@ CATALOG(pg_attribute,1249,AttributeRelationId) BKI_BOOTSTRAP BKI_ROWTYPE_OID(75,
/* This flag represents the "NOT NULL" constraint */
bool attnotnull;
+ /* false means "NOT NULL NOT VALID". true means XXX (need to consider attnotnull) */
+ bool attnotnullvalid;
+
/* Has DEFAULT value or not */
bool atthasdef BKI_DEFAULT(f);
diff --git a/src/include/catalog/pg_constraint.h b/src/include/catalog/pg_constraint.h
index 6da164e7e4d..fe27dde16fa 100644
--- a/src/include/catalog/pg_constraint.h
+++ b/src/include/catalog/pg_constraint.h
@@ -259,14 +259,14 @@ extern char *ChooseConstraintName(const char *name1, const char *name2,
const char *label, Oid namespaceid,
List *others);
-extern HeapTuple findNotNullConstraintAttnum(Oid relid, AttrNumber attnum);
-extern HeapTuple findNotNullConstraint(Oid relid, const char *colname);
+extern HeapTuple findNotNullConstraintAttnum(Oid relid, AttrNumber attnum, bool include_invalid);
+extern HeapTuple findNotNullConstraint(Oid relid, const char *colname, bool include_invalid);
extern HeapTuple findDomainNotNullConstraint(Oid typid);
extern AttrNumber extractNotNullColumn(HeapTuple constrTup);
-extern bool AdjustNotNullInheritance(Oid relid, AttrNumber attnum,
- bool is_local, bool is_no_inherit);
+extern bool AdjustNotNullInheritance(Relation rel, AttrNumber attnum,
+ bool is_local, bool is_no_inherit, bool is_notvalid);
extern List *RelationGetNotNullConstraints(Oid relid, bool cooked,
- bool include_noinh);
+ bool include_noinh, bool include_notvalid);
extern void RemoveConstraintById(Oid conId);
extern void RenameConstraintById(Oid conId, const char *newname);
diff --git a/src/test/regress/expected/constraints.out b/src/test/regress/expected/constraints.out
index 4f39100fcdf..7cf69fa7dd3 100644
--- a/src/test/regress/expected/constraints.out
+++ b/src/test/regress/expected/constraints.out
@@ -896,6 +896,175 @@ Not-null constraints:
"foobar" NOT NULL "a"
DROP TABLE notnull_tbl1;
+-------tests for NOT NULL NOT VALID
+PREPARE get_nnconstraint_info(regclass[]) AS
+SELECT conrelid::regclass, conname, convalidated, coninhcount
+FROM pg_constraint
+WHERE conrelid = ANY($1)
+ORDER BY 1;
+CREATE TABLE notnull_tbl1 (a INTEGER, b INTEGER);
+INSERT INTO notnull_tbl1 VALUES (NULL, 1),(NULL, 2), (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
+\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" NO VALID
+
+INSERT INTO notnull_tbl1 VALUES (NULL, 4); --error, invalid not-null constraint forbiden new null values
+ERROR: null value in column "a" of relation "notnull_tbl1" violates not-null constraint
+DETAIL: Failing row contains (null, 4).
+ALTER TABLE notnull_tbl1 ADD CONSTRAINT nn1 NOT NULL a not valid no inherit; --error
+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 can't change not-null status
+ERROR: cannot change invalid constraint "nn" on relation "notnull_tbl1" to valid
+HINT: You may need to use ALTER TABLE VALIDATE CONSTRAINT to validate constraint "nn"
+ALTER TABLE notnull_tbl1 ALTER a SET NOT NULL; --validate it then error out
+ERROR: column "a" of relation "notnull_tbl1" contains null values
+UPDATE notnull_tbl1 SET a = 100 WHERE b = 1;
+DELETE FROM notnull_tbl1 WHERE b = 2;
+SELECT * FROM notnull_tbl1 ORDER BY a, b;
+ a | b
+-----+---
+ 100 | 1
+ 300 | 3
+(2 rows)
+
+-- cannot add primary key on column marked as NOT VALID NOT NULL
+ALTER TABLE notnull_tbl1 ADD PRIMARY KEY (a);
+ERROR: primary key require an validated NOT NULL constraint
+DETAIL: NOT NULL constraint "nn" on Column "a" of table "notnull_tbl1" is not validated
+HINT: You may try ALTER TABLE VALIDATE CONSTRAINT to validate it "nn"
+-- however child table NOT NULL constraints should be valid.
+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
+EXECUTE get_nnconstraint_info('{notnull_tbl1_child, notnull_tbl1}');
+ conrelid | conname | convalidated | coninhcount
+--------------------+---------+--------------+-------------
+ notnull_tbl1 | nn | f | 0
+ notnull_tbl1_child | nn | t | 1
+(2 rows)
+
+ALTER TABLE notnull_tbl1 VALIDATE CONSTRAINT nn;
+---now we can add primary key
+ALTER TABLE notnull_tbl1 ADD PRIMARY KEY (a);
+DROP TABLE notnull_tbl1_child;
+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;
+-- parent have valid not null constraint then child table cannot have invalid one
+CREATE TABLE notnull_chld0 (a int);
+ALTER TABLE notnull_chld0 ADD 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"
+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;
+ALTER TABLE notnull_chld VALIDATE CONSTRAINT nn_child;
+-- parents and child not-null will all be validated.
+ALTER TABLE notnull_tbl1 VALIDATE CONSTRAINT nn_parent;
+EXECUTE get_nnconstraint_info('{notnull_tbl1, notnull_chld}');
+ conrelid | conname | convalidated | coninhcount
+--------------+-----------+--------------+-------------
+ notnull_tbl1 | nn_parent | t | 0
+ notnull_chld | nn_child | t | 1
+(2 rows)
+
+DROP TABLE notnull_chld0;
+DROP TABLE notnull_chld;
+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 ADD CONSTRAINT nn NOT NULL i; --error
+ERROR: cannot change invalid constraint "nn" on relation "inh_parent" to valid
+HINT: You may need to use ALTER TABLE VALIDATE CONSTRAINT to validate constraint "nn"
+ALTER TABLE inh_child ADD CONSTRAINT nn1 NOT NULL i; --error
+ERROR: cannot change invalid constraint "nn" on relation "inh_child" to valid
+HINT: You may need to use ALTER TABLE VALIDATE CONSTRAINT to validate constraint "nn"
+EXECUTE get_nnconstraint_info('{inh_parent, inh_child, inh_grandchild}'); --all should be invalid.
+ conrelid | conname | convalidated | coninhcount
+----------------+---------+--------------+-------------
+ inh_parent | nn | f | 0
+ inh_child | nn | f | 1
+ inh_grandchild | nn | f | 2
+(3 rows)
+
+ALTER TABLE inh_parent ALTER i SET NOT NULL; --ok
+EXECUTE get_nnconstraint_info('{inh_parent, inh_child, inh_grandchild}'); --all should be valid now.
+ 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;
+-- 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 INTEGER, b INTEGER) 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}');
+ conrelid | conname | convalidated | coninhcount
+----------------+-------------+--------------+-------------
+ notnull_tbl1 | notnull_con | f | 0
+ notnull_tbl1_1 | notnull_con | t | 1
+ notnull_tbl1_2 | nn2 | t | 1
+ notnull_tbl1_3 | nn3 | f | 1
+(4 rows)
+
+ALTER TABLE notnull_tbl1 ALTER COLUMN a SET NOT NULL; --error, table already 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}');
+ conrelid | conname | convalidated | coninhcount
+----------------+-------------+--------------+-------------
+ notnull_tbl1 | notnull_con | t | 0
+ notnull_tbl1_1 | notnull_con | t | 1
+ notnull_tbl1_2 | nn2 | t | 1
+ notnull_tbl1_3 | nn3 | t | 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 INTEGER, b INTEGER) PARTITION BY LIST (a);
+ALTER TABLE pp_nn ADD CONSTRAINT pp_nn_notnull NOT NULL 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;
+DEALLOCATE get_nnconstraint_info;
+-- 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), (NULL, 2), (300, 3);
+ALTER TABLE notnull_tbl1_upg ADD CONSTRAINT nn NOT NULL a NOT VALID;
+-- end of NOT NULL VALID/NOT VALID --------------------------------
-- 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
@@ -1392,3 +1561,17 @@ DROP TABLE constraint_comments_tbl;
DROP DOMAIN constraint_comments_dom;
DROP ROLE regress_constraint_comments;
DROP ROLE regress_constraint_comments_noaccess;
+--sanity check attnotnull and attnotnullvalid.
+select pc.relname, pa.attname, pa.attnum, pa.attnotnull, pa.attnotnullvalid
+from pg_attribute pa join pg_class pc
+on pa.attrelid = pc.oid
+where ( (pa.attnotnull and not pa.attnotnullvalid) or
+ (not pa.attnotnull and pa.attnotnullvalid) )
+and pc.relnamespace in
+ ('pg_catalog'::regnamespace,
+ 'pg_toast'::regnamespace,
+ 'information_schema'::regnamespace);
+ relname | attname | attnum | attnotnull | attnotnullvalid
+---------+---------+--------+------------+-----------------
+(0 rows)
+
diff --git a/src/test/regress/sql/constraints.sql b/src/test/regress/sql/constraints.sql
index 21ce4177de4..34ad33f2d55 100644
--- a/src/test/regress/sql/constraints.sql
+++ b/src/test/regress/sql/constraints.sql
@@ -640,6 +640,120 @@ ALTER TABLE notnull_tbl1 ADD CONSTRAINT foobar NOT NULL a;
\d+ notnull_tbl1
DROP TABLE notnull_tbl1;
+-------tests for NOT NULL NOT VALID
+PREPARE get_nnconstraint_info(regclass[]) AS
+SELECT conrelid::regclass, conname, convalidated, coninhcount
+FROM pg_constraint
+WHERE conrelid = ANY($1)
+ORDER BY 1;
+
+CREATE TABLE notnull_tbl1 (a INTEGER, b INTEGER);
+INSERT INTO notnull_tbl1 VALUES (NULL, 1),(NULL, 2), (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
+\d+ notnull_tbl1
+
+INSERT INTO notnull_tbl1 VALUES (NULL, 4); --error, invalid not-null constraint forbiden new null values
+ALTER TABLE notnull_tbl1 ADD CONSTRAINT nn1 NOT NULL a not valid no inherit; --error
+ALTER TABLE notnull_tbl1 ADD CONSTRAINT nn NOT NULL a; --error can't change not-null status
+ALTER TABLE notnull_tbl1 ALTER a SET NOT NULL; --validate it then error out
+
+UPDATE notnull_tbl1 SET a = 100 WHERE b = 1;
+DELETE FROM notnull_tbl1 WHERE b = 2;
+SELECT * FROM notnull_tbl1 ORDER BY a, b;
+
+-- cannot add primary key on column marked as NOT VALID NOT NULL
+ALTER TABLE notnull_tbl1 ADD PRIMARY KEY (a);
+-- however child table NOT NULL constraints should be valid.
+CREATE TABLE notnull_tbl1_child(a INTEGER, b INTEGER) INHERITS(notnull_tbl1);
+EXECUTE get_nnconstraint_info('{notnull_tbl1_child, notnull_tbl1}');
+
+ALTER TABLE notnull_tbl1 VALIDATE CONSTRAINT nn;
+---now we can add primary key
+ALTER TABLE notnull_tbl1 ADD PRIMARY KEY (a);
+DROP TABLE notnull_tbl1_child;
+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;
+
+-- parent have valid not null constraint then child table cannot have invalid one
+CREATE TABLE notnull_chld0 (a int);
+ALTER TABLE notnull_chld0 ADD CONSTRAINT nn_chld0 NOT NULL a;
+ALTER TABLE notnull_tbl1 INHERIT notnull_chld0; --error
+
+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;
+
+ALTER TABLE notnull_chld VALIDATE CONSTRAINT nn_child;
+-- parents and child not-null will all be validated.
+ALTER TABLE notnull_tbl1 VALIDATE CONSTRAINT nn_parent;
+EXECUTE get_nnconstraint_info('{notnull_tbl1, notnull_chld}');
+DROP TABLE notnull_chld0;
+DROP TABLE notnull_chld;
+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 ADD CONSTRAINT nn NOT NULL i; --error
+ALTER TABLE inh_child ADD CONSTRAINT nn1 NOT NULL i; --error
+EXECUTE get_nnconstraint_info('{inh_parent, inh_child, inh_grandchild}'); --all should be invalid.
+ALTER TABLE inh_parent ALTER i SET NOT NULL; --ok
+EXECUTE get_nnconstraint_info('{inh_parent, inh_child, inh_grandchild}'); --all should be valid now.
+DROP TABLE inh_parent, inh_child, inh_grandchild;
+
+
+-- Verify NOT NULL VALID/NOT VALID with partition table.
+DROP TABLE notnull_tbl1;
+CREATE TABLE notnull_tbl1 (a INTEGER, b INTEGER) 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, table already 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 INTEGER, b INTEGER) PARTITION BY LIST (a);
+ALTER TABLE pp_nn ADD CONSTRAINT pp_nn_notnull NOT NULL 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;
+
+DEALLOCATE get_nnconstraint_info;
+
+-- 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), (NULL, 2), (300, 3);
+ALTER TABLE notnull_tbl1_upg ADD CONSTRAINT nn NOT NULL a NOT VALID;
+-- end of NOT NULL VALID/NOT VALID --------------------------------
+
+
-- 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
@@ -836,3 +950,14 @@ DROP DOMAIN constraint_comments_dom;
DROP ROLE regress_constraint_comments;
DROP ROLE regress_constraint_comments_noaccess;
+
+--sanity check attnotnull and attnotnullvalid.
+select pc.relname, pa.attname, pa.attnum, pa.attnotnull, pa.attnotnullvalid
+from pg_attribute pa join pg_class pc
+on pa.attrelid = pc.oid
+where ( (pa.attnotnull and not pa.attnotnullvalid) or
+ (not pa.attnotnull and pa.attnotnullvalid) )
+and pc.relnamespace in
+ ('pg_catalog'::regnamespace,
+ 'pg_toast'::regnamespace,
+ 'information_schema'::regnamespace);
\ No newline at end of file
--
2.34.1
