notnull-constraints-2.patch
text/x-diff
Filename: notnull-constraints-2.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 |
| contrib/test_decoding/expected/ddl.out | 12 | 0 |
| src/backend/catalog/heap.c | 9 | 5 |
| src/backend/catalog/pg_constraint.c | 119 | 0 |
| src/backend/commands/constraint.c | 193 | 0 |
| src/backend/commands/createas.c | 2 | 0 |
| src/backend/commands/tablecmds.c | 832 | 243 |
| src/backend/commands/typecmds.c | 14 | 0 |
| src/backend/parser/parse_utilcmd.c | 177 | 63 |
| 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 | 20 | 4 |
| src/pl/plpgsql/src/expected/plpgsql_varprops.out | 2 | 2 |
| src/test/modules/test_pg_dump/t/001_base.pl | 8 | 5 |
| src/test/regress/expected/alter_table.out | 9 | 3 |
| src/test/regress/expected/cluster.out | 4 | 3 |
| src/test/regress/expected/collate.out | 4 | 0 |
| src/test/regress/expected/constraints.out | 99 | 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 | 3 | 0 |
| src/test/regress/expected/foreign_data.out | 91 | 5 |
| src/test/regress/expected/generated.out | 2 | 0 |
| src/test/regress/expected/identity.out | 9 | 0 |
| src/test/regress/expected/indexing.out | 12 | 10 |
| src/test/regress/expected/inherit.out | 365 | 13 |
| src/test/regress/expected/publication.out | 6 | 0 |
| src/test/regress/expected/replica_identity.out | 31 | 0 |
| src/test/regress/expected/rowsecurity.out | 4 | 0 |
| src/test/regress/expected/typed_table.out | 2 | 0 |
| src/test/regress/sql/alter_table.sql | 1 | 1 |
| 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 |
| src/test/regress/sql/replica_identity.sql | 11 | 0 |
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/contrib/test_decoding/expected/ddl.out b/contrib/test_decoding/expected/ddl.out
index 9a28b5ddc5..09e135a9da 100644
--- a/contrib/test_decoding/expected/ddl.out
+++ b/contrib/test_decoding/expected/ddl.out
@@ -492,6 +492,9 @@ WITH (user_catalog_table = true)
options | text[] | | | | extended | |
Indexes:
"replication_metadata_pkey" PRIMARY KEY, btree (id)
+Check constraints:
+ "replication_metadata_id_not_null" CHECK (id IS NOT NULL)
+ "replication_metadata_relation_not_null" CHECK (relation IS NOT NULL)
Options: user_catalog_table=true
INSERT INTO replication_metadata(relation, options)
@@ -506,6 +509,9 @@ ALTER TABLE replication_metadata RESET (user_catalog_table);
options | text[] | | | | extended | |
Indexes:
"replication_metadata_pkey" PRIMARY KEY, btree (id)
+Check constraints:
+ "replication_metadata_id_not_null" CHECK (id IS NOT NULL)
+ "replication_metadata_relation_not_null" CHECK (relation IS NOT NULL)
INSERT INTO replication_metadata(relation, options)
VALUES ('bar', ARRAY['a', 'b']);
@@ -519,6 +525,9 @@ ALTER TABLE replication_metadata SET (user_catalog_table = true);
options | text[] | | | | extended | |
Indexes:
"replication_metadata_pkey" PRIMARY KEY, btree (id)
+Check constraints:
+ "replication_metadata_id_not_null" CHECK (id IS NOT NULL)
+ "replication_metadata_relation_not_null" CHECK (relation IS NOT NULL)
Options: user_catalog_table=true
INSERT INTO replication_metadata(relation, options)
@@ -538,6 +547,9 @@ ALTER TABLE replication_metadata SET (user_catalog_table = false);
rewritemeornot | integer | | | | plain | |
Indexes:
"replication_metadata_pkey" PRIMARY KEY, btree (id)
+Check constraints:
+ "replication_metadata_id_not_null" CHECK (id IS NOT NULL)
+ "replication_metadata_relation_not_null" CHECK (relation IS NOT NULL)
Options: user_catalog_table=false
INSERT INTO replication_metadata(relation, options)
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..283eb8ac68 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,124 @@ 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;
+ AttrNumber check_attnum;
+ ArrayType *arr;
+ Datum adatum;
+ bool isnull;
+
+ /*
+ * 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;
+
+ check_attnum = tryExtractNotNullFromCatalog(conTup);
+ if (check_attnum == InvalidAttrNumber)
+ goto nope;
+
+ /*
+ * Surely tryExtractNotNullFromCatalog won't give us a mismatching
+ * constraint.
+ */
+ Assert(check_attnum == attnum);
+
+ /* 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..affe2d04a3 100644
--- a/src/backend/commands/constraint.c
+++ b/src/backend/commands/constraint.c
@@ -17,11 +17,15 @@
#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"
/*
@@ -203,3 +207,192 @@ 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.
+ * XXX provide a list of constraint names already reserved so we can ignore
+ * those.
+ *
+ * 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)
+{
+ Constraint *check;
+ 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;
+ }
+
+ 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 = nullexpr;
+ 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.
+ */
+char *
+tryExtractNotNullFromNode(Node *node, Relation rel)
+{
+ if (node == NULL)
+ return NULL;
+
+ /* Whatever we got, we can look inside a Constraint node */
+ 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 &&
+ IsA(nulltest->arg, ColumnRef))
+ {
+ ColumnRef *colref = (ColumnRef *) nulltest->arg;
+
+ if (list_length(colref->fields) == 1)
+ return strVal(linitial(colref->fields));
+ }
+ }
+
+ /*
+ * if no rel is passed, we can only check this much
+ */
+ if (rel == NULL)
+ return NULL;
+
+ 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;
+}
+
+/*
+ * Given a pg_constraint tuple for a CHECK constraint, see if it is a
+ * CHECK (IS NOT NULL) constraint, and return the column number it is for if
+ * so. Otherwise return InvalidAttrNumber.
+ */
+AttrNumber
+tryExtractNotNullFromCatalog(HeapTuple constrTup)
+{
+ Form_pg_constraint conForm = (Form_pg_constraint) GETSTRUCT(constrTup);
+ AttrNumber colnum = InvalidAttrNumber;
+ Datum val;
+ bool isnull;
+ char *conbin;
+ Node *node;
+
+ /* only tuples for CHECK constraints should be given */
+ Assert(conForm->contype == CONSTRAINT_CHECK);
+
+ val = SysCacheGetAttr(CONSTROID, constrTup, Anum_pg_constraint_conbin,
+ &isnull);
+ if (isnull)
+ elog(ERROR, "null conbin for constraint %u", conForm->oid);
+
+ conbin = TextDatumGetCString(val);
+ node = (Node *) stringToNode(conbin);
+
+ /* We expect a NullTest with an single Var within. */
+ if (IsA(node, NullTest))
+ {
+ NullTest *nulltest = (NullTest *) node;
+
+ if (nulltest->nulltesttype == IS_NOT_NULL)
+ {
+ if (IsA(nulltest->arg, Var))
+ {
+ Var *var = (Var *) nulltest->arg;
+
+ colnum = var->varattno;
+ }
+ }
+ }
+
+ /*
+ * XXX Need to check a few more possible wordings of NOT NULL:
+ *
+ * - foo IS DISTINCT FROM NULL
+ * - NOT (foo IS NULL)
+ */
+
+ pfree(conbin);
+ pfree(node);
+
+ return colnum;
+}
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 e3233a8f38..5e40806ae5 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,57 @@ 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.
+ */
+ foreach(listptr, stmt->notnull_check)
+ {
+ ConstraintNotNull *nn = lfirst_node(ConstraintNotNull, listptr);
+ ListCell *lc;
+
+ foreach(lc, stmt->tableElts)
+ {
+ ColumnDef *thiscol = lfirst(lc);
+
+ if (strcmp(thiscol->colname, nn->column) == 0)
+ {
+ thiscol->is_not_null = true;
+ break;
+ }
+ }
+ }
+ foreach(listptr, stmt->notnull_bare)
+ {
+ 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 +1290,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)
+ {
+ ConstraintNotNull *nn = lfirst_node(ConstraintNotNull, listptr);
+ Constraint *newcons;
+ AttrNumber colnum;
+ bool is_row = false; /* FIXME */
+
+ /* Don't create duplicate constraints */
+ colnum = get_attnum(RelationGetRelid(rel), nn->column);
+ if (bms_is_member(colnum, seencols))
+ continue;
+
+ newcons = makeCheckNotNullConstraint(namespaceId,
+ nn->conname,
+ relname,
+ nn->column,
+ 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 +2385,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 +2431,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 +2551,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 +2640,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 +2731,23 @@ MergeAttributes(List *schema, List *supers, char relpersistence,
}
def->inhcount++;
+
+ /*
+ * In regular inheritance, columns in the parent's primary key
+ * get an extra CHECK (NOT NULL) constraint. Partitioning
+ * doesn't need this, because the PK itself is going to be
+ * cloned to the partition.
+ */
+ if (!is_partition &&
+ bms_is_member(parent_attno - FirstLowInvalidHeapAttributeNumber,
+ pkattrs))
+ {
+ ConstraintNotNull *nn = makeNode(ConstraintNotNull);
+
+ nn->column = def->colname; /* XXX note no pstrdup */
+ nn->conname = NULL;
+ *notnullcols = lappend(*notnullcols, nn);
+ }
/* Merge of NOT NULL constraints = OR 'em together */
def->is_not_null |= attribute->attnotnull;
/* Default and other constraints are handled below */
@@ -2655,6 +2788,24 @@ MergeAttributes(List *schema, List *supers, char relpersistence,
def->compression = NULL;
inhSchema = lappend(inhSchema, def);
newattmap->attnums[parent_attno - 1] = ++child_attno;
+
+ /*
+ * In regular inheritance, columns in the parent's primary key
+ * get an extra CHECK (NOT NULL) constraint. Partitioning
+ * doesn't need this, because the PK itself is going to be
+ * cloned to the partition.
+ */
+ if (!is_partition &&
+ bms_is_member(parent_attno -
+ FirstLowInvalidHeapAttributeNumber,
+ pkattrs))
+ {
+ ConstraintNotNull *nn = makeNode(ConstraintNotNull);
+
+ nn->column = def->colname; /* XXX note no pstrdup */
+ nn->conname = NULL;
+ *notnullcols = lappend(*notnullcols, nn);
+ }
}
/*
@@ -2787,6 +2938,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 +3145,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 +3162,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 +4412,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 +4712,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 +5112,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 +5489,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 +5555,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 +6278,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 +6843,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 +7348,46 @@ 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)
+ATExecDropConstraint_internal(Relation rel, HeapTuple constraintTup, DropBehavior behavior,
+ bool recurse, bool recursing, bool missing_ok, LOCKMODE lockmode);
+
+static ObjectAddress
+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
@@ -7241,6 +7403,13 @@ ATExecDropNotNull(Relation rel, const char *colName, LOCKMODE lockmode)
attTup = (Form_pg_attribute) GETSTRUCT(tuple);
attnum = attTup->attnum;
+ /* If the column is already nullable there's nothing to do. */
+ if (!attTup->attnotnull)
+ {
+ table_close(attr_rel, RowExclusiveLock);
+ return InvalidObjectAddress;
+ }
+
/* Prevent them from altering a system attribute */
if (attnum <= 0)
ereport(ERROR,
@@ -7255,62 +7424,28 @@ ATExecDropNotNull(Relation rel, const char *colName, LOCKMODE lockmode)
colName, RelationGetRelationName(rel))));
/*
- * Check that the attribute is not in a primary key or in an index used as
- * a replica identity.
- *
- * Note: we'll throw error even if the pkey index is not valid.
+ * 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.
*/
-
- /* Loop over all indexes on the relation */
- indexoidlist = RelationGetIndexList(rel);
-
- foreach(indexoidscan, indexoidlist)
+ if (!recurse && rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)
{
- Oid indexoid = lfirst_oid(indexoidscan);
- HeapTuple indexTuple;
- Form_pg_index indexStruct;
- int i;
+ PartitionDesc partdesc;
- indexTuple = SearchSysCache1(INDEXRELID, ObjectIdGetDatum(indexoid));
- if (!HeapTupleIsValid(indexTuple))
- elog(ERROR, "cache lookup failed for index %u", indexoid);
- indexStruct = (Form_pg_index) GETSTRUCT(indexTuple);
+ partdesc = RelationGetPartitionDesc(rel, true);
- /*
- * If the index is not a primary key or an index used as replica
- * identity, skip the check.
- */
- if (indexStruct->indisprimary || indexStruct->indisreplident)
- {
- /*
- * Loop over each attribute in the primary key or the index used
- * as replica identity and see if it matches the to-be-altered
- * attribute.
- */
- for (i = 0; i < indexStruct->indnkeyatts; i++)
- {
- if (indexStruct->indkey.values[i] == attnum)
- {
- if (indexStruct->indisprimary)
- ereport(ERROR,
- (errcode(ERRCODE_INVALID_TABLE_DEFINITION),
- errmsg("column \"%s\" is in a primary key",
- colName)));
- else
- ereport(ERROR,
- (errcode(ERRCODE_INVALID_TABLE_DEFINITION),
- errmsg("column \"%s\" is in index used as replica identity",
- colName)));
- }
- }
- }
-
- ReleaseSysCache(indexTuple);
+ 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."));
}
- list_free(indexoidlist);
-
- /* If rel is partition, shouldn't drop NOT NULL if parent has the same */
+ /*
+ * 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);
@@ -7328,22 +7463,46 @@ ATExecDropNotNull(Relation rel, const char *colName, LOCKMODE lockmode)
}
/*
- * Okay, actually perform the catalog change ... if needed
+ * Find the constraint that makes this column NOT NULL. If there's more
+ * than one, we cannot cope well, so give up.
*/
- if (attTup->attnotnull)
+ conTup = findNotNullConstraint(rel, colName, &multiple);
+ if (conTup == NULL)
{
- attTup->attnotnull = false;
+ Bitmapset *pkcols;
- CatalogTupleUpdate(attr_rel, &tuple->t_self, tuple);
+ /*
+ * There's no CHECK (IS NOT NULL) constraint, so throw an error. If
+ * the column is in a primary key, we can throw a specific error.
+ * Otherwise, this is unexpected.
+ */
+ pkcols = RelationGetIndexAttrBitmap(rel, INDEX_ATTR_BITMAP_PRIMARY_KEY);
+ if (bms_is_member(attnum - FirstLowInvalidHeapAttributeNumber,
+ pkcols))
+ ereport(ERROR,
+ errcode(ERRCODE_INVALID_TABLE_DEFINITION),
+ errmsg("column \"%s\" is in a primary key", colName));
- ObjectAddressSubSet(address, RelationRelationId,
- RelationGetRelid(rel), attnum);
+ /* this shouldn't happen */
+ elog(ERROR, "no NOT NULL constraint found to drop");
}
- else
- address = InvalidObjectAddress;
- InvokeObjectPostAlterHook(RelationRelationId,
- RelationGetRelid(rel), attnum);
+ /*
+ * If there are several pg_constraint rows to which this not-null constraint
+ * can be attributed, raise an error about the ambiguity. We don't want to
+ * guess as to which one to drop; or worse, make this command work and have
+ * the user expect that the column is nullable afterwards.
+ */
+ 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."));
+
+ address = ATExecDropConstraint_internal(rel, conTup, DROP_RESTRICT, recurse, false,
+ false, lockmode);
+
+ heap_freetuple(conTup);
table_close(attr_rel, RowExclusiveLock);
@@ -7422,10 +7581,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 +7653,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 = findNotNullConstraintAttnum(rel, attnum, 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 +9092,175 @@ 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 a validated 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.
+ */
+ if (constr->initially_valid)
+ {
+ 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 +9278,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 +9309,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;
}
@@ -11051,6 +11370,7 @@ ATExecValidateConstraint(List **wqueue, Relation rel, char *constrName,
List *children = NIL;
ListCell *child;
NewConstraint *newcon;
+ AttrNumber attnum;
bool isnull;
Datum val;
char *conbin;
@@ -11123,6 +11443,26 @@ ATExecValidateConstraint(List **wqueue, Relation rel, char *constrName,
* constraint.
*/
CacheInvalidateRelcache(rel);
+
+ /*
+ * If we have validated a CHECK (IS NOT NULL) constraint, update
+ * the column so that it's marked attnotnull as well.
+ */
+ attnum = tryExtractNotNullFromCatalog(tuple);
+ if (attnum != InvalidAttrNumber)
+ {
+ Relation pgatt;
+ HeapTuple atttup;
+
+ pgatt = table_open(AttributeRelationId, RowExclusiveLock);
+ atttup = SearchSysCacheCopyAttNum(con->conrelid, attnum);
+ if (!((Form_pg_attribute) GETSTRUCT(atttup))->attnotnull)
+ {
+ ((Form_pg_attribute) GETSTRUCT(atttup))->attnotnull = true;
+ CatalogTupleUpdate(pgatt, &atttup->t_self, atttup);
+ }
+ table_close(pgatt, RowExclusiveLock);
+ }
}
/*
@@ -11818,16 +12158,11 @@ ATExecDropConstraint(Relation rel, const char *constrName,
bool recurse, bool recursing,
bool missing_ok, LOCKMODE lockmode)
{
- List *children;
- ListCell *child;
Relation conrel;
- Form_pg_constraint con;
SysScanDesc scan;
ScanKeyData skey[3];
HeapTuple tuple;
bool found = false;
- bool is_no_inherit_constraint = false;
- char contype;
/* At top level, permission check was done in ATPrepCmd, else do it */
if (recursing)
@@ -11856,47 +12191,8 @@ ATExecDropConstraint(Relation rel, const char *constrName,
/* There can be at most one matching row */
if (HeapTupleIsValid(tuple = systable_getnext(scan)))
{
- ObjectAddress conobj;
-
- con = (Form_pg_constraint) GETSTRUCT(tuple);
-
- /* Don't drop inherited constraints */
- if (con->coninhcount > 0 && !recursing)
- ereport(ERROR,
- (errcode(ERRCODE_INVALID_TABLE_DEFINITION),
- errmsg("cannot drop inherited constraint \"%s\" of relation \"%s\"",
- constrName, RelationGetRelationName(rel))));
-
- is_no_inherit_constraint = con->connoinherit;
- contype = con->contype;
-
- /*
- * If it's a foreign-key constraint, we'd better lock the referenced
- * table and check that that's not in use, just as we've already done
- * for the constrained table (else we might, eg, be dropping a trigger
- * that has unfired events). But we can/must skip that in the
- * self-referential case.
- */
- if (contype == CONSTRAINT_FOREIGN &&
- con->confrelid != RelationGetRelid(rel))
- {
- Relation frel;
-
- /* Must match lock taken by RemoveTriggerById: */
- frel = table_open(con->confrelid, AccessExclusiveLock);
- CheckTableNotInUse(frel, "ALTER TABLE");
- table_close(frel, NoLock);
- }
-
- /*
- * Perform the actual constraint deletion
- */
- conobj.classId = ConstraintRelationId;
- conobj.objectId = con->oid;
- conobj.objectSubId = 0;
-
- performDeletion(&conobj, behavior, 0);
-
+ ATExecDropConstraint_internal(rel, tuple, behavior, recurse, recursing,
+ missing_ok, lockmode);
found = true;
}
@@ -11905,31 +12201,224 @@ ATExecDropConstraint(Relation rel, const char *constrName,
if (!found)
{
if (!missing_ok)
- {
ereport(ERROR,
- (errcode(ERRCODE_UNDEFINED_OBJECT),
- errmsg("constraint \"%s\" of relation \"%s\" does not exist",
- constrName, RelationGetRelationName(rel))));
- }
+ errcode(ERRCODE_UNDEFINED_OBJECT),
+ errmsg("constraint \"%s\" of relation \"%s\" does not exist",
+ constrName, RelationGetRelationName(rel)));
else
- {
ereport(NOTICE,
- (errmsg("constraint \"%s\" of relation \"%s\" does not exist, skipping",
- constrName, RelationGetRelationName(rel))));
- table_close(conrel, RowExclusiveLock);
- return;
+ errmsg("constraint \"%s\" of relation \"%s\" does not exist, skipping",
+ constrName, RelationGetRelationName(rel)));
+ }
+
+ table_close(conrel, RowExclusiveLock);
+}
+
+/*
+ * Remove a constraint, using a pg_constraint tuple XXX complete description
+ *
+ * Implementation for ALTER TABLE DROP CONSTRAINT and ALTER TABLE ALTER COLUMN
+ * DROP NOT NULL.
+ *
+ * Returns the address of the constraint being removed.
+ */
+static ObjectAddress
+ATExecDropConstraint_internal(Relation rel, HeapTuple constraintTup, DropBehavior behavior,
+ bool recurse, bool recursing, bool missing_ok, LOCKMODE lockmode)
+{
+ Relation conrel;
+ Form_pg_constraint con;
+ ObjectAddress conobj;
+ List *children;
+ ListCell *child;
+ bool is_no_inherit_constraint = false;
+ bool dropping_pk = false;
+ char *constrName;
+ List *unconstrained_cols = NIL;
+
+ conrel = table_open(ConstraintRelationId, RowExclusiveLock);
+
+ con = (Form_pg_constraint) GETSTRUCT(constraintTup);
+ constrName = NameStr(con->conname);
+
+ /* Don't drop inherited constraints */
+ if (con->coninhcount > 0 && !recursing)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_TABLE_DEFINITION),
+ 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 obtain the list of
+ * columns that are constrained by the constraint being dropped.
+ */
+ if (con->contype == CONSTRAINT_CHECK)
+ {
+ AttrNumber colnum = tryExtractNotNullFromCatalog(constraintTup);
+
+ if (colnum != InvalidAttrNumber)
+ unconstrained_cols = list_make1_int(colnum);
+ }
+ else if (con->contype == CONSTRAINT_PRIMARY)
+ {
+ Datum adatum;
+ ArrayType *arr;
+ int numkeys;
+ bool isNull;
+ int16 *attnums;
+
+ dropping_pk = true;
+
+ adatum = heap_getattr(constraintTup, Anum_pg_constraint_conkey,
+ RelationGetDescr(conrel), &isNull);
+ if (isNull)
+ elog(ERROR, "null conkey for constraint %u", con->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;
+
+ /*
+ * If it's a foreign-key constraint, we'd better lock the referenced
+ * table and check that that's not in use, just as we've already done
+ * for the constrained table (else we might, eg, be dropping a trigger
+ * that has unfired events). But we can/must skip that in the
+ * self-referential case.
+ */
+ if (con->contype == CONSTRAINT_FOREIGN &&
+ con->confrelid != RelationGetRelid(rel))
+ {
+ Relation frel;
+
+ /* Must match lock taken by RemoveTriggerById: */
+ frel = table_open(con->confrelid, AccessExclusiveLock);
+ CheckTableNotInUse(frel, "ALTER TABLE");
+ table_close(frel, NoLock);
+ }
+
+ /*
+ * Perform the actual constraint deletion
+ */
+ ObjectAddressSet(conobj, ConstraintRelationId, con->oid);
+ performDeletion(&conobj, behavior, 0);
+
+ /*
+ * 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)
+ {
+ Relation attrel;
+ Bitmapset *pkcols;
+ Bitmapset *ircols;
+ ListCell *lc;
+
+ /* Make the above deletion visible */
+ CommandCounterIncrement();
+
+ attrel = table_open(AttributeRelationId, RowExclusiveLock);
+
+ /*
+ * 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);
+ ircols = RelationGetIndexAttrBitmap(rel, INDEX_ATTR_BITMAP_IDENTITY_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.
+ *
+ * XXX reword this comment
+ */
+ contup = findNotNullConstraintAttnum(rel, attnum, NULL);
+ if (contup ||
+ bms_is_member(attnum - FirstLowInvalidHeapAttributeNumber,
+ pkcols))
+ continue;
+
+ /*
+ * It's not valid to drop the last NOT NULL constraint for a
+ * GENERATED AS IDENTITY column.
+ */
+ if (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)));
+
+ /*
+ * It's not valid to drop the last NOT NULL constraint for the
+ * replica identity either. XXX make exception for FULL?
+ */
+ if (bms_is_member(lfirst_int(lc) - FirstLowInvalidHeapAttributeNumber, ircols))
+ ereport(ERROR,
+ errcode(ERRCODE_INVALID_TABLE_DEFINITION),
+ errmsg("column \"%s\" is in index used as replica identity",
+ get_attname(RelationGetRelid(rel), lfirst_int(lc), false)));
+
+ /* Reset attnotnull */
+ if (attForm->attnotnull)
+ {
+ attForm->attnotnull = false;
+ CatalogTupleUpdate(attrel, &atttup->t_self, atttup);
+ }
+
+ /* XXX free the catalog tuples? */
}
+ table_close(attrel, RowExclusiveLock);
}
/*
* For partitioned tables, non-CHECK inherited constraints are dropped via
* the dependency mechanism, so we're done here.
*/
- if (contype != CONSTRAINT_CHECK &&
+ if (con->contype != CONSTRAINT_CHECK &&
rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)
{
table_close(conrel, RowExclusiveLock);
- return;
+ return InvalidObjectAddress; /* XXX address */
}
/*
@@ -11958,7 +12447,11 @@ ATExecDropConstraint(Relation rel, const char *constrName,
{
Oid childrelid = lfirst_oid(child);
Relation childrel;
+ HeapTuple tuple;
HeapTuple copy_tuple;
+ SysScanDesc scan;
+ ScanKeyData skey[3];
+ /* XXX refactor this bit */
/* find_inheritance_children already got lock */
childrel = table_open(childrelid, NoLock);
@@ -12010,6 +12503,7 @@ ATExecDropConstraint(Relation rel, const char *constrName,
if (con->coninhcount == 1 && !con->conislocal)
{
/* Time to delete this child constraint, too */
+ /* XXX should recurse using ATExecDropConstraint_internal */
ATExecDropConstraint(childrel, constrName, behavior,
true, true,
false, lockmode);
@@ -12046,6 +12540,8 @@ ATExecDropConstraint(Relation rel, const char *constrName,
}
table_close(conrel, RowExclusiveLock);
+
+ return conobj;
}
/*
@@ -13366,10 +13862,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
@@ -15157,6 +15654,7 @@ MergeConstraintsIntoExisting(Relation child_rel, Relation parent_rel)
SysScanDesc parent_scan;
ScanKeyData parent_key;
HeapTuple parent_tuple;
+ Oid parent_relid = RelationGetRelid(parent_rel);
bool child_is_partition = false;
catalog_relation = table_open(ConstraintRelationId, RowExclusiveLock);
@@ -15170,13 +15668,14 @@ MergeConstraintsIntoExisting(Relation child_rel, Relation parent_rel)
ScanKeyInit(&parent_key,
Anum_pg_constraint_conrelid,
BTEqualStrategyNumber, F_OIDEQ,
- ObjectIdGetDatum(RelationGetRelid(parent_rel)));
+ ObjectIdGetDatum(parent_relid));
parent_scan = systable_beginscan(catalog_relation, ConstraintRelidTypidNameIndexId,
true, NULL, 1, &parent_key);
while (HeapTupleIsValid(parent_tuple = systable_getnext(parent_scan)))
{
Form_pg_constraint parent_con = (Form_pg_constraint) GETSTRUCT(parent_tuple);
+ AttrNumber parent_attnum;
SysScanDesc child_scan;
ScanKeyData child_key;
HeapTuple child_tuple;
@@ -15189,6 +15688,81 @@ 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.
+ */
+ parent_attnum = tryExtractNotNullFromCatalog(parent_tuple);
+ if (parent_attnum != InvalidAttrNumber)
+ {
+ AttrNumber child_attnum;
+ Form_pg_attribute att;
+ char *colname;
+
+ /* XXX do we want to use an attrmap instead? */
+ colname = get_attname(parent_relid, parent_attnum, false);
+ child_attnum = get_attnum(RelationGetRelid(child_rel), colname);
+
+ if (child_attnum == InvalidAttrNumber)
+ elog(ERROR, "cache lookup failure for attribute «%s» of relation %u",
+ get_attname(RelationGetRelid(parent_rel), parent_attnum, false),
+ RelationGetRelid(parent_rel));
+
+ att = TupleDescAttr(RelationGetDescr(child_rel), child_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.
+ *
+ * FIXME -- actually I think this fails if the column is
+ * attnotnull because of a PK.
+ */
+ conTup = findNotNullConstraintAttnum(child_rel, child_attnum, 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.
+ * XXX by doing this, we'll probably end with no constraint in
+ * the partition if we ATTACH then DETACH. Undesirable?
+ * Reconsider this.
+ */
+ 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,
@@ -15521,6 +16095,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..78227379b2 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;
@@ -595,6 +603,7 @@ transformColumnDefinition(CreateStmtContext *cxt, ColumnDef *column)
TypeCast *castnode;
FuncCall *funccallnode;
Constraint *constraint;
+ ConstraintNotNull *notnull;
generateSerialExtraStmts(cxt, column,
column->typeName->typeOid, NIL,
@@ -631,10 +640,11 @@ 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 */
+ notnull = makeNode(ConstraintNotNull);
+ notnull->conname = NULL;
+ notnull->column = column->colname;
+ cxt->notnulls_check = lappend(cxt->notnulls_check, notnull);
}
/* Process column constraints, if any... */
@@ -648,6 +658,7 @@ transformColumnDefinition(CreateStmtContext *cxt, ColumnDef *column)
foreach(clist, column->constraints)
{
Constraint *constraint = lfirst_node(Constraint, clist);
+ char *colname;
switch (constraint->contype)
{
@@ -664,6 +675,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 +687,24 @@ 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)
+ {
+ ConstraintNotNull *notnull;
+
+ column->is_not_null = true;
+
+ notnull = makeNode(ConstraintNotNull);
+ notnull->conname = constraint->conname;
+ notnull->column = column->colname;
+
+ cxt->notnulls_check = lappend(cxt->notnulls_check, notnull);
+ }
+
break;
case CONSTR_DEFAULT:
@@ -722,7 +754,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 +761,18 @@ 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)
+ {
+ ConstraintNotNull *notnull;
+
+ column->is_not_null = true;
+
+ notnull = makeNode(ConstraintNotNull);
+ notnull->conname = NULL;
+ notnull->column = column->colname;
+ cxt->notnulls_check = lappend(cxt->notnulls_check,
+ notnull);
+ }
saw_nullable = true;
break;
}
@@ -760,6 +802,40 @@ 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. In any case, save the column name so that it
+ * gets an attnotnull marker. We only need to do this for
+ * constraint that aren't NOT VALID, however.
+ */
+ if (constraint->initially_valid)
+ {
+ colname = tryExtractNotNullFromNode((Node *) constraint, NULL);
+ if (colname != NULL)
+ {
+ cxt->notnulls_nock = lappend(cxt->notnulls_nock,
+ makeString(pstrdup(colname)));
+
+ if (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 +951,8 @@ transformColumnDefinition(CreateStmtContext *cxt, ColumnDef *column)
static void
transformTableConstraint(CreateStmtContext *cxt, Constraint *constraint)
{
+ char *colname;
+
switch (constraint->contype)
{
case CONSTR_PRIMARY:
@@ -915,6 +993,13 @@ transformTableConstraint(CreateStmtContext *cxt, Constraint *constraint)
case CONSTR_CHECK:
cxt->ckconstraints = lappend(cxt->ckconstraints, constraint);
+ if (constraint->initially_valid)
+ {
+ 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 +1049,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 +1131,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 +1214,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 +1405,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 +1414,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 +1441,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 +2174,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 +2253,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,18 +2261,15 @@ 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)
{
IndexStmt *index;
- List *notnullcmds = NIL;
ListCell *lc;
index = makeNode(IndexStmt);
@@ -2449,14 +2551,14 @@ 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 +2662,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 +2770,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 +3427,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 +3648,27 @@ transformAlterTableStmt(Oid relid, AlterTableStmt *stmt,
}
}
+ /*
+ * Add CHECK constraints to match NOT NULL declarations from various
+ * sources.
+ *
+ * Cannot do it at this point: may not know is_row yet.
+ */
+ foreach(l, cxt.notnulls_check)
+ {
+ ConstraintNotNull *notnull = lfirst(l);
+ Constraint *newconstr;
+ bool is_row = false; /* FIXME */
+
+ newconstr = makeCheckNotNullConstraint(RelationGetNamespace(rel),
+ notnull->conname,
+ RelationGetRelationName(rel),
+ notnull->column,
+ 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 +3681,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 67b6d9079e..1aade47987 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..b6720060d7
--- /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 AttrNumber tryExtractNotNullFromCatalog(HeapTuple constrTup);
+
+#endif /* CONSTRAINT_H */
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 6958306a7d..42940b97b7 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -1955,6 +1955,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 */
@@ -2243,10 +2244,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).
* ----------------------
*/
@@ -2261,6 +2263,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 */
@@ -2342,6 +2349,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? */
@@ -2386,6 +2394,14 @@ typedef struct Constraint
bool initially_valid; /* mark the new constraint as valid? */
} Constraint;
+typedef struct ConstraintNotNull
+{
+ NodeTag type;
+
+ char *conname; /* Constraint name, or NULL if unnamed */
+ char *column; /* column on which it applies */
+} ConstraintNotNull;
+
/* ----------------------
* Create/Drop Table Space Statements
* ----------------------
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/modules/test_pg_dump/t/001_base.pl b/src/test/modules/test_pg_dump/t/001_base.pl
index f5da6bf46d..b89228b51c 100644
--- a/src/test/modules/test_pg_dump/t/001_base.pl
+++ b/src/test/modules/test_pg_dump/t/001_base.pl
@@ -289,8 +289,9 @@ my %tests = (
'ALTER EXTENSION test_pg_dump ADD TABLE regress_pg_dump_table_added;',
regexp => qr/^
\QCREATE TABLE public.regress_pg_dump_table_added (\E
- \n\s+\Qcol1 integer NOT NULL,\E
- \n\s+\Qcol2 integer\E
+ \n\s+\Qcol1 integer,\E
+ \n\s+\Qcol2 integer,\E
+ \n\s+\QCONSTRAINT regress_pg_dump_table_added_col1_not_null CHECK ((col1 IS NOT NULL))\E
\n\);\n/xm,
like => { binary_upgrade => 1, },
},
@@ -375,8 +376,9 @@ my %tests = (
'CREATE TABLE regress_pg_dump_table_added (col1 int not null, col2 int);',
regexp => qr/^
\QCREATE TABLE public.regress_pg_dump_table_added (\E
- \n\s+\Qcol1 integer NOT NULL,\E
- \n\s+\Qcol2 integer\E
+ \n\s+\Qcol1 integer,\E
+ \n\s+\Qcol2 integer,\E
+ \n\s+\QCONSTRAINT regress_pg_dump_table_added_col1_not_null CHECK ((col1 IS NOT NULL))\E
\n\);\n/xm,
like => { binary_upgrade => 1, },
},
@@ -411,8 +413,9 @@ my %tests = (
'CREATE TABLE regress_pg_dump_table' => {
regexp => qr/^
\QCREATE TABLE public.regress_pg_dump_table (\E
- \n\s+\Qcol1 integer NOT NULL,\E
+ \n\s+\Qcol1 integer,\E
\n\s+\Qcol2 integer,\E
+ \n\s+\QCONSTRAINT regress_pg_dump_table_col1_not_null CHECK ((col1 IS NOT NULL)),\E
\n\s+\QCONSTRAINT regress_pg_dump_table_col2_check CHECK ((col2 > 0))\E
\n\);\n/xm,
like => { binary_upgrade => 1, },
diff --git a/src/test/regress/expected/alter_table.out b/src/test/regress/expected/alter_table.out
index d63f4f1cba..dd37c6ab22 100644
--- a/src/test/regress/expected/alter_table.out
+++ b/src/test/regress/expected/alter_table.out
@@ -1119,9 +1119,13 @@ ERROR: relation "non_existent" does not exist
create table atacc1 (test int not null);
alter table atacc1 add constraint "atacc1_pkey" primary key (test);
alter table atacc1 alter column test drop not null;
-ERROR: column "test" is in a primary key
alter table atacc1 drop constraint "atacc1_pkey";
-alter table atacc1 alter column test drop not null;
+\d atacc1
+ Table "public.atacc1"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+---------
+ test | integer | | |
+
insert into atacc1 values (null);
alter table atacc1 alter test set not null;
ERROR: column "test" of relation "atacc1" contains null values
@@ -3235,7 +3239,7 @@ CREATE TABLE test_drop_constr_child () INHERITS (test_drop_constr_parent);
ALTER TABLE ONLY test_drop_constr_parent DROP CONSTRAINT "test_drop_constr_parent_c_check";
-- should fail
INSERT INTO test_drop_constr_child (c) VALUES (NULL);
-ERROR: new row for relation "test_drop_constr_child" violates check constraint "test_drop_constr_parent_c_check"
+ERROR: null value in column "c" of relation "test_drop_constr_child" violates not-null constraint
DETAIL: Failing row contains (null).
DROP TABLE test_drop_constr_parent CASCADE;
NOTICE: drop cascades to table test_drop_constr_child
@@ -3677,6 +3681,7 @@ 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:
@@ -3698,6 +3703,7 @@ 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 e6f6602d95..8602e8c248 100644
--- a/src/test/regress/expected/constraints.out
+++ b/src/test/regress/expected/constraints.out
@@ -754,6 +754,105 @@ 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_not_null" CHECK (a IS NOT NULL)
+ "notnull_tbl1_a_not_null1" 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_not_null1" CHECK (a IS NOT NULL)
+
+-- but removing the second constraint resets the flag
+ALTER TABLE notnull_tbl1 DROP CONSTRAINT notnull_tbl1_a_not_null1;
+\d notnull_tbl1
+ Table "public.notnull_tbl1"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+---------
+ a | integer | | |
+
+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..a63d9fd1b8 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,8 +468,10 @@ 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 identity=evttrig.part_10_20 name={evttrig,part_10_20} args={}
diff --git a/src/test/regress/expected/foreign_data.out b/src/test/regress/expected/foreign_data.out
index 33505352cc..e8fc8af18f 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
@@ -1612,6 +1652,9 @@ Child tables: ft2
c6 | integer | | not null | | | plain | |
c7 | integer | | | | | plain | |
c8 | text | | | | | external | |
+Check constraints:
+ "fd_pt1_c6_not_null" CHECK (c6 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 +1674,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 +1685,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 +1701,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 +1717,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 +1731,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 +1766,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 +1780,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 +1799,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 +1813,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 +1828,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 +1842,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 +1862,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 +1875,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 +1922,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 +1935,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 +1956,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 +1973,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 +1989,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 +2004,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 +2017,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 +2036,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,6 +2050,8 @@ 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')
@@ -1994,6 +2070,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 +2083,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 +2104,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 +2117,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..8a3e9e6400 100644
--- a/src/test/regress/expected/indexing.out
+++ b/src/test/regress/expected/indexing.out
@@ -1065,16 +1065,18 @@ 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_pkey | p | idxpart1 | idxpart1_pkey | {1,2}
+ idxpart2_pkey | p | idxpart2 | idxpart2_pkey | {1,2}
+ idxpart21_pkey | p | idxpart21 | idxpart21_pkey | {1,2}
+ idxpart22_pkey | p | idxpart22 | idxpart22_pkey | {1,2}
+ idxpart3_a_not_null | c | idxpart3 | - | {2}
+ idxpart3_b_not_null | c | idxpart3 | - | {1}
+ idxpart3_pkey | p | idxpart3 | idxpart3_pkey | {2,1}
+(8 rows)
drop table idxpart;
-- Verify that multi-layer partitioning honors the requirement that all
diff --git a/src/test/regress/expected/inherit.out b/src/test/regress/expected/inherit.out
index 2d49e765de..a718969975 100644
--- a/src/test/regress/expected/inherit.out
+++ b/src/test/regress/expected/inherit.out
@@ -832,10 +832,10 @@ select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount, pg
(2 rows)
insert into ac (aa) values (NULL);
-ERROR: new row for relation "ac" violates check constraint "ac_check"
+ERROR: null value in column "aa" of relation "ac" violates not-null constraint
DETAIL: Failing row contains (null).
insert into bc (aa) values (NULL);
-ERROR: new row for relation "bc" violates check constraint "ac_check"
+ERROR: null value in column "aa" of relation "bc" violates not-null constraint
DETAIL: Failing row contains (null, null).
alter table bc drop constraint ac_check; -- fail, disallowed
ERROR: cannot drop inherited constraint "ac_check" of relation "bc"
@@ -848,21 +848,21 @@ select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount, pg
-- try the unnamed-constraint case
alter table ac add check (aa is 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;
- 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)
+ relname | conname | contype | conislocal | coninhcount | consrc
+---------+----------------+---------+------------+-------------+------------------
+ ac | ac_aa_not_null | c | t | 0 | (aa IS NOT NULL)
+ bc | ac_aa_not_null | c | f | 1 | (aa IS NOT NULL)
(2 rows)
insert into ac (aa) values (NULL);
-ERROR: new row for relation "ac" violates check constraint "ac_aa_check"
+ERROR: null value in column "aa" of relation "ac" violates not-null constraint
DETAIL: Failing row contains (null).
insert into bc (aa) values (NULL);
-ERROR: new row for relation "bc" violates check constraint "ac_aa_check"
+ERROR: null value in column "aa" of relation "bc" violates not-null constraint
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: cannot drop inherited constraint "ac_aa_not_null" of relation "bc"
+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;
relname | conname | contype | conislocal | coninhcount | consrc
---------+---------+---------+------------+-------------+--------
@@ -925,7 +925,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 +1017,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 +1736,359 @@ 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:
+ "nn" 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:
+ "nn" 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:
+ "nn" 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:
+ "nn" 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 | nn | c | 0 | t
+ cc2 | nn | 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;
+ERROR: cannot drop inherited constraint "nn" of relation "cc2"
+-- remove constraint cc1, should succeed
+alter table cc1 alter column a2 drop not null;
+-- have a look at pg_constraint
+select conrelid::regclass, conname, contype, coninhcount, conislocal
+ from pg_constraint where contype = '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;
+ERROR: cannot drop inherited constraint "pp1_f1_not_null" of relation "cc2"
+-- remove from cc1, should fail again
+alter table cc1 alter column f1 drop not null;
+ERROR: cannot drop inherited constraint "pp1_f1_not_null" of relation "cc1"
+-- remove from pp1, should succeed
+alter table pp1 alter column f1 drop not null;
+ERROR: constraint "pp1_f1_not_null" of relation "cc2" does not exist
+-- 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)
+
+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/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..4c3e284cf3 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,12 +240,25 @@ 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.
ALTER TABLE test_replica_identity3 ALTER COLUMN id DROP NOT NULL;
ERROR: column "id" is in index used as replica identity
+-- Dropping the primary key is not allowed if that would leave the replica
+-- identity as nullable
+CREATE TABLE test_replica_identity4 (a int not null, b int, c int,
+ PRIMARY KEY (b, c));
+CREATE UNIQUE INDEX test_replica_identity4_a_b_key ON test_replica_identity4 (a, b);
+ALTER TABLE test_replica_identity4 REPLICA IDENTITY USING INDEX test_replica_identity4_a_b_key;
+ALTER TABLE test_replica_identity4 DROP CONSTRAINT test_replica_identity4_pkey;
+ERROR: column "b" is in index used as replica identity
+ALTER TABLE test_replica_identity4 ALTER b SET NOT NULL;
+ALTER TABLE test_replica_identity4 DROP CONSTRAINT test_replica_identity4_pkey;
DROP TABLE test_replica_identity;
DROP TABLE test_replica_identity2;
DROP TABLE test_replica_identity3;
+DROP TABLE test_replica_identity4;
DROP TABLE test_replica_identity_othertable;
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/alter_table.sql b/src/test/regress/sql/alter_table.sql
index e7013f5e15..c49fde679a 100644
--- a/src/test/regress/sql/alter_table.sql
+++ b/src/test/regress/sql/alter_table.sql
@@ -852,7 +852,7 @@ create table atacc1 (test int not null);
alter table atacc1 add constraint "atacc1_pkey" primary key (test);
alter table atacc1 alter column test drop not null;
alter table atacc1 drop constraint "atacc1_pkey";
-alter table atacc1 alter column test drop not null;
+\d atacc1
insert into atacc1 values (null);
alter table atacc1 alter test set not null;
delete from atacc1;
diff --git a/src/test/regress/sql/constraints.sql b/src/test/regress/sql/constraints.sql
index 5ffcd4ffc7..ae427d25e9 100644
--- a/src/test/regress/sql/constraints.sql
+++ b/src/test/regress/sql/constraints.sql
@@ -556,6 +556,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
--
diff --git a/src/test/regress/sql/replica_identity.sql b/src/test/regress/sql/replica_identity.sql
index 33da829713..d0acf99e6c 100644
--- a/src/test/regress/sql/replica_identity.sql
+++ b/src/test/regress/sql/replica_identity.sql
@@ -98,7 +98,18 @@ ALTER TABLE test_replica_identity3 ALTER COLUMN id TYPE bigint;
-- used as replica identity.
ALTER TABLE test_replica_identity3 ALTER COLUMN id DROP NOT NULL;
+-- Dropping the primary key is not allowed if that would leave the replica
+-- identity as nullable
+CREATE TABLE test_replica_identity4 (a int not null, b int, c int,
+ PRIMARY KEY (b, c));
+CREATE UNIQUE INDEX test_replica_identity4_a_b_key ON test_replica_identity4 (a, b);
+ALTER TABLE test_replica_identity4 REPLICA IDENTITY USING INDEX test_replica_identity4_a_b_key;
+ALTER TABLE test_replica_identity4 DROP CONSTRAINT test_replica_identity4_pkey;
+ALTER TABLE test_replica_identity4 ALTER b SET NOT NULL;
+ALTER TABLE test_replica_identity4 DROP CONSTRAINT test_replica_identity4_pkey;
+
DROP TABLE test_replica_identity;
DROP TABLE test_replica_identity2;
DROP TABLE test_replica_identity3;
+DROP TABLE test_replica_identity4;
DROP TABLE test_replica_identity_othertable;