diff options
| -rw-r--r-- | doc/src/sgml/catalogs.sgml | 21 | ||||
| -rw-r--r-- | src/backend/catalog/heap.c | 44 | ||||
| -rw-r--r-- | src/backend/commands/tablecmds.c | 1321 | ||||
| -rw-r--r-- | src/backend/commands/typecmds.c | 175 | ||||
| -rw-r--r-- | src/backend/parser/parse_utilcmd.c | 28 | ||||
| -rw-r--r-- | src/backend/utils/adt/ruleutils.c | 44 | ||||
| -rw-r--r-- | src/include/catalog/heap.h | 4 | ||||
| -rw-r--r-- | src/include/catalog/pg_constraint.h | 1 | ||||
| -rw-r--r-- | src/include/nodes/parsenodes.h | 2 | ||||
| -rw-r--r-- | src/test/regress/expected/alter_table.out | 16 | ||||
| -rw-r--r-- | src/test/regress/expected/cluster.out | 7 | ||||
| -rw-r--r-- | src/test/regress/expected/domain.out | 17 | ||||
| -rw-r--r-- | src/test/regress/expected/inherit.out | 303 | ||||
| -rw-r--r-- | src/test/regress/sql/domain.sql | 10 | ||||
| -rw-r--r-- | src/test/regress/sql/inherit.sql | 137 |
15 files changed, 2016 insertions, 114 deletions
diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml index 8504555bac..d6c28c8de3 100644 --- a/doc/src/sgml/catalogs.sgml +++ b/doc/src/sgml/catalogs.sgml @@ -1820,11 +1820,13 @@ <para> The catalog <structname>pg_constraint</structname> stores check, primary - key, unique, foreign key, and exclusion constraints on tables. + key, unique, foreign key, exclusion constraints and additional information + of not-null constraints on tables. (Column constraints are not treated specially. Every column constraint is equivalent to some table constraint.) - Not-null constraints are represented in the <structname>pg_attribute</> - catalog, not here. + Not-null constraints are primarily represented in the <structname>pg_attribute</> + catalog, but each attribute which has a not-null constraint in <structname>pg_attribute</> + has additional inheritance information stored in <structname>pg_constraint</>. </para> <para> @@ -1873,6 +1875,7 @@ <entry> <literal>c</> = check constraint, <literal>f</> = foreign key constraint, + <literal>n</> = not-null constraint <literal>p</> = primary key constraint, <literal>u</> = unique constraint, <literal>t</> = constraint trigger, @@ -1993,7 +1996,8 @@ <entry><type>int2[]</type></entry> <entry><literal><link linkend="catalog-pg-attribute"><structname>pg_attribute</structname></link>.attnum</></entry> <entry>If a table constraint (including foreign keys, but not constraint - triggers), list of the constrained columns</entry> + triggers), list of the constrained columns. This column is used to store + the referenced attribute number of a not-null constraint, too</entry> </row> <row> @@ -2058,6 +2062,15 @@ index.) </para> + <para> + Not-null constraints are used to store inheritance information for each not-null + constraint attached to a column. They shouldn't be confused with check + constraints, since they are handled differently. If a column in <structname>pg_attribute</> + has its <structfield>attnotnull</> set, there must be a matching not-null constraint + in <structname>pg_constraint</> with the attribute number of the column stored in + <structfield>conkey</>. There is always only one attribute number stored at the same time. + </para> + <note> <para> <structfield>consrc</structfield> is not updated when referenced objects diff --git a/src/backend/catalog/heap.c b/src/backend/catalog/heap.c index a6e541d858..281887b586 100644 --- a/src/backend/catalog/heap.c +++ b/src/backend/catalog/heap.c @@ -1838,6 +1838,47 @@ StoreAttrDefault(Relation rel, AttrNumber attnum, Node *expr) } /* + * Stores a NOT NULL constraint of a column into pg_constraint. + */ +void +StoreColumnNotNullConstraint(Relation rel, CookedConstraint *cooked) +{ + + /* + * Store the constraint. Reflect conislocal and coninhcount to + * match the same values as the attached column. + */ + CreateConstraintEntry(cooked->name, + RelationGetNamespace(rel), + CONSTRAINT_NOTNULL, + false, + false, + true, + RelationGetRelid(rel), + &(cooked->attnum), + 1, + InvalidOid, + InvalidOid, + InvalidOid, + NULL, + InvalidOid, + InvalidOid, + InvalidOid, + 0, + ' ', + ' ', + ' ', + NULL, + NULL, + NULL, + NULL, + cooked->is_local, + cooked->inhcount + ); + +} + +/* * Store a check-constraint expression for the given relation. * * Caller is responsible for updating the count of constraints @@ -1962,6 +2003,9 @@ StoreConstraints(Relation rel, List *cooked_constraints) switch (con->contype) { + case CONSTR_NOTNULL: + StoreColumnNotNullConstraint(rel, con); + break; case CONSTR_DEFAULT: StoreAttrDefault(rel, con->attnum, con->expr); break; diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index 2c9f855f53..12baca1e23 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -182,6 +182,27 @@ typedef struct NewColumnValue } NewColumnValue; /* + * Struct describing the constraint information + * to de-inherit. Currently CHECK and NOT NULL constraints + * are carried by ATExecDropInherit() within these struct. + */ +typedef struct DeinheritConstraintInfo +{ + char contype; /* constraint type */ + AttrNumber attnum; /* if NOT NULL constraint, the attnum */ + char *conname; /* if CHECK constraint, the constraint name */ +} DeinheritConstraintInfo; + +/* + * Struct carrying information about inherited NOT NULL constraint. + */ +typedef struct InheritedColumnNotNull +{ + AttrNumber attnum; + bool attnotnull; +} InheritedColumnNotNull; + +/* * Error-reporting support for RemoveRelations */ struct dropmsgstrings @@ -243,11 +264,13 @@ static const struct dropmsgstrings dropmsgstringarray[] = { static void truncate_check_rel(Relation rel); static List *MergeAttributes(List *schema, List *supers, char relpersistence, - List **supOids, List **supconstr, int *supOidCount); + List **supOids, List **supconstr, int *supOidCount); static bool MergeCheckConstraint(List *constraints, char *name, Node *expr); static bool change_varattnos_walker(Node *node, const AttrNumber *newattno); -static void MergeAttributesIntoExisting(Relation child_rel, Relation parent_rel); -static void MergeConstraintsIntoExisting(Relation child_rel, Relation parent_rel); +static void MergeAttributesIntoExisting(Relation child_rel, Relation parent_rel, + List **child_attnums); +static void MergeConstraintsIntoExisting(Relation child_rel, Relation parent_rel, + List *child_attnums); static void StoreCatalogInheritance(Oid relationId, List *supers); static void StoreCatalogInheritance1(Oid relationId, Oid parentOid, int16 seqNumber, Relation inhRelation); @@ -299,10 +322,20 @@ static void ATExecAddColumn(List **wqueue, AlteredTableInfo *tab, Relation rel, 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 ATPrepAddOids(List **wqueue, Relation rel, bool recurse, - AlterTableCmd *cmd, LOCKMODE lockmode); -static void ATExecDropNotNull(Relation rel, const char *colName, LOCKMODE lockmode); -static void ATExecSetNotNull(AlteredTableInfo *tab, Relation rel, - const char *colName, LOCKMODE lockmode); + AlterTableCmd *cmd, LOCKMODE lockmode); +static void ATExecDropNotNull(Relation rel, const char *colName, DropBehavior behavior, + bool recurse, bool recursing, LOCKMODE lockmode); +static void ATExecSetNotNull(List **wqueue, AlteredTableInfo *tab, Relation rel, + const char *colName, bool recurse, bool recursing, + bool is_new_constraint, LOCKMODE lockmode); +static void ATExecSetNotNullInternal(Relation rel, Relation attr_rel, + HeapTuple atttup, bool *is_new_constraint, + bool allow_new_constraint, bool is_local); +static void +ATExecDropNotNullInternal(Relation rel, Relation attr_rel, + DropBehavior behavior, + HeapTuple atttup, bool recurse, + bool recursing); static void ATExecColumnDefault(Relation rel, const char *colName, Node *newDefault, LOCKMODE lockmode); static void ATPrepSetStatistics(Relation rel, const char *colName, @@ -368,7 +401,16 @@ static void ATExecGenericOptions(Relation rel, List *options); static void copy_relation_data(SMgrRelation rel, SMgrRelation dst, ForkNumber forkNum, char relpersistence); static const char *storage_name(char c); - +static bool +CheckNotNullOnAttributeName(Relation rel, const char *colname, + AttrNumber *attnum); +static void +DropNotNullOnAttributeNum(Relation rel, AttrNumber attnum, + DropBehavior behavior, bool lock); +static bool +MergeNotNullConstraint(List **constraints, List *nnconstraints, AttrNumber attno); +static void +GetRelationNNConstraintList(List **nnconstraints, Oid relOid); /* ---------------------------------------------------------------- * DefineRelation @@ -399,6 +441,8 @@ DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId) TupleDesc descriptor; List *inheritOids; List *old_constraints; + List *oldDefaultsConstraints; + List *nnconstraints; bool localHasOids; int parentOidCount; List *rawDefaults; @@ -526,14 +570,67 @@ DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId) */ rawDefaults = NIL; cookedDefaults = NIL; + nnconstraints = NIL; attnum = 0; foreach(listptr, schema) { ColumnDef *colDef = lfirst(listptr); - attnum++; + if (colDef->is_not_null) + { + /* + * Check list of explicit defined NOT NULL constraints for the + * current column name. There should be only one Constraint node, + * so we give up immediately when we've found a constraint. + */ + + ListCell *nnconstrptr; + foreach(nnconstrptr, colDef->constraints) + { + CookedConstraint *cooked; + Constraint *nnconstr; + + nnconstr = (Constraint *) lfirst(nnconstrptr); + cooked = (CookedConstraint *) palloc(sizeof(CookedConstraint)); + + if (nnconstr->contype != CONSTR_NOTNULL) + continue; + + /* + * Cook the constraint + */ + cooked->contype = CONSTR_NOTNULL; + cooked->attnum = attnum; + cooked->expr = NULL; + cooked->is_local = true; + cooked->inhcount = 0; + nnconstraints = lappend(nnconstraints, cooked); + + /* + * Keep user defined constraint identifier + */ + if (nnconstr->conname != NULL) + { + cooked->name = pstrdup(nnconstr->conname); + } + else + { + cooked->name = ChooseConstraintName(relname, + NameStr(descriptor->attrs[attnum - 1]->attname), + "not_null", namespaceId, NIL); + } + + /* + * We expect only one constraint to be created, so abort the + * loop as soon we've done. + */ + break; + } + + } + if (colDef->raw_default != NULL) { RawColumnDefault *rawEnt; @@ -568,6 +665,15 @@ DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId) } /* + * Concat raw defaults, cooked defaults and NOT NULL constraints, + * so that we can pass 'em over to heap_create_with_catalog() together. + */ + oldDefaultsConstraints = list_concat(cookedDefaults, + old_constraints); + oldDefaultsConstraints = list_concat(oldDefaultsConstraints, + nnconstraints); + + /* * Create the relation. Inherited defaults and constraints are passed in * for immediate handling --- since they don't need parsing, they can be * stored immediately. @@ -580,8 +686,7 @@ DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId) ofTypeId, ownerId, descriptor, - list_concat(cookedDefaults, - old_constraints), + oldDefaultsConstraints, relkind, stmt->relation->relpersistence, false, @@ -1192,7 +1297,8 @@ storage_name(char c) * Output arguments: * 'supOids' receives a list of the OIDs of the parent relations. * 'supconstr' receives a list of constraints belonging to the parents, - * updated as necessary to be valid for the child. + * updated as necessary to be valid for the child. This includes + * inherited CHECK and NOT NULL constraints. * 'supOidCount' is set to the number of parents that have OID columns. * * Return value: @@ -1335,8 +1441,12 @@ MergeAttributes(List *schema, List *supers, char relpersistence, Relation relation; TupleDesc tupleDesc; TupleConstr *constr; - AttrNumber *newattno; + AttrNumber *newattno; + InheritedColumnNotNull *inhAttrNotNull; AttrNumber parent_attno; + List *nnconstraints; + + nnconstraints = NIL; /* * A self-exclusive lock is needed here. If two backends attempt to @@ -1393,6 +1503,10 @@ MergeAttributes(List *schema, List *supers, char relpersistence, newattno = (AttrNumber *) palloc(tupleDesc->natts * sizeof(AttrNumber)); + inhAttrNotNull = + (InheritedColumnNotNull *)palloc(tupleDesc->natts + * sizeof(InheritedColumnNotNull)); + for (parent_attno = 1; parent_attno <= tupleDesc->natts; parent_attno++) { @@ -1412,6 +1526,8 @@ MergeAttributes(List *schema, List *supers, char relpersistence, * out. */ newattno[parent_attno - 1] = 0; + inhAttrNotNull[parent_attno - 1].attnum = 0; + inhAttrNotNull[parent_attno - 1].attnotnull = false; continue; } @@ -1470,6 +1586,9 @@ MergeAttributes(List *schema, List *supers, char relpersistence, def->is_not_null |= attribute->attnotnull; /* Default and other constraints are handled below */ newattno[parent_attno - 1] = exist_attno; + + inhAttrNotNull[parent_attno - 1].attnum = exist_attno; + inhAttrNotNull[parent_attno - 1].attnotnull = def->is_not_null; } else { @@ -1492,6 +1611,13 @@ MergeAttributes(List *schema, List *supers, char relpersistence, def->constraints = NIL; inhSchema = lappend(inhSchema, def); newattno[parent_attno - 1] = ++child_attno; + + /* + * Also record the new inherited column in the + * not null constraint list. + */ + inhAttrNotNull[parent_attno - 1].attnum = newattno[parent_attno - 1]; + inhAttrNotNull[parent_attno - 1].attnotnull = def->is_not_null; } /* @@ -1574,7 +1700,52 @@ MergeAttributes(List *schema, List *supers, char relpersistence, } } + /* + * Build a list of all not null constraints of the + * given parent relation. We need them to identify and record + * any inheritance on not null constraints later. + */ + GetRelationNNConstraintList(&nnconstraints, RelationGetRelid(relation)); + + /* + * We need to adjust any existing not null constraint in the list + * to match the new attribute number in the child table. + * Then loop through the not null constraint list and check for + * any constraint to be created or inherited. + */ + if ((nnconstraints != NIL) && list_length(nnconstraints) > 0) + { + ListCell *nncon; + CookedConstraint *constr; + int i; + + foreach(nncon, nnconstraints) + { + constr = (CookedConstraint *) lfirst(nncon); + constr->attnum = newattno[constr->attnum - 1]; + constr->is_local = false; + constr->inhcount = 1; + } + + for (i = 1; i <= tupleDesc->natts; i++) + { + /* + * Skip any column marked nullable. + */ + if (!inhAttrNotNull[i - 1].attnotnull) + continue; + + /* + * MergeNotNullConstraint() keeps track of the inheritance + * counter if any inherited not null constraint exists + */ + MergeNotNullConstraint(&constraints, nnconstraints, newattno[i - 1]); + + } + } + pfree(newattno); + pfree(inhAttrNotNull); /* * Close the parent rel, but keep our AccessShareLock on it until xact @@ -1654,7 +1825,34 @@ MergeAttributes(List *schema, List *supers, char relpersistence, /* Mark the column as locally defined */ def->is_local = true; + /* Merge of NOT NULL constraints = OR 'em together */ + if (!def->is_not_null && newdef->is_not_null) + { + ListCell *constrCell; + + /* + * There must be a locally defined Constraint node attached + * to this column definition. We need to inject it to the + * list of local constraints, at least to keep a possible + * assigned constraint name. We do this by adding the + * Constraint node to the column's own list of constraints. + * Such constraints are treated as locally defined and will + * be handled later when the target table get's created in + * DefineRelation(). + */ + foreach (constrCell, newdef->constraints) + { + Constraint *newnnconstr = lfirst(constrCell); + if (newnnconstr->contype == CONSTR_NOTNULL) + { + def->constraints = lappend(def->constraints, + newnnconstr); + break; + } + } + } + def->is_not_null |= newdef->is_not_null; /* If new def has a default, override previous default */ if (newdef->raw_default != NULL) @@ -1754,6 +1952,145 @@ MergeCheckConstraint(List *constraints, char *name, Node *expr) return false; } +/* + * MergeNotNullConstraint + * + * Merge existing not null constraints identified by the given + * attribute number. This will adjust the inheritance counter of + * an existing not null constraint given in the nnconstraints list + * if the specified attribute number matches a constraint found in + * nnconstraints. + */ +static bool +MergeNotNullConstraint(List **constraints, List *nnconstraints, AttrNumber attno) +{ + ListCell *lc; + bool found; + + found = false; + + foreach(lc, nnconstraints) + { + CookedConstraint *child_nncon; + ListCell *merge_lc; + + child_nncon = (CookedConstraint *) lfirst(lc); + + /* + * Paranoid check, we don't expect any other constraint + * in the nnconstraint list here. + */ + Assert(child_nncon->contype == CONSTR_NOTNULL); + + /* + * Scan the list for the given attribute number. If found, check + * wether its already assigned to the final constraint list. If true, + * just bump up its inheritance counter, otherwise we need to + * save the new cooked constraint. + */ + if (child_nncon->attnum != attno) + continue; + + foreach(merge_lc, *constraints) + { + CookedConstraint *parent_nncon; + + parent_nncon = (CookedConstraint *) lfirst(merge_lc); + + if (parent_nncon->contype != CONSTR_NOTNULL) + continue; + + if (parent_nncon->attnum != attno) + continue; + + /* + * This not null constraint was already assigned from another parent. + * Bump its inheritance counter. + */ + parent_nncon->inhcount++; + found = true; + } + + if (!found) + { + /* + * Constraint wasn't already merged. Assign it to the + * constraints list. + */ + *constraints = lappend(*constraints, child_nncon); + } + + break; + } + + return found; +} + +/* + * GetRelationNNConstraintList + */ +static void +GetRelationNNConstraintList(List **nnconstraints, Oid relOid) +{ + HeapTuple constr_tup; + Relation constr_rel; + SysScanDesc scan; + ScanKeyData key; + + /* + * Scan through the pg_constraint tuples belonging to relOid, + * looking for CONSTRAINT_NOTNULL constraints. We build then a list + * of CookedConstraints for all matching not null constraints. + */ + + constr_rel = heap_open(ConstraintRelationId, RowExclusiveLock); + ScanKeyInit(&key, + Anum_pg_constraint_conrelid, + BTEqualStrategyNumber, F_OIDEQ, + ObjectIdGetDatum(relOid)); + scan = systable_beginscan(constr_rel, ConstraintRelidIndexId, + true, SnapshotNow, 1, &key); + + while (HeapTupleIsValid(constr_tup = systable_getnext(scan))) + { + Form_pg_constraint constr; + CookedConstraint *cooked; + Datum arrayp; + Datum *keyvals; + bool isnull; + int nelems; + + constr = (Form_pg_constraint) GETSTRUCT(constr_tup); + + if (constr->contype != CONSTRAINT_NOTNULL) + continue; + + cooked = (CookedConstraint *)palloc(sizeof(CookedConstraint)); + cooked->contype = CONSTR_NOTNULL; + cooked->name = pstrdup(NameStr(constr->conname)); + + /* + * A not null constraint tuple is expected to have one + * and only one assigned attribute number + */ + arrayp = SysCacheGetAttr(CONSTROID, constr_tup, + Anum_pg_constraint_conkey, + &isnull); + Assert(!isnull); + + deconstruct_array(DatumGetArrayTypeP(arrayp), INT2OID, 2, true, + 's', &keyvals, NULL, &nelems); + Assert(nelems == 1); + cooked->attnum = keyvals[0]; + cooked->is_local = constr->conislocal; + cooked->inhcount = constr->coninhcount; + + *nnconstraints = lappend(*nnconstraints, cooked); + } + + systable_endscan(scan); + heap_close(constr_rel, NoLock); +} /* * Replace varattno values in an expression tree according to the given @@ -2809,14 +3146,22 @@ ATPrepCmd(List **wqueue, Relation rel, AlterTableCmd *cmd, pass = cmd->def ? AT_PASS_ADD_CONSTR : AT_PASS_DROP; break; case AT_DropNotNull: /* ALTER COLUMN DROP NOT NULL */ + ATSimplePermissions(rel, ATT_TABLE | ATT_FOREIGN_TABLE); - ATSimpleRecursion(wqueue, rel, cmd, recurse, lockmode); + + if (recurse) + cmd->subtype = AT_DropNotNullRecurse; + /* No command-specific prep needed */ pass = AT_PASS_DROP; break; case AT_SetNotNull: /* ALTER COLUMN SET NOT NULL */ + ATSimplePermissions(rel, ATT_TABLE | ATT_FOREIGN_TABLE); - ATSimpleRecursion(wqueue, rel, cmd, recurse, lockmode); + + if (recurse) + cmd->subtype = AT_SetNotNullRecurse; + /* No command-specific prep needed */ pass = AT_PASS_ADD_CONSTR; break; @@ -3055,10 +3400,20 @@ ATExecCmd(List **wqueue, AlteredTableInfo *tab, Relation rel, ATExecColumnDefault(rel, cmd->name, cmd->def, lockmode); break; case AT_DropNotNull: /* ALTER COLUMN DROP NOT NULL */ - ATExecDropNotNull(rel, cmd->name, lockmode); + ATExecDropNotNull(rel, cmd->name, cmd->behavior, + false, false, lockmode); + break; + case AT_DropNotNullRecurse: /* ALTER COLUMN DROP NOT NULL with recursion */ + ATExecDropNotNull(rel, cmd->name, cmd->behavior, + true, false, lockmode); break; case AT_SetNotNull: /* ALTER COLUMN SET NOT NULL */ - ATExecSetNotNull(tab, rel, cmd->name, lockmode); + ATExecSetNotNull(wqueue, tab, rel, cmd->name, false, + false, false, lockmode); + break; + case AT_SetNotNullRecurse: /* ALTER COLUMN SET NOT NULL recursing */ + ATExecSetNotNull(wqueue, tab, rel, cmd->name, true, + false, false, lockmode); break; case AT_SetStatistics: /* ALTER COLUMN SET STATISTICS */ ATExecSetStatistics(rel, cmd->name, cmd->def, lockmode); @@ -3643,8 +3998,9 @@ ATRewriteTable(AlteredTableInfo *tab, Oid OIDNewHeap, LOCKMODE lockmode) if (heap_attisnull(tuple, attn + 1)) ereport(ERROR, (errcode(ERRCODE_NOT_NULL_VIOLATION), - errmsg("column \"%s\" contains null values", - NameStr(newTupDesc->attrs[attn]->attname)))); + errmsg("column \"%s\" of relation \"%s\" contains null values", + NameStr(newTupDesc->attrs[attn]->attname), + RelationGetRelationName(oldrel)))); } foreach(l, tab->constraints) @@ -4148,6 +4504,8 @@ ATExecAddColumn(List **wqueue, AlteredTableInfo *tab, Relation rel, Oid collOid; Form_pg_type tform; Expr *defval; + bool processDefaults; + bool processnnConstr; List *children; ListCell *child; @@ -4174,6 +4532,7 @@ ATExecAddColumn(List **wqueue, AlteredTableInfo *tab, Relation rel, Form_pg_attribute childatt = (Form_pg_attribute) GETSTRUCT(tuple); Oid ctypeId; int32 ctypmod; + bool new_constraint; Oid ccollid; /* Child column must match on type, typmod, and collation */ @@ -4206,6 +4565,22 @@ ATExecAddColumn(List **wqueue, AlteredTableInfo *tab, Relation rel, simple_heap_update(attrdesc, &tuple->t_self, tuple); CatalogUpdateIndexes(attrdesc, tuple); + /* + * We need a CCI here + */ + CommandCounterIncrement(); + + /* + * We shouldn't forget to adjust any NOT NULL constraints. Take care wether + * we have to create a new NOT NULL constraint (the existing attribute + * doesn't have a NOT NULL constraint attached already) or to adjust an + * existing with new inheritance information. Since we merge with + * an existing attribute, we need to supress the creation of an extra + * constraint tuple. + */ + ATExecSetNotNullInternal(rel, attrdesc, tuple, &new_constraint, + colDef->is_not_null, false); + heap_freetuple(tuple); /* Inform the user about the merge */ @@ -4311,6 +4686,9 @@ ATExecAddColumn(List **wqueue, AlteredTableInfo *tab, Relation rel, /* Make the attribute's catalog entry visible */ CommandCounterIncrement(); + processDefaults = false; + processnnConstr = false; + /* * Store the DEFAULT, if any, in the catalogs */ @@ -4333,11 +4711,70 @@ ATExecAddColumn(List **wqueue, AlteredTableInfo *tab, Relation rel, */ AddRelationNewConstraints(rel, list_make1(rawEnt), NIL, false, true); - /* Make the additional catalog changes visible */ - CommandCounterIncrement(); + /* + * We need to make changes visible, but defer it until + * we have processed NOT NULL constraints below. + */ + processDefaults = true; + } + + /* + * Add NOT NULL constraint to the list of constraint + * nodes. We need to get the transformed Constraint expression + * from the current node, if we want to keep a user defined + * constraint identifier. We can't use ATExecSetNotNullInternal() + * directly, since it generates *always* a constraint name. + */ + if (colDef->is_not_null) + { + + ListCell *nnconstrptr; + foreach(nnconstrptr, colDef->constraints) + { + CookedConstraint *cooked; + Constraint *nnconstr; + + nnconstr = (Constraint *) lfirst(nnconstrptr); + if (nnconstr->contype != CONSTR_NOTNULL) + continue; + + cooked = (CookedConstraint *) palloc(sizeof(CookedConstraint)); + + cooked->contype = CONSTR_NOTNULL; + cooked->attnum = attribute.attnum; + cooked->expr = NULL; + cooked->is_local = colDef->is_local; + cooked->inhcount = colDef->inhcount; + + /* + * Keep user defined constraint identifier + */ + if (nnconstr->conname != NULL) + { + cooked->name = pstrdup(nnconstr->conname); + } + else + { + cooked->name = ChooseConstraintName(RelationGetRelationName(rel), + NameStr(attribute.attname), + "not_null", + RelationGetNamespace(rel), + NIL); + } + + StoreColumnNotNullConstraint(rel, cooked); + processnnConstr = true; + break; + } } /* + * Make new defaults and NOT NULL constraints visible. + */ + if (processDefaults || processnnConstr) + CommandCounterIncrement(); + + /* * Tell Phase 3 to fill in the default expression, if there is one. * * If there is no default, Phase 3 doesn't have to do anything, because @@ -4543,13 +4980,30 @@ ATPrepAddOids(List **wqueue, Relation rel, bool recurse, AlterTableCmd *cmd, LOC * ALTER TABLE ALTER COLUMN DROP NOT NULL */ static void -ATExecDropNotNull(Relation rel, const char *colName, LOCKMODE lockmode) +ATExecDropNotNull(Relation rel, const char *colName, DropBehavior behavior, + bool recurse, bool recursing, LOCKMODE lockmode) { - HeapTuple tuple; + List *children; + ListCell *child; + HeapTuple tuple; AttrNumber attnum; - Relation attr_rel; - List *indexoidlist; - ListCell *indexoidscan; + Relation attr_rel; + + children = NIL; + + if (recursing) + ATSimplePermissions(rel, ATT_TABLE|ATT_FOREIGN_TABLE); + + children = find_inheritance_children(RelationGetRelid(rel), + AccessExclusiveLock); + + /* + * Lookup the attribute. This also checks for a dropped column + * or any attempts to alter a system column. Returns false in case + * no further work needs to be done (e.g. no NOT NULL present). + */ + if (!CheckNotNullOnAttributeName(rel, colName, &attnum)) + return; /* * lookup the attribute @@ -4570,8 +5024,288 @@ ATExecDropNotNull(Relation rel, const char *colName, LOCKMODE lockmode) if (attnum <= 0) ereport(ERROR, (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), - errmsg("cannot alter system column \"%s\"", - colName))); + errmsg("cannot alter system column \"%s\" of relation \"%s\"", + colName, RelationGetRelationName(rel)))); + + /* + * Treat it an error if there's an attempt to remove + * a NOT NULL constraint from a table but not from + * existing child tables (thus, ALTER TABLE ONLY...DROP NOT NULL + * was specified to a table inherited from others). + */ + if (!recurse) + { + if (children) + ereport(ERROR, + (errcode(ERRCODE_INVALID_TABLE_DEFINITION), + errmsg("NOT NULL constraint must be removed from child tables too"))); + } + + /* + * Everything looks okay, so perform the deletion on the parent. + */ + ATExecDropNotNullInternal(rel, attr_rel, behavior, tuple, + recurse, recursing); + //DropNotNullOnAttributeNum(rel, attnum, behavior, false); + + heap_freetuple(tuple); + heap_close(attr_rel, NoLock); + + /* + * Perform work on all children, if any + */ + foreach(child, children) + { + Oid childrelid = lfirst_oid(child); + Relation childrel; + + attr_rel = heap_open(AttributeRelationId, RowExclusiveLock); + childrel = heap_open(childrelid, NoLock); + CheckTableNotInUse(childrel, "ALTER TABLE"); + + /* + * Look for inherited column + */ + tuple = SearchSysCacheCopyAttName(childrelid, colName); + + /* not expected */ + Assert(HeapTupleIsValid(tuple)); + + /* Perform deletion */ + ATExecDropNotNull(childrel, colName, behavior, + recurse, true, lockmode); + + heap_freetuple(tuple); + heap_close(attr_rel, NoLock); + heap_close(childrel, NoLock); + } +} + +/* + * Checks if given attribute number is specified within + * the key array of the given pg_constraint tuple. This function + * is primarily used to check for matching constraints of + * type CONSTRAINT_NOTNULL, but can be used for other constraints types + * as well. + */ +static bool +constraint_is_for_single_col(HeapTuple constrtup, int attnum) +{ + Form_pg_constraint constrStruct; + Datum arrayp; + Datum *keyvals; + int nelems; + bool isnull; + bool found; + int i; + + constrStruct = (Form_pg_constraint) GETSTRUCT(constrtup); + arrayp = SysCacheGetAttr(CONSTROID, constrtup, + Anum_pg_constraint_conkey, + &isnull); + + if (isnull) + return false; + + found = false; + deconstruct_array(DatumGetArrayTypeP(arrayp), INT2OID, 2, true, + 's', &keyvals, NULL, &nelems); + for (i = 0; i < nelems; i++) + { + if (DatumGetInt16(keyvals[0]) != attnum) + found = false; + else + found = true; + } + + return found; +} + +/* + * Does the leg work on dropping a NOT NULL constraint + * identified by the given constrtup tuple. + * + * Note: The caller is responsible to pass a valid + * CONSTRAINT_NOTNULL tuple. + */ +static void +ATExecDropNotNullInternal(Relation rel, Relation attr_rel, + DropBehavior behavior, + HeapTuple atttup, bool recurse, + bool recursing) +{ + Form_pg_attribute attr; + HeapTuple constr_tup; + Relation constr_rel; + SysScanDesc scan; + ScanKeyData key; + bool found; + + found = false; + attr = (Form_pg_attribute) GETSTRUCT(atttup); + + /* exit immediately if nothing to do */ + if (!attr->attnotnull) + return; + + /* + * Lookup CONSTRAINT_NOTNULL tuple + */ + constr_rel = heap_open(ConstraintRelationId, RowExclusiveLock); + ScanKeyInit(&key, + Anum_pg_constraint_conrelid, + BTEqualStrategyNumber, F_OIDEQ, + ObjectIdGetDatum(RelationGetRelid(rel))); + scan = systable_beginscan(constr_rel, ConstraintRelidIndexId, + true, SnapshotNow, 1, &key); + + while (HeapTupleIsValid(constr_tup = systable_getnext(scan))) + { + Form_pg_constraint constr_struct; + + constr_struct = (Form_pg_constraint) GETSTRUCT(constr_tup); + + /* We need to look for NOT NULL constraint only */ + if (constr_struct->contype != CONSTRAINT_NOTNULL) + continue; + + if (constraint_is_for_single_col(constr_tup, attr->attnum)) + { + found = TRUE; + break; + } + } + + /* not expected */ + Assert(found); + + if (found) + { + HeapTuple copy_tuple = heap_copytuple(constr_tup); + Form_pg_constraint constrStruct = (Form_pg_constraint) GETSTRUCT(copy_tuple); + + /* + * Be paranoid, caller is responsible to pass a valid HeapTuple. + */ + Assert(constrStruct->contype == CONSTRAINT_NOTNULL); + + /* Preliminary check, it is okay to drop this constraint when recursing */ + if ((constrStruct->coninhcount > 0) && (!recursing)) + { + ereport(ERROR, + (errcode(ERRCODE_INVALID_TABLE_DEFINITION), + errmsg("cannot drop inherited NOT NULL constraint \"%s\", relation \"%s\"", + NameStr(constrStruct->conname), RelationGetRelationName(rel)))); + } + + if (recurse) + { + /* + * If the child constraint has other definition sources, just + * decrement its inheritance count; if not, recurse to delete + * it. + */ + if ((constrStruct->coninhcount == 0) + || (constrStruct->coninhcount == 1 && !constrStruct->conislocal)) + { + DropNotNullOnAttributeNum(rel, attr->attnum, behavior, false); + + /* + * Make sure changes are visible + */ + CommandCounterIncrement(); + } + else + { + /* Child constraint must survive my deletion */ + constrStruct->coninhcount--; + simple_heap_update(constr_rel, ©_tuple->t_self, copy_tuple); + CatalogUpdateIndexes(constr_rel, copy_tuple); + + /* Make update visible */ + CommandCounterIncrement(); + } + } + else + { + /* + * If we were told to drop ONLY in this table (no recursion), + * we need to mark the inheritors' constraints as locally + * defined rather than inherited. + */ + constrStruct->coninhcount--; + constrStruct->conislocal = true; + + simple_heap_update(constr_rel, ©_tuple->t_self, copy_tuple); + CatalogUpdateIndexes(constr_rel, copy_tuple); + + /* Make update visible */ + CommandCounterIncrement(); + } + + heap_freetuple(copy_tuple); + } + + systable_endscan(scan); + heap_close(constr_rel, NoLock); +} + +/* + * Checks wether the given attribute name has a NOT NULL constraint attached + * which can be dropped. + * + * Any attempts to drop a NOT NULL constraint on a system + * column or non-existing column will throw an error. Returns true + * in case the caller is allowed to proceed, false if no NOT NULL flag + * was set on the attribute. + */ +static bool +CheckNotNullOnAttributeName(Relation rel, const char *colname, AttrNumber *attnum) +{ + Relation attr_rel; + HeapTuple attr_tuple; + Form_pg_attribute attr_struct; + bool has_not_null; + List *indexoidlist; + ListCell *indexoidscan; + + has_not_null = false; + + attr_rel = heap_open(AttributeRelationId, RowExclusiveLock); + attr_tuple = SearchSysCacheCopyAttName(RelationGetRelid(rel), colname); + + if (!HeapTupleIsValid(attr_tuple)) + ereport(ERROR, + (errcode(ERRCODE_UNDEFINED_COLUMN), + errmsg("column \"%s\" of relation \"%s\" does not exist", + colname, + RelationGetRelationName(rel)))); + + /* attribute exists */ + attr_struct = (Form_pg_attribute) GETSTRUCT(attr_tuple); + + /* Prevent them from altering a system attribute */ + if (attr_struct->attnum <= 0) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("cannot alter system column \"%s\" of relation \"%s\"", + colname, + RelationGetRelationName(rel)))); + + /* + * Check for dropped attributes + */ + if (attr_struct->attisdropped) + { + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("attempt to drop NOT NULL constraint on dropped column \"%s\" of relation \"%s\"", + colname, + RelationGetRelationName(rel)))); + } + + if (attnum) + *attnum = attr_struct->attnum; /* * Check that the attribute is not in a primary key @@ -4582,8 +5316,8 @@ ATExecDropNotNull(Relation rel, const char *colName, LOCKMODE lockmode) foreach(indexoidscan, indexoidlist) { - Oid indexoid = lfirst_oid(indexoidscan); - HeapTuple indexTuple; + Oid indexoid = lfirst_oid(indexoidscan); + HeapTuple indexTuple; Form_pg_index indexStruct; int i; @@ -4601,45 +5335,136 @@ ATExecDropNotNull(Relation rel, const char *colName, LOCKMODE lockmode) */ for (i = 0; i < indexStruct->indnatts; i++) { - if (indexStruct->indkey.values[i] == attnum) + if (indexStruct->indkey.values[i] == attr_struct->attnum) ereport(ERROR, (errcode(ERRCODE_INVALID_TABLE_DEFINITION), errmsg("column \"%s\" is in a primary key", - colName))); + colname))); } } ReleaseSysCache(indexTuple); } - list_free(indexoidlist); + /* + * In case no NOT NULL constraint was set on the + * column, give up and tell the caller we haven't done + * anything. + */ + has_not_null = ((!attr_struct->attnotnull) ? false : true); + + heap_freetuple(attr_tuple); + heap_close(attr_rel, NoLock); + return has_not_null; +} + +/* + * Drops a NOT NULL constraint from the given attribute number of the + * specified relation. lock gives the caller the possibility to + * specify wether pg_attribute was already locked or not. + */ +static void +DropNotNullOnAttributeNum(Relation rel, AttrNumber attnum, + DropBehavior behavior, bool lock) +{ + Relation att_rel; + HeapTuple att_tuple; + Form_pg_attribute att_struct; + SysScanDesc scan; + ScanKeyData key; + bool found_constraint; + + if (lock) + att_rel = heap_open(AttributeRelationId, RowExclusiveLock); + else + att_rel = heap_open(AttributeRelationId, NoLock); + + att_tuple = SearchSysCacheCopy(ATTNUM, + ObjectIdGetDatum(RelationGetRelid(rel)), + Int16GetDatum(attnum), + 0, 0); + + att_struct = (Form_pg_attribute) GETSTRUCT(att_tuple); + + /* not expected, so be paranoid */ + Assert(att_struct->attnotnull); + + ((Form_pg_attribute) GETSTRUCT(att_tuple))->attnotnull = FALSE; + simple_heap_update(att_rel, &att_tuple->t_self, att_tuple); + + /* keep the system catalog indexes current */ + CatalogUpdateIndexes(att_rel, att_tuple); + + heap_close(att_rel, NoLock); /* - * Okay, actually perform the catalog change ... if needed + * We shouldn't forget any pg_constraint tuple belonging + * to this attributes NOT NULL constraint. */ - if (((Form_pg_attribute) GETSTRUCT(tuple))->attnotnull) + found_constraint = false; + att_rel = heap_open(ConstraintRelationId, RowExclusiveLock); + ScanKeyInit(&key, + Anum_pg_constraint_conrelid, + BTEqualStrategyNumber, F_OIDEQ, + ObjectIdGetDatum(RelationGetRelid(rel))); + scan = systable_beginscan(att_rel, ConstraintRelidIndexId, + true, SnapshotNow, 1, &key); + + while (HeapTupleIsValid(att_tuple = systable_getnext(scan))) { - ((Form_pg_attribute) GETSTRUCT(tuple))->attnotnull = FALSE; + Form_pg_constraint constr_struct; - simple_heap_update(attr_rel, &tuple->t_self, tuple); + constr_struct = (Form_pg_constraint) GETSTRUCT(att_tuple); - /* keep the system catalog indexes current */ - CatalogUpdateIndexes(attr_rel, tuple); + if (constr_struct->contype != CONSTRAINT_NOTNULL) + continue; + + if (constraint_is_for_single_col(att_tuple, attnum)) + { + ObjectAddress conobj; + + found_constraint = true; + conobj.classId = ConstraintRelationId; + conobj.objectId = HeapTupleGetOid(att_tuple); + conobj.objectSubId = 0; + + performDeletion(&conobj, behavior); + break; + } } - heap_close(attr_rel, RowExclusiveLock); + systable_endscan(scan); + heap_close(att_rel, NoLock); + + Assert(found_constraint); } /* * ALTER TABLE ALTER COLUMN SET NOT NULL + * + * Recursively add not null constraints to the given relation and + * column name. ATExecSetNotNull() also works on existing inheritance + * trees to ensure the inheritance counter for each not null constraint + * is adjusted accordingly. is_new_constraint carries wether the parent has + * created a new constraint on its own. */ static void -ATExecSetNotNull(AlteredTableInfo *tab, Relation rel, - const char *colName, LOCKMODE lockmode) +ATExecSetNotNull(List **wqueue, AlteredTableInfo *tab, Relation rel, + const char *colName, bool recurse, bool recursing, + bool is_new_constraint, LOCKMODE lockmode) { HeapTuple tuple; AttrNumber attnum; Relation attr_rel; + List *children; + ListCell *child; + /* bool is_new_constraint; */ + + if (recursing) + ATSimplePermissions(rel, ATT_TABLE|ATT_FOREIGN_TABLE); + + children = find_inheritance_children(RelationGetRelid(rel), + AccessExclusiveLock); /* * lookup the attribute @@ -4660,26 +5485,244 @@ ATExecSetNotNull(AlteredTableInfo *tab, Relation rel, if (attnum <= 0) ereport(ERROR, (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), - errmsg("cannot alter system column \"%s\"", - colName))); + errmsg("cannot alter system column \"%s\" of relation \"%s\"", + colName, RelationGetRelationName(rel)))); /* - * Okay, actually perform the catalog change ... if needed + * Treat it an error if there's an attempt to add + * a NOT NULL constraint to a table but not to + * existing child tables (thus, ALTER TABLE ONLY + * was specified to a table inherited from others). */ - if (!((Form_pg_attribute) GETSTRUCT(tuple))->attnotnull) + if (!recurse && !recursing) { - ((Form_pg_attribute) GETSTRUCT(tuple))->attnotnull = TRUE; + if (children) + ereport(ERROR, + (errcode(ERRCODE_INVALID_TABLE_DEFINITION), + errmsg("NOT NULL constraint must be added to child tables too"))); + } - simple_heap_update(attr_rel, &tuple->t_self, tuple); + /* + * Actually recursing? If not, check wether this attribute is already + * NOT NULL. If not, exit, since there's nothing to do and we would + * adjust the inheritance counter accidently. + */ + if (!recursing && ((Form_pg_attribute) GETSTRUCT(tuple))->attnotnull) + { + heap_freetuple(tuple); + heap_close(attr_rel, RowExclusiveLock); + return; + } - /* keep the system catalog indexes current */ - CatalogUpdateIndexes(attr_rel, tuple); + /* + * Okay, actually perform the catalog changes ... + * + * Setting attnotnull to TRUE requires to create a + * constraint tuple in pg_constraint as well. We do this + * to record wether this constraint was added locally or + * inherited by some other attribute. We also record the + * attribute number to determine which column this + * constraint belongs to. We need to take care wether + * the constraint was added locally to a child table or + * we're currently already recursing through an inheritance + * tree and wether we already created a new constraint during + * recursion. + */ + ATExecSetNotNullInternal(rel, attr_rel, tuple, + &is_new_constraint, true, !recursing); + + /* Tell Phase 3 it needs to test the constraint */ + tab->new_notnull = true; + + heap_freetuple(tuple); + heap_close(attr_rel, NoLock); + + /* + * Loop through all child relations + */ + foreach(child, children) + { + Oid childrelid = lfirst_oid(child); + Relation childrel; + AlteredTableInfo *childtab; + + attr_rel = heap_open(AttributeRelationId, RowExclusiveLock); + childrel = heap_open(childrelid, NoLock); + CheckTableNotInUse(childrel, "ALTER TABLE"); + + /* get work queue entry for child relation */ + childtab = ATGetQueueEntry(wqueue, childrel); + + /* + * Look for inherited column + */ + tuple = SearchSysCacheCopyAttName(childrelid, colName); + + /* shouldn't happen */ + Assert(HeapTupleIsValid(tuple)); + + /* perform the actual work */ + ATExecSetNotNull(wqueue, childtab, childrel, + colName, false, true, + is_new_constraint, lockmode); + + heap_freetuple(tuple); + heap_close(attr_rel, NoLock); + heap_close(childrel, NoLock); + } +} + +/* + * Internal function to ATExecSetNotNull() + * + * Takes a relation and the pg_attribute relation and tuple of the + * target column to create a NOT NULL constraint. + * The caller is responsible to pass a valid HeapTuple. + */ +static void +ATExecSetNotNullInternal(Relation rel, Relation attr_rel, + HeapTuple atttup, bool *is_new_constraint, + bool allow_new_constraint, bool is_local) +{ + Form_pg_attribute attr; + + attr = (Form_pg_attribute) GETSTRUCT(atttup); + + if (attr->attnotnull) + { + SysScanDesc scan; + ScanKeyData key; + HeapTuple constr_tup; + Relation constr_rel; + bool found; + + found = false; + + /* + * Column has already a NOT NULL constraint attached, + * fetch its pg_constraint tuple and adjust the inheritance + * information according to its pg_attribute tuple. + */ + constr_rel = heap_open(ConstraintRelationId, RowExclusiveLock); + ScanKeyInit(&key, + Anum_pg_constraint_conrelid, + BTEqualStrategyNumber, F_OIDEQ, + ObjectIdGetDatum(RelationGetRelid(rel))); + scan = systable_beginscan(constr_rel, ConstraintRelidIndexId, + true, SnapshotNow, 1, &key); + + while (HeapTupleIsValid(constr_tup = systable_getnext(scan))) + { + Form_pg_constraint constr_struct; + + constr_struct = (Form_pg_constraint) GETSTRUCT(constr_tup); + + /* We need to look for NOT NULL constraint only */ + if (constr_struct->contype != CONSTRAINT_NOTNULL) + continue; + + if (constraint_is_for_single_col(constr_tup, attr->attnum)) + { + found = TRUE; + break; + } + } + + /* there should be a NOT NULL constraint tuple */ + if (found) + { + HeapTuple copy_tuple = heap_copytuple(constr_tup); + Form_pg_constraint constr = (Form_pg_constraint) GETSTRUCT(copy_tuple); + + elog(DEBUG1, "visit constraint %s of relation %d with inhcount %d", + NameStr(constr->conname), constr->conrelid, constr->coninhcount); + + /* + * If we are forced to create a new not null constraint, we + * have is_new_constraint set to true. This means during recursion a + * parent has got a new not null constraint on this column. We + * have found an existing not null constraint now, which means either + * this one was locally created or inherited from another parent. + * + * We must adjust its inhcount only, when a parent has become a new + * constraint on its column. + */ + if (*is_new_constraint) + { + /* actual recursing */ + constr->coninhcount++; + } + else if (is_local) + { + constr->conislocal = true; + } + + /* + * Inform caller that we have found an existing constraint. + */ + *is_new_constraint = FALSE; + + /* + * Apply the changes... + */ + simple_heap_update(constr_rel, ©_tuple->t_self, copy_tuple); + + /* + * Keep system catalog indexes current and make + * changes visible + */ + CatalogUpdateIndexes(constr_rel, copy_tuple); + CommandCounterIncrement(); + + heap_freetuple(copy_tuple); + } + + systable_endscan(scan); + heap_close(constr_rel, NoLock); - /* Tell Phase 3 it needs to test the constraint */ - tab->new_notnull = true; } + else if (!attr->attnotnull && allow_new_constraint) + { + CookedConstraint *cooked; + + /* + * New NOT NULL constraint + */ + cooked = palloc(sizeof(CookedConstraint)); + cooked->contype = CONSTR_NOTNULL; + cooked->expr = NULL; + cooked->name = ChooseConstraintName(RelationGetRelationName(rel), + NameStr(attr->attname), + "not_null", RelationGetNamespace(rel), + NIL); + cooked->attnum = attr->attnum; + cooked->is_local = is_local; + cooked->inhcount = is_local ? 0 : 1; + + StoreColumnNotNullConstraint(rel, cooked); + + /* + * Adjust inherited attnotnull attribute accordingly. + * XXX: Unfortunately, we need a CCI here, since the + * attribute might be already updated before. Room + * for further optimization... + */ + CommandCounterIncrement(); - heap_close(attr_rel, RowExclusiveLock); + attr->attnotnull = TRUE; + simple_heap_update(attr_rel, &atttup->t_self, atttup); + + /* keep the system catalog indexes current */ + CatalogUpdateIndexes(attr_rel, atttup); + + /* + * Inform caller that we have created a new constraint + */ + *is_new_constraint = TRUE; + + /* Make changes visible */ + CommandCounterIncrement(); + } } /* @@ -6420,6 +7463,10 @@ ATExecDropConstraint(Relation rel, const char *constrName, con = (Form_pg_constraint) GETSTRUCT(tuple); + /* NOT NULL is handled by ALTER TABLE ... DROP/SET NOT NULL */ + if (con->contype == CONSTRAINT_NOTNULL) + continue; + if (strcmp(NameStr(con->conname), constrName) != 0) continue; @@ -6430,7 +7477,9 @@ ATExecDropConstraint(Relation rel, const char *constrName, errmsg("cannot drop inherited constraint \"%s\" of relation \"%s\"", constrName, RelationGetRelationName(rel)))); - /* Right now only CHECK constraints can be inherited */ + /* This is a CHECK constraint, remember that we found one + * and proceed to drop it + */ if (con->contype == CONSTRAINT_CHECK) is_check_constraint = true; @@ -6501,7 +7550,10 @@ ATExecDropConstraint(Relation rel, const char *constrName, con = (Form_pg_constraint) GETSTRUCT(tuple); - /* Right now only CHECK constraints can be inherited */ + /* Besides CHECK constraint we support inheritance of + * NOT NULL constraints, too. Ignore them here, since they + * are handled by ALTER TABLE...DROP/SET NOT NULL + */ if (con->contype != CONSTRAINT_CHECK) continue; @@ -7292,6 +8344,7 @@ ATPostAlterTypeParse(char *cmd, List **wqueue, LOCKMODE lockmode) tab->subcmds[AT_PASS_OLD_INDEX] = lappend(tab->subcmds[AT_PASS_OLD_INDEX], cmd); break; + case AT_SetNotNull: case AT_AddConstraint: tab->subcmds[AT_PASS_OLD_CONSTR] = lappend(tab->subcmds[AT_PASS_OLD_CONSTR], cmd); @@ -8057,6 +9110,7 @@ ATExecAddInherit(Relation child_rel, RangeVar *parent, LOCKMODE lockmode) HeapTuple inheritsTuple; int32 inhseqno; List *children; + List *child_attnums; /* * A self-exclusive lock is needed here. See the similar case in @@ -8144,10 +9198,11 @@ ATExecAddInherit(Relation child_rel, RangeVar *parent, LOCKMODE lockmode) RelationGetRelationName(parent_rel)))); /* Match up the columns and bump attinhcount as needed */ - MergeAttributesIntoExisting(child_rel, parent_rel); + child_attnums = NIL; + MergeAttributesIntoExisting(child_rel, parent_rel, &child_attnums); /* Match up the constraints and bump coninhcount as needed */ - MergeConstraintsIntoExisting(child_rel, parent_rel); + MergeConstraintsIntoExisting(child_rel, parent_rel, child_attnums); /* * OK, it looks valid. Make the catalog entries that show inheritance. @@ -8223,7 +9278,8 @@ constraints_equivalent(HeapTuple a, HeapTuple b, TupleDesc tupleDesc) * the child must be as well. Defaults are not compared, however. */ static void -MergeAttributesIntoExisting(Relation child_rel, Relation parent_rel) +MergeAttributesIntoExisting(Relation child_rel, Relation parent_rel, + List **attnums_list) { Relation attrrel; AttrNumber parent_attno; @@ -8279,6 +9335,11 @@ MergeAttributesIntoExisting(Relation child_rel, Relation parent_rel) attributeName))); /* + * Record the child's inherited attnum in attnums_list. + */ + *attnums_list = lappend_int(*attnums_list, childatt->attnum); + + /* * OK, bump the child column's inheritance count. (If we fail * later on, this change will just roll back.) */ @@ -8317,7 +9378,8 @@ MergeAttributesIntoExisting(Relation child_rel, Relation parent_rel) * a problem though. Even 100 constraints ought not be the end of the world. */ static void -MergeConstraintsIntoExisting(Relation child_rel, Relation parent_rel) +MergeConstraintsIntoExisting(Relation child_rel, Relation parent_rel, + List *child_attnums) { Relation catalog_relation; TupleDesc tuple_desc; @@ -8344,7 +9406,8 @@ MergeConstraintsIntoExisting(Relation child_rel, Relation parent_rel) HeapTuple child_tuple; bool found = false; - if (parent_con->contype != CONSTRAINT_CHECK) + if (parent_con->contype != CONSTRAINT_CHECK + && parent_con->contype != CONSTRAINT_NOTNULL) continue; /* Search for a child constraint matching this one */ @@ -8360,19 +9423,47 @@ MergeConstraintsIntoExisting(Relation child_rel, Relation parent_rel) Form_pg_constraint child_con = (Form_pg_constraint) GETSTRUCT(child_tuple); HeapTuple child_copy; - if (child_con->contype != CONSTRAINT_CHECK) - continue; - - if (strcmp(NameStr(parent_con->conname), - NameStr(child_con->conname)) != 0) - continue; - - if (!constraints_equivalent(parent_tuple, child_tuple, tuple_desc)) - ereport(ERROR, - (errcode(ERRCODE_DATATYPE_MISMATCH), - errmsg("child table \"%s\" has different definition for check constraint \"%s\"", - RelationGetRelationName(child_rel), - NameStr(parent_con->conname)))); + switch(child_con->contype) + { + case CONSTRAINT_CHECK: + { + if (strcmp(NameStr(parent_con->conname), + NameStr(child_con->conname)) != 0) + continue; + + if (!constraints_equivalent(parent_tuple, child_tuple, tuple_desc)) + ereport(ERROR, + (errcode(ERRCODE_DATATYPE_MISMATCH), + errmsg("child table \"%s\" has different definition for check constraint \"%s\"", + RelationGetRelationName(child_rel), + NameStr(parent_con->conname)))); + break; + } + case CONSTRAINT_NOTNULL: + { + Datum arrayp; + Datum *vals; + int nelems; + bool isnull; + + if (child_attnums == NIL) + continue; + + arrayp = SysCacheGetAttr(CONSTROID, child_tuple, + Anum_pg_constraint_conkey, &isnull); + /* should not happen */ + Assert(!isnull); + deconstruct_array(DatumGetArrayTypeP(arrayp), INT2OID, 2, true, + 's', &vals, NULL, &nelems); + Assert(nelems > 0); + + if (!list_member_int(child_attnums, DatumGetInt16(vals[0]))) + continue; + break; + } + default: + continue; + } /* * OK, bump the child constraint's inheritance count. (If we fail @@ -8415,8 +9506,8 @@ MergeConstraintsIntoExisting(Relation child_rel, Relation parent_rel) * surprise. But at least we'll never surprise by dropping columns someone * isn't expecting to be dropped which would actually mean data loss. * - * coninhcount and conislocal for inherited constraints are adjusted in - * exactly the same way. + * coninhcount and conislocal for inherited CHECK and NOT NULL constraints + * are adjusted in exactly the same way. */ static void ATExecDropInherit(Relation rel, RangeVar *parent, LOCKMODE lockmode) @@ -8427,8 +9518,8 @@ ATExecDropInherit(Relation rel, RangeVar *parent, LOCKMODE lockmode) ScanKeyData key[3]; HeapTuple inheritsTuple, attributeTuple, - constraintTuple; - List *connames; + constraintTuple; + List *constraints; bool found = false; /* @@ -8478,6 +9569,8 @@ ATExecDropInherit(Relation rel, RangeVar *parent, LOCKMODE lockmode) RelationGetRelationName(parent_rel), RelationGetRelationName(rel)))); + constraints = NIL; + /* * Search through child columns looking for ones matching parent rel */ @@ -8491,6 +9584,7 @@ ATExecDropInherit(Relation rel, RangeVar *parent, LOCKMODE lockmode) while (HeapTupleIsValid(attributeTuple = systable_getnext(scan))) { Form_pg_attribute att = (Form_pg_attribute) GETSTRUCT(attributeTuple); + HeapTuple parentattr_tuple; /* Ignore if dropped or not inherited */ if (att->attisdropped) @@ -8498,30 +9592,51 @@ ATExecDropInherit(Relation rel, RangeVar *parent, LOCKMODE lockmode) if (att->attinhcount <= 0) continue; - if (SearchSysCacheExistsAttName(RelationGetRelid(parent_rel), - NameStr(att->attname))) + parentattr_tuple = SearchSysCacheCopyAttName(RelationGetRelid(parent_rel), + NameStr(att->attname)); + + if (HeapTupleIsValid(parentattr_tuple)) { /* Decrement inhcount and possibly set islocal to true */ HeapTuple copyTuple = heap_copytuple(attributeTuple); Form_pg_attribute copy_att = (Form_pg_attribute) GETSTRUCT(copyTuple); + Form_pg_attribute parent_att = (Form_pg_attribute) GETSTRUCT(parentattr_tuple); copy_att->attinhcount--; if (copy_att->attinhcount == 0) copy_att->attislocal = true; + /* + * If attnotnull is set, record the inherited + * not null constraint. We save its attnum to deinherit + * its representation in pg_constraint below, but only when + * its ancestor has a NOT NULL constraint, too. + */ + if (copy_att->attnotnull + && parent_att->attnotnull) + { + DeinheritConstraintInfo *inhInfo + = (DeinheritConstraintInfo *)palloc(sizeof(DeinheritConstraintInfo)); + inhInfo->contype = CONSTRAINT_NOTNULL; + inhInfo->conname = NULL; + inhInfo->attnum = copy_att->attnum; + constraints = lappend(constraints, inhInfo); + } + simple_heap_update(catalogRelation, ©Tuple->t_self, copyTuple); CatalogUpdateIndexes(catalogRelation, copyTuple); heap_freetuple(copyTuple); + heap_freetuple(parentattr_tuple); } } systable_endscan(scan); heap_close(catalogRelation, RowExclusiveLock); /* - * Likewise, find inherited check constraints and disinherit them. To do - * this, we first need a list of the names of the parent's check - * constraints. (We cheat a bit by only checking for name matches, - * assuming that the expressions will match.) + * Likewise, find inherited check and NOT NULL constraints and disinherit + * them. To do this, we first need a list of the names of the parent's check + * and NOT NULL constraints. (For check constraints, we cheat a bit by + * only checking for name matches, assuming that the expressions will match.) */ catalogRelation = heap_open(ConstraintRelationId, RowExclusiveLock); ScanKeyInit(&key[0], @@ -8531,14 +9646,19 @@ ATExecDropInherit(Relation rel, RangeVar *parent, LOCKMODE lockmode) scan = systable_beginscan(catalogRelation, ConstraintRelidIndexId, true, SnapshotNow, 1, key); - connames = NIL; - while (HeapTupleIsValid(constraintTuple = systable_getnext(scan))) { Form_pg_constraint con = (Form_pg_constraint) GETSTRUCT(constraintTuple); if (con->contype == CONSTRAINT_CHECK) - connames = lappend(connames, pstrdup(NameStr(con->conname))); + { + DeinheritConstraintInfo *inhInfo + = (DeinheritConstraintInfo *)palloc(sizeof(DeinheritConstraintInfo)); + inhInfo->contype = CONSTRAINT_CHECK; + inhInfo->conname = pstrdup(NameStr(con->conname)); + inhInfo->attnum = 0; + constraints = lappend(constraints, inhInfo); + } } systable_endscan(scan); @@ -8557,13 +9677,33 @@ ATExecDropInherit(Relation rel, RangeVar *parent, LOCKMODE lockmode) bool match; ListCell *lc; - if (con->contype != CONSTRAINT_CHECK) + if (con->contype != CONSTRAINT_CHECK + && con->contype != CONSTRAINT_NOTNULL) continue; + /* + * We scan through all constraints of the current child relation, + * checking for CONSTRAINT_CHECK and CONSTRAINT_NOTNULL occurrences. + * + * CHECK constraints are assumed to be named equally to in the + * relation's ancestor, while NOT NULL constraints always are attached + * to a specific column. Check for attnum and adjust the inheritance + * information accordingly. + */ + match = false; - foreach(lc, connames) + foreach(lc, constraints) { - if (strcmp(NameStr(con->conname), (char *) lfirst(lc)) == 0) + DeinheritConstraintInfo *inhInfo = (DeinheritConstraintInfo *) lfirst(lc); + + if ((inhInfo->contype == CONSTRAINT_CHECK) + && (strcmp(NameStr(con->conname), inhInfo->conname) == 0)) + { + match = true; + break; + } + else if ((inhInfo->contype == CONSTRAINT_NOTNULL) + && (constraint_is_for_single_col(constraintTuple, inhInfo->attnum))) { match = true; break; @@ -8580,6 +9720,9 @@ ATExecDropInherit(Relation rel, RangeVar *parent, LOCKMODE lockmode) elog(ERROR, "relation %u has non-inherited constraint \"%s\"", RelationGetRelid(rel), NameStr(copy_con->conname)); + elog(DEBUG1, "deinherit not null constraint \"%s\", relation \"%s\"", + NameStr(copy_con->conname), RelationGetRelationName(rel)); + copy_con->coninhcount--; if (copy_con->coninhcount == 0) copy_con->conislocal = true; diff --git a/src/backend/commands/typecmds.c b/src/backend/commands/typecmds.c index 66c11de672..c680553e6b 100644 --- a/src/backend/commands/typecmds.c +++ b/src/backend/commands/typecmds.c @@ -93,7 +93,11 @@ static char *domainAddConstraint(Oid domainOid, Oid domainNamespace, Oid baseTypeOid, int typMod, Constraint *constr, char *domainName); - +static void domainAddNotNull(Oid domainOid, Oid domainNamespace, + Constraint *constr, char *domainName); +static void +addOrRemoveDomainNotNull(Oid domainOid, Oid domainNamespace, + char * domainName, bool is_new); /* * DefineType @@ -779,6 +783,7 @@ DefineDomain(CreateDomainStmt *stmt) Form_pg_type baseType; int32 basetypeMod; Oid baseColl; + bool saw_notnull = false; /* Convert list of names to a name and namespace */ domainNamespace = QualifiedNameGetCreationNamespace(stmt->domainname, @@ -971,8 +976,22 @@ DefineDomain(CreateDomainStmt *stmt) ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR), errmsg("conflicting NULL/NOT NULL constraints"))); + + if (saw_notnull) + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("multiple definition for NOT NULL constraint"))); + + /* + * We are going to create a pg_constraint entry for this + * constraint to record an explicit named NOT NULL constraint. + * We do this even in case it wasn't named explicitly. Since + * we need the domains' OID, this is done later. + */ + typNotNull = true; nullDefined = true; + saw_notnull = true; break; case CONSTR_NULL: @@ -1078,10 +1097,14 @@ DefineDomain(CreateDomainStmt *stmt) { Constraint *constr = lfirst(listptr); - /* it must be a Constraint, per check above */ + /* it must be a CHECK or NOT NULL Constraint, per check above */ switch (constr->contype) { + case CONSTR_NOTNULL: + domainAddNotNull(domainoid, domainNamespace, + constr, domainName); + break; case CONSTR_CHECK: domainAddConstraint(domainoid, domainNamespace, basetypeoid, basetypeMod, @@ -1854,12 +1877,99 @@ AlterDomainNotNull(List *names, bool notNull) CatalogUpdateIndexes(typrel, tup); + /* + * Depending on wether we are setting the domain to NOT NULL or NULL, we + * need to update pg_constraint as well. + */ + addOrRemoveDomainNotNull(domainoid, typTup->typnamespace, + NameStr(typTup->typname), typTup->typnotnull); + /* Clean up */ heap_freetuple(tup); heap_close(typrel, RowExclusiveLock); } /* + * Internal magic for pg_constraint and ALTER DOMAIN ... SET|DROP NOT NULL. + */ +static void +addOrRemoveDomainNotNull(Oid domainOid, Oid domainNamespace, + char * domainName, bool is_new) +{ + if (is_new) + { + /* + * Caller wants a new CONSTRAINT_NOTNULL tuple + * for the given domain OID. + */ + Constraint *constr; + constr = (Constraint *) palloc(sizeof(Constraint)); + + /* + * ALTER DOMAIN ... SET NOT NULL doesn't have an infrastructure + * to specify a name for the new constraint, so always generate + * a new one. + */ + constr->conname = NULL; + constr->contype = CONSTRAINT_NOTNULL; + + /* + * Finally do the creation. + */ + domainAddNotNull(domainOid, domainNamespace, + constr, domainName); + } + else + { + /* Existing constraint tuple should be dropped */ + + Relation conrel; + HeapTuple contup; + SysScanDesc conscan; + ScanKeyData key[1]; + + /* Grab an appropriate lock on the pg_constraint relation */ + conrel = heap_open(ConstraintRelationId, RowExclusiveLock); + + /* Use the index to scan only constraints of the target relation */ + ScanKeyInit(&key[0], + Anum_pg_constraint_contypid, + BTEqualStrategyNumber, F_OIDEQ, + ObjectIdGetDatum(domainOid)); + + conscan = systable_beginscan(conrel, ConstraintTypidIndexId, true, + SnapshotNow, 1, key); + + while ((contup = systable_getnext(conscan)) != NULL) + { + Form_pg_constraint con = (Form_pg_constraint) GETSTRUCT(contup); + + /* + * Perform deletion over pg_depend + */ + if (con->contype == CONSTRAINT_NOTNULL) + { + ObjectAddress conobj; + + conobj.classId = ConstraintRelationId; + conobj.objectId = HeapTupleGetOid(contup); + conobj.objectSubId = 0; + + performDeletion(&conobj, DEPENDENCY_AUTO); + + /* + * Only one tuple expected, break. + */ + break; + } + } + + systable_endscan(conscan); + heap_close(conrel, NoLock); + } +} + +/* * AlterDomainDropConstraint * * Implements the ALTER DOMAIN DROP CONSTRAINT statement @@ -2293,6 +2403,67 @@ checkDomainOwner(HeapTuple tup) } /* + * domainAddNotNull - add a NOT NULL constraint to + * pg_constraint. + */ +void +domainAddNotNull(Oid domainOid, Oid domainNamespace, + Constraint *constr, char *domainName) +{ + /* + * Validate constraint name or assign a new one + * if not specified already. + */ + if (constr->conname) + { + if (ConstraintNameIsUsed(CONSTRAINT_DOMAIN, + domainOid, + domainNamespace, + constr->conname)) + ereport(ERROR, + (errcode(ERRCODE_DUPLICATE_OBJECT), + errmsg("not null constraint \"%s\" for domain \"%s\" already exists", + constr->conname, domainName))); + } + else + constr->conname = ChooseConstraintName(domainName, + NULL, + "not_null", + domainNamespace, + NIL); + + /* + * Store the not null constraint in pg_constraint + */ + CreateConstraintEntry(constr->conname, /* Constraint Name */ + domainNamespace, /* namespace */ + CONSTRAINT_NOTNULL, /* Constraint Type */ + false, /* Is Deferrable */ + false, /* Is Deferred */ + true, /* Is Validated */ + InvalidOid, /* not a relation constraint */ + NULL, + 0, + domainOid, /* domain constraint */ + InvalidOid, /* no associated index */ + InvalidOid, /* Foreign key fields */ + NULL, + NULL, + NULL, + NULL, + 0, + ' ', + ' ', + ' ', + NULL, /* not an exclusion constraint */ + NULL, /* Tree form of check constraint */ + NULL, /* Binary form of check constraint */ + NULL, /* Source form of check constraint */ + true, /* is local */ + 0); /* inhcount */ +} + +/* * domainAddConstraint - code shared between CREATE and ALTER DOMAIN */ static char * diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c index 485bf4b8e7..a6a626f576 100644 --- a/src/backend/parser/parse_utilcmd.c +++ b/src/backend/parser/parse_utilcmd.c @@ -78,6 +78,7 @@ typedef struct List *ckconstraints; /* CHECK constraints */ List *fkconstraints; /* FOREIGN KEY constraints */ List *ixconstraints; /* index-creating constraints */ + List *nnconstraints; /* NOT NULL column constraints */ List *inh_indexes; /* cloned indexes from INCLUDING INDEXES */ List *blist; /* "before list" of things to do before * creating the table */ @@ -211,6 +212,7 @@ transformCreateStmt(CreateStmt *stmt, const char *queryString) cxt.ckconstraints = NIL; cxt.fkconstraints = NIL; cxt.ixconstraints = NIL; + cxt.nnconstraints = NIL; cxt.inh_indexes = NIL; cxt.blist = NIL; cxt.alist = NIL; @@ -493,6 +495,8 @@ transformColumnDefinition(CreateStmtContext *cxt, ColumnDef *column) parser_errposition(cxt->pstate, constraint->location))); column->is_not_null = TRUE; + + cxt->nnconstraints = lappend(cxt->nnconstraints, constraint); saw_nullable = true; break; @@ -2268,6 +2272,7 @@ transformAlterTableStmt(AlterTableStmt *stmt, const char *queryString) cxt.ckconstraints = NIL; cxt.fkconstraints = NIL; cxt.ixconstraints = NIL; + cxt.nnconstraints = NIL; cxt.inh_indexes = NIL; cxt.blist = NIL; cxt.alist = NIL; @@ -2288,6 +2293,8 @@ transformAlterTableStmt(AlterTableStmt *stmt, const char *queryString) case AT_AddColumnToView: { ColumnDef *def = (ColumnDef *) cmd->def; + List *nnconstr; + ListCell *constrptr; Assert(IsA(def, ColumnDef)); transformColumnDefinition(&cxt, def); @@ -2300,11 +2307,26 @@ transformAlterTableStmt(AlterTableStmt *stmt, const char *queryString) skipValidation = false; /* - * All constraints are processed in other ways. Remove the - * original list + * All constraints except CONSTR_NOTNULL nodes + * are processed in other ways. Remove the + * original list, but keep all NOT NULL constraints. + * We need the additional information to ease the + * creation of corresponding pg_constraint tuple in + * ATExecAddColumn(). */ - def->constraints = NIL; + nnconstr = NIL; + + foreach(constrptr, def->constraints) + { + Constraint *constraint = (Constraint *) lfirst(constrptr); + + if (constraint->contype != CONSTR_NOTNULL) + continue; + + nnconstr = lappend(nnconstr, constraint); + } + def->constraints = nnconstr; newcmds = lappend(newcmds, cmd); break; } diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c index 06cf6fa4f7..cb3f2d8b46 100644 --- a/src/backend/utils/adt/ruleutils.c +++ b/src/backend/utils/adt/ruleutils.c @@ -1106,13 +1106,51 @@ pg_get_constraintdef_worker(Oid constraintId, bool fullCommand, if (fullCommand && OidIsValid(conForm->conrelid)) { - appendStringInfo(&buf, "ALTER TABLE ONLY %s ADD CONSTRAINT %s ", - generate_relation_name(conForm->conrelid, NIL), - quote_identifier(NameStr(conForm->conname))); + /* XXX: TODO, not sure how to handle this right now? */ + if (conForm->contype == CONSTRAINT_NOTNULL) + { + appendStringInfo(&buf, "ALTER TABLE ONLY %s ALTER COLUMN ", + generate_relation_name(conForm->conrelid, NIL)); + } + else + { + appendStringInfo(&buf, "ALTER TABLE ONLY %s ADD CONSTRAINT %s ", + generate_relation_name(conForm->conrelid, NIL), + quote_identifier(NameStr(conForm->conname))); + } } switch (conForm->contype) { + case CONSTRAINT_NOTNULL: + { + Datum val; + bool isnull; + + /* XXX: TODO, not sure how to handle this right now? */ + + /* Fetch referenced column OID */ + val = SysCacheGetAttr(CONSTROID, tup, + Anum_pg_constraint_conkey, &isnull); + + if (conForm->conrelid != InvalidOid) + { + /* Should not happen */ + if (isnull) + elog(ERROR, "null conkey for constraint %u", + constraintId); + + /* relation constraint */ + decompile_column_index_array(val, conForm->conrelid, &buf); + appendStringInfo(&buf, " SET NOT NULL"); + } + else + { + /* Domain constraint */ + } + + break; + } case CONSTRAINT_FOREIGN: { Datum val; diff --git a/src/include/catalog/heap.h b/src/include/catalog/heap.h index c95e91303b..456587b653 100644 --- a/src/include/catalog/heap.h +++ b/src/include/catalog/heap.h @@ -26,7 +26,7 @@ typedef struct RawColumnDefault typedef struct CookedConstraint { - ConstrType contype; /* CONSTR_DEFAULT or CONSTR_CHECK */ + ConstrType contype; /* CONSTR_DEFAULT, CONSTR_CHECK or CONSTR_NOTNULL */ char *name; /* name, or NULL if none */ AttrNumber attnum; /* which attr (only for DEFAULT) */ Node *expr; /* transformed default or check expr */ @@ -92,7 +92,7 @@ extern List *AddRelationNewConstraints(Relation rel, bool is_local); extern void StoreAttrDefault(Relation rel, AttrNumber attnum, Node *expr); - +extern void StoreColumnNotNullConstraint(Relation rel, CookedConstraint *cooked); extern Node *cookDefault(ParseState *pstate, Node *raw_default, Oid atttypid, diff --git a/src/include/catalog/pg_constraint.h b/src/include/catalog/pg_constraint.h index 1566af2973..3088480437 100644 --- a/src/include/catalog/pg_constraint.h +++ b/src/include/catalog/pg_constraint.h @@ -177,6 +177,7 @@ typedef FormData_pg_constraint *Form_pg_constraint; /* Valid values for contype */ #define CONSTRAINT_CHECK 'c' +#define CONSTRAINT_NOTNULL 'n' #define CONSTRAINT_FOREIGN 'f' #define CONSTRAINT_PRIMARY 'p' #define CONSTRAINT_UNIQUE 'u' diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h index 14937d4363..cffe47930f 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -1178,7 +1178,9 @@ typedef enum AlterTableType AT_AddColumnToView, /* implicitly via CREATE OR REPLACE VIEW */ AT_ColumnDefault, /* alter column default */ AT_DropNotNull, /* alter column drop not null */ + AT_DropNotNullRecurse, /* internal to commands/tablecmds.c */ AT_SetNotNull, /* alter column set not null */ + AT_SetNotNullRecurse, /* internal to commands/tablecmds.c */ AT_SetStatistics, /* alter column set statistics */ AT_SetOptions, /* alter column set ( options ) */ AT_ResetOptions, /* alter column reset ( options ) */ diff --git a/src/test/regress/expected/alter_table.out b/src/test/regress/expected/alter_table.out index f84da45de6..8f11b238f7 100644 --- a/src/test/regress/expected/alter_table.out +++ b/src/test/regress/expected/alter_table.out @@ -519,7 +519,7 @@ create table atacc1 ( test int ); insert into atacc1 (test) values (NULL); -- add a primary key (fails) alter table atacc1 add constraint atacc_test1 primary key (test); -ERROR: column "test" contains null values +ERROR: column "test" of relation "atacc1" contains null values insert into atacc1 (test) values (3); drop table atacc1; -- let's do one where the primary key constraint fails @@ -536,7 +536,7 @@ insert into atacc1 (test) values (0); -- add a primary key column without a default (fails). alter table atacc1 add column test2 int primary key; NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "atacc1_pkey" for table "atacc1" -ERROR: column "test2" contains null values +ERROR: column "test2" of relation "atacc1" contains null values -- now add a primary key column with a default (succeeds). alter table atacc1 add column test2 int default 0 primary key; NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "atacc1_pkey" for table "atacc1" @@ -599,7 +599,7 @@ alter table atacc1 drop constraint "atacc1_pkey"; alter table atacc1 alter column test drop not null; insert into atacc1 values (null); alter table atacc1 alter test set not null; -ERROR: column "test" contains null values +ERROR: column "test" of relation "atacc1" contains null values delete from atacc1; alter table atacc1 alter test set not null; -- try altering a non-existent column, should fail @@ -609,9 +609,9 @@ alter table atacc1 alter bar drop not null; ERROR: column "bar" of relation "atacc1" does not exist -- try altering the oid column, should fail alter table atacc1 alter oid set not null; -ERROR: cannot alter system column "oid" +ERROR: cannot alter system column "oid" of relation "atacc1" alter table atacc1 alter oid drop not null; -ERROR: cannot alter system column "oid" +ERROR: cannot alter system column "oid" of relation "atacc1" -- try creating a view and altering that, should fail create view myview as select * from atacc1; alter table myview alter column test drop not null; @@ -632,13 +632,13 @@ alter table parent alter a drop not null; insert into parent values (NULL); insert into child (a, b) values (NULL, 'foo'); alter table only parent alter a set not null; -ERROR: column "a" contains null values +ERROR: NOT NULL constraint must be added to child tables too alter table child alter a set not null; -ERROR: column "a" contains null values +ERROR: column "a" of relation "child" contains null values delete from parent; alter table only parent alter a set not null; +ERROR: NOT NULL constraint must be added to child tables too insert into parent values (NULL); -ERROR: null value in column "a" violates not-null constraint alter table child alter a set not null; insert into child (a, b) values (NULL, 'foo'); ERROR: null value in column "a" violates not-null constraint diff --git a/src/test/regress/expected/cluster.out b/src/test/regress/expected/cluster.out index 979057d26f..1273d01657 100644 --- a/src/test/regress/expected/cluster.out +++ b/src/test/regress/expected/cluster.out @@ -251,11 +251,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/domain.out b/src/test/regress/expected/domain.out index 2586b07241..7484d65b32 100644 --- a/src/test/regress/expected/domain.out +++ b/src/test/regress/expected/domain.out @@ -305,6 +305,23 @@ drop domain dnotnulltest cascade; NOTICE: drop cascades to 2 other objects DETAIL: drop cascades to table domnotnull column col1 drop cascades to table domnotnull column col2 +create domain dnotnulltest integer constraint dnn not null; +select conname, contype, contypid::regtype from pg_constraint c + where contype = 'n' and contypid <> 0 order by 1; + conname | contype | contypid +----------------+---------+-------------- + ddef5_not_null | n | ddef5 + dnn | n | dnotnulltest +(2 rows) + +drop domain dnotnulltest; +select conname, contype, contypid::regtype from pg_constraint + where contype = 'n' and contypid <> 0 order by 1; + conname | contype | contypid +----------------+---------+---------- + ddef5_not_null | n | ddef5 +(1 row) + -- Test ALTER DOMAIN .. DEFAULT .. create table domdeftest (col1 ddef1); insert into domdeftest default values; diff --git a/src/test/regress/expected/inherit.out b/src/test/regress/expected/inherit.out index d59ca449dc..621346f0aa 100644 --- a/src/test/regress/expected/inherit.out +++ b/src/test/regress/expected/inherit.out @@ -1242,3 +1242,306 @@ NOTICE: drop cascades to 3 other objects DETAIL: drop cascades to table matest1 drop cascades to table matest2 drop cascades to table matest3 +-- +-- 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 | Modifiers +--------+---------+----------- + 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 | Modifiers +--------+------------------+----------- + 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 | Modifiers +--------+---------+----------- + f1 | integer | + f2 | text | + f3 | integer | + a2 | integer | not null +Inherits: pp1 +Number of child tables: 1 (Use \d+ to list them.) + +\d cc2 + Table "public.cc2" + Column | Type | Modifiers +--------+------------------+----------- + f1 | integer | + f2 | text | + f3 | integer | + f4 | double precision | + a2 | integer | not null +Inherits: pp1, + cc1 + +alter table pp1 alter column f1 set not null; +\d pp1 + Table "public.pp1" + Column | Type | Modifiers +--------+---------+----------- + f1 | integer | not null +Number of child tables: 2 (Use \d+ to list them.) + +\d cc1 + Table "public.cc1" + Column | Type | Modifiers +--------+---------+----------- + f1 | integer | not null + f2 | text | + f3 | integer | + a2 | integer | not null +Inherits: pp1 +Number of child tables: 1 (Use \d+ to list them.) + +\d cc2 + Table "public.cc2" + Column | Type | Modifiers +--------+------------------+----------- + f1 | integer | not null + f2 | text | + f3 | integer | + f4 | double precision | + a2 | integer | not null +Inherits: pp1, + cc1 + +-- have a look at pg_constraint +select conrelid::regclass, conname, contype, coninhcount, conislocal from pg_constraint where contype = 'n' order by 2; + conrelid | conname | contype | coninhcount | conislocal +----------+------------------+---------+-------------+------------ + cc1 | cc1_f1_not_null | n | 1 | f + cc2 | cc2_f1_not_null | n | 2 | f + inhx | inhx_xx_not_null | n | 0 | t + cc1 | nn | n | 0 | t + cc2 | nn | n | 1 | f + pp1 | pp1_f1_not_null | n | 0 | t +(6 rows) + +-- remove constraint from cc2, should fail +alter table cc2 alter column a2 drop not null; +ERROR: cannot drop inherited NOT NULL constraint "nn", relation "cc2" +-- 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 = 'n' order by 2; + conrelid | conname | contype | coninhcount | conislocal +----------+------------------+---------+-------------+------------ + cc1 | cc1_f1_not_null | n | 1 | f + cc2 | cc2_f1_not_null | n | 2 | f + inhx | inhx_xx_not_null | n | 0 | t + pp1 | pp1_f1_not_null | n | 0 | t +(4 rows) + +-- same for cc2 +alter table cc2 alter column f1 drop not null; +ERROR: cannot drop inherited NOT NULL constraint "cc2_f1_not_null", relation "cc2" +-- remove from cc1, should fail again +alter table cc1 alter column f1 drop not null; +ERROR: cannot drop inherited NOT NULL constraint "cc1_f1_not_null", relation "cc1" +-- 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 = 'n' order by 2; + conrelid | conname | contype | coninhcount | conislocal +----------+------------------+---------+-------------+------------ + inhx | inhx_xx_not_null | n | 0 | t +(1 row) + +drop table pp1 cascade; +NOTICE: drop cascades to 2 other objects +DETAIL: drop cascades to table cc1 +drop cascades to table cc2 +-- have a look at pg_constraint, everything should be clean now +select conrelid::regclass, conname, contype, coninhcount, conislocal from pg_constraint where contype = 'n' order by 2; + conrelid | conname | contype | coninhcount | conislocal +----------+------------------+---------+-------------+------------ + inhx | inhx_xx_not_null | n | 0 | t +(1 row) + +-- +-- 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 | Modifiers +--------+---------+----------- + f1 | integer | not null +Number of child tables: 1 (Use \d+ to list them.) + +\d child1 + Table "public.child1" + Column | Type | Modifiers +--------+---------+----------- + f1 | integer | not null +Inherits: parent +Number of child tables: 1 (Use \d+ to list them.) + +\d child2 + Table "public.child2" + Column | Type | Modifiers +--------+---------+----------- + f1 | integer | not null +Inherits: child1 + +select conrelid::regclass, conname, contype, coninhcount, conislocal from pg_constraint where contype = 'n' order by 2; + conrelid | conname | contype | coninhcount | conislocal +----------+--------------------+---------+-------------+------------ + child1 | child1_f1_not_null | n | 1 | t + child2 | child2_f1_not_null | n | 1 | t + inhx | inhx_xx_not_null | n | 0 | t + parent | parent_f1_not_null | n | 0 | t +(4 rows) + +-- +-- test deinherit procedure +-- +-- deinherit child1 +alter table child1 no inherit parent; +\d parent + Table "public.parent" + Column | Type | Modifiers +--------+---------+----------- + f1 | integer | not null + +\d child1 + Table "public.child1" + Column | Type | Modifiers +--------+---------+----------- + f1 | integer | not null +Number of child tables: 1 (Use \d+ to list them.) + +\d child2 + Table "public.child2" + Column | Type | Modifiers +--------+---------+----------- + f1 | integer | not null +Inherits: child1 + +select conrelid::regclass, conname, contype, coninhcount, conislocal from pg_constraint where contype = 'n' order by 2; + conrelid | conname | contype | coninhcount | conislocal +----------+--------------------+---------+-------------+------------ + child1 | child1_f1_not_null | n | 0 | t + child2 | child2_f1_not_null | n | 1 | t + inhx | inhx_xx_not_null | n | 0 | t + parent | parent_f1_not_null | n | 0 | t +(4 rows) + +-- test inhcount of child2, should fail +alter table child2 alter f1 drop not null; +ERROR: cannot drop inherited NOT NULL constraint "child2_f1_not_null", relation "child2" +-- 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 = 'n' order by 2; + conrelid | conname | contype | coninhcount | conislocal +----------+--------------------+---------+-------------+------------ + inhx | inhx_xx_not_null | n | 0 | t + parent | parent_f1_not_null | n | 0 | t + c1 | parent_f1_not_null | n | 1 | f + c2 | parent_f1_not_null | n | 1 | f + d1 | parent_f1_not_null | n | 2 | f +(5 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 parent1(f1 int); +create table parent2(f2 text); +create table child(f1 int not null, f2 text not null) inherits(parent1, parent2); +NOTICE: merging column "f1" with inherited definition +NOTICE: merging column "f2" with inherited definition +-- show constraint info +select conrelid::regclass, conname, contype, coninhcount, conislocal, conkey from pg_constraint where contype = 'n' order by 2; + conrelid | conname | contype | coninhcount | conislocal | conkey +----------+-------------------+---------+-------------+------------+-------- + child | child_f1_not_null | n | 0 | t | {1} + child | child_f2_not_null | n | 0 | t | {2} + inhx | inhx_xx_not_null | n | 0 | t | {1} +(3 rows) + +-- also drops child table +drop table parent1 cascade; +NOTICE: drop cascades to table child +drop table parent2; +-- 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, conkey from pg_constraint where contype = 'n' order by 2; + conrelid | conname | contype | coninhcount | conislocal | conkey +----------+------------------+---------+-------------+------------+-------- + inhx | inhx_xx_not_null | n | 0 | t | {1} + p1 | p1_f1_not_null | n | 0 | t | {1} + p2 | p2_f1_not_null | n | 0 | t | {1} + p4 | p4_f1_not_null | n | 0 | t | {1} + p4 | p4_f3_not_null | n | 0 | t | {2} +(5 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, conkey from pg_constraint where contype = 'n' order by 2; + conrelid | conname | contype | coninhcount | conislocal | conkey +----------+------------------+---------+-------------+------------+-------- + inhx | inhx_xx_not_null | n | 0 | t | {1} + p1 | p1_f1_not_null | n | 0 | t | {1} + p2 | p2_f1_not_null | n | 0 | t | {1} + p4 | p4_f1_not_null | n | 0 | t | {1} + p4 | p4_f3_not_null | n | 0 | t | {2} +(5 rows) + +drop table p1 cascade; +drop table p2; +drop table p3; +drop table p4; diff --git a/src/test/regress/sql/domain.sql b/src/test/regress/sql/domain.sql index 1cc447b8a2..c3ad7ebca3 100644 --- a/src/test/regress/sql/domain.sql +++ b/src/test/regress/sql/domain.sql @@ -224,6 +224,16 @@ 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 contype = 'n' and contypid <> 0 order by 1; + +drop domain dnotnulltest; + +select conname, contype, contypid::regtype from pg_constraint + where contype = 'n' and contypid <> 0 order by 1; + -- Test ALTER DOMAIN .. DEFAULT .. create table domdeftest (col1 ddef1); diff --git a/src/test/regress/sql/inherit.sql b/src/test/regress/sql/inherit.sql index 3087a14b72..8edc4d3784 100644 --- a/src/test/regress/sql/inherit.sql +++ b/src/test/regress/sql/inherit.sql @@ -406,3 +406,140 @@ select * from matest0 order by 1-id; reset enable_seqscan; drop table matest0 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 = 'n' order by 2; + +-- remove constraint from cc2, should fail +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 = 'n' order by 2; + +-- 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 = 'n' order by 2; + +drop table pp1 cascade; + +-- have a look at pg_constraint, everything should be clean now +select conrelid::regclass, conname, contype, coninhcount, conislocal from pg_constraint where contype = 'n' order by 2; + +-- +-- 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 = 'n' order by 2; + +-- +-- 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 = 'n' order by 2; + +-- 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 = 'n' order by 2; + +drop table parent cascade; + +-- test child table with inherited columns and +-- with explicitely specified not null constraints +create table parent1(f1 int); +create table parent2(f2 text); +create table child(f1 int not null, f2 text not null) inherits(parent1, parent2); + +-- show constraint info +select conrelid::regclass, conname, contype, coninhcount, conislocal, conkey from pg_constraint where contype = 'n' order by 2; + +-- also drops child table +drop table parent1 cascade; +drop table parent2; + +-- 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, conkey from pg_constraint where contype = 'n' order by 2; + +create table d(a int not null, f1 int) inherits(p3, c); + +select conrelid::regclass, conname, contype, coninhcount, conislocal, conkey from pg_constraint where contype = 'n' order by 2; + +drop table p1 cascade; +drop table p2; +drop table p3; +drop table p4; |