diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out index 7bf35602b0..576a034455 100644 --- a/contrib/postgres_fdw/expected/postgres_fdw.out +++ b/contrib/postgres_fdw/expected/postgres_fdw.out @@ -8933,6 +8933,8 @@ IMPORT FOREIGN SCHEMA import_source FROM SERVER loopback INTO import_dest1; --------+-------------------+-----------+----------+---------+-------------------- c1 | integer | | | | (column_name 'c1') c2 | character varying | | not null | | (column_name 'c2') +Check constraints: + "t1_c2_not_null" CHECK (c2 IS NOT NULL) Server: loopback FDW options: (schema_name 'import_source', table_name 't1') @@ -9006,6 +9008,8 @@ IMPORT FOREIGN SCHEMA import_source FROM SERVER loopback INTO import_dest2 --------+-------------------+-----------+----------+---------+-------------------- c1 | integer | | | | (column_name 'c1') c2 | character varying | | not null | | (column_name 'c2') +Check constraints: + "t1_c2_not_null" CHECK (c2 IS NOT NULL) Server: loopback FDW options: (schema_name 'import_source', table_name 't1') diff --git a/src/backend/catalog/heap.c b/src/backend/catalog/heap.c index 9b03579e6e..09fe18cf7a 100644 --- a/src/backend/catalog/heap.c +++ b/src/backend/catalog/heap.c @@ -101,6 +101,7 @@ static ObjectAddress AddNewRelationType(const char *typeName, Oid new_array_type); static void RelationRemoveInheritance(Oid relid); static Oid StoreRelCheck(Relation rel, const char *ccname, Node *expr, + Oid parent_oid, bool is_validated, bool is_local, int inhcount, bool is_no_inherit, bool is_internal); static void StoreConstraints(Relation rel, List *cooked_constraints, @@ -2061,7 +2062,7 @@ SetAttrMissing(Oid relid, char *attname, char *value) * The OID of the new constraint is returned. */ static Oid -StoreRelCheck(Relation rel, const char *ccname, Node *expr, +StoreRelCheck(Relation rel, const char *ccname, Node *expr, Oid parent_oid, bool is_validated, bool is_local, int inhcount, bool is_no_inherit, bool is_internal) { @@ -2129,7 +2130,7 @@ StoreRelCheck(Relation rel, const char *ccname, Node *expr, false, /* Is Deferrable */ false, /* Is Deferred */ is_validated, - InvalidOid, /* no parent constraint */ + parent_oid, RelationGetRelid(rel), /* relation */ attNos, /* attrs in the constraint */ keycount, /* # key attrs in the constraint */ @@ -2198,7 +2199,7 @@ StoreConstraints(Relation rel, List *cooked_constraints, bool is_internal) break; case CONSTR_CHECK: con->conoid = - StoreRelCheck(rel, con->name, con->expr, + StoreRelCheck(rel, con->name, con->expr, con->parent_oid, !con->skip_validation, con->is_local, con->inhcount, con->is_no_inherit, is_internal); @@ -2403,6 +2404,7 @@ AddRelationNewConstraints(Relation rel, * (We omit the duplicate constraint from the result, which is * what ATAddCheckConstraint wants.) */ + /* XXX need to handle this case? */ if (MergeWithExistingConstraint(rel, ccname, expr, allow_merge, is_local, cdef->initially_valid, @@ -2452,8 +2454,9 @@ AddRelationNewConstraints(Relation rel, * OK, store it. */ constrOid = - StoreRelCheck(rel, ccname, expr, cdef->initially_valid, is_local, - is_local ? 0 : 1, cdef->is_no_inherit, is_internal); + StoreRelCheck(rel, ccname, expr, cdef->parent_oid, cdef->initially_valid, + is_local, is_local ? 0 : 1, cdef->is_no_inherit, + is_internal); numchecks++; @@ -2461,6 +2464,7 @@ AddRelationNewConstraints(Relation rel, cooked->contype = CONSTR_CHECK; cooked->conoid = constrOid; cooked->name = ccname; + cooked->parent_oid = cdef->parent_oid; cooked->attnum = 0; cooked->expr = expr; cooked->skip_validation = cdef->skip_validation; diff --git a/src/backend/catalog/pg_constraint.c b/src/backend/catalog/pg_constraint.c index bb65fb1e0a..48a82c2b54 100644 --- a/src/backend/catalog/pg_constraint.c +++ b/src/backend/catalog/pg_constraint.c @@ -26,6 +26,7 @@ #include "catalog/pg_constraint.h" #include "catalog/pg_operator.h" #include "catalog/pg_type.h" +#include "commands/constraint.h" #include "commands/defrem.h" #include "commands/tablecmds.h" #include "utils/array.h" @@ -562,6 +563,125 @@ ChooseConstraintName(const char *name1, const char *name2, return conname; } +/* + * Find and return the pg_constraint tuple that implements a validated + * NOT NULL constraint for the given column of the given relation. + * + * If there's more than one such constraint and *multiple is not NULL, + * we set that true. + * + * XXX This would be much 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(Relation rel, AttrNumber attnum, bool *multiple) +{ + Relation pg_constraint; + HeapTuple conTup, + retval = NULL; + SysScanDesc scan; + ScanKeyData key; + + if (multiple) + *multiple = false; + + pg_constraint = table_open(ConstraintRelationId, AccessShareLock); + ScanKeyInit(&key, + Anum_pg_constraint_conrelid, + BTEqualStrategyNumber, F_OIDEQ, + ObjectIdGetDatum(RelationGetRelid(rel))); + scan = systable_beginscan(pg_constraint, ConstraintRelidTypidNameIndexId, + true, NULL, 1, &key); + + while (HeapTupleIsValid(conTup = systable_getnext(scan))) + { + Form_pg_constraint con = (Form_pg_constraint) GETSTRUCT(conTup); + AttrNumber conkey; + ArrayType *arr; + Datum adatum; + bool isnull; + char *constcolname; + + /* + * We're looking for a CHECK constraint that's marked validated, with + * the column we're looking for as the sole element in conkey, and + * from whose expression our NOT NULL extractor returns a column name. + * (We verify only in an assertion that that column is in fact the one + * we want, because that seems a redundant check.) + */ + if (con->contype != CONSTRAINT_CHECK) + continue; + + if (!con->convalidated) + continue; + + adatum = SysCacheGetAttr(CONSTROID, conTup, + Anum_pg_constraint_conkey, &isnull); + if (isnull) + continue; + arr = DatumGetArrayTypeP(adatum); + if (ARR_NDIM(arr) != 1 || + ARR_HASNULL(arr) || + ARR_ELEMTYPE(arr) != INT2OID) + elog(ERROR, "conkey is not a 1-D smallint array"); + if (ARR_DIMS(arr)[0] != 1) + goto nope; + + memcpy(&conkey, ARR_DATA_PTR(arr), sizeof(int16)); + if (conkey != attnum) + goto nope; + + constcolname = tryExtractNotNullFromCatalog(conTup, rel); + if (constcolname == NULL) + goto nope; + + /* + * Surely tryExtractNotNullFromCatalog won't give us a mismatching + * constraint. + */ + Assert(strcmp(constcolname, + get_attname(RelationGetRelid(rel), attnum, false)) == 0); + + /* Found it */ + if (retval != NULL) + { + Assert(multiple); + *multiple = true; + break; + } + + retval = heap_copytuple(conTup); + if (multiple == NULL) + break; + +nope: + pfree(arr); + continue; + } + + systable_endscan(scan); + table_close(pg_constraint, AccessShareLock); + + return retval; +} + +/* + * Find and return the pg_constraint tuple that implements a validated + * NOT NULL constraint for the given column of the given relation. + * + * If there's more than one such constraint and *multiple is not NULL, + * we set that true. + */ +HeapTuple +findNotNullConstraint(Relation rel, const char *colname, bool *multiple) +{ + AttrNumber attnum = get_attnum(RelationGetRelid(rel), colname); + + return findNotNullConstraintAttnum(rel, attnum, multiple); +} + /* * Delete a single constraint record. */ diff --git a/src/backend/commands/constraint.c b/src/backend/commands/constraint.c index 721de178ca..f7c377bb9d 100644 --- a/src/backend/commands/constraint.c +++ b/src/backend/commands/constraint.c @@ -17,11 +17,20 @@ #include "access/heapam.h" #include "access/tableam.h" #include "catalog/index.h" +#include "catalog/pg_constraint.h" +#include "commands/constraint.h" #include "commands/trigger.h" #include "executor/executor.h" +#include "nodes/makefuncs.h" #include "utils/builtins.h" #include "utils/rel.h" #include "utils/snapmgr.h" +#include "utils/syscache.h" + + +static Constraint *makeNNCheckConstraint(Oid nspid, char *constraint_name, + const char *relname, const char *colname, + Oid parent_oid, Node *expr); /* @@ -203,3 +212,187 @@ unique_key_recheck(PG_FUNCTION_ARGS) return PointerGetDatum(NULL); } + +/* + * Create and return a Constraint node representing a "col IS NOT NULL" + * expression using a ColumnRef within, for the given relation and column. + * + * If the constraint name is not provided, a standard one is generated. + * + * Note: this is a "raw" node that must undergo transformation. + */ +Constraint * +makeCheckNotNullConstraint(Oid nspid, char *constraint_name, + const char *relname, const char *colname, + bool is_row, Oid parent_oid) +{ + ColumnRef *colref; + Node *nullexpr; + + colref = (ColumnRef *) makeNode(ColumnRef); + colref->fields = list_make1(makeString(pstrdup(colname))); + + if (is_row) + { + A_Expr *expr; + A_Const *constnull; + + constnull = makeNode(A_Const); + constnull->isnull = true; + + expr = makeSimpleA_Expr(AEXPR_DISTINCT, "=", + (Node *) colref, (Node *) constnull, -1); + nullexpr = (Node *) expr; + } + else + { + NullTest *nulltest; + + nulltest = makeNode(NullTest); + nulltest->argisrow = is_row; + nulltest->nulltesttype = IS_NOT_NULL; + nulltest->arg = (Expr *) colref; + + nullexpr = (Node *) nulltest; + } + + return makeNNCheckConstraint(nspid, constraint_name, relname, colname, + parent_oid, nullexpr); +} + +/* + * Return a CHECK constraint with the given names and expression, for use in + * NOT NULL column constraints. + * + * subroutine for makeCheckNotNullConstraint and + * makeCheckDistinctNotNullConstraint + */ +static Constraint * +makeNNCheckConstraint(Oid nspid, char *constraint_name, const char *relname, + const char *colname, Oid parent_oid, Node *nulltest) +{ + Constraint *check = makeNode(Constraint); + + check->contype = CONSTR_CHECK; + check->location = -1; + check->conname = constraint_name ? constraint_name : + ChooseConstraintName(relname, colname, "not_null", nspid, NIL); + check->parent_oid = parent_oid; + check->deferrable = false; + check->initdeferred = false; + + check->is_no_inherit = false; + check->raw_expr = nulltest; + check->cooked_expr = NULL; + + check->skip_validation = false; + check->initially_valid = true; + + return check; +} + +/* + * Given the Node representation for a CHECK (col IS NOT NULL) constraint, + * return the column name that it is for. If it doesn't represent a constraint + * of that shape, NULL is returned. 'rel' is the relation that the constraint is + * for. + * + * XXX Would it be possible to return a column number instead? When a ColumnRef + * is involved, it's not. Maybe augment the API to return both values. + */ +char * +tryExtractNotNullFromNode(Node *node, Relation rel) +{ + if (node == NULL) + return NULL; + + /* + * if no rel is passed, we can only check this much + */ + if (rel == NULL) + { + if (IsA(node, NullTest)) + { + NullTest *nulltest = (NullTest *) node; + + if (nulltest->nulltesttype == IS_NOT_NULL) + { + if (IsA(nulltest->arg, ColumnRef)) + { + ColumnRef *colref = (ColumnRef *) nulltest->arg; + + if (list_length(colref->fields) == 1) + return strVal(linitial(colref->fields)); + } + } + } + return false; + } + + if (IsA(node, Constraint)) + { + Constraint *constraint = (Constraint *) node; + + if (constraint->cooked_expr != NULL) + return tryExtractNotNullFromNode(stringToNode(constraint->cooked_expr), rel); + else + return tryExtractNotNullFromNode(constraint->raw_expr, rel); + } + + if (IsA(node, NullTest)) + { + NullTest *nulltest = (NullTest *) node; + + if (nulltest->nulltesttype == IS_NOT_NULL) + { + if (IsA(nulltest->arg, Var)) + { + Var *var = (Var *) nulltest->arg; + + return NameStr(TupleDescAttr(RelationGetDescr(rel), + var->varattno - 1)->attname); + } + } + } + + /* + * XXX Need to check a few more possible wordings of NOT NULL: + * + * - foo IS DISTINCT FROM NULL + * - NOT (foo IS NULL) + */ + + return NULL; +} + +/* + * Like tryExtractNotNullFromNode, but use a pg_constraint row as input. + */ +char * +tryExtractNotNullFromCatalog(HeapTuple constrTup, Relation rel) +{ + Datum val; + bool isnull; + char *conbin; + Node *node; + char *colname; + + /* only tuples for CHECK constraints should be given */ + Assert(((Form_pg_constraint) GETSTRUCT(constrTup))->contype == CONSTRAINT_CHECK); + + val = SysCacheGetAttr(CONSTROID, constrTup, Anum_pg_constraint_conbin, + &isnull); + if (isnull) + elog(ERROR, "null conbin for constraint %u", + ((Form_pg_constraint) GETSTRUCT(constrTup))->oid); + conbin = TextDatumGetCString(val); + node = (Node *) stringToNode(conbin); + + colname = tryExtractNotNullFromNode(node, rel); + + /* XXX worth it? */ + pfree(conbin); + pfree(node); + + return colname; +} diff --git a/src/backend/commands/createas.c b/src/backend/commands/createas.c index 152c29b551..89987c5821 100644 --- a/src/backend/commands/createas.c +++ b/src/backend/commands/createas.c @@ -104,6 +104,8 @@ create_ctas_internal(List *attrList, IntoClause *into) create->inhRelations = NIL; create->ofTypename = NULL; create->constraints = NIL; + create->notnull_check = NIL; + create->notnull_bare = NIL; create->options = into->options; create->oncommit = into->onCommit; create->tablespacename = into->tableSpaceName; diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index dacc989d85..53d45e353d 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -52,6 +52,7 @@ #include "catalog/toasting.h" #include "commands/cluster.h" #include "commands/comment.h" +#include "commands/constraint.h" #include "commands/defrem.h" #include "commands/event_trigger.h" #include "commands/policy.h" @@ -67,7 +68,6 @@ #include "miscadmin.h" #include "nodes/makefuncs.h" #include "nodes/nodeFuncs.h" -#include "nodes/parsenodes.h" #include "optimizer/optimizer.h" #include "parser/parse_clause.h" #include "parser/parse_coerce.h" @@ -349,7 +349,7 @@ static void truncate_check_activity(Relation rel); static void RangeVarCallbackForTruncate(const RangeVar *relation, Oid relId, Oid oldRelId, void *arg); static List *MergeAttributes(List *schema, List *supers, char relpersistence, - bool is_partition, List **supconstr); + bool is_partition, List **supconstr, List **notnullcols); static bool MergeCheckConstraint(List *constraints, char *name, Node *expr); static void MergeAttributesIntoExisting(Relation child_rel, Relation parent_rel); static void MergeConstraintsIntoExisting(Relation child_rel, Relation parent_rel); @@ -430,14 +430,16 @@ static bool check_for_column_name_collision(Relation rel, const char *colname, bool if_not_exists); static void add_column_datatype_dependency(Oid relid, int32 attnum, Oid typid); static void add_column_collation_dependency(Oid relid, int32 attnum, Oid collid); -static void ATPrepDropNotNull(Relation rel, bool recurse, bool recursing); -static ObjectAddress ATExecDropNotNull(Relation rel, const char *colName, LOCKMODE lockmode); +static ObjectAddress ATExecDropNotNull(Relation rel, const char *colName, bool recurse, + LOCKMODE lockmode); static void ATPrepSetNotNull(List **wqueue, Relation rel, AlterTableCmd *cmd, bool recurse, bool recursing, LOCKMODE lockmode, AlterTableUtilityContext *context); -static ObjectAddress ATExecSetNotNull(AlteredTableInfo *tab, Relation rel, - const char *colName, LOCKMODE lockmode); +static ObjectAddress ATExecSetNotNull(List **wqueue, AlteredTableInfo *tab, + Relation rel, bool direct, bool attnotnull_only, + char *constrname, const char *colName, + LOCKMODE lockmode); static void ATExecCheckNotNull(AlteredTableInfo *tab, Relation rel, const char *colName, LOCKMODE lockmode); static bool NotNullImpliedByRelConstraints(Relation rel, Form_pg_attribute attr); @@ -484,6 +486,17 @@ static ObjectAddress ATAddCheckConstraint(List **wqueue, Constraint *constr, bool recurse, bool recursing, bool is_readd, LOCKMODE lockmode); +static ObjectAddress ATAddCheckConstraint_internal(List **wqueue, + AlteredTableInfo *tab, Relation rel, + Constraint *constr, + bool recursing, + bool check_it, bool is_readd, + LOCKMODE lockmode); +static void ATAddCheckConstraint_recurse(List **wqueue, List *children, + Constraint *constr, Oid parent_constraint_oid, + bool check_it, bool is_readd, + List **already_done_rels, + LOCKMODE lockmode); static ObjectAddress ATAddForeignKeyConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel, Constraint *fkconstraint, bool recurse, bool recursing, @@ -853,19 +866,42 @@ DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId, /* * Look up inheritance ancestors and generate relation schema, including - * inherited attributes. (Note that stmt->tableElts is destructively - * modified by MergeAttributes.) + * inherited attributes. (Note that stmt->tableElts and ->notnull_check + * are destructively modified by MergeAttributes.) */ stmt->tableElts = MergeAttributes(stmt->tableElts, inheritOids, stmt->relation->relpersistence, stmt->partbound != NULL, - &old_constraints); + &old_constraints, &stmt->notnull_check); + + /* + * If there are any additional columns to be marked attnotnull, prepare to + * do so. Be careful to leave notnull_check unmodified though, as we need + * it again later. + */ + foreach(listptr, list_concat(stmt->notnull_bare, stmt->notnull_check)) + { + char *colname = strVal(lfirst(listptr)); + ListCell *lc; + + foreach(lc, stmt->tableElts) + { + ColumnDef *thiscol = lfirst(lc); + + if (strcmp(thiscol->colname, colname) == 0) + { + thiscol->is_not_null = true; + break; + } + } + } /* * Create a tuple descriptor from the relation schema. Note that this - * deals with column names, types, and NOT NULL constraints, but not - * default values or CHECK constraints; we handle those below. + * deals with column names, types, and in-descriptor NOT NULL constraints, + * but not default values or CHECK constraints (including the CHECK (foo + * IS NOT NULL) part of not-null constraints); we handle those below. */ descriptor = BuildDescForRelation(stmt->tableElts); @@ -1239,13 +1275,67 @@ DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId, } /* - * Now add any newly specified CHECK constraints to the new relation. Same - * as for defaults above, but these need to come after partitioning is set - * up. + * Now add any newly specified CHECK constraints to the new relation, + * including manufactured CHECK constraints for columns declared NOT NULL + * in various ways (straight NOT NULL, serial, identity, etc). Same as for + * defaults above, but these need to come after partitioning is set up. */ - if (stmt->constraints) - AddRelationNewConstraints(rel, NIL, stmt->constraints, + if (stmt->constraints || stmt->notnull_check != NIL) + { + List *nncks = NIL; + Bitmapset *seencols = NULL; + + /* + * First, walk all the explicitly declared constraints and mark + * any columns that appear in a CHECK (foo IS NOT NULL) constraint + * as seen. This way, named constraints take precedence over + * unnamed ones. + */ + foreach(listptr, stmt->constraints) + { + Constraint *c = lfirst(listptr); + char *colname; + + if (c->contype != CONSTRAINT_CHECK) + continue; + colname = tryExtractNotNullFromNode((Node *) c, rel); + if (!colname) + continue; + seencols = bms_add_member(seencols, + get_attnum(RelationGetRelid(rel), colname)); + } + + /* + * Manufacture CHECK constraints for any columns marked NOT NULL that + * we didn't already see above. + */ + foreach(listptr, stmt->notnull_check) + { + Constraint *newcons; + char *colname = strVal(lfirst(listptr)); + AttrNumber colnum; + bool is_row = false; /* FIXME */ + + /* Don't create duplicate constraints */ + colnum = get_attnum(RelationGetRelid(rel), colname); + if (bms_is_member(colnum, seencols)) + continue; + + newcons = makeCheckNotNullConstraint(namespaceId, + NULL, + relname, + colname, + is_row, + InvalidOid); + seencols = bms_add_member(seencols, colnum); + nncks = lappend(nncks, newcons); + } + + /* And create all collected constraints */ + AddRelationNewConstraints(rel, NIL, + list_concat(nncks, stmt->constraints), true, true, false, queryString); + } ObjectAddressSet(address, RelationRelationId, relationId); @@ -2280,6 +2370,8 @@ storage_name(char c) * Output arguments: * 'supconstr' receives a list of constraints belonging to the parents, * updated as necessary to be valid for the child. + * 'notnullcols' is appended additional columns that have to receive a + * CHECK (IS NOT NULL) constraint. * * Return value: * Completed schema list. @@ -2324,8 +2416,9 @@ storage_name(char c) *---------- */ static List * -MergeAttributes(List *schema, List *supers, char relpersistence, - bool is_partition, List **supconstr) +MergeAttributes(List *schema, List *supers, + char relpersistence, bool is_partition, + List **supconstr, List **notnullcols) { List *inhSchema = NIL; List *constraints = NIL; @@ -2443,6 +2536,7 @@ MergeAttributes(List *schema, List *supers, char relpersistence, AttrNumber parent_attno; ListCell *lc1; ListCell *lc2; + Bitmapset *pkattrs; /* caller already got lock */ relation = table_open(parent, NoLock); @@ -2531,6 +2625,13 @@ MergeAttributes(List *schema, List *supers, char relpersistence, /* We can't process inherited defaults until newattmap is complete. */ inherited_defaults = cols_with_defaults = NIL; + /* + * All columns that are part of the parent's primary key need to get a + * NOT NULL constraint, if they don't have one already. + */ + pkattrs = RelationGetIndexAttrBitmap(relation, + INDEX_ATTR_BITMAP_PRIMARY_KEY); + for (parent_attno = 1; parent_attno <= tupleDesc->natts; parent_attno++) { @@ -2615,6 +2716,18 @@ MergeAttributes(List *schema, List *supers, char relpersistence, } def->inhcount++; + + /* + * Columns in the parent's primary key get an extra CHECK (NOT + * NULL) constraint. + */ + if (bms_is_member(parent_attno - FirstLowInvalidHeapAttributeNumber, + pkattrs)) + { + *notnullcols = lappend(*notnullcols, + makeString(pstrdup(attributeName))); + } + /* Merge of NOT NULL constraints = OR 'em together */ def->is_not_null |= attribute->attnotnull; /* Default and other constraints are handled below */ @@ -2655,6 +2768,15 @@ MergeAttributes(List *schema, List *supers, char relpersistence, def->compression = NULL; inhSchema = lappend(inhSchema, def); newattmap->attnums[parent_attno - 1] = ++child_attno; + + /* + * Columns in the parent's primary key get a CHECK (foo IS NOT + * NULL) constraint. + */ + if (bms_is_member(parent_attno - + FirstLowInvalidHeapAttributeNumber, + pkattrs)) + *notnullcols = lappend(*notnullcols, makeString(def->colname)); } /* @@ -2787,6 +2909,7 @@ MergeAttributes(List *schema, List *supers, char relpersistence, cooked = (CookedConstraint *) palloc(sizeof(CookedConstraint)); cooked->contype = CONSTR_CHECK; cooked->conoid = InvalidOid; /* until created */ + cooked->parent_oid = check[i].ccoid; cooked->name = pstrdup(name); cooked->attnum = 0; /* not used for constraints */ cooked->expr = expr; @@ -2993,8 +3116,7 @@ MergeAttributes(List *schema, List *supers, char relpersistence, /* * Now that we have the column definition list for a partition, we can * check whether the columns referenced in the column constraint specs - * actually exist. Also, we merge NOT NULL and defaults into each - * corresponding column definition. + * actually exist. */ if (is_partition) { @@ -3011,7 +3133,6 @@ MergeAttributes(List *schema, List *supers, char relpersistence, if (strcmp(coldef->colname, restdef->colname) == 0) { found = true; - coldef->is_not_null |= restdef->is_not_null; /* * Override the parent's default value for this column @@ -4262,6 +4383,7 @@ AlterTableGetLockLevel(List *cmds) case AT_AddIndexConstraint: case AT_ReplicaIdentity: case AT_SetNotNull: + case AT_SetAttNotNull: case AT_EnableRowSecurity: case AT_DisableRowSecurity: case AT_ForceRowSecurity: @@ -4561,10 +4683,12 @@ ATPrepCmd(List **wqueue, Relation rel, AlterTableCmd *cmd, break; case AT_DropNotNull: /* ALTER COLUMN DROP NOT NULL */ ATSimplePermissions(cmd->subtype, rel, ATT_TABLE | ATT_FOREIGN_TABLE); - ATPrepDropNotNull(rel, recurse, recursing); - ATSimpleRecursion(wqueue, rel, cmd, recurse, lockmode, context); + /* Set up recursion for phase 2; no other prep needed */ + if (recurse) + cmd->recurse = true; pass = AT_PASS_DROP; break; + case AT_SetAttNotNull: /* XXX ok to share implementation? */ case AT_SetNotNull: /* ALTER COLUMN SET NOT NULL */ ATSimplePermissions(cmd->subtype, rel, ATT_TABLE | ATT_FOREIGN_TABLE); /* Need command-specific recursion decision */ @@ -4959,10 +5083,15 @@ ATExecCmd(List **wqueue, AlteredTableInfo *tab, address = ATExecDropIdentity(rel, cmd->name, cmd->missing_ok, lockmode); break; case AT_DropNotNull: /* ALTER COLUMN DROP NOT NULL */ - address = ATExecDropNotNull(rel, cmd->name, lockmode); + address = ATExecDropNotNull(rel, cmd->name, cmd->recurse, lockmode); break; case AT_SetNotNull: /* ALTER COLUMN SET NOT NULL */ - address = ATExecSetNotNull(tab, rel, cmd->name, lockmode); + address = ATExecSetNotNull(wqueue, tab, rel, true, false, NULL, + cmd->name, lockmode); + break; + case AT_SetAttNotNull: + address = ATExecSetNotNull(wqueue, tab, rel, true, true, NULL, + cmd->name, lockmode); break; case AT_CheckNotNull: /* check column is already marked NOT NULL */ ATExecCheckNotNull(tab, rel, cmd->name, lockmode); @@ -5331,6 +5460,7 @@ ATParseTransformCmd(List **wqueue, AlteredTableInfo *tab, Relation rel, switch (cmd2->subtype) { case AT_SetNotNull: + case AT_SetAttNotNull: /* Need command-specific recursion decision */ ATPrepSetNotNull(wqueue, rel, cmd2, recurse, false, @@ -5396,8 +5526,8 @@ ATParseTransformCmd(List **wqueue, AlteredTableInfo *tab, Relation rel, newcmd = cmd2; } else - elog(ERROR, "ALTER TABLE scheduling failure: bogus item for pass %d", - pass); + elog(ERROR, "ALTER TABLE scheduling failure: bogus item %s for pass %d, cmd %s", + newcmd ? nodeToString(newcmd) : "(null)", pass, nodeToString(cmd)); } } @@ -6119,6 +6249,8 @@ alter_table_type_to_string(AlterTableType cmdtype) return "ALTER COLUMN ... DROP NOT NULL"; case AT_SetNotNull: return "ALTER COLUMN ... SET NOT NULL"; + case AT_SetAttNotNull: + return NULL; /* not real grammar */ case AT_DropExpression: return "ALTER COLUMN ... DROP EXPRESSION"; case AT_CheckNotNull: @@ -6682,8 +6814,7 @@ ATPrepAddColumn(List **wqueue, Relation rel, bool recurse, bool recursing, */ static ObjectAddress ATExecAddColumn(List **wqueue, AlteredTableInfo *tab, Relation rel, - AlterTableCmd **cmd, - bool recurse, bool recursing, + AlterTableCmd **cmd, bool recurse, bool recursing, LOCKMODE lockmode, int cur_pass, AlterTableUtilityContext *context) { @@ -7188,44 +7319,45 @@ add_column_collation_dependency(Oid relid, int32 attnum, Oid collid) } } -/* - * ALTER TABLE ALTER COLUMN DROP NOT NULL - */ - -static void -ATPrepDropNotNull(Relation rel, bool recurse, bool recursing) -{ - /* - * If the parent is a partitioned table, like check constraints, we do not - * support removing the NOT NULL while partitions exist. - */ - if (rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE) - { - PartitionDesc partdesc = RelationGetPartitionDesc(rel, true); - - Assert(partdesc != NULL); - if (partdesc->nparts > 0 && !recurse && !recursing) - ereport(ERROR, - (errcode(ERRCODE_INVALID_TABLE_DEFINITION), - errmsg("cannot remove constraint from only the partitioned table when partitions exist"), - errhint("Do not specify the ONLY keyword."))); - } -} - /* * Return the address of the modified column. If the column was already * nullable, InvalidObjectAddress is returned. + * + * + * There are two ways in which NOT NULL constraints can be dropped: DROP NOT + * NULL and DROP CONSTRAINT. For DROP NOT NULL, the algorithm is: + * + * 0. search for the relevant constraint. If there's more than one, error + * 1. drop the constraint (by OID) + * 2. see if after the drop we can unmark (must be true, because of 1) + * 3. recurse on 2 + * + * For DROP CONSTRAINT, the algorithm is: + * 0. look up constraint OID by name + * 1. drop the constraint (by OID) + * 2. see if after drop we can unmark (may not be true, it's ok if so) + * 3. recurse on 1 + * + * Recursion: + * - for each children + * * look up the constraint that is child of the given constraint + * * drop the constraint + * * see if after drop we can unmark (may not be true, it's OK if so) + * * if it has children, recurse */ static ObjectAddress -ATExecDropNotNull(Relation rel, const char *colName, LOCKMODE lockmode) +ATExecDropNotNull(Relation rel, const char *colName, bool recurse, + LOCKMODE lockmode) { HeapTuple tuple; + HeapTuple conTup; Form_pg_attribute attTup; AttrNumber attnum; Relation attr_rel; List *indexoidlist; ListCell *indexoidscan; ObjectAddress address; + bool multiple; /* * lookup the attribute @@ -7254,6 +7386,24 @@ ATExecDropNotNull(Relation rel, const char *colName, LOCKMODE lockmode) errmsg("column \"%s\" of relation \"%s\" is an identity column", colName, RelationGetRelationName(rel)))); + /* + * It's not OK to remove a constraint only for the partitioned table + * itself and leave it in the partitions, so disallow that. But for + * legacy inheritance, it's not a problem. + */ + if (!recurse && rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE) + { + PartitionDesc partdesc; + + partdesc = RelationGetPartitionDesc(rel, true); + + if (partdesc->nparts > 0) + ereport(ERROR, + errcode(ERRCODE_INVALID_TABLE_DEFINITION), + errmsg("cannot remove constraint from only the partitioned table when partitions exist"), + errhint("Do not specify the ONLY keyword.")); + } + /* * Check that the attribute is not in a primary key or in an index used as * a replica identity. @@ -7310,7 +7460,35 @@ ATExecDropNotNull(Relation rel, const char *colName, LOCKMODE lockmode) list_free(indexoidlist); - /* If rel is partition, shouldn't drop NOT NULL if parent has the same */ + /* + * Find the constraint that makes this column NOT NULL. If there's more + * than one, we cannot cope well, so give up. + */ + conTup = findNotNullConstraint(rel, colName, &multiple); + if (conTup == NULL) + ereport(ERROR, + errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE), + errmsg("no NOT NULL constraint found to drop")); + if (multiple) + ereport(ERROR, + errcode(ERRCODE_INVALID_TABLE_DEFINITION), + errmsg("cannot DROP NOT NULL when multiple possible constraints exist"), + errhint("Consider specifying which constraint to drop with ALTER TABLE .. DROP CONSTRAINT.")); + /* XXX clean this up? */ + { + ObjectAddress conobj; + + ObjectAddressSet(conobj, + ConstraintRelationId, + ((Form_pg_constraint) GETSTRUCT(conTup))->oid); + performDeletion(&conobj, DROP_RESTRICT, 0); + } + + /* + * If rel is partition, shouldn't drop NOT NULL if parent has the same. + * XXX is this consideration still valid? Can we get rid of this by + * changing the type of dependency between the two constraints instead? + */ if (rel->rd_rel->relispartition) { Oid parentId = get_partition_parent(RelationGetRelid(rel), false); @@ -7422,10 +7600,19 @@ ATPrepSetNotNull(List **wqueue, Relation rel, /* * Return the address of the modified column. If the column was already NOT * NULL, InvalidObjectAddress is returned. + * + * When ALTER TABLE/ALTER COLUMN/SET NOT NULL is called, 'direct' is true + * and we avoid creating a duplicate constraint. However, if ALTER TABLE/ + * ADD CONSTRAINT is called to create an IS NOT NULL constraint, we do not + * avoid a duplicate constraint. + * + * XXX maybe better to split things in small subroutines for SET NOT NULL + * and ADD CONSTRAINT to use, rather than this labyrinth. */ static ObjectAddress -ATExecSetNotNull(AlteredTableInfo *tab, Relation rel, - const char *colName, LOCKMODE lockmode) +ATExecSetNotNull(List **wqueue, AlteredTableInfo *tab, Relation rel, + bool direct, bool attnotnull_only, + char *constrname, const char *colName, LOCKMODE lockmode) { HeapTuple tuple; AttrNumber attnum; @@ -7485,6 +7672,50 @@ ATExecSetNotNull(AlteredTableInfo *tab, Relation rel, InvokeObjectPostAlterHook(RelationRelationId, RelationGetRelid(rel), attnum); + /* + * We also add a new pg_constraint row. Use ATAddCheckConstraint_internal + * for that, letting it know that it doesn't need to test the constraint; + * we already did that above, if necessary. However, we don't do this for + * system catalogs, because that creates relcache recursion issues. Also + * skip it if we already have one equivalent constraint. + */ + if (!attnotnull_only && !IsCatalogRelation(rel)) + { + HeapTuple constr; + + /* See if there's one already, and skip this if so. */ + constr = findNotNullConstraint(rel, colName, NULL); + if (constr && direct) + heap_freetuple(constr); /* nothing to do */ + else + { + Constraint *newconstr; + ObjectAddress addr; + List *children; + List *already_done_rels; + + newconstr = makeCheckNotNullConstraint(rel->rd_rel->relnamespace, + constrname, + NameStr(rel->rd_rel->relname), + colName, + false, /* XXX is_row */ + InvalidOid); + + addr = ATAddCheckConstraint_internal(wqueue, tab, rel, newconstr, + false, false, false, lockmode); + already_done_rels = list_make1_oid(RelationGetRelid(rel)); + + /* and recurse into children, if there are any */ + children = find_inheritance_children(RelationGetRelid(rel), lockmode); + ATAddCheckConstraint_recurse(wqueue, children, newconstr, + addr.objectId, + /* XXX verify these bools */ + true, false, + &already_done_rels, + lockmode); + } + } + table_close(attr_rel, RowExclusiveLock); return address; @@ -8880,17 +9111,172 @@ static ObjectAddress ATAddCheckConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel, Constraint *constr, bool recurse, bool recursing, bool is_readd, LOCKMODE lockmode) +{ + char *colname; + ObjectAddress address; + List *children; + List *already_done_rels; + + Assert(constr->contype == CONSTR_CHECK); + + /* At the top level, permission check was done in ATPrepCmd */ + + /* + * If the constraint we're adding is CHECK (col IS NOT NULL), route it + * through ATExecSetNotNull instead of handling it here. + * + * The reason for this is to get the attnotnull bit set for the column. + */ + colname = tryExtractNotNullFromNode((Node *) constr, rel); + if (colname != NULL) + return ATExecSetNotNull(wqueue, tab, rel, false, false, + constr->conname, colname, lockmode); + + /* Not a single-column NOT NULL constraint -- do the regular dance */ + address = ATAddCheckConstraint_internal(wqueue, tab, rel, constr, + recursing, true, is_readd, + lockmode); + + /* + * If adding a NO INHERIT constraint, no need to find our children. + */ + if (constr->is_no_inherit) + return address; + + /* If the constraint was merged with some preexisting one, we're done. + * We mustn't recurse to child tables in this case, because they've + * already got the constraint, and visiting them again would leave to an + * incorrect value for coninhcount. + */ + if (address.classId == InvalidOid) + return address; + + /* + * Propagate to children as appropriate. Unlike most other ALTER + * routines, we have to do this one level of recursion at a time, because + * some children may already have a similar constraint with which this one + * is merged. If that happens, we need to stop recursing at that point. + * So we can't use find_all_inheritors to do it in one pass. + */ + children = find_inheritance_children(RelationGetRelid(rel), lockmode); + + /* + * Check if ONLY was specified with ALTER TABLE. If so, allow the + * constraint creation only if there are no children currently. Error out + * otherwise. + */ + if (!recurse && children != NIL) + ereport(ERROR, + (errcode(ERRCODE_INVALID_TABLE_DEFINITION), + errmsg("constraint must be added to child tables too"))); + + already_done_rels = list_make1_oid(RelationGetRelid(rel)); + ATAddCheckConstraint_recurse(wqueue, children, constr, constr->parent_oid, + true, is_readd, /* XXX verify bools */ + &already_done_rels, + lockmode); + + return address; +} + +/* + * Recursive subroutine for ATAddCheckConstraint and siblings. + * + * It applies ATAddCheckConstraint_internal to each relation in the given + * children list; and it recurses into any children that any of them might + * have. + * + * *already_done_rels is a list of relations which have already been visited + * by ATAddCheckConstraint_internal for this constraint (and child relations + * are added to the list). This is used to avoid modifying tables twice in + * case of multiple inheritance. + */ +static void +ATAddCheckConstraint_recurse(List **wqueue, List *children, Constraint *constr, + Oid parent_constraint_oid, + bool check_it, bool is_readd, + List **already_done_rels, + LOCKMODE lockmode) +{ + ListCell *child; + + foreach(child, children) + { + Oid childrelid = lfirst_oid(child); + Relation childrel; + AlteredTableInfo *childtab; + ObjectAddress addr; + + /* Don't do it twice to the same rel */ + if (list_member_oid(*already_done_rels, childrelid)) + continue; + + /* caller already got lock */ + childrel = table_open(childrelid, NoLock); + CheckTableNotInUse(childrel, "ALTER TABLE"); + + ATSimplePermissions(AT_AddConstraint, childrel, + ATT_TABLE | ATT_FOREIGN_TABLE); + + /* Find or create work queue entry for this table */ + childtab = ATGetQueueEntry(wqueue, childrel); + + /* Create the constraint on this relation */ + constr->parent_oid = parent_constraint_oid; + addr = ATAddCheckConstraint_internal(wqueue, childtab, childrel, + constr, true, + true, /* XXX verify this */ + is_readd, lockmode); + *already_done_rels = lappend_oid(*already_done_rels, childrelid); + + /* If this relation has children, recurse into them as well */ + if (childrel->rd_rel->relhassubclass) + { + List *subchld = find_inheritance_children(childrelid, lockmode); + + /* + * Increment command counter, in case we visit the same table more + * than once. This is only possible with legacy inheritance, not + * partitioning. + */ + if (childrel->rd_rel->relkind != RELKIND_PARTITIONED_TABLE) + CommandCounterIncrement(); + + /* XXX update parent constraint OID */ + ATAddCheckConstraint_recurse(wqueue, subchld, constr, + addr.objectId, + check_it, is_readd, + already_done_rels, + lockmode); + + list_free(subchld); + } + + table_close(childrel, NoLock); + } +} + +/* + * Workhorse for various situations that need to add some form of CHECK + * constraint to a single relation. + * + * This includes setting a column as NOT NULL as well as adding generic CHECK + * constraints, and also ALTER TABLE ADD COLUMN ... NOT NULL. + * + * Caller must do any permissions checking. + * + * This routine does not recurse; caller must do that as appropriate. + */ +static ObjectAddress +ATAddCheckConstraint_internal(List **wqueue, AlteredTableInfo *tab, + Relation rel, Constraint *constr, + bool recursing, bool check_it, bool is_readd, + LOCKMODE lockmode) { List *newcons; ListCell *lcon; - List *children; - ListCell *child; ObjectAddress address = InvalidObjectAddress; - /* At top level, permission check was done in ATPrepCmd, else do it */ - if (recursing) - ATSimplePermissions(AT_AddConstraint, rel, ATT_TABLE | ATT_FOREIGN_TABLE); - /* * Call AddRelationNewConstraints to do the work, making sure it works on * a copy of the Constraint so transformExpr can't modify the original. It @@ -8908,6 +9294,13 @@ ATAddCheckConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel, is_readd, /* is_internal */ NULL); /* queryString not available * here */ + if (newcons != NIL) + { + /* XXX this'd be two lines shorter if CookedConstraint was Node */ + CookedConstraint *cc = (CookedConstraint *) linitial(newcons); + + ObjectAddressSet(address, ConstraintRelationId, cc->conoid); + } /* we don't expect more than one constraint here */ Assert(list_length(newcons) <= 1); @@ -8932,69 +9325,11 @@ ATAddCheckConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel, /* Save the actually assigned name if it was defaulted */ if (constr->conname == NULL) constr->conname = ccon->name; - - ObjectAddressSet(address, ConstraintRelationId, ccon->conoid); } /* At this point we must have a locked-down name to use */ Assert(constr->conname != NULL); - /* Advance command counter in case same table is visited multiple times */ - CommandCounterIncrement(); - - /* - * If the constraint got merged with an existing constraint, we're done. - * We mustn't recurse to child tables in this case, because they've - * already got the constraint, and visiting them again would lead to an - * incorrect value for coninhcount. - */ - if (newcons == NIL) - return address; - - /* - * If adding a NO INHERIT constraint, no need to find our children. - */ - if (constr->is_no_inherit) - return address; - - /* - * Propagate to children as appropriate. Unlike most other ALTER - * routines, we have to do this one level of recursion at a time; we can't - * use find_all_inheritors to do it in one pass. - */ - children = - find_inheritance_children(RelationGetRelid(rel), lockmode); - - /* - * Check if ONLY was specified with ALTER TABLE. If so, allow the - * constraint creation only if there are no children currently. Error out - * otherwise. - */ - if (!recurse && children != NIL) - ereport(ERROR, - (errcode(ERRCODE_INVALID_TABLE_DEFINITION), - errmsg("constraint must be added to child tables too"))); - - foreach(child, children) - { - Oid childrelid = lfirst_oid(child); - Relation childrel; - AlteredTableInfo *childtab; - - /* find_inheritance_children already got lock */ - childrel = table_open(childrelid, NoLock); - CheckTableNotInUse(childrel, "ALTER TABLE"); - - /* Find or create work queue entry for this table */ - childtab = ATGetQueueEntry(wqueue, childrel); - - /* Recurse to child */ - ATAddCheckConstraint(wqueue, childtab, childrel, - constr, recurse, true, is_readd, lockmode); - - table_close(childrel, NoLock); - } - return address; } @@ -11815,7 +12150,9 @@ ATExecDropConstraint(Relation rel, const char *constrName, HeapTuple tuple; bool found = false; bool is_no_inherit_constraint = false; + bool dropping_pk = false; char contype; + List *unconstrained_cols = NIL; /* At top level, permission check was done in ATPrepCmd, else do it */ if (recursing) @@ -11855,6 +12192,54 @@ ATExecDropConstraint(Relation rel, const char *constrName, errmsg("cannot drop inherited constraint \"%s\" of relation \"%s\"", constrName, RelationGetRelationName(rel)))); + /* + * See if we have a CHECK (IS NOT NULL) constraint or a PRIMARY KEY. + * If so, we have more checks and actions below, so we obtain the + * list of columns that are constrained. + */ + if (con->contype == CONSTRAINT_CHECK) + { + char *colname = tryExtractNotNullFromCatalog(tuple, rel); + + if (colname) + { + AttrNumber attnum; + + attnum = get_attnum(RelationGetRelid(rel), colname); + if (attnum == InvalidAttrNumber) /* shouldn't happen */ + elog(ERROR, "cache lookup failed for column %s of table %s", + colname, RelationGetRelationName(rel)); + unconstrained_cols = list_make1_int(attnum); + } + } + else if (con->contype == CONSTRAINT_PRIMARY) + { + Datum adatum; + ArrayType *arr; + int numkeys; + bool isNull; + int16 *attnums; + + dropping_pk = true; + + adatum = heap_getattr(tuple, Anum_pg_constraint_conkey, + RelationGetDescr(conrel), &isNull); + if (isNull) + elog(ERROR, "null conkey for constraint %u", + ((Form_pg_constraint) GETSTRUCT(tuple))->oid); + arr = DatumGetArrayTypeP(adatum); /* ensure not toasted */ + numkeys = ARR_DIMS(arr)[0]; + if (ARR_NDIM(arr) != 1 || + numkeys < 0 || + ARR_HASNULL(arr) || + ARR_ELEMTYPE(arr) != INT2OID) + elog(ERROR, "conkey is not a 1-D smallint array"); + attnums = (int16 *) ARR_DATA_PTR(arr); + + for (int i = 0; i < numkeys; i++) + unconstrained_cols = lappend_int(unconstrained_cols, attnums[i]); + } + is_no_inherit_constraint = con->connoinherit; contype = con->contype; @@ -11909,6 +12294,92 @@ ATExecDropConstraint(Relation rel, const char *constrName, } } + /* + * If this was a CHECK (col IS NOT NULL) or the primary key, the + * constrained columns must have had pg_attribute.attnotnull set. See if + * we need to reset it, and do so. + */ + if (unconstrained_cols) + { + Bitmapset *pkcols; + ListCell *lc; + + /* Make the above deletion visible */ + CommandCounterIncrement(); + + /* + * We want to test columns for their presence in the primary key, but + * only if we're not dropping it. + */ + pkcols = dropping_pk ? NULL : + RelationGetIndexAttrBitmap(rel, + INDEX_ATTR_BITMAP_PRIMARY_KEY); + + foreach(lc, unconstrained_cols) + { + AttrNumber attnum = lfirst_int(lc); + HeapTuple atttup; + HeapTuple contup; + Form_pg_attribute attForm; + + /* + * Obtain pg_attribute tuple and verify conditions on it. We use + * a copy we can scribble on. + */ + atttup = SearchSysCacheCopyAttNum(RelationGetRelid(rel), attnum); + if (!HeapTupleIsValid(atttup)) + elog(ERROR, "cache lookup failed for column %d", attnum); + attForm = (Form_pg_attribute) GETSTRUCT(atttup); + + /* + * Since the above deletion has been made visible, we can now + * search for any remaining constraints on this column (or these + * columns, in the case we're dropping a multicol primary key.) + * + * Then, verify whether any further NOT NULL constraints exist, + * and reset attnotnull if none. However, if this is a generated + * identity column, abort the whole thing with a specific error + * message, because the constraint is required in that case. + * + * Do not reset attnotnull if we still have a primary key and + * the column in question is part of it. + */ + contup = findNotNullConstraintAttnum(rel, attnum, NULL); + + /* + * It's not valid to drop the last NOT NULL constraint for a + * GENERATED AS IDENTITY column. + */ + if (!contup && attForm->attidentity) + ereport(ERROR, + errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE), + errmsg("column \"%s\" of relation \"%s\" is an identity column", + get_attname(RelationGetRelid(rel), attnum, + false), + RelationGetRelationName(rel))); + + /* Finally we know whether to reset attnotnull */ + if (!contup && + !bms_is_member(attnum - FirstLowInvalidHeapAttributeNumber, + pkcols)) + { + Relation attrel; + + attrel = table_open(AttributeRelationId, RowExclusiveLock); + + if (attForm->attnotnull) + { + attForm->attnotnull = false; + CatalogTupleUpdate(attrel, &atttup->t_self, atttup); + } + + table_close(attrel, RowExclusiveLock); + } + + /* XXX free the catalog tuples? */ + } + } + /* * For partitioned tables, non-CHECK inherited constraints are dropped via * the dependency mechanism, so we're done here. @@ -13354,10 +13825,11 @@ ATPostAlterTypeParse(Oid oldId, Oid oldRelId, Oid refRelId, char *cmd, NIL, con->conname); } - else if (cmd->subtype == AT_SetNotNull) + else if (cmd->subtype == AT_SetNotNull || + cmd->subtype == AT_SetAttNotNull) { /* - * The parser will create AT_SetNotNull subcommands for + * The parser will create AT_SetAttNotNull subcommands for * columns of PRIMARY KEY indexes/constraints, but we need * not do anything with them here, because the columns' * NOT NULL marks will already have been propagated into @@ -15169,6 +15641,7 @@ MergeConstraintsIntoExisting(Relation child_rel, Relation parent_rel) ScanKeyData child_key; HeapTuple child_tuple; bool found = false; + char *colname; if (parent_con->contype != CONSTRAINT_CHECK) continue; @@ -15177,6 +15650,66 @@ MergeConstraintsIntoExisting(Relation child_rel, Relation parent_rel) if (parent_con->connoinherit) continue; + /* + * If the constraint is a NOT NULL one, verify that the child has any + * NOT NULL constraint on the same column. + */ + colname = tryExtractNotNullFromCatalog(parent_tuple, parent_rel); + if (colname != NULL) + { + AttrNumber attnum; + Form_pg_attribute att; + + attnum = get_attnum(RelationGetRelid(child_rel), colname); + att = TupleDescAttr(RelationGetDescr(child_rel), attnum - 1); + if (att->attnotnull) + { + HeapTuple conTup; + Form_pg_constraint childCon; + + /* + * OK, the column is marked NOT NULL, so search for the + * corresponding pg_constraint row and mark it as a child of + * this one. + */ + conTup = findNotNullConstraint(child_rel, colname, NULL); + if (conTup == NULL) /* shouldn't happen */ + elog(ERROR, "could not find CHECK (IS NOT NULL) constraint for column \"%s\"", + colname); + childCon = (Form_pg_constraint) GETSTRUCT(conTup); + + /* + * If this is being done to a partitioned table, mark this + * constraint as parent of the child's. If not, increment + * coninhcount. + */ + if (child_is_partition) + ConstraintSetParentConstraint(childCon->oid, + parent_con->oid, + RelationGetRelid(child_rel)); + else + { + HeapTuple child_copy; + + child_copy = heap_copytuple(conTup); + childCon = (Form_pg_constraint) GETSTRUCT(child_copy); + childCon->coninhcount++; + + CatalogTupleUpdate(catalog_relation, &child_copy->t_self, child_copy); + heap_freetuple(child_copy); + } + + /* All done */ + heap_freetuple(conTup); + continue; + } + + ereport(ERROR, + errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE), + errmsg("column \"%s\" in child table must be marked NOT NULL", + colname)); + } + /* Search for a child constraint matching this one */ ScanKeyInit(&child_key, Anum_pg_constraint_conrelid, @@ -15509,6 +16042,21 @@ RemoveInheritance(Relation child_rel, Relation parent_rel, bool expect_detached) if (con->contype != CONSTRAINT_CHECK) continue; + /* + * CHECK (IS NOT NULL) constraints use 'conparentid'. + */ + if (con->conparentid != InvalidOid) + { + ConstraintSetParentConstraint(con->oid, + InvalidOid, + RelationGetRelid(child_rel)); + continue; + } + + /* + * Other CHECK constraints use the old-fashioned way of just setting + * conislocal/coninhconut. XXX this should be changed sometime. + */ match = false; foreach(lc, connames) { diff --git a/src/backend/commands/typecmds.c b/src/backend/commands/typecmds.c index 33b64fd279..fbf1be8ce4 100644 --- a/src/backend/commands/typecmds.c +++ b/src/backend/commands/typecmds.c @@ -52,6 +52,7 @@ #include "catalog/pg_proc.h" #include "catalog/pg_range.h" #include "catalog/pg_type.h" +#include "commands/constraint.h" #include "commands/defrem.h" #include "commands/tablecmds.h" #include "commands/typecmds.h" @@ -1099,6 +1100,7 @@ DefineDomain(CreateDomainStmt *stmt) foreach(listptr, schema) { Constraint *constr = lfirst(listptr); + Constraint *newck; /* it must be a Constraint, per check above */ @@ -1110,6 +1112,18 @@ DefineDomain(CreateDomainStmt *stmt) constr, domainName, NULL); break; + case CONSTR_NOTNULL: + newck = makeCheckNotNullConstraint(domainNamespace, + constr->conname, + domainName, + "value", + false, + InvalidOid); + domainAddConstraint(address.objectId, domainNamespace, + basetypeoid, basetypeMod, + newck, domainName, NULL); + break; + /* Other constraint types were fully processed above */ default: diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c index 6d283006e3..d3df5bd924 100644 --- a/src/backend/parser/parse_utilcmd.c +++ b/src/backend/parser/parse_utilcmd.c @@ -39,6 +39,7 @@ #include "catalog/pg_operator.h" #include "catalog/pg_statistic_ext.h" #include "catalog/pg_type.h" +#include "commands/constraint.h" #include "commands/comment.h" #include "commands/defrem.h" #include "commands/sequence.h" @@ -83,6 +84,9 @@ typedef struct List *ckconstraints; /* CHECK constraints */ List *fkconstraints; /* FOREIGN KEY constraints */ List *ixconstraints; /* index-creating constraints */ + List *notnulls_check; /* list of columns to get an additional CHECK + * (IS NOT NULL) constraint */ + List *notnulls_nock; /* list of columns implicitly NOT NULL */ List *likeclauses; /* LIKE clauses that need post-processing */ List *extstats; /* cloned extended statistics */ List *blist; /* "before list" of things to do before @@ -244,6 +248,8 @@ transformCreateStmt(CreateStmt *stmt, const char *queryString) cxt.ckconstraints = NIL; cxt.fkconstraints = NIL; cxt.ixconstraints = NIL; + cxt.notnulls_check = NIL; + cxt.notnulls_nock = NIL; cxt.likeclauses = NIL; cxt.extstats = NIL; cxt.blist = NIL; @@ -348,6 +354,8 @@ transformCreateStmt(CreateStmt *stmt, const char *queryString) */ stmt->tableElts = cxt.columns; stmt->constraints = cxt.ckconstraints; + stmt->notnull_check = cxt.notnulls_check; + stmt->notnull_bare = cxt.notnulls_nock; result = lappend(cxt.blist, stmt); result = list_concat(result, cxt.alist); @@ -530,8 +538,8 @@ static void transformColumnDefinition(CreateStmtContext *cxt, ColumnDef *column) { bool is_serial; - bool saw_nullable; bool saw_default; + bool saw_nullable; bool saw_identity; bool saw_generated; ListCell *clist; @@ -631,10 +639,9 @@ transformColumnDefinition(CreateStmtContext *cxt, ColumnDef *column) constraint->cooked_expr = NULL; column->constraints = lappend(column->constraints, constraint); - constraint = makeNode(Constraint); - constraint->contype = CONSTR_NOTNULL; - constraint->location = -1; - column->constraints = lappend(column->constraints, constraint); + /* have a NOT NULL constraint added later */ + cxt->notnulls_check = lappend(cxt->notnulls_check, + makeString(pstrdup(column->colname))); } /* Process column constraints, if any... */ @@ -648,6 +655,7 @@ transformColumnDefinition(CreateStmtContext *cxt, ColumnDef *column) foreach(clist, column->constraints) { Constraint *constraint = lfirst_node(Constraint, clist); + char *colname; switch (constraint->contype) { @@ -664,6 +672,11 @@ transformColumnDefinition(CreateStmtContext *cxt, ColumnDef *column) break; case CONSTR_NOTNULL: + /* + * For NOT NULL declarations, we need to mark the column as + * not nullable, and set things up to have a CHECK constraint + * created. + */ if (saw_nullable && !column->is_not_null) ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR), @@ -671,8 +684,18 @@ transformColumnDefinition(CreateStmtContext *cxt, ColumnDef *column) column->colname, cxt->relation->relname), parser_errposition(cxt->pstate, constraint->location))); - column->is_not_null = true; - saw_nullable = true; + + /* + * If this is the first time we see this column being marked + * not null, keep track to later add a CHECK constraint. + */ + if (!column->is_not_null) + { + column->is_not_null = true; + cxt->notnulls_check = lappend(cxt->notnulls_check, + makeString(pstrdup(column->colname))); + } + break; case CONSTR_DEFAULT: @@ -722,7 +745,6 @@ transformColumnDefinition(CreateStmtContext *cxt, ColumnDef *column) column->identity = constraint->generated_when; saw_identity = true; - /* An identity column is implicitly NOT NULL */ if (saw_nullable && !column->is_not_null) ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR), @@ -730,7 +752,13 @@ transformColumnDefinition(CreateStmtContext *cxt, ColumnDef *column) column->colname, cxt->relation->relname), parser_errposition(cxt->pstate, constraint->location))); - column->is_not_null = true; + if (!column->is_not_null) + { + column->is_not_null = true; + cxt->notnulls_check = + lappend(cxt->notnulls_check, + makeString(pstrdup(column->colname))); + } saw_nullable = true; break; } @@ -760,6 +788,28 @@ transformColumnDefinition(CreateStmtContext *cxt, ColumnDef *column) case CONSTR_CHECK: cxt->ckconstraints = lappend(cxt->ckconstraints, constraint); + + /* + * If there is a CHECK (foo IS NOT NULL) constraint + * declaration, we check the column name used in the + * constraint. If it's the same name as the column being + * defined, check there's no IS NULL already, and set + * saw_isnotnull in the column definition to conflict with any + * future one. + */ + colname = tryExtractNotNullFromNode((Node *) constraint, NULL); + if (colname != NULL && strcmp(colname, column->colname) == 0) + { + if (saw_nullable && !column->is_not_null) + ereport(errcode(ERRCODE_SYNTAX_ERROR), + errmsg("conflicting NULL/NOT NULL declarations for column \"%s\" of table \"%s\"", + column->colname, cxt->relation->relname), + parser_errposition(cxt->pstate, + constraint->location)); + + column->is_not_null = true; + saw_nullable = true; + } break; case CONSTR_PRIMARY: @@ -875,6 +925,8 @@ transformColumnDefinition(CreateStmtContext *cxt, ColumnDef *column) static void transformTableConstraint(CreateStmtContext *cxt, Constraint *constraint) { + char *colname; + switch (constraint->contype) { case CONSTR_PRIMARY: @@ -915,6 +967,10 @@ transformTableConstraint(CreateStmtContext *cxt, Constraint *constraint) case CONSTR_CHECK: cxt->ckconstraints = lappend(cxt->ckconstraints, constraint); + colname = tryExtractNotNullFromNode((Node *) constraint, cxt->rel); + if (colname != NULL) + cxt->notnulls_nock = lappend(cxt->notnulls_nock, + makeString(pstrdup(colname))); break; case CONSTR_FOREIGN: @@ -964,6 +1020,7 @@ transformTableLikeClause(CreateStmtContext *cxt, TableLikeClause *table_like_cla AclResult aclresult; char *comment; ParseCallbackState pcbstate; + bool process_notnull_constraints; setup_parser_errposition_callback(&pcbstate, cxt->pstate, table_like_clause->relation->location); @@ -1045,6 +1102,8 @@ transformTableLikeClause(CreateStmtContext *cxt, TableLikeClause *table_like_cla def->inhcount = 0; def->is_local = true; def->is_not_null = attribute->attnotnull; + if (attribute->attnotnull) + process_notnull_constraints = true; def->is_from_type = false; def->storage = 0; def->raw_default = NULL; @@ -1126,14 +1185,19 @@ transformTableLikeClause(CreateStmtContext *cxt, TableLikeClause *table_like_cla * we don't yet know what column numbers the copied columns will have in * the finished table. If any of those options are specified, add the * LIKE clause to cxt->likeclauses so that expandTableLikeClause will be - * called after we do know that. Also, remember the relation OID so that + * called after we do know that; in addition, do that if there are any NOT + * NULL constraints, because those must be propagated even if not + * explicitly requested. + * + * In order for this to work, we remember the relation OID so that * expandTableLikeClause is certain to open the same table. */ - if (table_like_clause->options & + if ((table_like_clause->options & (CREATE_TABLE_LIKE_DEFAULTS | CREATE_TABLE_LIKE_GENERATED | CREATE_TABLE_LIKE_CONSTRAINTS | - CREATE_TABLE_LIKE_INDEXES)) + CREATE_TABLE_LIKE_INDEXES)) || + process_notnull_constraints) { table_like_clause->relationOid = RelationGetRelid(relation); cxt->likeclauses = lappend(cxt->likeclauses, table_like_clause); @@ -1312,8 +1376,7 @@ expandTableLikeClause(RangeVar *heapRel, TableLikeClause *table_like_clause) * Copy CHECK constraints if requested, being careful to adjust attribute * numbers so they match the child. */ - if ((table_like_clause->options & CREATE_TABLE_LIKE_CONSTRAINTS) && - constr != NULL) + if (constr != NULL) { int ccnum; @@ -1322,15 +1385,18 @@ expandTableLikeClause(RangeVar *heapRel, TableLikeClause *table_like_clause) char *ccname = constr->check[ccnum].ccname; char *ccbin = constr->check[ccnum].ccbin; bool ccnoinherit = constr->check[ccnum].ccnoinherit; - Node *ccbin_node; + Node *ccnode_parent; + Node *ccnode_newrel; bool found_whole_row; + char *colname; Constraint *n; AlterTableCmd *atsubcmd; - ccbin_node = map_variable_attnos(stringToNode(ccbin), - 1, 0, - attmap, - InvalidOid, &found_whole_row); + ccnode_parent = stringToNode(ccbin); + ccnode_newrel = map_variable_attnos(ccnode_parent, + 1, 0, + attmap, + InvalidOid, &found_whole_row); /* * We reject whole-row variables because the whole point of LIKE @@ -1346,13 +1412,23 @@ expandTableLikeClause(RangeVar *heapRel, TableLikeClause *table_like_clause) ccname, RelationGetRelationName(relation)))); + /* + * NOT NULL constraints must be copied regardless of whether + * INCLUDING CONSTRAINTS was given, per the SQL standard; if that + * option was not given, skip other constraints. + */ + colname = tryExtractNotNullFromNode(ccnode_parent, relation); + if (!(table_like_clause->options & CREATE_TABLE_LIKE_CONSTRAINTS) && + !colname) + continue; + n = makeNode(Constraint); n->contype = CONSTR_CHECK; n->conname = pstrdup(ccname); n->location = -1; n->is_no_inherit = ccnoinherit; n->raw_expr = NULL; - n->cooked_expr = nodeToString(ccbin_node); + n->cooked_expr = nodeToString(ccnode_newrel); /* We can skip validation, since the new table should be empty. */ n->skip_validation = true; @@ -2069,10 +2145,12 @@ transformIndexConstraints(CreateStmtContext *cxt) ListCell *lc; /* - * Run through the constraints that need to generate an index. For PRIMARY - * KEY, mark each column as NOT NULL and create an index. For UNIQUE or - * EXCLUDE, create an index as for PRIMARY KEY, but do not insist on NOT - * NULL. + * Run through the constraints that need to generate an index, and do so. + * + * For PRIMARY KEY, in addition we set each column's attnotnull flag true. + * We do not create a separate CHECK (IS NOT NULL) constraint, as that + * would be redundant: the PRIMARY KEY constraint ktself fulfills that + * role. Other constraint types don't need any NOT NULL markings. */ foreach(lc, cxt->ixconstraints) { @@ -2146,9 +2224,7 @@ transformIndexConstraints(CreateStmtContext *cxt) } /* - * Now append all the IndexStmts to cxt->alist. If we generated an ALTER - * TABLE SET NOT NULL statement to support a primary key, it's already in - * cxt->alist. + * Now append all the IndexStmts to cxt->alist. */ cxt->alist = list_concat(cxt->alist, finalindexlist); } @@ -2156,12 +2232,10 @@ transformIndexConstraints(CreateStmtContext *cxt) /* * transformIndexConstraint * Transform one UNIQUE, PRIMARY KEY, or EXCLUDE constraint for - * transformIndexConstraints. + * transformIndexConstraints. We return an IndexStmt. * - * We return an IndexStmt. For a PRIMARY KEY constraint, we additionally - * produce NOT NULL constraints, either by marking ColumnDefs in cxt->columns - * as is_not_null or by adding an ALTER TABLE SET NOT NULL command to - * cxt->alist. + * For a PRIMARY KEY constraint, we additionally force the columns to be + * marked as NOT NULL, without producing a CHECK (IS NOT NULL) constraint. */ static IndexStmt * transformIndexConstraint(Constraint *constraint, CreateStmtContext *cxt) @@ -2449,13 +2523,15 @@ transformIndexConstraint(Constraint *constraint, CreateStmtContext *cxt) * column is defined in the new table. For PRIMARY KEY, we * can apply the NOT NULL constraint cheaply here ... unless * the column is marked is_from_type, in which case marking it - * here would be ineffective (see MergeAttributes). + * here would be ineffective (see MergeAttributes). Note that + * this isn't effective in ALTER TABLE either, unless the + * column is being added in the same command. */ if (constraint->contype == CONSTR_PRIMARY && !column->is_from_type) { - column->is_not_null = true; - forced_not_null = true; + cxt->notnulls_nock = lappend(cxt->notnulls_nock, + makeString(pstrdup(key))); } } else if (SystemAttributeByName(key) != NULL) @@ -2560,17 +2636,13 @@ transformIndexConstraint(Constraint *constraint, CreateStmtContext *cxt) index->indexParams = lappend(index->indexParams, iparam); /* - * For a primary-key column, also create an item for ALTER TABLE - * SET NOT NULL if we couldn't ensure it via is_not_null above. + * For a primary-key column, also have it be marked attnotnull + * later without creating a CHECK constraint for it (the + * PRIMARY KEY fulfills that role already). */ if (constraint->contype == CONSTR_PRIMARY && !forced_not_null) - { - AlterTableCmd *notnullcmd = makeNode(AlterTableCmd); - - notnullcmd->subtype = AT_SetNotNull; - notnullcmd->name = pstrdup(key); - notnullcmds = lappend(notnullcmds, notnullcmd); - } + cxt->notnulls_nock = lappend(cxt->notnulls_nock, + makeString(pstrdup(key))); } } @@ -2672,22 +2744,6 @@ transformIndexConstraint(Constraint *constraint, CreateStmtContext *cxt) index->indexIncludingParams = lappend(index->indexIncludingParams, iparam); } - /* - * If we found anything that requires run-time SET NOT NULL, build a full - * ALTER TABLE command for that and add it to cxt->alist. - */ - if (notnullcmds) - { - AlterTableStmt *alterstmt = makeNode(AlterTableStmt); - - alterstmt->relation = copyObject(cxt->relation); - alterstmt->cmds = notnullcmds; - alterstmt->objtype = OBJECT_TABLE; - alterstmt->missing_ok = false; - - cxt->alist = lappend(cxt->alist, alterstmt); - } - return index; } @@ -3345,6 +3401,8 @@ transformAlterTableStmt(Oid relid, AlterTableStmt *stmt, cxt.ckconstraints = NIL; cxt.fkconstraints = NIL; cxt.ixconstraints = NIL; + cxt.notnulls_check = NIL; + cxt.notnulls_nock = NIL; cxt.likeclauses = NIL; cxt.extstats = NIL; cxt.blist = NIL; @@ -3564,6 +3622,27 @@ transformAlterTableStmt(Oid relid, AlterTableStmt *stmt, } } + /* + * Add CHECK constraints to match NOT NULL declarations from various + * sources. + * + * Cannot do it at this point: don't know is_row yet. + */ + foreach(l, cxt.notnulls_check) + { + Constraint *newconstr; + char *colname = strVal(lfirst(l)); + bool is_row = false; /* FIXME */ + + newconstr = makeCheckNotNullConstraint(RelationGetNamespace(rel), + NULL, + RelationGetRelationName(rel), + colname, + is_row, + InvalidOid); + cxt.ckconstraints = lappend(cxt.ckconstraints, newconstr); + } + /* * Transfer anything we already have in cxt.alist into save_alist, to keep * it separate from the output of transformIndexConstraints. @@ -3576,6 +3655,15 @@ transformAlterTableStmt(Oid relid, AlterTableStmt *stmt, transformFKConstraints(&cxt, skipValidation, true); transformCheckConstraints(&cxt, false); + /* have attnotnull set for columns that need it */ + foreach(l, cxt.notnulls_nock) + { + newcmd = makeNode(AlterTableCmd); + newcmd->subtype = AT_SetAttNotNull; + newcmd->name = strVal(lfirst(l)); + newcmds = lappend(newcmds, newcmd); + } + /* * Push any index-creation commands into the ALTER, so that they can be * scheduled nicely by tablecmds.c. Note that tablecmds.c assumes that diff --git a/src/backend/utils/cache/relcache.c b/src/backend/utils/cache/relcache.c index 00dc0f2403..865ca880f6 100644 --- a/src/backend/utils/cache/relcache.c +++ b/src/backend/utils/cache/relcache.c @@ -4550,6 +4550,7 @@ CheckConstraintFetch(Relation relation) break; } + check[found].ccoid = conform->oid; check[found].ccvalid = conform->convalidated; check[found].ccnoinherit = conform->connoinherit; check[found].ccname = MemoryContextStrdup(CacheMemoryContext, diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c index d25709ad5f..f7c063fe9e 100644 --- a/src/bin/pg_dump/pg_dump.c +++ b/src/bin/pg_dump/pg_dump.c @@ -8175,7 +8175,6 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables) "a.attstattarget,\n" "a.attstorage,\n" "t.typstorage,\n" - "a.attnotnull,\n" "a.atthasdef,\n" "a.attisdropped,\n" "a.attlen,\n" @@ -8192,6 +8191,17 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables) "ORDER BY option_name" "), E',\n ') AS attfdwoptions,\n"); + /* + * Versions 16 and up have pg_constraint rows for NOT NULL constraints, so + * we don't need to handle them separately here. + */ + if (fout->remoteVersion < 160000) + appendPQExpBufferStr(q, + "a.attnotnull,\n"); + else + appendPQExpBufferStr(q, + "false as attnotnull,\n"); + if (fout->remoteVersion >= 140000) appendPQExpBufferStr(q, "a.attcompression AS attcompression,\n"); @@ -10866,7 +10876,12 @@ dumpDomain(Archive *fout, const TypeInfo *tyinfo) appendPQExpBufferStr(query, "PREPARE dumpDomain(pg_catalog.oid) AS\n"); - appendPQExpBufferStr(query, "SELECT t.typnotnull, " + appendPQExpBufferStr(query, "SELECT "); + if (fout->remoteVersion >= 160000) + appendPQExpBufferStr(query, "false as typnotnull, "); + else + appendPQExpBufferStr(query, "t.typnotnull, "); + appendPQExpBufferStr(query, "pg_catalog.format_type(t.typbasetype, t.typtypmod) AS typdefn, " "pg_catalog.pg_get_expr(t.typdefaultbin, 'pg_catalog.pg_type'::pg_catalog.regclass) AS typdefaultbin, " "t.typdefault, " diff --git a/src/bin/pg_dump/t/002_pg_dump.pl b/src/bin/pg_dump/t/002_pg_dump.pl index 2873b662fb..b2f79faecf 100644 --- a/src/bin/pg_dump/t/002_pg_dump.pl +++ b/src/bin/pg_dump/t/002_pg_dump.pl @@ -2629,11 +2629,12 @@ my %tests = ( ) WITH (autovacuum_enabled = false, fillfactor=80);', regexp => qr/^ \QCREATE TABLE dump_test.test_table (\E\n - \s+\Qcol1 integer NOT NULL,\E\n + \s+\Qcol1 integer,\E\n \s+\Qcol2 text,\E\n \s+\Qcol3 text,\E\n \s+\Qcol4 text,\E\n - \s+\QCONSTRAINT test_table_col1_check CHECK ((col1 <= 1000))\E\n + \s+\QCONSTRAINT test_table_col1_check CHECK ((col1 <= 1000)),\E\n + \s+\QCONSTRAINT test_table_col1_not_null CHECK ((col1 IS NOT NULL))\E\n \Q)\E\n \QWITH (autovacuum_enabled='false', fillfactor='80');\E\n/xm, like => { @@ -2655,7 +2656,7 @@ my %tests = ( );', regexp => qr/^ \QCREATE TABLE dump_test.fk_reference_test_table (\E - \n\s+\Qcol1 integer NOT NULL\E + \n\s+\Qcol1 integer\E \n\); /xm, like => @@ -2713,10 +2714,12 @@ my %tests = ( \Q-- Name: measurement;\E.*\n \Q--\E\n\n \QCREATE TABLE dump_test.measurement (\E\n - \s+\Qcity_id integer NOT NULL,\E\n - \s+\Qlogdate date NOT NULL,\E\n + \s+\Qcity_id integer,\E\n + \s+\Qlogdate date,\E\n \s+\Qpeaktemp integer,\E\n \s+\Qunitsales integer,\E\n + \s+\QCONSTRAINT measurement_city_id_not_null CHECK ((city_id IS NOT NULL)),\E\n + \s+\QCONSTRAINT measurement_logdate_not_null CHECK ((logdate IS NOT NULL)),\E\n \s+\QCONSTRAINT measurement_peaktemp_check CHECK ((peaktemp >= '-460'::integer))\E\n \)\n \QPARTITION BY RANGE (logdate);\E\n @@ -2739,10 +2742,12 @@ my %tests = ( FOR VALUES FROM (\'2006-02-01\') TO (\'2006-03-01\');', regexp => qr/^ \QCREATE TABLE dump_test_second_schema.measurement_y2006m2 (\E\n - \s+\Qcity_id integer DEFAULT nextval('dump_test.measurement_city_id_seq'::regclass) NOT NULL,\E\n - \s+\Qlogdate date NOT NULL,\E\n + \s+\Qcity_id integer DEFAULT nextval('dump_test.measurement_city_id_seq'::regclass),\E\n + \s+\Qlogdate date,\E\n \s+\Qpeaktemp integer,\E\n \s+\Qunitsales integer DEFAULT 0,\E\n + \s+\QCONSTRAINT measurement_city_id_not_null CHECK ((city_id IS NOT NULL)),\E\n + \s+\QCONSTRAINT measurement_logdate_not_null CHECK ((logdate IS NOT NULL)),\E\n \s+\QCONSTRAINT measurement_peaktemp_check CHECK ((peaktemp >= '-460'::integer)),\E\n \s+\QCONSTRAINT measurement_y2006m2_unitsales_check CHECK ((unitsales >= 0))\E\n \);\n @@ -2941,8 +2946,9 @@ my %tests = ( );', regexp => qr/^ \QCREATE TABLE dump_test.test_table_identity (\E\n - \s+\Qcol1 integer NOT NULL,\E\n - \s+\Qcol2 text\E\n + \s+\Qcol1 integer,\E\n + \s+\Qcol2 text,\E\n + \s+\QCONSTRAINT test_table_identity_col1_not_null CHECK ((col1 IS NOT NULL))\E\n \); .* \QALTER TABLE dump_test.test_table_identity ALTER COLUMN col1 ADD GENERATED ALWAYS AS IDENTITY (\E\n @@ -2967,7 +2973,7 @@ my %tests = ( );', regexp => qr/^ \QCREATE TABLE dump_test.test_table_generated (\E\n - \s+\Qcol1 integer NOT NULL,\E\n + \s+\Qcol1 integer,\E\n \s+\Qcol2 integer GENERATED ALWAYS AS ((col1 * 2)) STORED\E\n \); /xms, @@ -2982,6 +2988,7 @@ my %tests = ( INHERITS (dump_test.test_table_generated);', regexp => qr/^ \QCREATE TABLE dump_test.test_table_generated_child1 (\E\n + \s+\QCONSTRAINT test_table_generated_child1_col1_not_null CHECK ((col1 IS NOT NULL))\E\n \)\n \QINHERITS (dump_test.test_table_generated);\E\n /xms, @@ -3010,7 +3017,8 @@ my %tests = ( regexp => qr/^ \QCREATE TABLE dump_test.test_table_generated_child2 (\E\n \s+\Qcol1 integer,\E\n - \s+\Qcol2 integer\E\n + \s+\Qcol2 integer,\E\n + \s+\QCONSTRAINT test_table_generated_child2_col1_not_null CHECK ((col1 IS NOT NULL))\E\n \)\n \QINHERITS (dump_test.test_table_generated);\E\n /xms, @@ -3052,8 +3060,9 @@ my %tests = ( );', regexp => qr/^ \QCREATE TABLE dump_test.test_inheritance_parent (\E\n - \s+\Qcol1 integer NOT NULL,\E\n + \s+\Qcol1 integer,\E\n \s+\Qcol2 integer,\E\n + \s+\QCONSTRAINT test_inheritance_parent_col1_not_null CHECK ((col1 IS NOT NULL)),\E\n \s+\QCONSTRAINT test_inheritance_parent_col2_check CHECK ((col2 >= 42))\E\n \Q);\E\n /xm, @@ -3071,7 +3080,8 @@ my %tests = ( regexp => qr/^ \QCREATE TABLE dump_test.test_inheritance_child (\E\n \s+\Qcol1 integer,\E\n - \s+\QCONSTRAINT test_inheritance_child CHECK ((col2 >= 142857))\E\n + \s+\QCONSTRAINT test_inheritance_child CHECK ((col2 >= 142857)),\E\n + \s+\QCONSTRAINT test_inheritance_child_col1_not_null CHECK ((col1 IS NOT NULL))\E\n \)\n \QINHERITS (dump_test.test_inheritance_parent);\E\n /xm, diff --git a/src/include/access/tupdesc.h b/src/include/access/tupdesc.h index 28dd6de18b..2ff0006864 100644 --- a/src/include/access/tupdesc.h +++ b/src/include/access/tupdesc.h @@ -27,6 +27,7 @@ typedef struct AttrDefault typedef struct ConstrCheck { + Oid ccoid; /* pg_constraint OID */ char *ccname; char *ccbin; /* nodeToString representation of expr */ bool ccvalid; diff --git a/src/include/catalog/heap.h b/src/include/catalog/heap.h index 5774c46471..d110ba2b79 100644 --- a/src/include/catalog/heap.h +++ b/src/include/catalog/heap.h @@ -37,6 +37,7 @@ typedef struct CookedConstraint ConstrType contype; /* CONSTR_DEFAULT or CONSTR_CHECK */ Oid conoid; /* constr OID if created, otherwise Invalid */ char *name; /* name, or NULL if none */ + Oid parent_oid; /* constr OID of parent, if any */ AttrNumber attnum; /* which attr (only for DEFAULT) */ Node *expr; /* transformed default or check expr */ bool skip_validation; /* skip validation? (only for CHECK) */ diff --git a/src/include/catalog/pg_constraint.h b/src/include/catalog/pg_constraint.h index e7d967f137..7fe75816f9 100644 --- a/src/include/catalog/pg_constraint.h +++ b/src/include/catalog/pg_constraint.h @@ -237,9 +237,6 @@ extern Oid CreateConstraintEntry(const char *constraintName, bool conNoInherit, bool is_internal); -extern void RemoveConstraintById(Oid conId); -extern void RenameConstraintById(Oid conId, const char *newname); - extern bool ConstraintNameIsUsed(ConstraintCategory conCat, Oid objId, const char *conname); extern bool ConstraintNameExists(const char *conname, Oid namespaceid); @@ -247,6 +244,14 @@ extern char *ChooseConstraintName(const char *name1, const char *name2, const char *label, Oid namespaceid, List *others); +extern HeapTuple findNotNullConstraintAttnum(Relation rel, AttrNumber attnum, + bool *report_multiple); +extern HeapTuple findNotNullConstraint(Relation rel, const char *colname, + bool *report_multiple); + +extern void RemoveConstraintById(Oid conId); +extern void RenameConstraintById(Oid conId, const char *newname); + extern void AlterConstraintNamespaces(Oid ownerId, Oid oldNspId, Oid newNspId, bool isType, ObjectAddresses *objsMoved); extern void ConstraintSetParentConstraint(Oid childConstrId, diff --git a/src/include/commands/constraint.h b/src/include/commands/constraint.h new file mode 100644 index 0000000000..9eb5b14a6c --- /dev/null +++ b/src/include/commands/constraint.h @@ -0,0 +1,30 @@ +/*------------------------------------------------------------------------- + * + * constraint.h + * PostgreSQL CONSTRAINT support declarations + * + * Portions Copyright (c) 1996-2022, PostgreSQL Global Development Group + * Portions Copyright (c) 1994, Regents of the University of California + * + * IDENTIFICATION + * src/include/commands/constraint.h + * + *------------------------------------------------------------------------- + */ +#ifndef CONSTRAINT_H +#define CONSTRAINT_H + +#include "nodes/parsenodes.h" +#include "utils/relcache.h" + +extern Constraint *makeCheckNotNullConstraint(Oid nspid, + char *constraint_name, + const char *relname, + const char *colname, + bool is_row, + Oid parent_oid); + +extern char *tryExtractNotNullFromNode(Node *node, Relation rel); +extern char *tryExtractNotNullFromCatalog(HeapTuple constrTup, Relation rel); + +#endif /* CONSTRAINT_H */ diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h index 469a5c46f6..dc7231dc53 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -2242,6 +2242,7 @@ typedef enum AlterTableType AT_CookedColumnDefault, /* add a pre-cooked column default */ AT_DropNotNull, /* alter column drop not null */ AT_SetNotNull, /* alter column set not null */ + AT_SetAttNotNull, /* set not null, without CHECK constraint */ AT_DropExpression, /* alter column drop expression */ AT_CheckNotNull, /* check column is already marked not null */ AT_SetStatistics, /* alter column set statistics */ @@ -2530,10 +2531,11 @@ typedef struct VariableShowStmt * Create Table Statement * * NOTE: in the raw gram.y output, ColumnDef and Constraint nodes are - * intermixed in tableElts, and constraints is NIL. After parse analysis, - * tableElts contains just ColumnDefs, and constraints contains just - * Constraint nodes (in fact, only CONSTR_CHECK nodes, in the present - * implementation). + * intermixed in tableElts, and constraints and notnullcols are NIL. After + * parse analysis, tableElts contains just ColumnDefs, notnullcols has been + * filled with not-nullable column names from various sources, and constraints + * contains just Constraint nodes (in fact, only CONSTR_CHECK nodes, in the + * present implementation). * ---------------------- */ @@ -2548,6 +2550,11 @@ typedef struct CreateStmt PartitionSpec *partspec; /* PARTITION BY clause */ TypeName *ofTypename; /* OF typename */ List *constraints; /* constraints (list of Constraint nodes) */ + List *notnull_check; /* list of column names for which to add a + * CHECK (IS NOT NULL) constraint for */ + List *notnull_bare; /* list of column names for which to set the + * attnotnull flag without a CHECK + * constraint */ List *options; /* options from WITH clause */ OnCommitAction oncommit; /* what do we do at COMMIT? */ char *tablespacename; /* table space to use, or NULL */ @@ -2629,6 +2636,7 @@ typedef struct Constraint bool deferrable; /* DEFERRABLE? */ bool initdeferred; /* INITIALLY DEFERRED? */ int location; /* token location, or -1 if unknown */ + Oid parent_oid; /* OID of parent constraint, if any */ /* Fields used for constraints with expressions (CHECK and DEFAULT): */ bool is_no_inherit; /* is constraint non-inheritable? */ diff --git a/src/pl/plpgsql/src/expected/plpgsql_varprops.out b/src/pl/plpgsql/src/expected/plpgsql_varprops.out index 25115a02bd..6db1ab1093 100644 --- a/src/pl/plpgsql/src/expected/plpgsql_varprops.out +++ b/src/pl/plpgsql/src/expected/plpgsql_varprops.out @@ -255,8 +255,8 @@ begin x := null; -- fail end$$; NOTICE: x = (1,2) -ERROR: domain var_record_nn does not allow null values -CONTEXT: PL/pgSQL function inline_code_block line 6 at assignment +ERROR: value for domain var_record_nn violates check constraint "var_record_nn_value_not_null" +CONTEXT: PL/pgSQL function inline_code_block line 5 at assignment do $$ declare x var_record_colnn; -- fail begin diff --git a/src/test/regress/expected/alter_table.out b/src/test/regress/expected/alter_table.out index d63f4f1cba..d793a6c179 100644 --- a/src/test/regress/expected/alter_table.out +++ b/src/test/regress/expected/alter_table.out @@ -1190,10 +1190,11 @@ DETAIL: Failing row contains (null, foo). alter table parent alter a drop not null; insert into parent values (NULL); insert into child (a, b) values (NULL, 'foo'); +ERROR: null value in column "a" of relation "child" violates not-null constraint +DETAIL: Failing row contains (null, foo). alter table only parent alter a set not null; ERROR: column "a" of relation "parent" contains null values alter table child alter a set not null; -ERROR: column "a" of relation "child" contains null values delete from parent; alter table only parent alter a set not null; insert into parent values (NULL); @@ -3672,11 +3673,12 @@ ALTER TABLE test_add_column c2 | integer | | | c3 | integer | | not null | c4 | integer | | | - c5 | integer | | not null | nextval('test_add_column_c5_seq'::regclass) + c5 | integer | | | nextval('test_add_column_c5_seq'::regclass) Indexes: "test_add_column_pkey" PRIMARY KEY, btree (c3) Check constraints: "test_add_column_c5_check" CHECK (c5 > 8) + "test_add_column_c5_not_null" CHECK (c5 IS NOT NULL) Foreign-key constraints: "test_add_column_c4_fkey" FOREIGN KEY (c4) REFERENCES test_add_column(c3) Referenced by: @@ -3693,11 +3695,12 @@ NOTICE: column "c5" of relation "test_add_column" already exists, skipping c2 | integer | | | c3 | integer | | not null | c4 | integer | | | - c5 | integer | | not null | nextval('test_add_column_c5_seq'::regclass) + c5 | integer | | | nextval('test_add_column_c5_seq'::regclass) Indexes: "test_add_column_pkey" PRIMARY KEY, btree (c3) Check constraints: "test_add_column_c5_check" CHECK (c5 > 8) + "test_add_column_c5_not_null" CHECK (c5 IS NOT NULL) Foreign-key constraints: "test_add_column_c4_fkey" FOREIGN KEY (c4) REFERENCES test_add_column(c3) Referenced by: diff --git a/src/test/regress/expected/cluster.out b/src/test/regress/expected/cluster.out index 542c2e098c..a666d89ef5 100644 --- a/src/test/regress/expected/cluster.out +++ b/src/test/regress/expected/cluster.out @@ -247,11 +247,12 @@ ERROR: insert or update on table "clstr_tst" violates foreign key constraint "c DETAIL: Key (b)=(1111) is not present in table "clstr_tst_s". SELECT conname FROM pg_constraint WHERE conrelid = 'clstr_tst'::regclass ORDER BY 1; - conname ----------------- + conname +---------------------- + clstr_tst_a_not_null clstr_tst_con clstr_tst_pkey -(2 rows) +(3 rows) SELECT relname, relkind, EXISTS(SELECT 1 FROM pg_class WHERE oid = c.reltoastrelid) AS hastoast diff --git a/src/test/regress/expected/collate.out b/src/test/regress/expected/collate.out index 246832575c..78675a79ef 100644 --- a/src/test/regress/expected/collate.out +++ b/src/test/regress/expected/collate.out @@ -21,6 +21,8 @@ CREATE TABLE collate_test1 ( --------+---------+-----------+----------+--------- a | integer | | | b | text | C | not null | +Check constraints: + "collate_test1_b_not_null" CHECK (b IS NOT NULL) CREATE TABLE collate_test_fail ( a int COLLATE "C", @@ -38,6 +40,8 @@ CREATE TABLE collate_test_like ( --------+---------+-----------+----------+--------- a | integer | | | b | text | C | not null | +Check constraints: + "collate_test1_b_not_null" CHECK (b IS NOT NULL) CREATE TABLE collate_test2 ( a int, diff --git a/src/test/regress/expected/constraints.out b/src/test/regress/expected/constraints.out index 05b7244e4a..d62c0ac52b 100644 --- a/src/test/regress/expected/constraints.out +++ b/src/test/regress/expected/constraints.out @@ -731,6 +731,108 @@ ALTER TABLE deferred_excl ADD EXCLUDE (f1 WITH =); ERROR: could not create exclusion constraint "deferred_excl_f1_excl" DETAIL: Key (f1)=(3) conflicts with key (f1)=(3). DROP TABLE deferred_excl; +-- verify CHECK constraints created for NOT NULL clauses +CREATE TABLE notnull_tbl1 (a INTEGER NOT NULL); +\d notnull_tbl1 + Table "public.notnull_tbl1" + Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+--------- + a | integer | | not null | +Check constraints: + "notnull_tbl1_a_not_null" CHECK (a IS NOT NULL) + +-- DROP NOT NULL gets rid of both the attnotnull flag and the constraint itself +ALTER TABLE notnull_tbl1 ALTER a DROP NOT NULL; +\d notnull_tbl1 + Table "public.notnull_tbl1" + Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+--------- + a | integer | | | + +-- SET NOT NULL puts both back +ALTER TABLE notnull_tbl1 ALTER a SET NOT NULL; +\d notnull_tbl1 + Table "public.notnull_tbl1" + Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+--------- + a | integer | | not null | +Check constraints: + "notnull_tbl1_a_not_null" CHECK (a IS NOT NULL) + +-- The simple syntax must not create redundant constraint +ALTER TABLE notnull_tbl1 ALTER a SET NOT NULL; +\d notnull_tbl1 + Table "public.notnull_tbl1" + Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+--------- + a | integer | | not null | +Check constraints: + "notnull_tbl1_a_not_null" CHECK (a IS NOT NULL) + +-- but this should create a second one +ALTER TABLE notnull_tbl1 ADD check (a IS NOT NULL); +\d notnull_tbl1 + Table "public.notnull_tbl1" + Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+--------- + a | integer | | not null | +Check constraints: + "notnull_tbl1_a_check" CHECK (a IS NOT NULL) + "notnull_tbl1_a_not_null" CHECK (a IS NOT NULL) + +-- Dropping the first one keeps attnotnull intact +ALTER TABLE notnull_tbl1 DROP CONSTRAINT notnull_tbl1_a_not_null; +\d notnull_tbl1 + Table "public.notnull_tbl1" + Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+--------- + a | integer | | not null | +Check constraints: + "notnull_tbl1_a_check" CHECK (a IS NOT NULL) + +-- but removing the second constraint resets the flag +ALTER TABLE notnull_tbl1 DROP CONSTRAINT notnull_tbl1_a_not_null1; +ERROR: constraint "notnull_tbl1_a_not_null1" of relation "notnull_tbl1" does not exist +\d notnull_tbl1 + Table "public.notnull_tbl1" + Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+--------- + a | integer | | not null | +Check constraints: + "notnull_tbl1_a_check" CHECK (a IS NOT NULL) + +DROP TABLE notnull_tbl1; +CREATE TABLE notnull_tbl2 (a INTEGER PRIMARY KEY); +ALTER TABLE notnull_tbl2 ALTER a DROP NOT NULL; +ERROR: column "a" is in a primary key +CREATE TABLE notnull_tbl3 (a INTEGER NOT NULL, CHECK (a IS NOT NULL)); +ALTER TABLE notnull_tbl3 ALTER A DROP NOT NULL; +ERROR: cannot DROP NOT NULL when multiple possible constraints exist +HINT: Consider specifying which constraint to drop with ALTER TABLE .. DROP CONSTRAINT. +ALTER TABLE notnull_tbl3 ADD b int, ADD CONSTRAINT pk PRIMARY KEY (a, b); +\d notnull_tbl3 + Table "public.notnull_tbl3" + Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+--------- + a | integer | | not null | + b | integer | | not null | +Indexes: + "pk" PRIMARY KEY, btree (a, b) +Check constraints: + "notnull_tbl3_a_check" CHECK (a IS NOT NULL) + "notnull_tbl3_a_not_null" CHECK (a IS NOT NULL) + +ALTER TABLE notnull_tbl3 DROP CONSTRAINT pk; +\d notnull_tbl3 + Table "public.notnull_tbl3" + Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+--------- + a | integer | | not null | + b | integer | | | +Check constraints: + "notnull_tbl3_a_check" CHECK (a IS NOT NULL) + "notnull_tbl3_a_not_null" CHECK (a IS NOT NULL) + -- Comments -- Setup a low-level role to enforce non-superuser checks. CREATE ROLE regress_constraint_comments; diff --git a/src/test/regress/expected/create_table.out b/src/test/regress/expected/create_table.out index 4407a017a9..80401b0f14 100644 --- a/src/test/regress/expected/create_table.out +++ b/src/test/regress/expected/create_table.out @@ -766,22 +766,26 @@ CREATE TABLE part_b PARTITION OF parted ( ) FOR VALUES IN ('b'); NOTICE: merging constraint "check_a" with inherited definition -- conislocal should be false for any merged constraints, true otherwise -SELECT conislocal, coninhcount FROM pg_constraint WHERE conrelid = 'part_b'::regclass ORDER BY conislocal, coninhcount; - conislocal | coninhcount -------------+------------- - f | 1 - t | 0 -(2 rows) +SELECT conname, conislocal, coninhcount FROM pg_constraint WHERE conrelid = 'part_b'::regclass ORDER BY coninhcount DESC, conname; + conname | conislocal | coninhcount +-------------------+------------+------------- + check_a | f | 1 + parted_b_not_null | f | 1 + check_b | t | 0 + part_b_b_not_null | t | 0 +(4 rows) -- Once check_b is added to the parent, it should be made non-local for part_b ALTER TABLE parted ADD CONSTRAINT check_b CHECK (b >= 0); NOTICE: merging constraint "check_b" with inherited definition -SELECT conislocal, coninhcount FROM pg_constraint WHERE conrelid = 'part_b'::regclass; +SELECT conislocal, coninhcount FROM pg_constraint WHERE conrelid = 'part_b'::regclass ORDER BY coninhcount DESC, conname; conislocal | coninhcount ------------+------------- f | 1 f | 1 -(2 rows) + f | 1 + t | 0 +(4 rows) -- Neither check_a nor check_b are droppable from part_b ALTER TABLE part_b DROP CONSTRAINT check_a; @@ -792,10 +796,12 @@ ERROR: cannot drop inherited constraint "check_b" of relation "part_b" -- traditional inheritance where they will be left behind, because they would -- be local constraints. ALTER TABLE parted DROP CONSTRAINT check_a, DROP CONSTRAINT check_b; -SELECT conislocal, coninhcount FROM pg_constraint WHERE conrelid = 'part_b'::regclass; - conislocal | coninhcount -------------+------------- -(0 rows) +SELECT conname, conislocal, coninhcount FROM pg_constraint WHERE conrelid = 'part_b'::regclass ORDER BY coninhcount; + conname | conislocal | coninhcount +-------------------+------------+------------- + part_b_b_not_null | t | 0 + parted_b_not_null | f | 1 +(2 rows) -- specify PARTITION BY for a partition CREATE TABLE fail_part_col_not_found PARTITION OF parted FOR VALUES IN ('c') PARTITION BY RANGE (c); @@ -819,6 +825,9 @@ DETAIL: Failing row contains (1, null). a | integer | | not null | 1 b | integer | | not null | 1 Partition of: parted_notnull_inh_test FOR VALUES IN (1) +Check constraints: + "parted_notnull_inh_test1_a_not_null" CHECK (a IS NOT NULL) + "parted_notnull_inh_test_b_not_null" CHECK (b IS NOT NULL) drop table parted_notnull_inh_test; -- check that collations are assigned in partition bound expressions @@ -859,6 +868,9 @@ drop table test_part_coll_posix; b | integer | | not null | 1 | plain | | Partition of: parted FOR VALUES IN ('b') Partition constraint: ((a IS NOT NULL) AND (a = 'b'::text)) +Check constraints: + "part_b_b_not_null" CHECK (b IS NOT NULL) + "parted_b_not_null" CHECK (b IS NOT NULL) -- Both partition bound and partition key in describe output \d+ part_c @@ -870,6 +882,9 @@ Partition constraint: ((a IS NOT NULL) AND (a = 'b'::text)) Partition of: parted FOR VALUES IN ('c') Partition constraint: ((a IS NOT NULL) AND (a = 'c'::text)) Partition key: RANGE (b) +Check constraints: + "part_c_b_not_null" CHECK (b IS NOT NULL) + "parted_b_not_null" CHECK (b IS NOT NULL) Partitions: part_c_1_10 FOR VALUES FROM (1) TO (10) -- a level-2 partition's constraint will include the parent's expressions @@ -881,6 +896,9 @@ Partitions: part_c_1_10 FOR VALUES FROM (1) TO (10) b | integer | | not null | 0 | plain | | Partition of: part_c FOR VALUES FROM (1) TO (10) Partition constraint: ((a IS NOT NULL) AND (a = 'c'::text) AND (b IS NOT NULL) AND (b >= 1) AND (b < 10)) +Check constraints: + "part_c_b_not_null" CHECK (b IS NOT NULL) + "parted_b_not_null" CHECK (b IS NOT NULL) -- Show partition count in the parent's describe output -- Tempted to include \d+ output listing partitions with bound info but @@ -893,6 +911,8 @@ Partition constraint: ((a IS NOT NULL) AND (a = 'c'::text) AND (b IS NOT NULL) A a | text | | | b | integer | | not null | 0 Partition key: LIST (a) +Check constraints: + "parted_b_not_null" CHECK (b IS NOT NULL) Number of partitions: 3 (Use \d+ to list them.) \d hash_parted diff --git a/src/test/regress/expected/create_table_like.out b/src/test/regress/expected/create_table_like.out index 0ed94f1d2f..8a5692a8fa 100644 --- a/src/test/regress/expected/create_table_like.out +++ b/src/test/regress/expected/create_table_like.out @@ -73,6 +73,8 @@ CREATE TABLE test_like_id_1 (a bigint GENERATED ALWAYS AS IDENTITY, b text); --------+--------+-----------+----------+------------------------------ a | bigint | | not null | generated always as identity b | text | | | +Check constraints: + "test_like_id_1_a_not_null" CHECK (a IS NOT NULL) INSERT INTO test_like_id_1 (b) VALUES ('b1'); SELECT * FROM test_like_id_1; @@ -88,6 +90,8 @@ CREATE TABLE test_like_id_2 (LIKE test_like_id_1); --------+--------+-----------+----------+--------- a | bigint | | not null | b | text | | | +Check constraints: + "test_like_id_1_a_not_null" CHECK (a IS NOT NULL) INSERT INTO test_like_id_2 (b) VALUES ('b2'); ERROR: null value in column "a" of relation "test_like_id_2" violates not-null constraint @@ -104,6 +108,8 @@ CREATE TABLE test_like_id_3 (LIKE test_like_id_1 INCLUDING IDENTITY); --------+--------+-----------+----------+------------------------------ a | bigint | | not null | generated always as identity b | text | | | +Check constraints: + "test_like_id_1_a_not_null" CHECK (a IS NOT NULL) INSERT INTO test_like_id_3 (b) VALUES ('b3'); SELECT * FROM test_like_id_3; -- identity was copied and applied @@ -355,6 +361,7 @@ NOTICE: merging constraint "ctlt1_a_check" with inherited definition b | text | | | | extended | | B Check constraints: "ctlt1_a_check" CHECK (length(a) > 2) + "ctlt1_inh_a_not_null" CHECK (a IS NOT NULL) Inherits: ctlt1 SELECT description FROM pg_description, pg_constraint c WHERE classoid = 'pg_constraint'::regclass AND objoid = c.oid AND c.conrelid = 'ctlt1_inh'::regclass; @@ -373,6 +380,7 @@ NOTICE: merging multiple inherited definitions of column "a" b | text | | | | extended | | c | text | | | | external | | Check constraints: + "ctlt13_inh_a_not_null" CHECK (a IS NOT NULL) "ctlt1_a_check" CHECK (length(a) > 2) "ctlt3_a_check" CHECK (length(a) < 5) "ctlt3_c_check" CHECK (length(c) < 7) @@ -391,6 +399,7 @@ NOTICE: merging column "a" with inherited definition Indexes: "ctlt13_like_expr_idx" btree ((a || c)) Check constraints: + "ctlt13_like_a_not_null" CHECK (a IS NOT NULL) "ctlt1_a_check" CHECK (length(a) > 2) "ctlt3_a_check" CHECK (length(a) < 5) "ctlt3_c_check" CHECK (length(c) < 7) diff --git a/src/test/regress/expected/domain.out b/src/test/regress/expected/domain.out index 73b010f6ed..d8260a069c 100644 --- a/src/test/regress/expected/domain.out +++ b/src/test/regress/expected/domain.out @@ -688,6 +688,15 @@ drop domain dnotnulltest cascade; NOTICE: drop cascades to 2 other objects DETAIL: drop cascades to column col2 of table domnotnull drop cascades to column col1 of table domnotnull +create domain dnotnulltest integer constraint dnn not null; +select conname, contype, contypid::regtype from pg_constraint c + where contypid = 'dnotnulltest'::regtype; + conname | contype | contypid +---------+---------+-------------- + dnn | c | dnotnulltest +(1 row) + +drop domain dnotnulltest; -- Test ALTER DOMAIN .. DEFAULT .. create table domdeftest (col1 ddef1); insert into domdeftest default values; diff --git a/src/test/regress/expected/event_trigger.out b/src/test/regress/expected/event_trigger.out index 5a10958df5..bf8b883d6e 100644 --- a/src/test/regress/expected/event_trigger.out +++ b/src/test/regress/expected/event_trigger.out @@ -448,6 +448,7 @@ NOTICE: END: command_tag=ALTER TABLE type=table identity=evttrig.one ALTER TABLE evttrig.one DROP COLUMN col_c; NOTICE: NORMAL: orig=t normal=f istemp=f type=table column identity=evttrig.one.col_c name={evttrig,one,col_c} args={} NOTICE: NORMAL: orig=f normal=t istemp=f type=default value identity=for evttrig.one.col_c name={evttrig,one,col_c} args={} +NOTICE: NORMAL: orig=f normal=t istemp=f type=table constraint identity=one_col_c_not_null on evttrig.one name={evttrig,one,one_col_c_not_null} args={} NOTICE: END: command_tag=ALTER TABLE type=table identity=evttrig.one ALTER TABLE evttrig.id ALTER COLUMN col_d SET DATA TYPE bigint; NOTICE: END: command_tag=ALTER SEQUENCE type=sequence identity=evttrig.id_col_d_seq @@ -467,13 +468,21 @@ NOTICE: NORMAL: orig=t normal=f istemp=f type=schema identity=evttrig name={evt NOTICE: NORMAL: orig=f normal=t istemp=f type=table identity=evttrig.one name={evttrig,one} args={} NOTICE: NORMAL: orig=f normal=t istemp=f type=sequence identity=evttrig.one_col_a_seq name={evttrig,one_col_a_seq} args={} NOTICE: NORMAL: orig=f normal=t istemp=f type=default value identity=for evttrig.one.col_a name={evttrig,one,col_a} args={} +NOTICE: NORMAL: orig=f normal=t istemp=f type=table constraint identity=one_col_a_not_null on evttrig.one name={evttrig,one,one_col_a_not_null} args={} NOTICE: NORMAL: orig=f normal=t istemp=f type=table identity=evttrig.two name={evttrig,two} args={} NOTICE: NORMAL: orig=f normal=t istemp=f type=table identity=evttrig.id name={evttrig,id} args={} +NOTICE: NORMAL: orig=f normal=t istemp=f type=table constraint identity=id_col_d_not_null on evttrig.id name={evttrig,id,id_col_d_not_null} args={} NOTICE: NORMAL: orig=f normal=t istemp=f type=table identity=evttrig.parted name={evttrig,parted} args={} NOTICE: NORMAL: orig=f normal=t istemp=f type=table identity=evttrig.part_1_10 name={evttrig,part_1_10} args={} +NOTICE: NORMAL: orig=f normal=t istemp=f type=table constraint identity=part_1_10_id_not_null on evttrig.part_1_10 name={evttrig,part_1_10,part_1_10_id_not_null} args={} NOTICE: NORMAL: orig=f normal=t istemp=f type=table identity=evttrig.part_10_20 name={evttrig,part_10_20} args={} +NOTICE: NORMAL: orig=f normal=t istemp=f type=table constraint identity=part_10_20_id_not_null on evttrig.part_10_20 name={evttrig,part_10_20,part_10_20_id_not_null} args={} NOTICE: NORMAL: orig=f normal=t istemp=f type=table identity=evttrig.part_10_15 name={evttrig,part_10_15} args={} +NOTICE: NORMAL: orig=f normal=t istemp=f type=table constraint identity=part_10_20_id_not_null on evttrig.part_10_15 name={evttrig,part_10_15,part_10_20_id_not_null} args={} +NOTICE: NORMAL: orig=f normal=t istemp=f type=table constraint identity=part_10_15_id_not_null on evttrig.part_10_15 name={evttrig,part_10_15,part_10_15_id_not_null} args={} NOTICE: NORMAL: orig=f normal=t istemp=f type=table identity=evttrig.part_15_20 name={evttrig,part_15_20} args={} +NOTICE: NORMAL: orig=f normal=t istemp=f type=table constraint identity=part_10_20_id_not_null on evttrig.part_15_20 name={evttrig,part_15_20,part_10_20_id_not_null} args={} +NOTICE: NORMAL: orig=f normal=t istemp=f type=table constraint identity=part_15_20_id_not_null on evttrig.part_15_20 name={evttrig,part_15_20,part_15_20_id_not_null} args={} DROP TABLE a_temp_tbl; NOTICE: NORMAL: orig=t normal=f istemp=t type=table identity=pg_temp.a_temp_tbl name={pg_temp,a_temp_tbl} args={} -- CREATE OPERATOR CLASS without FAMILY clause should report diff --git a/src/test/regress/expected/foreign_data.out b/src/test/regress/expected/foreign_data.out index 33505352cc..04c48a63ac 100644 --- a/src/test/regress/expected/foreign_data.out +++ b/src/test/regress/expected/foreign_data.out @@ -742,6 +742,7 @@ COMMENT ON COLUMN ft1.c1 IS 'ft1.c1'; c2 | text | | | | (param2 'val2', param3 'val3') | extended | | c3 | date | | | | | plain | | Check constraints: + "ft1_c1_not_null" CHECK (c1 IS NOT NULL) "ft1_c2_check" CHECK (c2 <> ''::text) "ft1_c3_check" CHECK (c3 >= '01-01-1994'::date AND c3 <= '01-31-1994'::date) Server: s0 @@ -864,8 +865,10 @@ ALTER FOREIGN TABLE ft1 ALTER COLUMN c8 SET STORAGE PLAIN; c9 | integer | | | | | plain | | c10 | integer | | | | (p1 'v1') | plain | | Check constraints: + "ft1_c1_not_null" CHECK (c1 IS NOT NULL) "ft1_c2_check" CHECK (c2 <> ''::text) "ft1_c3_check" CHECK (c3 >= '01-01-1994'::date AND c3 <= '01-31-1994'::date) + "ft1_c6_not_null" CHECK (c6 IS NOT NULL) Server: s0 FDW options: (delimiter ',', quote '"', "be quoted" 'value') @@ -913,8 +916,10 @@ ALTER FOREIGN TABLE foreign_schema.ft1 RENAME TO foreign_table_1; c8 | text | | | | (p2 'V2') c10 | integer | | | | (p1 'v1') Check constraints: + "ft1_c1_not_null" CHECK (foreign_column_1 IS NOT NULL) "ft1_c2_check" CHECK (c2 <> ''::text) "ft1_c3_check" CHECK (c3 >= '01-01-1994'::date AND c3 <= '01-31-1994'::date) + "ft1_c6_not_null" CHECK (c6 IS NOT NULL) Server: s0 FDW options: (quote '~', "be quoted" 'value', escape '@') @@ -1406,6 +1411,8 @@ CREATE FOREIGN TABLE ft2 () INHERITS (fd_pt1) c1 | integer | | not null | | plain | | c2 | text | | | | extended | | c3 | date | | | | plain | | +Check constraints: + "fd_pt1_c1_not_null" CHECK (c1 IS NOT NULL) Child tables: ft2 \d+ ft2 @@ -1415,6 +1422,8 @@ Child tables: ft2 c1 | integer | | not null | | | plain | | c2 | text | | | | | extended | | c3 | date | | | | | plain | | +Check constraints: + "fd_pt1_c1_not_null" CHECK (c1 IS NOT NULL) Server: s0 FDW options: (delimiter ',', quote '"', "be quoted" 'value') Inherits: fd_pt1 @@ -1427,6 +1436,8 @@ DROP FOREIGN TABLE ft2; c1 | integer | | not null | | plain | | c2 | text | | | | extended | | c3 | date | | | | plain | | +Check constraints: + "fd_pt1_c1_not_null" CHECK (c1 IS NOT NULL) CREATE FOREIGN TABLE ft2 ( c1 integer NOT NULL, @@ -1440,6 +1451,8 @@ CREATE FOREIGN TABLE ft2 ( c1 | integer | | not null | | | plain | | c2 | text | | | | | extended | | c3 | date | | | | | plain | | +Check constraints: + "ft2_c1_not_null" CHECK (c1 IS NOT NULL) Server: s0 FDW options: (delimiter ',', quote '"', "be quoted" 'value') @@ -1451,6 +1464,8 @@ ALTER FOREIGN TABLE ft2 INHERIT fd_pt1; c1 | integer | | not null | | plain | | c2 | text | | | | extended | | c3 | date | | | | plain | | +Check constraints: + "fd_pt1_c1_not_null" CHECK (c1 IS NOT NULL) Child tables: ft2 \d+ ft2 @@ -1460,6 +1475,8 @@ Child tables: ft2 c1 | integer | | not null | | | plain | | c2 | text | | | | | extended | | c3 | date | | | | | plain | | +Check constraints: + "ft2_c1_not_null" CHECK (c1 IS NOT NULL) Server: s0 FDW options: (delimiter ',', quote '"', "be quoted" 'value') Inherits: fd_pt1 @@ -1481,6 +1498,8 @@ NOTICE: merging column "c3" with inherited definition c1 | integer | | not null | | | plain | | c2 | text | | | | | extended | | c3 | date | | | | | plain | | +Check constraints: + "ft2_c1_not_null" CHECK (c1 IS NOT NULL) Server: s0 FDW options: (delimiter ',', quote '"', "be quoted" 'value') Inherits: fd_pt1 @@ -1494,6 +1513,8 @@ Child tables: ct3, c1 | integer | | not null | | plain | | c2 | text | | | | extended | | c3 | date | | | | plain | | +Check constraints: + "ft2_c1_not_null" CHECK (c1 IS NOT NULL) Inherits: ft2 \d+ ft3 @@ -1503,6 +1524,9 @@ Inherits: ft2 c1 | integer | | not null | | | plain | | c2 | text | | | | | extended | | c3 | date | | | | | plain | | +Check constraints: + "ft2_c1_not_null" CHECK (c1 IS NOT NULL) + "ft3_c1_not_null" CHECK (c1 IS NOT NULL) Server: s0 Inherits: ft2 @@ -1524,6 +1548,9 @@ ALTER TABLE fd_pt1 ADD COLUMN c8 integer; c6 | integer | | | | plain | | c7 | integer | | not null | | plain | | c8 | integer | | | | plain | | +Check constraints: + "fd_pt1_c1_not_null" CHECK (c1 IS NOT NULL) + "fd_pt1_c7_not_null" CHECK (c7 IS NOT NULL) Child tables: ft2 \d+ ft2 @@ -1538,6 +1565,9 @@ Child tables: ft2 c6 | integer | | | | | plain | | c7 | integer | | not null | | | plain | | c8 | integer | | | | | plain | | +Check constraints: + "fd_pt1_c7_not_null" CHECK (c7 IS NOT NULL) + "ft2_c1_not_null" CHECK (c1 IS NOT NULL) Server: s0 FDW options: (delimiter ',', quote '"', "be quoted" 'value') Inherits: fd_pt1 @@ -1556,6 +1586,9 @@ Child tables: ct3, c6 | integer | | | | plain | | c7 | integer | | not null | | plain | | c8 | integer | | | | plain | | +Check constraints: + "fd_pt1_c7_not_null" CHECK (c7 IS NOT NULL) + "ft2_c1_not_null" CHECK (c1 IS NOT NULL) Inherits: ft2 \d+ ft3 @@ -1570,6 +1603,10 @@ Inherits: ft2 c6 | integer | | | | | plain | | c7 | integer | | not null | | | plain | | c8 | integer | | | | | plain | | +Check constraints: + "fd_pt1_c7_not_null" CHECK (c7 IS NOT NULL) + "ft2_c1_not_null" CHECK (c1 IS NOT NULL) + "ft3_c1_not_null" CHECK (c1 IS NOT NULL) Server: s0 Inherits: ft2 @@ -1598,6 +1635,9 @@ ALTER TABLE fd_pt1 ALTER COLUMN c8 SET STORAGE EXTERNAL; c6 | integer | | not null | | plain | | c7 | integer | | | | plain | | c8 | text | | | | external | | +Check constraints: + "fd_pt1_c1_not_null" CHECK (c1 IS NOT NULL) + "fd_pt1_c6_not_null" CHECK (c6 IS NOT NULL) Child tables: ft2 \d+ ft2 @@ -1610,8 +1650,12 @@ Child tables: ft2 c4 | integer | | | 0 | | plain | | c5 | integer | | | | | plain | | c6 | integer | | not null | | | plain | | - c7 | integer | | | | | plain | | + c7 | integer | | not null | | | plain | | c8 | text | | | | | external | | +Check constraints: + "fd_pt1_c6_not_null" CHECK (c6 IS NOT NULL) + "fd_pt1_c7_not_null" CHECK (c7 IS NOT NULL) + "ft2_c1_not_null" CHECK (c1 IS NOT NULL) Server: s0 FDW options: (delimiter ',', quote '"', "be quoted" 'value') Inherits: fd_pt1 @@ -1631,6 +1675,8 @@ ALTER TABLE fd_pt1 DROP COLUMN c8; c1 | integer | | not null | | plain | 10000 | c2 | text | | | | extended | | c3 | date | | | | plain | | +Check constraints: + "fd_pt1_c1_not_null" CHECK (c1 IS NOT NULL) Child tables: ft2 \d+ ft2 @@ -1640,6 +1686,8 @@ Child tables: ft2 c1 | integer | | not null | | | plain | 10000 | c2 | text | | | | | extended | | c3 | date | | | | | plain | | +Check constraints: + "ft2_c1_not_null" CHECK (c1 IS NOT NULL) Server: s0 FDW options: (delimiter ',', quote '"', "be quoted" 'value') Inherits: fd_pt1 @@ -1654,11 +1702,12 @@ SELECT relname, conname, contype, conislocal, coninhcount, connoinherit FROM pg_class AS pc JOIN pg_constraint AS pgc ON (conrelid = pc.oid) WHERE pc.relname = 'fd_pt1' ORDER BY 1,2; - relname | conname | contype | conislocal | coninhcount | connoinherit ----------+------------+---------+------------+-------------+-------------- - fd_pt1 | fd_pt1chk1 | c | t | 0 | t - fd_pt1 | fd_pt1chk2 | c | t | 0 | f -(2 rows) + relname | conname | contype | conislocal | coninhcount | connoinherit +---------+--------------------+---------+------------+-------------+-------------- + fd_pt1 | fd_pt1_c1_not_null | c | t | 0 | f + fd_pt1 | fd_pt1chk1 | c | t | 0 | t + fd_pt1 | fd_pt1chk2 | c | t | 0 | f +(3 rows) -- child does not inherit NO INHERIT constraints \d+ fd_pt1 @@ -1669,6 +1718,7 @@ SELECT relname, conname, contype, conislocal, coninhcount, connoinherit c2 | text | | | | extended | | c3 | date | | | | plain | | Check constraints: + "fd_pt1_c1_not_null" CHECK (c1 IS NOT NULL) "fd_pt1chk1" CHECK (c1 > 0) NO INHERIT "fd_pt1chk2" CHECK (c2 <> ''::text) Child tables: ft2 @@ -1682,6 +1732,7 @@ Child tables: ft2 c3 | date | | | | | plain | | Check constraints: "fd_pt1chk2" CHECK (c2 <> ''::text) + "ft2_c1_not_null" CHECK (c1 IS NOT NULL) Server: s0 FDW options: (delimiter ',', quote '"', "be quoted" 'value') Inherits: fd_pt1 @@ -1716,6 +1767,7 @@ ALTER FOREIGN TABLE ft2 INHERIT fd_pt1; c2 | text | | | | extended | | c3 | date | | | | plain | | Check constraints: + "fd_pt1_c1_not_null" CHECK (c1 IS NOT NULL) "fd_pt1chk1" CHECK (c1 > 0) NO INHERIT "fd_pt1chk2" CHECK (c2 <> ''::text) Child tables: ft2 @@ -1729,6 +1781,7 @@ Child tables: ft2 c3 | date | | | | | plain | | Check constraints: "fd_pt1chk2" CHECK (c2 <> ''::text) + "ft2_c1_not_null" CHECK (c1 IS NOT NULL) Server: s0 FDW options: (delimiter ',', quote '"', "be quoted" 'value') Inherits: fd_pt1 @@ -1747,6 +1800,7 @@ ALTER TABLE fd_pt1 ADD CONSTRAINT fd_pt1chk3 CHECK (c2 <> '') NOT VALID; c2 | text | | | | extended | | c3 | date | | | | plain | | Check constraints: + "fd_pt1_c1_not_null" CHECK (c1 IS NOT NULL) "fd_pt1chk3" CHECK (c2 <> ''::text) NOT VALID Child tables: ft2 @@ -1760,6 +1814,7 @@ Child tables: ft2 Check constraints: "fd_pt1chk2" CHECK (c2 <> ''::text) "fd_pt1chk3" CHECK (c2 <> ''::text) NOT VALID + "ft2_c1_not_null" CHECK (c1 IS NOT NULL) Server: s0 FDW options: (delimiter ',', quote '"', "be quoted" 'value') Inherits: fd_pt1 @@ -1774,6 +1829,7 @@ ALTER TABLE fd_pt1 VALIDATE CONSTRAINT fd_pt1chk3; c2 | text | | | | extended | | c3 | date | | | | plain | | Check constraints: + "fd_pt1_c1_not_null" CHECK (c1 IS NOT NULL) "fd_pt1chk3" CHECK (c2 <> ''::text) Child tables: ft2 @@ -1787,6 +1843,7 @@ Child tables: ft2 Check constraints: "fd_pt1chk2" CHECK (c2 <> ''::text) "fd_pt1chk3" CHECK (c2 <> ''::text) + "ft2_c1_not_null" CHECK (c1 IS NOT NULL) Server: s0 FDW options: (delimiter ',', quote '"', "be quoted" 'value') Inherits: fd_pt1 @@ -1806,6 +1863,7 @@ ALTER TABLE fd_pt1 RENAME CONSTRAINT fd_pt1chk3 TO f2_check; f3 | date | | | | plain | | Check constraints: "f2_check" CHECK (f2 <> ''::text) + "fd_pt1_c1_not_null" CHECK (f1 IS NOT NULL) Child tables: ft2 \d+ ft2 @@ -1818,6 +1876,7 @@ Child tables: ft2 Check constraints: "f2_check" CHECK (f2 <> ''::text) "fd_pt1chk2" CHECK (f2 <> ''::text) + "ft2_c1_not_null" CHECK (f1 IS NOT NULL) Server: s0 FDW options: (delimiter ',', quote '"', "be quoted" 'value') Inherits: fd_pt1 @@ -1864,6 +1923,8 @@ CREATE FOREIGN TABLE fd_pt2_1 PARTITION OF fd_pt2 FOR VALUES IN (1) c2 | text | | | | extended | | c3 | date | | | | plain | | Partition key: LIST (c1) +Check constraints: + "fd_pt2_c1_not_null" CHECK (c1 IS NOT NULL) Partitions: fd_pt2_1 FOR VALUES IN (1) \d+ fd_pt2_1 @@ -1875,6 +1936,8 @@ Partitions: fd_pt2_1 FOR VALUES IN (1) c3 | date | | | | | plain | | Partition of: fd_pt2 FOR VALUES IN (1) Partition constraint: ((c1 IS NOT NULL) AND (c1 = 1)) +Check constraints: + "fd_pt2_c1_not_null" CHECK (c1 IS NOT NULL) Server: s0 FDW options: (delimiter ',', quote '"', "be quoted" 'value') @@ -1894,6 +1957,8 @@ CREATE FOREIGN TABLE fd_pt2_1 ( c2 | text | | | | | extended | | c3 | date | | | | | plain | | c4 | character(1) | | | | | extended | | +Check constraints: + "fd_pt2_1_c1_not_null" CHECK (c1 IS NOT NULL) Server: s0 FDW options: (delimiter ',', quote '"', "be quoted" 'value') @@ -1909,6 +1974,8 @@ DROP FOREIGN TABLE fd_pt2_1; c2 | text | | | | extended | | c3 | date | | | | plain | | Partition key: LIST (c1) +Check constraints: + "fd_pt2_c1_not_null" CHECK (c1 IS NOT NULL) Number of partitions: 0 CREATE FOREIGN TABLE fd_pt2_1 ( @@ -1923,6 +1990,8 @@ CREATE FOREIGN TABLE fd_pt2_1 ( c1 | integer | | not null | | | plain | | c2 | text | | | | | extended | | c3 | date | | | | | plain | | +Check constraints: + "fd_pt2_1_c1_not_null" CHECK (c1 IS NOT NULL) Server: s0 FDW options: (delimiter ',', quote '"', "be quoted" 'value') @@ -1936,6 +2005,8 @@ ALTER TABLE fd_pt2 ATTACH PARTITION fd_pt2_1 FOR VALUES IN (1); c2 | text | | | | extended | | c3 | date | | | | plain | | Partition key: LIST (c1) +Check constraints: + "fd_pt2_c1_not_null" CHECK (c1 IS NOT NULL) Partitions: fd_pt2_1 FOR VALUES IN (1) \d+ fd_pt2_1 @@ -1947,6 +2018,8 @@ Partitions: fd_pt2_1 FOR VALUES IN (1) c3 | date | | | | | plain | | Partition of: fd_pt2 FOR VALUES IN (1) Partition constraint: ((c1 IS NOT NULL) AND (c1 = 1)) +Check constraints: + "fd_pt2_1_c1_not_null" CHECK (c1 IS NOT NULL) Server: s0 FDW options: (delimiter ',', quote '"', "be quoted" 'value') @@ -1964,6 +2037,8 @@ ALTER TABLE fd_pt2_1 ADD CONSTRAINT p21chk CHECK (c2 <> ''); c2 | text | | | | extended | | c3 | date | | | | plain | | Partition key: LIST (c1) +Check constraints: + "fd_pt2_c1_not_null" CHECK (c1 IS NOT NULL) Partitions: fd_pt2_1 FOR VALUES IN (1) \d+ fd_pt2_1 @@ -1976,13 +2051,16 @@ Partitions: fd_pt2_1 FOR VALUES IN (1) Partition of: fd_pt2 FOR VALUES IN (1) Partition constraint: ((c1 IS NOT NULL) AND (c1 = 1)) Check constraints: + "fd_pt2_1_c1_not_null" CHECK (c1 IS NOT NULL) + "fd_pt2_1_c3_not_null" CHECK (c3 IS NOT NULL) "p21chk" CHECK (c2 <> ''::text) Server: s0 FDW options: (delimiter ',', quote '"', "be quoted" 'value') -- cannot drop inherited NOT NULL constraint from a partition ALTER TABLE fd_pt2_1 ALTER c1 DROP NOT NULL; -ERROR: column "c1" is marked NOT NULL in parent table +ERROR: cannot drop constraint fd_pt2_1_c1_not_null on foreign table fd_pt2_1 because constraint fd_pt2_c1_not_null on table fd_pt2 requires it +HINT: You can drop constraint fd_pt2_c1_not_null on table fd_pt2 instead. -- partition must have parent's constraints ALTER TABLE fd_pt2 DETACH PARTITION fd_pt2_1; ALTER TABLE fd_pt2 ALTER c2 SET NOT NULL; @@ -1994,6 +2072,9 @@ ALTER TABLE fd_pt2 ALTER c2 SET NOT NULL; c2 | text | | not null | | extended | | c3 | date | | | | plain | | Partition key: LIST (c1) +Check constraints: + "fd_pt2_c1_not_null" CHECK (c1 IS NOT NULL) + "fd_pt2_c2_not_null" CHECK (c2 IS NOT NULL) Number of partitions: 0 \d+ fd_pt2_1 @@ -2004,6 +2085,8 @@ Number of partitions: 0 c2 | text | | | | | extended | | c3 | date | | not null | | | plain | | Check constraints: + "fd_pt2_1_c1_not_null" CHECK (c1 IS NOT NULL) + "fd_pt2_1_c3_not_null" CHECK (c3 IS NOT NULL) "p21chk" CHECK (c2 <> ''::text) Server: s0 FDW options: (delimiter ',', quote '"', "be quoted" 'value') @@ -2023,6 +2106,8 @@ ALTER TABLE fd_pt2 ADD CONSTRAINT fd_pt2chk1 CHECK (c1 > 0); c3 | date | | | | plain | | Partition key: LIST (c1) Check constraints: + "fd_pt2_c1_not_null" CHECK (c1 IS NOT NULL) + "fd_pt2_c2_not_null" CHECK (c2 IS NOT NULL) "fd_pt2chk1" CHECK (c1 > 0) Number of partitions: 0 @@ -2034,6 +2119,9 @@ Number of partitions: 0 c2 | text | | not null | | | extended | | c3 | date | | not null | | | plain | | Check constraints: + "fd_pt2_1_c1_not_null" CHECK (c1 IS NOT NULL) + "fd_pt2_1_c2_not_null" CHECK (c2 IS NOT NULL) + "fd_pt2_1_c3_not_null" CHECK (c3 IS NOT NULL) "p21chk" CHECK (c2 <> ''::text) Server: s0 FDW options: (delimiter ',', quote '"', "be quoted" 'value') diff --git a/src/test/regress/expected/generated.out b/src/test/regress/expected/generated.out index bb4190340e..28f5175fec 100644 --- a/src/test/regress/expected/generated.out +++ b/src/test/regress/expected/generated.out @@ -252,6 +252,8 @@ SELECT * FROM gtest1_1; --------+---------+-----------+----------+------------------------------------ a | integer | | not null | b | integer | | | generated always as (a * 2) stored +Check constraints: + "gtest1_1_a_not_null" CHECK (a IS NOT NULL) Inherits: gtest1 INSERT INTO gtest1_1 VALUES (4); diff --git a/src/test/regress/expected/identity.out b/src/test/regress/expected/identity.out index 5f03d8e14f..d194961054 100644 --- a/src/test/regress/expected/identity.out +++ b/src/test/regress/expected/identity.out @@ -362,6 +362,8 @@ SELECT seqtypid::regtype FROM pg_sequence WHERE seqrelid = 'itest3_a_seq'::regcl --------+---------+-----------+----------+---------------------------------- a | integer | | not null | generated by default as identity b | text | | | +Check constraints: + "itest3_a_not_null" CHECK (a IS NOT NULL) ALTER TABLE itest3 ALTER COLUMN a TYPE text; -- error ERROR: identity column type must be smallint, integer, or bigint @@ -376,6 +378,9 @@ ALTER TABLE itest3 a | integer | | not null | generated by default as identity b | text | | | c | integer | | not null | generated always as identity +Check constraints: + "itest3_a_not_null" CHECK (a IS NOT NULL) + "itest3_c_not_null" CHECK (c IS NOT NULL) -- ALTER COLUMN ... SET CREATE TABLE itest6 (a int GENERATED ALWAYS AS IDENTITY, b text); @@ -506,6 +511,10 @@ TABLE itest8; f3 | integer | | not null | generated by default as identity | plain | | f4 | bigint | | not null | generated always as identity | plain | | f5 | bigint | | | | plain | | +Check constraints: + "itest8_f2_not_null" CHECK (f2 IS NOT NULL) + "itest8_f3_not_null" CHECK (f3 IS NOT NULL) + "itest8_f4_not_null" CHECK (f4 IS NOT NULL) \d itest8_f2_seq Sequence "public.itest8_f2_seq" diff --git a/src/test/regress/expected/indexing.out b/src/test/regress/expected/indexing.out index 1bdd430f06..addf1d24d5 100644 --- a/src/test/regress/expected/indexing.out +++ b/src/test/regress/expected/indexing.out @@ -1027,6 +1027,9 @@ create table idxpart1 partition of idxpart for values from (0, 0) to (1000, 1000 Partition of: idxpart FOR VALUES FROM (0, 0) TO (1000, 1000) Indexes: "idxpart1_pkey" PRIMARY KEY, btree (a, b) +Check constraints: + "idxpart1_a_not_null" CHECK (a IS NOT NULL) + "idxpart1_b_not_null" CHECK (b IS NOT NULL) drop table idxpart; -- use ALTER TABLE to add a unique constraint @@ -1065,16 +1068,30 @@ create table idxpart3 (b int not null, a int not null); alter table idxpart attach partition idxpart3 for values from (20, 20) to (30, 30); select conname, contype, conrelid::regclass, conindid::regclass, conkey from pg_constraint where conrelid::regclass::text like 'idxpart%' - order by conname; - conname | contype | conrelid | conindid | conkey -----------------+---------+-----------+----------------+-------- - idxpart1_pkey | p | idxpart1 | idxpart1_pkey | {1,2} - idxpart21_pkey | p | idxpart21 | idxpart21_pkey | {1,2} - idxpart22_pkey | p | idxpart22 | idxpart22_pkey | {1,2} - idxpart2_pkey | p | idxpart2 | idxpart2_pkey | {1,2} - idxpart3_pkey | p | idxpart3 | idxpart3_pkey | {2,1} - idxpart_pkey | p | idxpart | idxpart_pkey | {1,2} -(6 rows) + order by conrelid::regclass::text, conname; + conname | contype | conrelid | conindid | conkey +----------------------+---------+-----------+----------------+-------- + idxpart_pkey | p | idxpart | idxpart_pkey | {1,2} + idxpart1_a_not_null | c | idxpart1 | - | {1} + idxpart1_b_not_null | c | idxpart1 | - | {2} + idxpart1_pkey | p | idxpart1 | idxpart1_pkey | {1,2} + idxpart2_a_not_null | c | idxpart2 | - | {1} + idxpart2_b_not_null | c | idxpart2 | - | {2} + idxpart2_pkey | p | idxpart2 | idxpart2_pkey | {1,2} + idxpart21_a_not_null | c | idxpart21 | - | {1} + idxpart21_b_not_null | c | idxpart21 | - | {2} + idxpart21_pkey | p | idxpart21 | idxpart21_pkey | {1,2} + idxpart2_a_not_null | c | idxpart21 | - | {1} + idxpart2_b_not_null | c | idxpart21 | - | {2} + idxpart22_a_not_null | c | idxpart22 | - | {1} + idxpart22_b_not_null | c | idxpart22 | - | {2} + idxpart22_pkey | p | idxpart22 | idxpart22_pkey | {1,2} + idxpart2_a_not_null | c | idxpart22 | - | {1} + idxpart2_b_not_null | c | idxpart22 | - | {2} + idxpart3_a_not_null | c | idxpart3 | - | {2} + idxpart3_b_not_null | c | idxpart3 | - | {1} + idxpart3_pkey | p | idxpart3 | idxpart3_pkey | {2,1} +(20 rows) drop table idxpart; -- Verify that multi-layer partitioning honors the requirement that all @@ -1100,12 +1117,18 @@ create table idxpart21 partition of idxpart2 for values from (0) to (1000); select conname, contype, conrelid::regclass, conindid::regclass, conkey from pg_constraint where conrelid::regclass::text like 'idxpart%' order by conname; - conname | contype | conrelid | conindid | conkey -----------------+---------+-----------+----------------+-------- - idxpart21_pkey | p | idxpart21 | idxpart21_pkey | {1,2} - idxpart2_pkey | p | idxpart2 | idxpart2_pkey | {1,2} - idxpart_pkey | p | idxpart | idxpart_pkey | {1,2} -(3 rows) + conname | contype | conrelid | conindid | conkey +----------------------+---------+-----------+----------------+-------- + idxpart21_a_not_null | c | idxpart21 | - | {1} + idxpart21_b_not_null | c | idxpart21 | - | {2} + idxpart21_pkey | p | idxpart21 | idxpart21_pkey | {1,2} + idxpart2_a_not_null | c | idxpart21 | - | {1} + idxpart2_a_not_null | c | idxpart2 | - | {1} + idxpart2_b_not_null | c | idxpart21 | - | {2} + idxpart2_b_not_null | c | idxpart2 | - | {2} + idxpart2_pkey | p | idxpart2 | idxpart2_pkey | {1,2} + idxpart_pkey | p | idxpart | idxpart_pkey | {1,2} +(9 rows) drop table idxpart; -- If a partitioned table has a unique/PK constraint, then it's not possible diff --git a/src/test/regress/expected/inherit.out b/src/test/regress/expected/inherit.out index 2d49e765de..942b032796 100644 --- a/src/test/regress/expected/inherit.out +++ b/src/test/regress/expected/inherit.out @@ -860,35 +860,44 @@ DETAIL: Failing row contains (null). insert into bc (aa) values (NULL); ERROR: new row for relation "bc" violates check constraint "ac_aa_check" DETAIL: Failing row contains (null, null). -alter table bc drop constraint ac_aa_check; -- fail, disallowed -ERROR: cannot drop inherited constraint "ac_aa_check" of relation "bc" -alter table ac drop constraint ac_aa_check; +alter table bc drop constraint ac_aa_not_null; -- fail, disallowed +ERROR: constraint "ac_aa_not_null" of relation "bc" does not exist +alter table ac drop constraint ac_aa_not_null; +ERROR: constraint "ac_aa_not_null" of relation "ac" does not exist select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount, pg_get_expr(pgc.conbin, pc.oid) as consrc from pg_class as pc inner join pg_constraint as pgc on (pgc.conrelid = pc.oid) where pc.relname in ('ac', 'bc') order by 1,2; - relname | conname | contype | conislocal | coninhcount | consrc ----------+---------+---------+------------+-------------+-------- -(0 rows) + relname | conname | contype | conislocal | coninhcount | consrc +---------+-------------+---------+------------+-------------+------------------ + ac | ac_aa_check | c | t | 0 | (aa IS NOT NULL) + bc | ac_aa_check | c | f | 1 | (aa IS NOT NULL) +(2 rows) alter table ac add constraint ac_check check (aa is not null); alter table bc no inherit ac; select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount, pg_get_expr(pgc.conbin, pc.oid) as consrc from pg_class as pc inner join pg_constraint as pgc on (pgc.conrelid = pc.oid) where pc.relname in ('ac', 'bc') order by 1,2; - relname | conname | contype | conislocal | coninhcount | consrc ----------+----------+---------+------------+-------------+------------------ - ac | ac_check | c | t | 0 | (aa IS NOT NULL) - bc | ac_check | c | t | 0 | (aa IS NOT NULL) -(2 rows) + relname | conname | contype | conislocal | coninhcount | consrc +---------+-------------+---------+------------+-------------+------------------ + ac | ac_aa_check | c | t | 0 | (aa IS NOT NULL) + ac | ac_check | c | t | 0 | (aa IS NOT NULL) + bc | ac_aa_check | c | t | 0 | (aa IS NOT NULL) + bc | ac_check | c | t | 0 | (aa IS NOT NULL) +(4 rows) alter table bc drop constraint ac_check; select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount, pg_get_expr(pgc.conbin, pc.oid) as consrc from pg_class as pc inner join pg_constraint as pgc on (pgc.conrelid = pc.oid) where pc.relname in ('ac', 'bc') order by 1,2; - relname | conname | contype | conislocal | coninhcount | consrc ----------+----------+---------+------------+-------------+------------------ - ac | ac_check | c | t | 0 | (aa IS NOT NULL) -(1 row) + relname | conname | contype | conislocal | coninhcount | consrc +---------+-------------+---------+------------+-------------+------------------ + ac | ac_aa_check | c | t | 0 | (aa IS NOT NULL) + ac | ac_check | c | t | 0 | (aa IS NOT NULL) + bc | ac_aa_check | c | t | 0 | (aa IS NOT NULL) +(3 rows) alter table ac drop constraint ac_check; select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount, pg_get_expr(pgc.conbin, pc.oid) as consrc from pg_class as pc inner join pg_constraint as pgc on (pgc.conrelid = pc.oid) where pc.relname in ('ac', 'bc') order by 1,2; - relname | conname | contype | conislocal | coninhcount | consrc ----------+---------+---------+------------+-------------+-------- -(0 rows) + relname | conname | contype | conislocal | coninhcount | consrc +---------+-------------+---------+------------+-------------+------------------ + ac | ac_aa_check | c | t | 0 | (aa IS NOT NULL) + bc | ac_aa_check | c | t | 0 | (aa IS NOT NULL) +(2 rows) drop table bc; drop table ac; @@ -925,7 +934,7 @@ select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount, pg ---------+---------+---------+------------+-------------+---------- ac | check_a | c | t | 0 | (a <> 0) bc | check_b | c | t | 0 | (b <> 0) - cc | check_a | c | f | 1 | (a <> 0) + cc | check_a | c | t | 0 | (a <> 0) cc | check_b | c | t | 0 | (b <> 0) cc | check_c | c | t | 0 | (c <> 0) (5 rows) @@ -1017,7 +1026,6 @@ Inherits: pp1, alter table pp1 add column a2 int check (a2 > 0); NOTICE: merging definition of column "a2" for child "cc2" -NOTICE: merging constraint "pp1_a2_check" with inherited definition \d cc2 Table "public.cc2" Column | Type | Collation | Nullable | Default @@ -1737,6 +1745,352 @@ select * from cnullparent where f1 = 2; drop table cnullparent cascade; NOTICE: drop cascades to table cnullchild -- +-- Test inheritance of NOT NULL constraints +-- +create table pp1 (f1 int); +create table cc1 (f2 text, f3 int) inherits (pp1); +\d cc1 + Table "public.cc1" + Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+--------- + f1 | integer | | | + f2 | text | | | + f3 | integer | | | +Inherits: pp1 + +create table cc2(f4 float) inherits(pp1,cc1); +NOTICE: merging multiple inherited definitions of column "f1" +\d cc2 + Table "public.cc2" + Column | Type | Collation | Nullable | Default +--------+------------------+-----------+----------+--------- + f1 | integer | | | + f2 | text | | | + f3 | integer | | | + f4 | double precision | | | +Inherits: pp1, + cc1 + +-- named NOT NULL constraint +alter table cc1 add column a2 int constraint nn not null; +\d cc1 + Table "public.cc1" + Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+--------- + f1 | integer | | | + f2 | text | | | + f3 | integer | | | + a2 | integer | | not null | +Check constraints: + "cc1_a2_not_null" CHECK (a2 IS NOT NULL) +Inherits: pp1 +Number of child tables: 1 (Use \d+ to list them.) + +\d cc2 + Table "public.cc2" + Column | Type | Collation | Nullable | Default +--------+------------------+-----------+----------+--------- + f1 | integer | | | + f2 | text | | | + f3 | integer | | | + f4 | double precision | | | + a2 | integer | | not null | +Check constraints: + "cc1_a2_not_null" CHECK (a2 IS NOT NULL) +Inherits: pp1, + cc1 + +alter table pp1 alter column f1 set not null; +\d pp1 + Table "public.pp1" + Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+--------- + f1 | integer | | not null | +Check constraints: + "pp1_f1_not_null" CHECK (f1 IS NOT NULL) +Number of child tables: 2 (Use \d+ to list them.) + +\d cc1 + Table "public.cc1" + Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+--------- + f1 | integer | | not null | + f2 | text | | | + f3 | integer | | | + a2 | integer | | not null | +Check constraints: + "cc1_a2_not_null" CHECK (a2 IS NOT NULL) + "pp1_f1_not_null" CHECK (f1 IS NOT NULL) +Inherits: pp1 +Number of child tables: 1 (Use \d+ to list them.) + +\d cc2 + Table "public.cc2" + Column | Type | Collation | Nullable | Default +--------+------------------+-----------+----------+--------- + f1 | integer | | not null | + f2 | text | | | + f3 | integer | | | + f4 | double precision | | | + a2 | integer | | not null | +Check constraints: + "cc1_a2_not_null" CHECK (a2 IS NOT NULL) + "pp1_f1_not_null" CHECK (f1 IS NOT NULL) +Inherits: pp1, + cc1 + +-- have a look at pg_constraint +select conrelid::regclass, conname, contype, coninhcount, conislocal + from pg_constraint where contype = 'c' and + conrelid in ('pp1'::regclass, 'cc1'::regclass, 'cc2'::regclass) + order by 2, 1; + conrelid | conname | contype | coninhcount | conislocal +----------+-----------------+---------+-------------+------------ + cc1 | cc1_a2_not_null | c | 0 | t + cc2 | cc1_a2_not_null | c | 1 | f + pp1 | pp1_f1_not_null | c | 0 | t + cc1 | pp1_f1_not_null | c | 1 | f + cc2 | pp1_f1_not_null | c | 1 | f +(5 rows) + +-- remove constraint from cc2; one is gone, the other stays +alter table cc2 alter column a2 drop not null; +-- remove constraint cc1, should succeed +alter table cc1 alter column a2 drop not null; +-- have a look at pg_constraint +select conrelid::regclass, conname, contype, coninhcount, conislocal + from pg_constraint where contype = 'c' and + conrelid in ('pp1'::regclass, 'cc1'::regclass, 'cc2'::regclass) + order by 2, 1; + conrelid | conname | contype | coninhcount | conislocal +----------+-----------------+---------+-------------+------------ + pp1 | pp1_f1_not_null | c | 0 | t + cc1 | pp1_f1_not_null | c | 1 | f + cc2 | pp1_f1_not_null | c | 1 | f +(3 rows) + +-- same for cc2 +alter table cc2 alter column f1 drop not null; +-- remove from cc1, should fail again +alter table cc1 alter column f1 drop not null; +-- remove from pp1, should succeed +alter table pp1 alter column f1 drop not null; +-- have a look at pg_constraint +select conrelid::regclass, conname, contype, coninhcount, conislocal + from pg_constraint where contype = 'c' and + conrelid in ('pp1'::regclass, 'cc1'::regclass, 'cc2'::regclass) + order by 2, 1; + conrelid | conname | contype | coninhcount | conislocal +----------+---------+---------+-------------+------------ +(0 rows) + +drop table pp1 cascade; +NOTICE: drop cascades to 2 other objects +DETAIL: drop cascades to table cc1 +drop cascades to table cc2 +\d cc1 +\d cc2 +-- +-- test inherit/deinherit +-- +create table parent(f1 int); +create table child1(f1 int not null); +create table child2(f1 int); +-- child1 should have not null constraint +alter table child1 inherit parent; +-- should fail, missing NOT NULL constraint +alter table child2 inherit child1; +ERROR: column "f1" in child table must be marked NOT NULL +alter table child2 alter column f1 set not null; +alter table child2 inherit child1; +-- add NOT NULL constraint recursively +alter table parent alter column f1 set not null; +\d parent + Table "public.parent" + Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+--------- + f1 | integer | | not null | +Check constraints: + "parent_f1_not_null" CHECK (f1 IS NOT NULL) +Number of child tables: 1 (Use \d+ to list them.) + +\d child1 + Table "public.child1" + Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+--------- + f1 | integer | | not null | +Check constraints: + "child1_f1_not_null" CHECK (f1 IS NOT NULL) + "parent_f1_not_null" CHECK (f1 IS NOT NULL) +Inherits: parent +Number of child tables: 1 (Use \d+ to list them.) + +\d child2 + Table "public.child2" + Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+--------- + f1 | integer | | not null | +Check constraints: + "child2_f1_not_null" CHECK (f1 IS NOT NULL) + "parent_f1_not_null" CHECK (f1 IS NOT NULL) +Inherits: child1 + +select conrelid::regclass, conname, contype, coninhcount, conislocal + from pg_constraint where contype = 'c' and + conrelid in ('parent'::regclass, 'child1'::regclass, 'child2'::regclass) + order by 2, 1; + conrelid | conname | contype | coninhcount | conislocal +----------+--------------------+---------+-------------+------------ + child1 | child1_f1_not_null | c | 0 | t + child2 | child2_f1_not_null | c | 1 | t + parent | parent_f1_not_null | c | 0 | t + child1 | parent_f1_not_null | c | 1 | f + child2 | parent_f1_not_null | c | 1 | f +(5 rows) + +-- +-- test deinherit procedure +-- +-- deinherit child1 +alter table child1 no inherit parent; +\d parent + Table "public.parent" + Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+--------- + f1 | integer | | not null | +Check constraints: + "parent_f1_not_null" CHECK (f1 IS NOT NULL) + +\d child1 + Table "public.child1" + Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+--------- + f1 | integer | | not null | +Check constraints: + "child1_f1_not_null" CHECK (f1 IS NOT NULL) + "parent_f1_not_null" CHECK (f1 IS NOT NULL) +Number of child tables: 1 (Use \d+ to list them.) + +\d child2 + Table "public.child2" + Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+--------- + f1 | integer | | not null | +Check constraints: + "child2_f1_not_null" CHECK (f1 IS NOT NULL) + "parent_f1_not_null" CHECK (f1 IS NOT NULL) +Inherits: child1 + +select conrelid::regclass, conname, contype, coninhcount, conislocal + from pg_constraint where contype = 'c' and + conrelid in ('parent'::regclass, 'child1'::regclass, 'child2'::regclass) + order by 2, 1; + conrelid | conname | contype | coninhcount | conislocal +----------+--------------------+---------+-------------+------------ + child1 | child1_f1_not_null | c | 0 | t + child2 | child2_f1_not_null | c | 1 | t + parent | parent_f1_not_null | c | 0 | t + child1 | parent_f1_not_null | c | 0 | t + child2 | parent_f1_not_null | c | 1 | f +(5 rows) + +-- test inhcount of child2, should fail +alter table child2 alter f1 drop not null; +ERROR: cannot DROP NOT NULL when multiple possible constraints exist +HINT: Consider specifying which constraint to drop with ALTER TABLE .. DROP CONSTRAINT. +-- should succeed +drop table parent; +drop table child1 cascade; +NOTICE: drop cascades to table child2 +-- +-- test multi inheritance tree +-- +create table parent(f1 int not null); +create table c1() inherits(parent); +create table c2() inherits(parent); +create table d1() inherits(c1, c2); +NOTICE: merging multiple inherited definitions of column "f1" +-- show constraint info +select conrelid::regclass, conname, contype, coninhcount, conislocal + from pg_constraint where contype = 'c' and + conrelid in ('parent'::regclass, 'c1'::regclass, 'c2'::regclass, 'd1'::regclass) + order by 2, 1; + conrelid | conname | contype | coninhcount | conislocal +----------+--------------------+---------+-------------+------------ + parent | parent_f1_not_null | c | 0 | t + c1 | parent_f1_not_null | c | 1 | f + c2 | parent_f1_not_null | c | 1 | f + d1 | parent_f1_not_null | c | 2 | f +(4 rows) + +drop table parent cascade; +NOTICE: drop cascades to 3 other objects +DETAIL: drop cascades to table c1 +drop cascades to table c2 +drop cascades to table d1 +-- test child table with inherited columns and +-- with explicitely specified not null constraints +create table parent_1(f1 int); +create table parent_2(f2 text); +create table child(f1 int not null, f2 text not null) inherits(parent_1, parent_2); +NOTICE: merging column "f1" with inherited definition +NOTICE: merging column "f2" with inherited definition +-- show constraint info +select conrelid::regclass, conname, contype, coninhcount, conislocal + from pg_constraint where contype = 'c' and + conrelid in ('parent_1'::regclass, 'parent_2'::regclass, 'child'::regclass) + order by 2, 1; + conrelid | conname | contype | coninhcount | conislocal +----------+-------------------+---------+-------------+------------ + child | child_f1_not_null | c | 0 | t + child | child_f2_not_null | c | 0 | t +(2 rows) + +-- also drops child table +drop table parent_1 cascade; +NOTICE: drop cascades to table child +drop table parent_2; +-- test multi layer inheritance tree +create table p1(f1 int not null); +create table p2(f1 int not null); +create table p3(f2 int); +create table p4(f1 int not null, f3 text not null); +create table c() inherits(p1, p2, p3, p4); +NOTICE: merging multiple inherited definitions of column "f1" +NOTICE: merging multiple inherited definitions of column "f1" +ERROR: relation "c" already exists +-- constraint on f1 should have three parents +select conrelid::regclass, conname, contype, coninhcount, conislocal + from pg_constraint where contype = 'c' and + conrelid in ('p1'::regclass, 'p2'::regclass, 'p3'::regclass, 'p4'::regclass, 'c'::regclass) + order by 2, 1; + conrelid | conname | contype | coninhcount | conislocal +----------+----------------+---------+-------------+------------ + p1 | p1_f1_not_null | c | 0 | t + p2 | p2_f1_not_null | c | 0 | t + p4 | p4_f1_not_null | c | 0 | t + p4 | p4_f3_not_null | c | 0 | t +(4 rows) + +create table d(a int not null, f1 int) inherits(p3, c); +ERROR: relation "d" already exists +select conrelid::regclass, conname, contype, coninhcount, conislocal + from pg_constraint where contype = 'c' and + conrelid in ('p1'::regclass, 'p2'::regclass, 'p3'::regclass, 'p4'::regclass, 'c'::regclass, 'd'::regclass) + order by 2, 1; + conrelid | conname | contype | coninhcount | conislocal +----------+----------------+---------+-------------+------------ + p1 | p1_f1_not_null | c | 0 | t + p2 | p2_f1_not_null | c | 0 | t + p4 | p4_f1_not_null | c | 0 | t + p4 | p4_f3_not_null | c | 0 | t +(4 rows) + +drop table p1 cascade; +drop table p2; +drop table p3; +drop table p4; +-- -- Check use of temporary tables with inheritance trees -- create table inh_perm_parent (a1 int); diff --git a/src/test/regress/expected/merge.out b/src/test/regress/expected/merge.out index 729ae2eb06..75e4cdfedd 100644 --- a/src/test/regress/expected/merge.out +++ b/src/test/regress/expected/merge.out @@ -496,6 +496,7 @@ SELECT * FROM target ORDER BY tid; -- remove constraints alter table target drop CONSTRAINT target_pkey; alter table target alter column tid drop not null; +ERROR: no NOT NULL constraint found to drop -- multiple actions BEGIN; MERGE INTO target t diff --git a/src/test/regress/expected/publication.out b/src/test/regress/expected/publication.out index e6e082de2f..cc79dd4fdd 100644 --- a/src/test/regress/expected/publication.out +++ b/src/test/regress/expected/publication.out @@ -155,6 +155,8 @@ SELECT pubname, puballtables FROM pg_publication WHERE pubname = 'testpub_forall data | text | | | | extended | | Indexes: "testpub_tbl2_pkey" PRIMARY KEY, btree (id) +Check constraints: + "testpub_tbl2_id_not_null" CHECK (id IS NOT NULL) Publications: "testpub_foralltables" @@ -1066,6 +1068,8 @@ Publications: data | text | | | | extended | | Indexes: "testpub_tbl1_pkey" PRIMARY KEY, btree (id) +Check constraints: + "testpub_tbl1_id_not_null" CHECK (id IS NOT NULL) Publications: "testpib_ins_trunct" "testpub_default" @@ -1092,6 +1096,8 @@ ERROR: relation "testpub_nopk" is not part of the publication data | text | | | | extended | | Indexes: "testpub_tbl1_pkey" PRIMARY KEY, btree (id) +Check constraints: + "testpub_tbl1_id_not_null" CHECK (id IS NOT NULL) Publications: "testpib_ins_trunct" "testpub_fortbl" diff --git a/src/test/regress/expected/replica_identity.out b/src/test/regress/expected/replica_identity.out index e25ec06a84..a77d2d72da 100644 --- a/src/test/regress/expected/replica_identity.out +++ b/src/test/regress/expected/replica_identity.out @@ -92,6 +92,10 @@ Indexes: "test_replica_identity_partial" UNIQUE, btree (keya, keyb) WHERE keyb <> '3'::text "test_replica_identity_unique_defer" UNIQUE CONSTRAINT, btree (keya, keyb) DEFERRABLE "test_replica_identity_unique_nondefer" UNIQUE CONSTRAINT, btree (keya, keyb) +Check constraints: + "test_replica_identity_id_not_null" CHECK (id IS NOT NULL) + "test_replica_identity_keya_not_null" CHECK (keya IS NOT NULL) + "test_replica_identity_keyb_not_null" CHECK (keyb IS NOT NULL) -- succeed, nondeferrable unique constraint over nonnullable cols ALTER TABLE test_replica_identity REPLICA IDENTITY USING INDEX test_replica_identity_unique_nondefer; @@ -122,6 +126,10 @@ Indexes: "test_replica_identity_partial" UNIQUE, btree (keya, keyb) WHERE keyb <> '3'::text "test_replica_identity_unique_defer" UNIQUE CONSTRAINT, btree (keya, keyb) DEFERRABLE "test_replica_identity_unique_nondefer" UNIQUE CONSTRAINT, btree (keya, keyb) +Check constraints: + "test_replica_identity_id_not_null" CHECK (id IS NOT NULL) + "test_replica_identity_keya_not_null" CHECK (keya IS NOT NULL) + "test_replica_identity_keyb_not_null" CHECK (keyb IS NOT NULL) SELECT count(*) FROM pg_index WHERE indrelid = 'test_replica_identity'::regclass AND indisreplident; count @@ -170,6 +178,10 @@ Indexes: "test_replica_identity_partial" UNIQUE, btree (keya, keyb) WHERE keyb <> '3'::text "test_replica_identity_unique_defer" UNIQUE CONSTRAINT, btree (keya, keyb) DEFERRABLE "test_replica_identity_unique_nondefer" UNIQUE CONSTRAINT, btree (keya, keyb) +Check constraints: + "test_replica_identity_id_not_null" CHECK (id IS NOT NULL) + "test_replica_identity_keya_not_null" CHECK (keya IS NOT NULL) + "test_replica_identity_keyb_not_null" CHECK (keyb IS NOT NULL) Replica Identity: FULL ALTER TABLE test_replica_identity REPLICA IDENTITY NOTHING; @@ -192,6 +204,8 @@ ALTER TABLE test_replica_identity2 REPLICA IDENTITY USING INDEX test_replica_ide id | integer | | not null | Indexes: "test_replica_identity2_id_key" UNIQUE CONSTRAINT, btree (id) REPLICA IDENTITY +Check constraints: + "test_replica_identity2_id_not_null" CHECK (id IS NOT NULL) ALTER TABLE test_replica_identity2 ALTER COLUMN id TYPE bigint; \d test_replica_identity2 @@ -201,6 +215,8 @@ ALTER TABLE test_replica_identity2 ALTER COLUMN id TYPE bigint; id | bigint | | not null | Indexes: "test_replica_identity2_id_key" UNIQUE CONSTRAINT, btree (id) REPLICA IDENTITY +Check constraints: + "test_replica_identity2_id_not_null" CHECK (id IS NOT NULL) -- straight index variant CREATE TABLE test_replica_identity3 (id int NOT NULL); @@ -213,6 +229,8 @@ ALTER TABLE test_replica_identity3 REPLICA IDENTITY USING INDEX test_replica_ide id | integer | | not null | Indexes: "test_replica_identity3_id_key" UNIQUE, btree (id) REPLICA IDENTITY +Check constraints: + "test_replica_identity3_id_not_null" CHECK (id IS NOT NULL) ALTER TABLE test_replica_identity3 ALTER COLUMN id TYPE bigint; \d test_replica_identity3 @@ -222,6 +240,8 @@ ALTER TABLE test_replica_identity3 ALTER COLUMN id TYPE bigint; id | bigint | | not null | Indexes: "test_replica_identity3_id_key" UNIQUE, btree (id) REPLICA IDENTITY +Check constraints: + "test_replica_identity3_id_not_null" CHECK (id IS NOT NULL) -- ALTER TABLE DROP NOT NULL is not allowed for columns part of an index -- used as replica identity. diff --git a/src/test/regress/expected/rowsecurity.out b/src/test/regress/expected/rowsecurity.out index b5f6eecba1..b295abe8fe 100644 --- a/src/test/regress/expected/rowsecurity.out +++ b/src/test/regress/expected/rowsecurity.out @@ -123,6 +123,8 @@ CREATE POLICY p1r ON document AS RESTRICTIVE TO regress_rls_dave dtitle | text | | | Indexes: "document_pkey" PRIMARY KEY, btree (did) +Check constraints: + "document_dlevel_not_null" CHECK (dlevel IS NOT NULL) Foreign-key constraints: "document_cid_fkey" FOREIGN KEY (cid) REFERENCES category(cid) Policies: @@ -947,6 +949,8 @@ CREATE POLICY pp1r ON part_document AS RESTRICTIVE TO regress_rls_dave dauthor | name | | | | plain | | dtitle | text | | | | extended | | Partition key: RANGE (cid) +Check constraints: + "part_document_dlevel_not_null" CHECK (dlevel IS NOT NULL) Policies: POLICY "pp1" USING ((dlevel <= ( SELECT uaccount.seclv diff --git a/src/test/regress/expected/typed_table.out b/src/test/regress/expected/typed_table.out index 2e47ecbcf5..ca0331c0a6 100644 --- a/src/test/regress/expected/typed_table.out +++ b/src/test/regress/expected/typed_table.out @@ -129,5 +129,7 @@ CREATE TABLE persons3 OF person_type ( name | text | | not null | ''::text Indexes: "persons3_pkey" PRIMARY KEY, btree (id) +Check constraints: + "persons3_name_not_null" CHECK (name IS NOT NULL) Typed table of type: person_type diff --git a/src/test/regress/sql/constraints.sql b/src/test/regress/sql/constraints.sql index 833819a32e..63598c7a8b 100644 --- a/src/test/regress/sql/constraints.sql +++ b/src/test/regress/sql/constraints.sql @@ -537,6 +537,39 @@ ALTER TABLE deferred_excl ADD EXCLUDE (f1 WITH =); DROP TABLE deferred_excl; +-- verify CHECK constraints created for NOT NULL clauses +CREATE TABLE notnull_tbl1 (a INTEGER NOT NULL); +\d notnull_tbl1 +-- DROP NOT NULL gets rid of both the attnotnull flag and the constraint itself +ALTER TABLE notnull_tbl1 ALTER a DROP NOT NULL; +\d notnull_tbl1 +-- SET NOT NULL puts both back +ALTER TABLE notnull_tbl1 ALTER a SET NOT NULL; +\d notnull_tbl1 +-- The simple syntax must not create redundant constraint +ALTER TABLE notnull_tbl1 ALTER a SET NOT NULL; +\d notnull_tbl1 +-- but this should create a second one +ALTER TABLE notnull_tbl1 ADD check (a IS NOT NULL); +\d notnull_tbl1 +-- Dropping the first one keeps attnotnull intact +ALTER TABLE notnull_tbl1 DROP CONSTRAINT notnull_tbl1_a_not_null; +\d notnull_tbl1 +-- but removing the second constraint resets the flag +ALTER TABLE notnull_tbl1 DROP CONSTRAINT notnull_tbl1_a_not_null1; +\d notnull_tbl1 +DROP TABLE notnull_tbl1; + +CREATE TABLE notnull_tbl2 (a INTEGER PRIMARY KEY); +ALTER TABLE notnull_tbl2 ALTER a DROP NOT NULL; + +CREATE TABLE notnull_tbl3 (a INTEGER NOT NULL, CHECK (a IS NOT NULL)); +ALTER TABLE notnull_tbl3 ALTER A DROP NOT NULL; +ALTER TABLE notnull_tbl3 ADD b int, ADD CONSTRAINT pk PRIMARY KEY (a, b); +\d notnull_tbl3 +ALTER TABLE notnull_tbl3 DROP CONSTRAINT pk; +\d notnull_tbl3 + -- Comments -- Setup a low-level role to enforce non-superuser checks. CREATE ROLE regress_constraint_comments; diff --git a/src/test/regress/sql/create_table.sql b/src/test/regress/sql/create_table.sql index 5175f404f7..6418ef8196 100644 --- a/src/test/regress/sql/create_table.sql +++ b/src/test/regress/sql/create_table.sql @@ -532,11 +532,11 @@ CREATE TABLE part_b PARTITION OF parted ( CONSTRAINT check_b CHECK (b >= 0) ) FOR VALUES IN ('b'); -- conislocal should be false for any merged constraints, true otherwise -SELECT conislocal, coninhcount FROM pg_constraint WHERE conrelid = 'part_b'::regclass ORDER BY conislocal, coninhcount; +SELECT conname, conislocal, coninhcount FROM pg_constraint WHERE conrelid = 'part_b'::regclass ORDER BY coninhcount DESC, conname; -- Once check_b is added to the parent, it should be made non-local for part_b ALTER TABLE parted ADD CONSTRAINT check_b CHECK (b >= 0); -SELECT conislocal, coninhcount FROM pg_constraint WHERE conrelid = 'part_b'::regclass; +SELECT conislocal, coninhcount FROM pg_constraint WHERE conrelid = 'part_b'::regclass ORDER BY coninhcount DESC, conname; -- Neither check_a nor check_b are droppable from part_b ALTER TABLE part_b DROP CONSTRAINT check_a; @@ -546,7 +546,7 @@ ALTER TABLE part_b DROP CONSTRAINT check_b; -- traditional inheritance where they will be left behind, because they would -- be local constraints. ALTER TABLE parted DROP CONSTRAINT check_a, DROP CONSTRAINT check_b; -SELECT conislocal, coninhcount FROM pg_constraint WHERE conrelid = 'part_b'::regclass; +SELECT conname, conislocal, coninhcount FROM pg_constraint WHERE conrelid = 'part_b'::regclass ORDER BY coninhcount; -- specify PARTITION BY for a partition CREATE TABLE fail_part_col_not_found PARTITION OF parted FOR VALUES IN ('c') PARTITION BY RANGE (c); diff --git a/src/test/regress/sql/domain.sql b/src/test/regress/sql/domain.sql index f2ca1fb675..3a2a782501 100644 --- a/src/test/regress/sql/domain.sql +++ b/src/test/regress/sql/domain.sql @@ -408,6 +408,13 @@ update domnotnull set col1 = null; drop domain dnotnulltest cascade; +create domain dnotnulltest integer constraint dnn not null; + +select conname, contype, contypid::regtype from pg_constraint c + where contypid = 'dnotnulltest'::regtype; + +drop domain dnotnulltest; + -- Test ALTER DOMAIN .. DEFAULT .. create table domdeftest (col1 ddef1); diff --git a/src/test/regress/sql/indexing.sql b/src/test/regress/sql/indexing.sql index 429120e710..b395bb79cb 100644 --- a/src/test/regress/sql/indexing.sql +++ b/src/test/regress/sql/indexing.sql @@ -522,7 +522,7 @@ create table idxpart3 (b int not null, a int not null); alter table idxpart attach partition idxpart3 for values from (20, 20) to (30, 30); select conname, contype, conrelid::regclass, conindid::regclass, conkey from pg_constraint where conrelid::regclass::text like 'idxpart%' - order by conname; + order by conrelid::regclass::text, conname; drop table idxpart; -- Verify that multi-layer partitioning honors the requirement that all diff --git a/src/test/regress/sql/inherit.sql b/src/test/regress/sql/inherit.sql index 195aedb5ff..e714c6ce7c 100644 --- a/src/test/regress/sql/inherit.sql +++ b/src/test/regress/sql/inherit.sql @@ -279,8 +279,8 @@ select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount, pg insert into ac (aa) values (NULL); insert into bc (aa) values (NULL); -alter table bc drop constraint ac_aa_check; -- fail, disallowed -alter table ac drop constraint ac_aa_check; +alter table bc drop constraint ac_aa_not_null; -- fail, disallowed +alter table ac drop constraint ac_aa_not_null; select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount, pg_get_expr(pgc.conbin, pc.oid) as consrc from pg_class as pc inner join pg_constraint as pgc on (pgc.conrelid = pc.oid) where pc.relname in ('ac', 'bc') order by 1,2; alter table ac add constraint ac_check check (aa is not null); @@ -641,6 +641,169 @@ select * from cnullparent; select * from cnullparent where f1 = 2; drop table cnullparent cascade; +-- +-- Test inheritance of NOT NULL constraints +-- +create table pp1 (f1 int); +create table cc1 (f2 text, f3 int) inherits (pp1); +\d cc1 +create table cc2(f4 float) inherits(pp1,cc1); +\d cc2 + +-- named NOT NULL constraint +alter table cc1 add column a2 int constraint nn not null; +\d cc1 +\d cc2 +alter table pp1 alter column f1 set not null; +\d pp1 +\d cc1 +\d cc2 + +-- have a look at pg_constraint +select conrelid::regclass, conname, contype, coninhcount, conislocal + from pg_constraint where contype = 'c' and + conrelid in ('pp1'::regclass, 'cc1'::regclass, 'cc2'::regclass) + order by 2, 1; + +-- remove constraint from cc2; one is gone, the other stays +alter table cc2 alter column a2 drop not null; + +-- remove constraint cc1, should succeed +alter table cc1 alter column a2 drop not null; + +-- have a look at pg_constraint +select conrelid::regclass, conname, contype, coninhcount, conislocal + from pg_constraint where contype = 'c' and + conrelid in ('pp1'::regclass, 'cc1'::regclass, 'cc2'::regclass) + order by 2, 1; + +-- same for cc2 +alter table cc2 alter column f1 drop not null; + +-- remove from cc1, should fail again +alter table cc1 alter column f1 drop not null; + +-- remove from pp1, should succeed +alter table pp1 alter column f1 drop not null; + +-- have a look at pg_constraint +select conrelid::regclass, conname, contype, coninhcount, conislocal + from pg_constraint where contype = 'c' and + conrelid in ('pp1'::regclass, 'cc1'::regclass, 'cc2'::regclass) + order by 2, 1; + +drop table pp1 cascade; +\d cc1 +\d cc2 + +-- +-- test inherit/deinherit +-- +create table parent(f1 int); +create table child1(f1 int not null); +create table child2(f1 int); + +-- child1 should have not null constraint +alter table child1 inherit parent; + +-- should fail, missing NOT NULL constraint +alter table child2 inherit child1; + +alter table child2 alter column f1 set not null; +alter table child2 inherit child1; + +-- add NOT NULL constraint recursively +alter table parent alter column f1 set not null; + +\d parent +\d child1 +\d child2 + +select conrelid::regclass, conname, contype, coninhcount, conislocal + from pg_constraint where contype = 'c' and + conrelid in ('parent'::regclass, 'child1'::regclass, 'child2'::regclass) + order by 2, 1; + +-- +-- test deinherit procedure +-- + +-- deinherit child1 +alter table child1 no inherit parent; +\d parent +\d child1 +\d child2 +select conrelid::regclass, conname, contype, coninhcount, conislocal + from pg_constraint where contype = 'c' and + conrelid in ('parent'::regclass, 'child1'::regclass, 'child2'::regclass) + order by 2, 1; + +-- test inhcount of child2, should fail +alter table child2 alter f1 drop not null; + +-- should succeed + +drop table parent; +drop table child1 cascade; + +-- +-- test multi inheritance tree +-- +create table parent(f1 int not null); +create table c1() inherits(parent); +create table c2() inherits(parent); +create table d1() inherits(c1, c2); + +-- show constraint info +select conrelid::regclass, conname, contype, coninhcount, conislocal + from pg_constraint where contype = 'c' and + conrelid in ('parent'::regclass, 'c1'::regclass, 'c2'::regclass, 'd1'::regclass) + order by 2, 1; + +drop table parent cascade; + +-- test child table with inherited columns and +-- with explicitely specified not null constraints +create table parent_1(f1 int); +create table parent_2(f2 text); +create table child(f1 int not null, f2 text not null) inherits(parent_1, parent_2); + +-- show constraint info +select conrelid::regclass, conname, contype, coninhcount, conislocal + from pg_constraint where contype = 'c' and + conrelid in ('parent_1'::regclass, 'parent_2'::regclass, 'child'::regclass) + order by 2, 1; + +-- also drops child table +drop table parent_1 cascade; +drop table parent_2; + +-- test multi layer inheritance tree +create table p1(f1 int not null); +create table p2(f1 int not null); +create table p3(f2 int); +create table p4(f1 int not null, f3 text not null); + +create table c() inherits(p1, p2, p3, p4); + +-- constraint on f1 should have three parents +select conrelid::regclass, conname, contype, coninhcount, conislocal + from pg_constraint where contype = 'c' and + conrelid in ('p1'::regclass, 'p2'::regclass, 'p3'::regclass, 'p4'::regclass, 'c'::regclass) + order by 2, 1; + +create table d(a int not null, f1 int) inherits(p3, c); + +select conrelid::regclass, conname, contype, coninhcount, conislocal + from pg_constraint where contype = 'c' and + conrelid in ('p1'::regclass, 'p2'::regclass, 'p3'::regclass, 'p4'::regclass, 'c'::regclass, 'd'::regclass) + order by 2, 1; + +drop table p1 cascade; +drop table p2; +drop table p3; +drop table p4; + -- -- Check use of temporary tables with inheritance trees --