catalog-notnull-2.patch

commit 10e83e9463baefe88e2e833bd823bc87945ae2ff
Author: Alvaro Herrera <alvherre@alvh.no-ip.org>
Date:   Mon Jul 30 17:07:45 2012 -0400

    catalog NOT NULL constraints

diff --git a/src/backend/commands/constraint.c b/src/backend/commands/constraint.c
index 8ac8373..20d62b1 100644
--- a/src/backend/commands/constraint.c
+++ b/src/backend/commands/constraint.c
@@ -14,6 +14,8 @@
 #include "postgres.h"
 
 #include "catalog/index.h"
+#include "catalog/pg_constraint.h"
+#include "commands/constraint.h"
 #include "commands/trigger.h"
 #include "executor/executor.h"
 #include "utils/builtins.h"
@@ -188,3 +190,57 @@ unique_key_recheck(PG_FUNCTION_ARGS)
 
 	return PointerGetDatum(NULL);
 }
+
+Constraint *
+createCheckNotNullConstraint(Oid nspid, char *constraint_name,
+							 const char *relname, const char *colname)
+{
+	Constraint *check = makeNode(Constraint);
+	ColumnRef  *colref;
+	NullTest   *nulltest;
+
+	colref = (ColumnRef *) makeNode(ColumnRef);
+	colref->fields = list_make1(makeString(pstrdup(colname)));
+
+	nulltest = (NullTest *) makeNode(NullTest);
+	nulltest->argisrow = false;	/* FIXME -- may be bogus! */
+	nulltest->nulltesttype = IS_NOT_NULL;
+	nulltest->arg = (Expr *) colref;
+
+	check->contype = CONSTR_CHECK;
+	check->location = -1;
+	check->conname = constraint_name ? constraint_name :
+		ChooseConstraintName(relname, colname, "not_null", nspid,
+							 NIL);
+	check->raw_expr = (Node *) nulltest;
+	check->cooked_expr = NULL;
+
+	return check;
+}
+
+/*
+ * Given a CHECK constraint, examine it and determine whether it is CHECK (col
+ * IS NOT NULL).  If it is, return the column name for which it is.  Otherwise
+ * return NULL.
+ */
+char *
+tryExtractNotNullFromCheckConstr(Constraint *constr)
+{
+	Assert(constr->raw_expr != NULL);
+	if (IsA(constr->raw_expr, NullTest))
+	{
+		NullTest *nulltest = (NullTest *) constr->raw_expr;
+
+		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));
+		}
+	}
+	/* XXX what if raw_expr is not set and instead we get cooked_expr? */
+
+	return NULL;
+}
diff --git a/src/backend/commands/createas.c b/src/backend/commands/createas.c
index dc0665e..a3bff7d 100644
--- a/src/backend/commands/createas.c
+++ b/src/backend/commands/createas.c
@@ -231,6 +231,7 @@ intorel_startup(DestReceiver *self, int operation, TupleDesc typeinfo)
 	create->inhRelations = NIL;
 	create->ofTypename = NULL;
 	create->constraints = NIL;
+	create->notnullcols = 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 d044295..a907710 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -43,6 +43,7 @@
 #include "catalog/toasting.h"
 #include "commands/cluster.h"
 #include "commands/comment.h"
+#include "commands/constraint.h"
 #include "commands/defrem.h"
 #include "commands/sequence.h"
 #include "commands/tablecmds.h"
@@ -251,8 +252,9 @@ struct DropRelationCallbackState
 #define		ATT_FOREIGN_TABLE		0x0010
 
 static void truncate_check_rel(Relation rel);
-static List *MergeAttributes(List *schema, List *supers, char relpersistence,
-				List **supOids, List **supconstr, int *supOidCount);
+static List *MergeAttributes(List *schema, List *notnullcols, List *supers,
+				char relpersistence, List **supOids, List **supconstr,
+				int *supOidCount);
 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);
@@ -313,8 +315,9 @@ static void add_column_collation_dependency(Oid relid, int32 attnum, Oid collid)
 static void ATPrepAddOids(List **wqueue, Relation rel, bool recurse,
 			  AlterTableCmd *cmd, LOCKMODE lockmode);
 static void ATExecDropNotNull(Relation rel, const char *colName, LOCKMODE lockmode);
-static void ATExecSetNotNull(AlteredTableInfo *tab, Relation rel,
-				 const char *colName, LOCKMODE lockmode);
+static void ATExecSetNotNull(List **wqueue, AlteredTableInfo *tab,
+				 Relation rel, char *constrname, const char *colName,
+				 LOCKMODE lockmode);
 static void ATExecColumnDefault(Relation rel, const char *colName,
 					Node *newDefault, LOCKMODE lockmode);
 static void ATPrepSetStatistics(Relation rel, const char *colName,
@@ -341,7 +344,13 @@ static void ATExecAddIndexConstraint(AlteredTableInfo *tab, Relation rel,
 static void ATAddCheckConstraint(List **wqueue,
 					 AlteredTableInfo *tab, Relation rel,
 					 Constraint *constr,
-					 bool recurse, bool recursing, LOCKMODE lockmode);
+					 bool recurse, bool recursing,
+					 LOCKMODE lockmode);
+static void ATAddCheckConstraint_internal(List **wqueue,
+							  AlteredTableInfo *tab, Relation rel,
+							  Constraint *constr,
+							  bool recurse, bool recursing,
+							  bool check_it, LOCKMODE lockmode);
 static void ATAddForeignKeyConstraint(AlteredTableInfo *tab, Relation rel,
 						  Constraint *fkconstraint, LOCKMODE lockmode);
 static void ATExecDropConstraint(Relation rel, const char *constrName,
@@ -535,7 +544,7 @@ DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId)
 	 * Look up inheritance ancestors and generate relation schema, including
 	 * inherited attributes.
 	 */
-	schema = MergeAttributes(schema, stmt->inhRelations,
+	schema = MergeAttributes(schema, stmt->notnullcols, stmt->inhRelations,
 							 stmt->relation->relpersistence,
 							 &inheritOids, &old_constraints, &parentOidCount);
 
@@ -1288,6 +1297,8 @@ storage_name(char c)
  * Input arguments:
  * 'schema' is the column/attribute definition for the table. (It's a list
  *		of ColumnDef's.) It is destructively changed.
+ * 'notnullcols' is a list of column names that have NOT NULL constraints.
+ *		Some of these columns may already have is_not_null already set.
  * 'supers' is a list of names (as RangeVar nodes) of parent relations.
  * 'relpersistence' is a persistence type of the table.
  *
@@ -1340,10 +1351,12 @@ storage_name(char c)
  *----------
  */
 static List *
-MergeAttributes(List *schema, List *supers, char relpersistence,
-				List **supOids, List **supconstr, int *supOidCount)
+MergeAttributes(List *schema, List *notnullcols, List *supers,
+				char relpersistence, List **supOids, List **supconstr,
+				int *supOidCount)
 {
 	ListCell   *entry;
+	ListCell   *cell;
 	List	   *inhSchema = NIL;
 	List	   *parentOids = NIL;
 	List	   *constraints = NIL;
@@ -1805,6 +1818,23 @@ MergeAttributes(List *schema, List *supers, char relpersistence,
 	}
 
 	/*
+	 * If we have NOT NULL constraint declarations, set the is_not_null bits
+	 * in the correspoding ColumnDef elements.
+	 */
+	foreach (cell, notnullcols)
+	{
+		char   *colname = lfirst(cell);
+
+		foreach (entry, schema)
+		{
+			ColumnDef *coldef = lfirst(entry);
+
+			if (strcmp(coldef->colname, colname) == 0)
+				coldef->is_not_null = true;
+		}
+	}
+
+	/*
 	 * If we found any conflicting parent default values, check to make sure
 	 * they were overridden by the child.
 	 */
@@ -3218,7 +3248,7 @@ ATExecCmd(List **wqueue, AlteredTableInfo *tab, Relation rel,
 			ATExecDropNotNull(rel, cmd->name, lockmode);
 			break;
 		case AT_SetNotNull:		/* ALTER COLUMN SET NOT NULL */
-			ATExecSetNotNull(tab, rel, cmd->name, lockmode);
+			ATExecSetNotNull(wqueue, tab, rel, NULL, cmd->name, lockmode);
 			break;
 		case AT_SetStatistics:	/* ALTER COLUMN SET STATISTICS */
 			ATExecSetStatistics(rel, cmd->name, cmd->def, lockmode);
@@ -4835,8 +4865,8 @@ ATExecDropNotNull(Relation rel, const char *colName, LOCKMODE lockmode)
  * ALTER TABLE ALTER COLUMN SET NOT NULL
  */
 static void
-ATExecSetNotNull(AlteredTableInfo *tab, Relation rel,
-				 const char *colName, LOCKMODE lockmode)
+ATExecSetNotNull(List **wqueue, AlteredTableInfo *tab, Relation rel,
+				 char *constrname, const char *colName, LOCKMODE lockmode)
 {
 	HeapTuple	tuple;
 	AttrNumber	attnum;
@@ -4869,6 +4899,8 @@ ATExecSetNotNull(AlteredTableInfo *tab, Relation rel,
 	 */
 	if (!((Form_pg_attribute) GETSTRUCT(tuple))->attnotnull)
 	{
+		Constraint	*newconstr;
+
 		((Form_pg_attribute) GETSTRUCT(tuple))->attnotnull = TRUE;
 
 		simple_heap_update(attr_rel, &tuple->t_self, tuple);
@@ -4876,6 +4908,18 @@ ATExecSetNotNull(AlteredTableInfo *tab, Relation rel,
 		/* keep the system catalog indexes current */
 		CatalogUpdateIndexes(attr_rel, tuple);
 
+		/*
+		 * We also need to add a new pg_constraint row.  Use
+		 * ATAddCheckConstraint_internal for that, but let it know that it
+		 * doesn't need to test the constraint; we will tell phase 3 to do it.
+		 */
+		newconstr = createCheckNotNullConstraint(rel->rd_rel->relnamespace,
+												 constrname,
+												 NameStr(rel->rd_rel->relname),
+												 colName);
+
+		ATAddCheckConstraint_internal(wqueue, tab, rel, newconstr,
+									  true, false, false, lockmode);
 		/* Tell Phase 3 it needs to test the constraint */
 		tab->new_notnull = true;
 	}
@@ -5569,6 +5613,39 @@ ATAddCheckConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel,
 					 Constraint *constr, bool recurse, bool recursing,
 					 LOCKMODE lockmode)
 {
+	char *colname;
+
+	/*
+	 * If the constraint we're adding is CHECK (col IS NOT NULL), then we route
+	 * it through ATExecSetNotNull instead of working directly with it; that
+	 * function is responsible for getting back to us to recurse, etc.
+	 *
+	 * The reason for this is to get the attnotnull bit set for the column, and
+	 * also to avoid having a second NOT NULL constraint for a column that
+	 * might already have one.  (XXX is the latter actually a desirable
+	 * property?  Consider inherited tables here.)
+	 */
+	Assert(constr->contype == CONSTR_CHECK);
+
+	colname = tryExtractNotNullFromCheckConstr(constr);
+	if (colname != NULL)
+	{
+		ATExecSetNotNull(wqueue, tab, rel, constr->conname,
+						 colname, lockmode);
+		return;
+	}
+
+
+	/* Not a single-column NOT NULL constraint -- do the regular dance */
+	ATAddCheckConstraint_internal(wqueue, tab, rel, constr, recurse,
+								  recursing, true, lockmode);
+}
+
+static void
+ATAddCheckConstraint_internal(List **wqueue, AlteredTableInfo *tab,
+							  Relation rel, Constraint *constr, bool recurse,
+							  bool recursing, bool check_it, LOCKMODE lockmode)
+{
 	List	   *newcons;
 	ListCell   *lcon;
 	List	   *children;
@@ -5668,8 +5745,9 @@ ATAddCheckConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel,
 		childtab = ATGetQueueEntry(wqueue, childrel);
 
 		/* Recurse to child */
-		ATAddCheckConstraint(wqueue, childtab, childrel,
-							 constr, recurse, true, lockmode);
+		ATAddCheckConstraint_internal(wqueue, childtab, childrel,
+									  constr, recurse, true, check_it,
+									  lockmode);
 
 		heap_close(childrel, NoLock);
 	}
@@ -10155,7 +10233,7 @@ AtEOSubXact_on_commit_actions(bool isCommit, SubTransactionId mySubid,
  * This is intended as a callback for RangeVarGetRelidExtended().  It allows
  * the table to be locked only if (1) it's a plain table or TOAST table and
  * (2) the current user is the owner (or the superuser).  This meets the
- * permission-checking needs of both CLUTER and REINDEX TABLE; we expose it
+ * permission-checking needs of both CLUSTER and REINDEX TABLE; we expose it
  * here so that it can be used by both.
  */
 void
diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c
index c22c6ed..f50dbc4 100644
--- a/src/backend/parser/parse_utilcmd.c
+++ b/src/backend/parser/parse_utilcmd.c
@@ -37,6 +37,7 @@
 #include "catalog/pg_operator.h"
 #include "catalog/pg_type.h"
 #include "commands/comment.h"
+#include "commands/constraint.h"
 #include "commands/defrem.h"
 #include "commands/tablecmds.h"
 #include "commands/tablespace.h"
@@ -75,6 +76,7 @@ typedef struct
 	List	   *ckconstraints;	/* CHECK constraints */
 	List	   *fkconstraints;	/* FOREIGN KEY constraints */
 	List	   *ixconstraints;	/* index-creating constraints */
+	List	   *notnulls;		/* list of column names declared NOT NULL */
 	List	   *inh_indexes;	/* cloned indexes from INCLUDING INDEXES */
 	List	   *blist;			/* "before list" of things to do before
 								 * creating the table */
@@ -100,6 +102,8 @@ typedef struct
 
 static void transformColumnDefinition(CreateStmtContext *cxt,
 						  ColumnDef *column);
+static Constraint *transformNotNullConstraint(CreateStmtContext *cxt,
+						   Constraint *constraint, ColumnDef *column);
 static void transformTableConstraint(CreateStmtContext *cxt,
 						 Constraint *constraint);
 static void transformTableLikeClause(CreateStmtContext *cxt,
@@ -205,6 +209,7 @@ transformCreateStmt(CreateStmt *stmt, const char *queryString)
 	cxt.ckconstraints = NIL;
 	cxt.fkconstraints = NIL;
 	cxt.ixconstraints = NIL;
+	cxt.notnulls = NIL;
 	cxt.inh_indexes = NIL;
 	cxt.blist = NIL;
 	cxt.alist = NIL;
@@ -269,6 +274,7 @@ transformCreateStmt(CreateStmt *stmt, const char *queryString)
 	 */
 	stmt->tableElts = cxt.columns;
 	stmt->constraints = cxt.ckconstraints;
+	stmt->notnullcols = cxt.notnulls;
 
 	result = lappend(cxt.blist, stmt);
 	result = list_concat(result, cxt.alist);
@@ -471,6 +477,9 @@ transformColumnDefinition(CreateStmtContext *cxt, ColumnDef *column)
 
 	foreach(clist, column->constraints)
 	{
+		Constraint *newckconstr;
+		char	   *colname;
+
 		constraint = lfirst(clist);
 		Assert(IsA(constraint, Constraint));
 
@@ -489,6 +498,11 @@ transformColumnDefinition(CreateStmtContext *cxt, ColumnDef *column)
 				break;
 
 			case CONSTR_NOTNULL:
+				/*
+				 * For NOT NULL declarations, we need to mark the column as
+				 * not nullable; and furthermore we need to create a new
+				 * CHECK constraint for this.
+				 */
 				if (saw_nullable && !column->is_not_null)
 					ereport(ERROR,
 							(errcode(ERRCODE_SYNTAX_ERROR),
@@ -497,6 +511,9 @@ transformColumnDefinition(CreateStmtContext *cxt, ColumnDef *column)
 							 parser_errposition(cxt->pstate,
 												constraint->location)));
 				column->is_not_null = TRUE;
+				newckconstr = transformNotNullConstraint(cxt, constraint,
+														 column);
+				cxt->ckconstraints = lappend(cxt->ckconstraints, newckconstr);
 				saw_nullable = true;
 				break;
 
@@ -515,6 +532,21 @@ 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, simply set the is_not_null flag in the column
+				 * definition; otherwise remember the column name for later.
+				 */
+				colname = tryExtractNotNullFromCheckConstr(constraint);
+				if (colname != NULL)
+				{
+					if (strcmp(colname, column->colname) == 0)
+						column->is_not_null = true;
+					else
+						cxt->notnulls = lappend(cxt->notnulls, colname);
+				}
 				break;
 
 			case CONSTR_PRIMARY:
@@ -586,6 +618,8 @@ transformColumnDefinition(CreateStmtContext *cxt, ColumnDef *column)
 static void
 transformTableConstraint(CreateStmtContext *cxt, Constraint *constraint)
 {
+	char *colname;
+
 	switch (constraint->contype)
 	{
 		case CONSTR_PRIMARY:
@@ -596,6 +630,9 @@ transformTableConstraint(CreateStmtContext *cxt, Constraint *constraint)
 
 		case CONSTR_CHECK:
 			cxt->ckconstraints = lappend(cxt->ckconstraints, constraint);
+			colname = tryExtractNotNullFromCheckConstr(constraint);
+			if (colname != NULL)
+				cxt->notnulls = lappend(cxt->notnulls, colname);
 			break;
 
 		case CONSTR_FOREIGN:
@@ -621,6 +658,30 @@ transformTableConstraint(CreateStmtContext *cxt, Constraint *constraint)
 }
 
 /*
+ * Given a NOT NULL column declaration, transform it into a new Constraint node
+ * representing the equivalent CHECK (col) IS NOT NULL.
+ */
+static Constraint *
+transformNotNullConstraint(CreateStmtContext *cxt, Constraint *constraint,
+						   ColumnDef *column)
+{
+	Constraint *check;
+	Oid		nspid;
+
+	if (cxt->rel)
+		nspid = RelationGetNamespace(cxt->rel);
+	else
+		nspid = RangeVarGetCreationNamespace(cxt->relation);
+
+	check = createCheckNotNullConstraint(nspid,
+										 NULL,
+										 cxt->relation->relname,
+										 column->colname);
+
+	return check;
+}
+
+/*
  * transformTableLikeClause
  *
  * Change the LIKE <srctable> portion of a CREATE TABLE statement into
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index 09ca6dd..95bb2d5 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -5729,7 +5729,35 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables)
 
 		resetPQExpBuffer(q);
 
-		if (fout->remoteVersion >= 90200)
+		if (fout->remoteVersion >= 90300)
+		{
+			/*
+			 * In 9.3, NOT NULL constraints are in pg_constraint and will be
+			 * dumped as table constraints, so it's unnecessary to dump them
+			 * here.
+			 */
+			appendPQExpBuffer(q, "SELECT a.attnum, a.attname, a.atttypmod, "
+							  "a.attstattarget, a.attstorage, t.typstorage, "
+							  "false as attnotnull, a.atthasdef, a.attisdropped, "
+							  "a.attlen, a.attalign, a.attislocal, "
+				  "pg_catalog.format_type(t.oid,a.atttypmod) AS atttypname, "
+						"array_to_string(a.attoptions, ', ') AS attoptions, "
+							  "CASE WHEN a.attcollation <> t.typcollation "
+						   "THEN a.attcollation ELSE 0 END AS attcollation, "
+							  "pg_catalog.array_to_string(ARRAY("
+							  "SELECT pg_catalog.quote_ident(option_name) || "
+							  "' ' || pg_catalog.quote_literal(option_value) "
+						"FROM pg_catalog.pg_options_to_table(attfdwoptions) "
+							  "ORDER BY option_name"
+							  "), E',\n    ') AS attfdwoptions "
+			 "FROM pg_catalog.pg_attribute a LEFT JOIN pg_catalog.pg_type t "
+							  "ON a.atttypid = t.oid "
+							  "WHERE a.attrelid = '%u'::pg_catalog.oid "
+							  "AND a.attnum > 0::pg_catalog.int2 "
+							  "ORDER BY a.attrelid, a.attnum",
+							  tbinfo->dobj.catId.oid);
+		}
+		else if (fout->remoteVersion >= 90200)
 		{
 			/*
 			 * attfdwoptions is new in 9.2.
diff --git a/src/include/commands/constraint.h b/src/include/commands/constraint.h
new file mode 100644
index 0000000..e18d549
--- /dev/null
+++ b/src/include/commands/constraint.h
@@ -0,0 +1,11 @@
+#ifndef CONSTRAINT_H
+#define CONSTRAINT_H
+
+#include "nodes/parsenodes.h"
+
+extern char *tryExtractNotNullFromCheckConstr(Constraint *constr);
+
+extern Constraint *createCheckNotNullConstraint(Oid nspid, char *constraint_name,
+							 const char *relname, const char *colname);
+
+#endif /* CONSTRAINT_H */
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 1f89cd5..8522f53 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -1436,10 +1436,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).
  * ----------------------
  */
 
@@ -1452,6 +1453,7 @@ typedef struct CreateStmt
 								 * inhRelation) */
 	TypeName   *ofTypename;		/* OF typename */
 	List	   *constraints;	/* constraints (list of Constraint nodes) */
+	List	   *notnullcols;	/* list of column names with NOT NULL */
 	List	   *options;		/* options from WITH clause */
 	OnCommitAction oncommit;	/* what do we do at COMMIT? */
 	char	   *tablespacename; /* table space to use, or NULL */