From eb8c8197743a30c558cb208d40d82b66f9c8fc58 Mon Sep 17 00:00:00 2001 From: Rushabh Lathia Date: Mon, 10 Feb 2025 16:22:30 +0530 Subject: [PATCH 2/3] Support NOT VALID and VALIDATE CONSTRAINT for named NOT NULL constraints. --- src/backend/commands/tablecmds.c | 209 ++++++++++++++++++++++++++++-- src/backend/executor/execMain.c | 3 +- src/backend/parser/gram.y | 4 +- src/bin/psql/describe.c | 10 +- src/include/catalog/pg_attribute.h | 1 + src/test/regress/expected/constraints.out | 110 ++++++++++++++++ src/test/regress/sql/constraints.sql | 59 +++++++++ 7 files changed, 380 insertions(+), 16 deletions(-) diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index 8fa71ed..a889b6b 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -408,6 +408,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, + char *constrName, 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, @@ -471,7 +474,7 @@ static void add_column_collation_dependency(Oid relid, int32 attnum, Oid collid) static ObjectAddress ATExecDropNotNull(Relation rel, const char *colName, bool recurse, LOCKMODE lockmode); static void set_attnotnull(List **wqueue, Relation rel, AttrNumber attnum, - LOCKMODE lockmode); + bool skip_validation, LOCKMODE lockmode); static ObjectAddress ATExecSetNotNull(List **wqueue, Relation rel, char *constrname, char *colName, bool recurse, bool recursing, @@ -700,6 +703,8 @@ 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); +static char *getNNConnameForAttnum(Oid relid, AttrNumber attnum); /* ---------------------------------------------------------------- @@ -1317,7 +1322,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, false, NoLock); ObjectAddressSet(address, RelationRelationId, relationId); @@ -6189,7 +6194,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); } @@ -7710,7 +7716,7 @@ ATExecDropNotNull(Relation rel, const char *colName, bool recurse, */ static void set_attnotnull(List **wqueue, Relation rel, AttrNumber attnum, - LOCKMODE lockmode) + bool skip_validation, LOCKMODE lockmode) { Form_pg_attribute attr; @@ -7738,14 +7744,19 @@ set_attnotnull(List **wqueue, Relation rel, AttrNumber attnum, attr = (Form_pg_attribute) GETSTRUCT(tuple); Assert(attr->attnotnull == ATTRIBUTE_NOTNULL_FALSE); - attr->attnotnull = ATTRIBUTE_NOTNULL_TRUE; + + if (skip_validation) + attr->attnotnull = ATTRIBUTE_NOTNULL_INVALID; + else + attr->attnotnull = ATTRIBUTE_NOTNULL_TRUE; 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. */ - if (wqueue && !NotNullImpliedByRelConstraints(rel, attr)) + if (!skip_validation && + wqueue && !NotNullImpliedByRelConstraints(rel, attr)) { AlteredTableInfo *tab; @@ -7914,7 +7925,7 @@ ATExecSetNotNull(List **wqueue, Relation rel, char *conName, char *colName, RelationGetRelid(rel), attnum); /* Mark pg_attribute.attnotnull for the column */ - set_attnotnull(wqueue, rel, attnum, lockmode); + set_attnotnull(wqueue, rel, attnum, constraint->skip_validation, lockmode); /* * Recurse to propagate the constraint to children that don't have one. @@ -9360,6 +9371,16 @@ ATPrepAddPrimaryKey(List **wqueue, Relation rel, AlterTableCmd *cmd, { AlterTableCmd *newcmd; Constraint *nnconstr; + String *colname = lfirst(lc); + + /* + * Throw an error if relation key column has invalid not null + * constraint. + */ + if (check_for_invalid_notnull(RelationGetRelid(rel), colname->sval)) + ereport(ERROR, + errmsg("column \"%s\" of table \"%s\" is marked as NOT VALID NOT NULL constrint", + colname->sval, RelationGetRelationName(rel))); nnconstr = makeNotNullConstraint(lfirst(lc)); @@ -9373,6 +9394,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 * * There is no such command in the grammar, but parse_utilcmd.c converts @@ -9739,7 +9784,7 @@ 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, lockmode); ObjectAddressSet(address, ConstraintRelationId, ccon->conoid); } @@ -12169,7 +12214,8 @@ ATExecValidateConstraint(List **wqueue, Relation rel, char *constrName, con = (Form_pg_constraint) GETSTRUCT(tuple); if (con->contype != CONSTRAINT_FOREIGN && - con->contype != CONSTRAINT_CHECK) + con->contype != CONSTRAINT_CHECK && + con->contype != CONSTRAINT_NOTNULL) ereport(ERROR, (errcode(ERRCODE_WRONG_OBJECT_TYPE), errmsg("constraint \"%s\" of relation \"%s\" is not a foreign key or check constraint", @@ -12191,6 +12237,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, constrName, + tuple, recurse, recursing, lockmode); + } ObjectAddressSet(address, ConstraintRelationId, con->oid); } @@ -12408,6 +12459,146 @@ QueueCheckConstraintValidation(List **wqueue, Relation conrel, Relation rel, } /* + * QueueNNConstraintValidation + * + * Add an entry to the wqueue to validate the given notnull 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, + char *constrName, HeapTuple contuple, + bool recurse, bool recursing, LOCKMODE lockmode) +{ + Form_pg_constraint con; + AlteredTableInfo *tab; + HeapTuple copyTuple; + Form_pg_constraint copy_con; + + List *children = NIL; + ListCell *child; + AttrNumber attnum; + + 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. 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. + * + * We recurse before validating on the parent, to reduce risk of + * deadlocks. + */ + foreach(child, children) + { + Oid childoid = lfirst_oid(child); + Relation childrel; + 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"))); + + /* find_all_inheritors already got lock */ + childrel = table_open(childoid, NoLock); + conname = getNNConnameForAttnum(childoid, attnum); + if (conname == NULL) + continue; + + 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 catalog, 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); +} + +/* + * Function returns the invalid not null constrint name for the given + * relation and attnumber. + */ +static char * +getNNConnameForAttnum(Oid relid, AttrNumber attnum) +{ + Relation constrRel; + HeapTuple htup; + SysScanDesc conscan; + ScanKeyData skey; + char *conname = NULL; + + constrRel = table_open(ConstraintRelationId, AccessShareLock); + ScanKeyInit(&skey, + Anum_pg_constraint_conrelid, + BTEqualStrategyNumber, F_OIDEQ, + ObjectIdGetDatum(relid)); + conscan = systable_beginscan(constrRel, ConstraintRelidTypidNameIndexId, true, + NULL, 1, &skey); + + while (HeapTupleIsValid(htup = systable_getnext(conscan))) + { + Form_pg_constraint conForm = (Form_pg_constraint) GETSTRUCT(htup); + AttrNumber colnum; + + if (conForm->contype != CONSTRAINT_NOTNULL) + continue; + if (conForm->convalidated == true) + continue; + + colnum = extractNotNullColumn(htup); + + if (colnum != attnum) + continue; + + conname = pstrdup(NameStr(conForm->conname)); + break; + } + + systable_endscan(conscan); + table_close(constrRel, AccessShareLock); + + return conname; +} + +/* * transformColumnNameList - transform list of column names * * Lookup each name and return its attnum and, optionally, type and collation diff --git a/src/backend/executor/execMain.c b/src/backend/executor/execMain.c index 06688ff..d2cf422 100644 --- a/src/backend/executor/execMain.c +++ b/src/backend/executor/execMain.c @@ -1966,7 +1966,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 d388762..bfbf752 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -4196,9 +4196,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/psql/describe.c b/src/bin/psql/describe.c index 3b7ba66..583b5ac 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 b51a267..a01a0fa 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/test/regress/expected/constraints.out b/src/test/regress/expected/constraints.out index 692a69f..a16422b 100644 --- a/src/test/regress/expected/constraints.out +++ b/src/test/regress/expected/constraints.out @@ -897,6 +897,116 @@ 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 constrint +-- 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 constrint 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 constrint 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 +--Create table with NOT NULL INVALID constrint, 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 market 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 d6742f8..b62d8c6 100644 --- a/src/test/regress/sql/constraints.sql +++ b/src/test/regress/sql/constraints.sql @@ -641,6 +641,65 @@ 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 constrint 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 constrint 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; +--Create table with NOT NULL INVALID constrint, 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 market 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 -- 1.8.3.1