notnull-constraints-1.patch
text/x-diff
Filename: notnull-constraints-1.patch
Type: text/x-diff
Part: 0
Message:
Re: cataloguing NOT NULL constraints
Patch
Same data as JSON:
GET /api/v1/attachments/:id/patch
the parsed metadata as JSON — format, series position, per-file stats; never the diff bytes.
API reference →
Format: unified
| File | + | − |
|---|---|---|
| contrib/postgres_fdw/expected/postgres_fdw.out | 4 | 0 |
| src/backend/catalog/heap.c | 9 | 5 |
| src/backend/catalog/pg_constraint.c | 120 | 0 |
| src/backend/commands/constraint.c | 193 | 0 |
| src/backend/commands/createas.c | 2 | 0 |
| src/backend/commands/tablecmds.c | 671 | 123 |
| src/backend/commands/typecmds.c | 14 | 0 |
| src/backend/parser/parse_utilcmd.c | 148 | 60 |
| src/backend/utils/cache/relcache.c | 1 | 0 |
| src/bin/pg_dump/pg_dump.c | 17 | 2 |
| src/bin/pg_dump/t/002_pg_dump.pl | 23 | 13 |
| src/include/access/tupdesc.h | 1 | 0 |
| src/include/catalog/heap.h | 1 | 0 |
| src/include/catalog/pg_constraint.h | 8 | 3 |
| src/include/commands/constraint.h | 30 | 0 |
| src/include/nodes/parsenodes.h | 12 | 4 |
| src/pl/plpgsql/src/expected/plpgsql_varprops.out | 2 | 2 |
| src/test/regress/expected/alter_table.out | 6 | 3 |
| src/test/regress/expected/cluster.out | 4 | 3 |
| src/test/regress/expected/collate.out | 4 | 0 |
| src/test/regress/expected/constraints.out | 102 | 0 |
| src/test/regress/expected/create_table_like.out | 9 | 0 |
| src/test/regress/expected/create_table.out | 32 | 12 |
| src/test/regress/expected/domain.out | 9 | 0 |
| src/test/regress/expected/event_trigger.out | 9 | 0 |
| src/test/regress/expected/foreign_data.out | 95 | 7 |
| src/test/regress/expected/generated.out | 2 | 0 |
| src/test/regress/expected/identity.out | 9 | 0 |
| src/test/regress/expected/indexing.out | 39 | 16 |
| src/test/regress/expected/inherit.out | 374 | 20 |
| src/test/regress/expected/merge.out | 1 | 0 |
| src/test/regress/expected/publication.out | 6 | 0 |
| src/test/regress/expected/replica_identity.out | 20 | 0 |
| src/test/regress/expected/rowsecurity.out | 4 | 0 |
| src/test/regress/expected/typed_table.out | 2 | 0 |
| src/test/regress/sql/constraints.sql | 33 | 0 |
| src/test/regress/sql/create_table.sql | 3 | 3 |
| src/test/regress/sql/domain.sql | 7 | 0 |
| src/test/regress/sql/indexing.sql | 1 | 1 |
| src/test/regress/sql/inherit.sql | 165 | 2 |
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
--