Fix all known problems with pg_dump's handling of serial sequences

by abandoning the idea that it should say SERIAL in the dump.  Instead,
dump serial sequences and column defaults just like regular ones.
Add a new backend command ALTER SEQUENCE OWNED BY to let pg_dump recreate
the sequence-to-column dependency that was formerly created "behind the
scenes" by SERIAL.  This restores SERIAL to being truly "just a macro"
consisting of component operations that can be stated explicitly in SQL.
Furthermore, the new command allows sequence ownership to be reassigned,
so that old mistakes can be cleaned up.

Also, downgrade the OWNED-BY dependency from INTERNAL to AUTO, since there
is no longer any very compelling argument why the sequence couldn't be
dropped while keeping the column.  (This forces initdb, to be sure the
right kinds of dependencies are in there.)

Along the way, add checks to prevent ALTER OWNER or SET SCHEMA on an
owned sequence; you can now only do this indirectly by changing the
owning table's owner or schema.  This is an oversight in previous
releases, but probably not worth back-patching.

12 files changed, 355 insertions, 191 deletions

diff --git a/src/backend/catalog/pg_depend.c b/src/backend/catalog/pg_depend.c
index 5a297d4a275..99cdf5e7e6a 100644
--- a/src/backend/catalog/pg_depend.c
+++ b/src/backend/catalog/pg_depend.c
@@ -8,7 +8,7 @@
  *
  *
  * IDENTIFICATION
- *	  $PostgreSQL: pgsql/src/backend/catalog/pg_depend.c,v 1.21 2006/07/11 17:26:58 momjian Exp $
+ *	  $PostgreSQL: pgsql/src/backend/catalog/pg_depend.c,v 1.22 2006/08/21 00:57:24 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -163,58 +163,6 @@ deleteDependencyRecordsFor(Oid classId, Oid objectId)
 }
 
 /*
- * objectIsInternalDependency -- return whether the specified object
- * is listed as an internal dependency for some other object.
- *
- * This is used to implement DROP/REASSIGN OWNED.  We cannot invoke
- * performDeletion blindly, because it may try to drop or modify an internal-
- * dependent object before the "main" object, so we need to skip the first
- * object and expect it to be automatically dropped when the main object is
- * dropped.
- */
-bool
-objectIsInternalDependency(Oid classId, Oid objectId)
-{
-	Relation	depRel;
-	ScanKeyData key[2];
-	SysScanDesc scan;
-	HeapTuple	tup;
-	bool		isdep = false;
-
-	depRel = heap_open(DependRelationId, AccessShareLock);
-
-	ScanKeyInit(&key[0],
-				Anum_pg_depend_classid,
-				BTEqualStrategyNumber, F_OIDEQ,
-				ObjectIdGetDatum(classId));
-	ScanKeyInit(&key[1],
-				Anum_pg_depend_objid,
-				BTEqualStrategyNumber, F_OIDEQ,
-				ObjectIdGetDatum(objectId));
-
-	scan = systable_beginscan(depRel, DependDependerIndexId, true,
-							  SnapshotNow, 2, key);
-
-	while (HeapTupleIsValid(tup = systable_getnext(scan)))
-	{
-		Form_pg_depend depForm = (Form_pg_depend) GETSTRUCT(tup);
-
-		if (depForm->deptype == DEPENDENCY_INTERNAL)
-		{
-			/* No need to keep scanning */
-			isdep = true;
-			break;
-		}
-	}
-
-	systable_endscan(scan);
-
-	heap_close(depRel, AccessShareLock);
-
-	return isdep;
-}
-
-/*
  * Adjust dependency record(s) to point to a different object of the same type
  *
  * classId/objectId specify the referencing object.
@@ -313,6 +261,105 @@ changeDependencyFor(Oid classId, Oid objectId,
 }
 
 /*
+ * Detect whether a sequence is marked as "owned" by a column
+ *
+ * An ownership marker is an AUTO dependency from the sequence to the
+ * column.  If we find one, store the identity of the owning column
+ * into *tableId and *colId and return TRUE; else return FALSE.
+ *
+ * Note: if there's more than one such pg_depend entry then you get
+ * a random one of them returned into the out parameters.  This should
+ * not happen, though.
+ */
+bool
+sequenceIsOwned(Oid seqId, Oid *tableId, int32 *colId)
+{
+	bool		ret = false;
+	Relation	depRel;
+	ScanKeyData key[2];
+	SysScanDesc scan;
+	HeapTuple	tup;
+
+	depRel = heap_open(DependRelationId, AccessShareLock);
+
+	ScanKeyInit(&key[0],
+				Anum_pg_depend_classid,
+				BTEqualStrategyNumber, F_OIDEQ,
+				ObjectIdGetDatum(RelationRelationId));
+	ScanKeyInit(&key[1],
+				Anum_pg_depend_objid,
+				BTEqualStrategyNumber, F_OIDEQ,
+				ObjectIdGetDatum(seqId));
+
+	scan = systable_beginscan(depRel, DependDependerIndexId, true,
+							  SnapshotNow, 2, key);
+
+	while (HeapTupleIsValid((tup = systable_getnext(scan))))
+	{
+		Form_pg_depend depform = (Form_pg_depend) GETSTRUCT(tup);
+
+		if (depform->refclassid == RelationRelationId &&
+			depform->deptype == DEPENDENCY_AUTO)
+		{
+			*tableId = depform->refobjid;
+			*colId = depform->refobjsubid;
+			ret = true;
+			break;				/* no need to keep scanning */
+		}
+	}
+
+	systable_endscan(scan);
+
+	heap_close(depRel, AccessShareLock);
+
+	return ret;
+}
+
+/*
+ * Remove any existing "owned" markers for the specified sequence.
+ *
+ * Note: we don't provide a special function to install an "owned"
+ * marker; just use recordDependencyOn().
+ */
+void
+markSequenceUnowned(Oid seqId)
+{
+	Relation	depRel;
+	ScanKeyData key[2];
+	SysScanDesc scan;
+	HeapTuple	tup;
+
+	depRel = heap_open(DependRelationId, RowExclusiveLock);
+
+	ScanKeyInit(&key[0],
+				Anum_pg_depend_classid,
+				BTEqualStrategyNumber, F_OIDEQ,
+				ObjectIdGetDatum(RelationRelationId));
+	ScanKeyInit(&key[1],
+				Anum_pg_depend_objid,
+				BTEqualStrategyNumber, F_OIDEQ,
+				ObjectIdGetDatum(seqId));
+
+	scan = systable_beginscan(depRel, DependDependerIndexId, true,
+							  SnapshotNow, 2, key);
+
+	while (HeapTupleIsValid((tup = systable_getnext(scan))))
+	{
+		Form_pg_depend depform = (Form_pg_depend) GETSTRUCT(tup);
+
+		if (depform->refclassid == RelationRelationId &&
+			depform->deptype == DEPENDENCY_AUTO)
+		{
+			simple_heap_delete(depRel, &tup->t_self);
+		}
+	}
+
+	systable_endscan(scan);
+
+	heap_close(depRel, RowExclusiveLock);
+}
+
+/*
  * isObjectPinned()
  *
  * Test if an object is required for basic database functionality.
diff --git a/src/backend/catalog/pg_shdepend.c b/src/backend/catalog/pg_shdepend.c
index 31f1f654de0..85e3d968d48 100644
--- a/src/backend/catalog/pg_shdepend.c
+++ b/src/backend/catalog/pg_shdepend.c
@@ -8,7 +8,7 @@
  *
  *
  * IDENTIFICATION
- *	  $PostgreSQL: pgsql/src/backend/catalog/pg_shdepend.c,v 1.13 2006/08/20 21:56:16 alvherre Exp $
+ *	  $PostgreSQL: pgsql/src/backend/catalog/pg_shdepend.c,v 1.14 2006/08/21 00:57:24 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -17,6 +17,7 @@
 #include "access/genam.h"
 #include "access/heapam.h"
 #include "access/xact.h"
+#include "catalog/catalog.h"
 #include "catalog/dependency.h"
 #include "catalog/indexing.h"
 #include "catalog/pg_authid.h"
@@ -869,30 +870,17 @@ shdepDropDependency(Relation sdepRel, Oid classId, Oid objectId,
  * Get the database Id that should be used in pg_shdepend, given the OID
  * of the catalog containing the object.  For shared objects, it's 0
  * (InvalidOid); for all other objects, it's the current database Id.
- *
- * XXX it's awfully tempting to hard-wire this instead of doing a syscache
- * lookup ... but resist the temptation, unless you can prove it's a
- * bottleneck.
  */
 static Oid
 classIdGetDbId(Oid classId)
 {
 	Oid			dbId;
-	HeapTuple	tup;
-
-	tup = SearchSysCache(RELOID,
-						 ObjectIdGetDatum(classId),
-						 0, 0, 0);
-	if (!HeapTupleIsValid(tup))
-		elog(ERROR, "cache lookup failed for relation %u", classId);
 
-	if (((Form_pg_class) GETSTRUCT(tup))->relisshared)
+	if (IsSharedRelation(classId))
 		dbId = InvalidOid;
 	else
 		dbId = MyDatabaseId;
 
-	ReleaseSysCache(tup);
-
 	return dbId;
 }
 
@@ -1055,6 +1043,11 @@ isSharedObjectPinned(Oid classId, Oid objectId, Relation sdepRel)
  * Drop the objects owned by any one of the given RoleIds.	If a role has
  * access to an object, the grant will be removed as well (but the object
  * will not, of course.)
+ *
+ * We can revoke grants immediately while doing the scan, but drops are
+ * saved up and done all at once with performMultipleDeletions.  This
+ * is necessary so that we don't get failures from trying to delete
+ * interdependent objects in the wrong order.
  */
 void
 shdepDropOwned(List *roleids, DropBehavior behavior)
@@ -1113,7 +1106,7 @@ shdepDropOwned(List *roleids, DropBehavior behavior)
 			InternalGrant	istmt;
 			Form_pg_shdepend sdepForm = (Form_pg_shdepend) GETSTRUCT(tuple);
 
-			/* We only operate on objects on the current database */
+			/* We only operate on objects in the current database */
 			if (sdepForm->dbid != MyDatabaseId)
 				continue;
 
@@ -1128,24 +1121,8 @@ shdepDropOwned(List *roleids, DropBehavior behavior)
 					switch (sdepForm->classid)
 					{
 						case RelationRelationId:
-							{
-								/* is it a sequence or non-sequence? */
-								Form_pg_class pg_class_tuple;
-								HeapTuple	tuple;
-
-								tuple = SearchSysCache(RELOID,
-													   ObjectIdGetDatum(sdepForm->objid),
-													   0, 0, 0);
-								if (!HeapTupleIsValid(tuple))
-									elog(ERROR, "cache lookup failed for relation %u",
-										 sdepForm->objid);
-								pg_class_tuple = (Form_pg_class) GETSTRUCT(tuple);
-								if (pg_class_tuple->relkind == RELKIND_SEQUENCE)
-									istmt.objtype = ACL_OBJECT_SEQUENCE;
-								else
-									istmt.objtype = ACL_OBJECT_RELATION;
-								ReleaseSysCache(tuple);
-							}
+							/* it's OK to use RELATION for a sequence */
+							istmt.objtype = ACL_OBJECT_RELATION;
 							break;
 						case DatabaseRelationId:
 							istmt.objtype = ACL_OBJECT_DATABASE;
@@ -1180,11 +1157,10 @@ shdepDropOwned(List *roleids, DropBehavior behavior)
 					ExecGrantStmt_oids(&istmt);
 					break;
 				case SHARED_DEPENDENCY_OWNER:
-					/* Save it for later deleting it */
+					/* Save it for deletion below */
 					obj.classId = sdepForm->classid;
 					obj.objectId = sdepForm->objid;
 					obj.objectSubId = 0;
-
 					add_exact_object_address(&obj, deleteobjs);
 					break;
 			}
@@ -1259,7 +1235,7 @@ shdepReassignOwned(List *roleids, Oid newrole)
 		{
 			Form_pg_shdepend sdepForm = (Form_pg_shdepend) GETSTRUCT(tuple);
 
-			/* We only operate on objects on the current database */
+			/* We only operate on objects in the current database */
 			if (sdepForm->dbid != MyDatabaseId)
 				continue;
 
@@ -1271,15 +1247,7 @@ shdepReassignOwned(List *roleids, Oid newrole)
 			if (sdepForm->deptype != SHARED_DEPENDENCY_OWNER)
 				continue;
 
-			/*
-			 * If there's a regular (non-shared) dependency on this object
-			 * marked with DEPENDENCY_INTERNAL, skip this object.  We will
-			 * alter the referencer object instead.
-			 */
-			if (objectIsInternalDependency(sdepForm->classid, sdepForm->objid))
-				continue;
-
-			/* Issue the appropiate ALTER OWNER call */
+			/* Issue the appropriate ALTER OWNER call */
 			switch (sdepForm->classid)
 			{
 				case ConversionRelationId:
@@ -1299,7 +1267,12 @@ shdepReassignOwned(List *roleids, Oid newrole)
 					break;
 
 				case RelationRelationId:
-					ATExecChangeOwner(sdepForm->objid, newrole, false);
+					/*
+					 * Pass recursing = true so that we don't fail on
+					 * indexes, owned sequences, etc when we happen
+					 * to visit them before their parent table.
+					 */
+					ATExecChangeOwner(sdepForm->objid, newrole, true);
 					break;
 
 				case ProcedureRelationId:
diff --git a/src/backend/commands/sequence.c b/src/backend/commands/sequence.c
index 6154a4ed3da..865c2f60fe5 100644
--- a/src/backend/commands/sequence.c
+++ b/src/backend/commands/sequence.c
@@ -8,7 +8,7 @@
  *
  *
  * IDENTIFICATION
- *	  $PostgreSQL: pgsql/src/backend/commands/sequence.c,v 1.138 2006/07/31 20:09:00 tgl Exp $
+ *	  $PostgreSQL: pgsql/src/backend/commands/sequence.c,v 1.139 2006/08/21 00:57:24 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -17,6 +17,7 @@
 #include "access/heapam.h"
 #include "access/transam.h"
 #include "access/xact.h"
+#include "catalog/dependency.h"
 #include "catalog/namespace.h"
 #include "catalog/pg_type.h"
 #include "commands/defrem.h"
@@ -26,6 +27,7 @@
 #include "nodes/makefuncs.h"
 #include "utils/acl.h"
 #include "utils/builtins.h"
+#include "utils/lsyscache.h"
 #include "utils/resowner.h"
 #include "utils/syscache.h"
 
@@ -82,8 +84,11 @@ static int64 nextval_internal(Oid relid);
 static Relation open_share_lock(SeqTable seq);
 static void init_sequence(Oid relid, SeqTable *p_elm, Relation *p_rel);
 static Form_pg_sequence read_info(SeqTable elm, Relation rel, Buffer *buf);
-static void init_params(List *options, Form_pg_sequence new, bool isInit);
+static void init_params(List *options, bool isInit,
+						Form_pg_sequence new, List **owned_by);
 static void do_setval(Oid relid, int64 next, bool iscalled);
+static void process_owned_by(Relation seqrel, List *owned_by);
+
 
 /*
  * DefineSequence
@@ -93,6 +98,7 @@ void
 DefineSequence(CreateSeqStmt *seq)
 {
 	FormData_pg_sequence new;
+	List	   *owned_by;
 	CreateStmt *stmt = makeNode(CreateStmt);
 	Oid			seqoid;
 	Relation	rel;
@@ -107,7 +113,7 @@ DefineSequence(CreateSeqStmt *seq)
 	NameData	name;
 
 	/* Check and set all option values */
-	init_params(seq->options, &new, true);
+	init_params(seq->options, true, &new, &owned_by);
 
 	/*
 	 * Create relation (and fill *null & *value)
@@ -123,7 +129,6 @@ DefineSequence(CreateSeqStmt *seq)
 		coldef->raw_default = NULL;
 		coldef->cooked_default = NULL;
 		coldef->constraints = NIL;
-		coldef->support = NULL;
 
 		null[i - 1] = ' ';
 
@@ -287,6 +292,10 @@ DefineSequence(CreateSeqStmt *seq)
 
 	UnlockReleaseBuffer(buf);
 
+	/* process OWNED BY if given */
+	if (owned_by)
+		process_owned_by(rel, owned_by);
+
 	heap_close(rel, NoLock);
 }
 
@@ -305,6 +314,7 @@ AlterSequence(AlterSeqStmt *stmt)
 	Page		page;
 	Form_pg_sequence seq;
 	FormData_pg_sequence new;
+	List	   *owned_by;
 
 	/* open and AccessShareLock sequence */
 	relid = RangeVarGetRelid(stmt->sequence, false);
@@ -323,7 +333,7 @@ AlterSequence(AlterSeqStmt *stmt)
 	memcpy(&new, seq, sizeof(FormData_pg_sequence));
 
 	/* Check and set new values */
-	init_params(stmt->options, &new, false);
+	init_params(stmt->options, false, &new, &owned_by);
 
 	/* Now okay to update the on-disk tuple */
 	memcpy(seq, &new, sizeof(FormData_pg_sequence));
@@ -366,6 +376,10 @@ AlterSequence(AlterSeqStmt *stmt)
 
 	UnlockReleaseBuffer(buf);
 
+	/* process OWNED BY if given */
+	if (owned_by)
+		process_owned_by(seqrel, owned_by);
+
 	relation_close(seqrel, NoLock);
 }
 
@@ -933,13 +947,15 @@ read_info(SeqTable elm, Relation rel, Buffer *buf)
 
 /*
  * init_params: process the options list of CREATE or ALTER SEQUENCE,
- * and store the values into appropriate fields of *new.
+ * and store the values into appropriate fields of *new.  Also set
+ * *owned_by to any OWNED BY option, or to NIL if there is none.
  *
  * If isInit is true, fill any unspecified options with default values;
  * otherwise, do not change existing options that aren't explicitly overridden.
  */
 static void
-init_params(List *options, Form_pg_sequence new, bool isInit)
+init_params(List *options, bool isInit,
+			Form_pg_sequence new, List **owned_by)
 {
 	DefElem    *last_value = NULL;
 	DefElem    *increment_by = NULL;
@@ -949,6 +965,8 @@ init_params(List *options, Form_pg_sequence new, bool isInit)
 	DefElem    *is_cycled = NULL;
 	ListCell   *option;
 
+	*owned_by = NIL;
+
 	foreach(option, options)
 	{
 		DefElem    *defel = (DefElem *) lfirst(option);
@@ -1006,6 +1024,14 @@ init_params(List *options, Form_pg_sequence new, bool isInit)
 						 errmsg("conflicting or redundant options")));
 			is_cycled = defel;
 		}
+		else if (strcmp(defel->defname, "owned_by") == 0)
+		{
+			if (*owned_by)
+				ereport(ERROR,
+						(errcode(ERRCODE_SYNTAX_ERROR),
+						 errmsg("conflicting or redundant options")));
+			*owned_by = defGetQualifiedName(defel);
+		}
 		else
 			elog(ERROR, "option \"%s\" not recognized",
 				 defel->defname);
@@ -1130,6 +1156,99 @@ init_params(List *options, Form_pg_sequence new, bool isInit)
 		new->cache_value = 1;
 }
 
+/*
+ * Process an OWNED BY option for CREATE/ALTER SEQUENCE
+ *
+ * Ownership permissions on the sequence are already checked,
+ * but if we are establishing a new owned-by dependency, we must
+ * enforce that the referenced table has the same owner and namespace
+ * as the sequence.
+ */
+static void
+process_owned_by(Relation seqrel, List *owned_by)
+{
+	int			nnames;
+	Relation	tablerel;
+	AttrNumber	attnum;
+
+	nnames = list_length(owned_by);
+	Assert(nnames > 0);
+	if (nnames == 1)
+	{
+		/* Must be OWNED BY NONE */
+		if (strcmp(strVal(linitial(owned_by)), "none") != 0)
+			ereport(ERROR,
+					(errcode(ERRCODE_SYNTAX_ERROR),
+					 errmsg("invalid OWNED BY option"),
+					 errhint("Specify OWNED BY table.column or OWNED BY NONE.")));
+		tablerel = NULL;
+		attnum = 0;
+	}
+	else
+	{
+		List	   *relname;
+		char	   *attrname;
+		RangeVar   *rel;
+
+		/* Separate relname and attr name */
+		relname = list_truncate(list_copy(owned_by), nnames - 1);
+		attrname = strVal(lfirst(list_tail(owned_by)));
+
+		/* Open and lock rel to ensure it won't go away meanwhile */
+		rel = makeRangeVarFromNameList(relname);
+		tablerel = relation_openrv(rel, AccessShareLock);
+
+		/* Must be a regular table */
+		if (tablerel->rd_rel->relkind != RELKIND_RELATION)
+			ereport(ERROR,
+					(errcode(ERRCODE_WRONG_OBJECT_TYPE),
+					 errmsg("referenced relation \"%s\" is not a table",
+							RelationGetRelationName(tablerel))));
+
+		/* We insist on same owner and schema */
+		if (seqrel->rd_rel->relowner != tablerel->rd_rel->relowner)
+			ereport(ERROR,
+					(errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
+					 errmsg("sequence must have same owner as table it is owned by")));
+		if (RelationGetNamespace(seqrel) != RelationGetNamespace(tablerel))
+			ereport(ERROR,
+					(errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
+					 errmsg("sequence must be in same schema as table it is owned by")));
+
+		/* Now, fetch the attribute number from the system cache */
+		attnum = get_attnum(RelationGetRelid(tablerel), attrname);
+		if (attnum == InvalidAttrNumber)
+			ereport(ERROR,
+					(errcode(ERRCODE_UNDEFINED_COLUMN),
+					 errmsg("column \"%s\" of relation \"%s\" does not exist",
+							attrname, RelationGetRelationName(tablerel))));
+	}
+
+	/*
+	 * OK, we are ready to update pg_depend.  First remove any existing
+	 * AUTO dependencies for the sequence, then optionally add a new one.
+	 */
+	markSequenceUnowned(RelationGetRelid(seqrel));
+
+	if (tablerel)
+	{
+		ObjectAddress refobject,
+					depobject;
+
+		refobject.classId = RelationRelationId;
+		refobject.objectId = RelationGetRelid(tablerel);
+		refobject.objectSubId = attnum;
+		depobject.classId = RelationRelationId;
+		depobject.objectId = RelationGetRelid(seqrel);
+		depobject.objectSubId = 0;
+		recordDependencyOn(&depobject, &refobject, DEPENDENCY_AUTO);
+	}
+
+	/* Done, but hold lock until commit */
+	if (tablerel)
+		relation_close(tablerel, NoLock);
+}
+
 
 void
 seq_redo(XLogRecPtr lsn, XLogRecord *record)
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index cd4c4eb2304..a1f76033377 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -8,7 +8,7 @@
  *
  *
  * IDENTIFICATION
- *	  $PostgreSQL: pgsql/src/backend/commands/tablecmds.c,v 1.199 2006/08/03 20:57:06 tgl Exp $
+ *	  $PostgreSQL: pgsql/src/backend/commands/tablecmds.c,v 1.200 2006/08/21 00:57:24 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -209,8 +209,6 @@ static void ATPrepAddColumn(List **wqueue, Relation rel, bool recurse,
 static void ATExecAddColumn(AlteredTableInfo *tab, Relation rel,
 				ColumnDef *colDef);
 static void add_column_datatype_dependency(Oid relid, int32 attnum, Oid typid);
-static void add_column_support_dependency(Oid relid, int32 attnum,
-							  RangeVar *support);
 static void ATExecDropNotNull(Relation rel, const char *colName);
 static void ATExecSetNotNull(AlteredTableInfo *tab, Relation rel,
 				 const char *colName);
@@ -476,10 +474,6 @@ DefineRelation(CreateStmt *stmt, char relkind)
 	 * work unless we have a pre-existing relation. So, the transformation has
 	 * to be postponed to this final step of CREATE TABLE.
 	 *
-	 * Another task that's conveniently done at this step is to add dependency
-	 * links between columns and supporting relations (such as SERIAL
-	 * sequences).
-	 *
 	 * First, scan schema to find new column defaults.
 	 */
 	rawDefaults = NIL;
@@ -502,10 +496,6 @@ DefineRelation(CreateStmt *stmt, char relkind)
 			rawEnt->raw_default = colDef->raw_default;
 			rawDefaults = lappend(rawDefaults, rawEnt);
 		}
-
-		/* Create dependency for supporting relation for this column */
-		if (colDef->support != NULL)
-			add_column_support_dependency(relationId, attnum, colDef->support);
 	}
 
 	/*
@@ -944,7 +934,6 @@ MergeAttributes(List *schema, List *supers, bool istemp,
 				def->raw_default = NULL;
 				def->cooked_default = NULL;
 				def->constraints = NIL;
-				def->support = NULL;
 				inhSchema = lappend(inhSchema, def);
 				newattno[parent_attno - 1] = ++child_attno;
 			}
@@ -1159,9 +1148,10 @@ varattnos_map(TupleDesc old, TupleDesc new)
 	return attmap;
 }
 
-/* Generate a map for change_varattnos_of_a_node from a tupledesc and a list of
- * ColumnDefs */
-
+/*
+ * Generate a map for change_varattnos_of_a_node from a tupledesc and a list of
+ * ColumnDefs
+ */
 AttrNumber *
 varattnos_map_schema(TupleDesc old, List *schema) 
 {
@@ -3017,8 +3007,6 @@ ATPrepAddColumn(List **wqueue, Relation rel, bool recurse,
 		/* Child should see column as singly inherited */
 		colDefChild->inhcount = 1;
 		colDefChild->is_local = false;
-		/* and don't make a support dependency on the child */
-		colDefChild->support = NULL;
 
 		ATOneLevelRecursion(wqueue, rel, childCmd);
 	}
@@ -3259,8 +3247,6 @@ ATExecAddColumn(AlteredTableInfo *tab, Relation rel,
 	 * Add needed dependency entries for the new column.
 	 */
 	add_column_datatype_dependency(myrelid, i, attribute->atttypid);
-	if (colDef->support != NULL)
-		add_column_support_dependency(myrelid, i, colDef->support);
 }
 
 /*
@@ -3282,24 +3268,6 @@ add_column_datatype_dependency(Oid relid, int32 attnum, Oid typid)
 }
 
 /*
- * Install a dependency for a column's supporting relation (serial sequence).
- */
-static void
-add_column_support_dependency(Oid relid, int32 attnum, RangeVar *support)
-{
-	ObjectAddress colobject,
-				suppobject;
-
-	colobject.classId = RelationRelationId;
-	colobject.objectId = relid;
-	colobject.objectSubId = attnum;
-	suppobject.classId = RelationRelationId;
-	suppobject.objectId = RangeVarGetRelid(support, false);
-	suppobject.objectSubId = 0;
-	recordDependencyOn(&suppobject, &colobject, DEPENDENCY_INTERNAL);
-}
-
-/*
  * ALTER TABLE ALTER COLUMN DROP NOT NULL
  */
 static void
@@ -5444,9 +5412,9 @@ ATPostAlterTypeParse(char *cmd, List **wqueue)
 /*
  * ALTER TABLE OWNER
  *
- * recursing is true if we are recursing from a table to its indexes or
- * toast table.  We don't allow the ownership of those things to be
- * changed separately from the parent table.  Also, we can skip permission
+ * recursing is true if we are recursing from a table to its indexes,
+ * sequences, or toast table.  We don't allow the ownership of those things to
+ * be changed separately from the parent table.  Also, we can skip permission
  * checks (this is necessary not just an optimization, else we'd fail to
  * handle toast tables properly).
  */
@@ -5479,7 +5447,6 @@ ATExecChangeOwner(Oid relationOid, Oid newOwnerId, bool recursing)
 	{
 		case RELKIND_RELATION:
 		case RELKIND_VIEW:
-		case RELKIND_SEQUENCE:
 			/* ok to change owner */
 			break;
 		case RELKIND_INDEX:
@@ -5502,6 +5469,24 @@ ATExecChangeOwner(Oid relationOid, Oid newOwnerId, bool recursing)
 				newOwnerId = tuple_class->relowner;
 			}
 			break;
+		case RELKIND_SEQUENCE:
+			if (!recursing &&
+				tuple_class->relowner != newOwnerId)
+			{
+				/* if it's an owned sequence, disallow changing it by itself */
+				Oid		tableId;
+				int32	colId;
+
+				if (sequenceIsOwned(relationOid, &tableId, &colId))
+					ereport(ERROR,
+							(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+							 errmsg("cannot change owner of sequence \"%s\"",
+									NameStr(tuple_class->relname)),
+							 errdetail("Sequence \"%s\" is linked to table \"%s\".",
+									   NameStr(tuple_class->relname),
+									   get_rel_name(tableId))));
+			}
+			break;
 		case RELKIND_TOASTVALUE:
 			if (recursing)
 				break;
@@ -5644,7 +5629,7 @@ change_owner_recurse_to_sequences(Oid relationOid, Oid newOwnerId)
 	HeapTuple	tup;
 
 	/*
-	 * SERIAL sequences are those having an internal dependency on one of the
+	 * SERIAL sequences are those having an auto dependency on one of the
 	 * table's columns (we don't care *which* column, exactly).
 	 */
 	depRel = heap_open(DependRelationId, AccessShareLock);
@@ -5667,11 +5652,11 @@ change_owner_recurse_to_sequences(Oid relationOid, Oid newOwnerId)
 		Form_pg_depend depForm = (Form_pg_depend) GETSTRUCT(tup);
 		Relation	seqRel;
 
-		/* skip dependencies other than internal dependencies on columns */
+		/* skip dependencies other than auto dependencies on columns */
 		if (depForm->refobjsubid == 0 ||
 			depForm->classid != RelationRelationId ||
 			depForm->objsubid != 0 ||
-			depForm->deptype != DEPENDENCY_INTERNAL)
+			depForm->deptype != DEPENDENCY_AUTO)
 			continue;
 
 		/* Use relation_open just in case it's an index */
@@ -5686,7 +5671,7 @@ change_owner_recurse_to_sequences(Oid relationOid, Oid newOwnerId)
 		}
 
 		/* We don't need to close the sequence while we alter it. */
-		ATExecChangeOwner(depForm->objid, newOwnerId, false);
+		ATExecChangeOwner(depForm->objid, newOwnerId, true);
 
 		/* Now we can close it.  Keep the lock till end of transaction. */
 		relation_close(seqRel, NoLock);
@@ -6549,6 +6534,9 @@ AlterTableNamespace(RangeVar *relation, const char *newschema)
 
 	rel = heap_openrv(relation, AccessExclusiveLock);
 
+	relid = RelationGetRelid(rel);
+	oldNspOid = RelationGetNamespace(rel);
+
 	/* heap_openrv allows TOAST, but we don't want to */
 	if (rel->rd_rel->relkind == RELKIND_TOASTVALUE)
 		ereport(ERROR,
@@ -6556,8 +6544,20 @@ AlterTableNamespace(RangeVar *relation, const char *newschema)
 				 errmsg("\"%s\" is a TOAST relation",
 						RelationGetRelationName(rel))));
 
-	relid = RelationGetRelid(rel);
-	oldNspOid = RelationGetNamespace(rel);
+	/* if it's an owned sequence, disallow moving it by itself */
+	if (rel->rd_rel->relkind == RELKIND_SEQUENCE)
+	{
+		Oid		tableId;
+		int32	colId;
+
+		if (sequenceIsOwned(relid, &tableId, &colId))
+			ereport(ERROR,
+					(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+					 errmsg("cannot move an owned sequence into another schema"),
+					 errdetail("Sequence \"%s\" is linked to table \"%s\".",
+							   RelationGetRelationName(rel),
+							   get_rel_name(tableId))));
+	}
 
 	/* get schema OID and check its permissions */
 	nspOid = LookupCreationNamespace(newschema);
@@ -6699,7 +6699,7 @@ AlterSeqNamespaces(Relation classRel, Relation rel,
 	HeapTuple	tup;
 
 	/*
-	 * SERIAL sequences are those having an internal dependency on one of the
+	 * SERIAL sequences are those having an auto dependency on one of the
 	 * table's columns (we don't care *which* column, exactly).
 	 */
 	depRel = heap_open(DependRelationId, AccessShareLock);
@@ -6722,11 +6722,11 @@ AlterSeqNamespaces(Relation classRel, Relation rel,
 		Form_pg_depend depForm = (Form_pg_depend) GETSTRUCT(tup);
 		Relation	seqRel;
 
-		/* skip dependencies other than internal dependencies on columns */
+		/* skip dependencies other than auto dependencies on columns */
 		if (depForm->refobjsubid == 0 ||
 			depForm->classid != RelationRelationId ||
 			depForm->objsubid != 0 ||
-			depForm->deptype != DEPENDENCY_INTERNAL)
+			depForm->deptype != DEPENDENCY_AUTO)
 			continue;
 
 		/* Use relation_open just in case it's an index */
diff --git a/src/backend/commands/view.c b/src/backend/commands/view.c
index 63850cbeae7..df7f479f316 100644
--- a/src/backend/commands/view.c
+++ b/src/backend/commands/view.c
@@ -8,7 +8,7 @@
  *
  *
  * IDENTIFICATION
- *	  $PostgreSQL: pgsql/src/backend/commands/view.c,v 1.96 2006/07/13 16:49:14 momjian Exp $
+ *	  $PostgreSQL: pgsql/src/backend/commands/view.c,v 1.97 2006/08/21 00:57:24 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -126,7 +126,6 @@ DefineVirtualRelation(const RangeVar *relation, List *tlist, bool replace)
 			def->raw_default = NULL;
 			def->cooked_default = NULL;
 			def->constraints = NIL;
-			def->support = NULL;
 
 			attrList = lappend(attrList, def);
 		}
diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c
index 2b8f3af09b9..40e35a37966 100644
--- a/src/backend/nodes/copyfuncs.c
+++ b/src/backend/nodes/copyfuncs.c
@@ -15,7 +15,7 @@
  * Portions Copyright (c) 1994, Regents of the University of California
  *
  * IDENTIFICATION
- *	  $PostgreSQL: pgsql/src/backend/nodes/copyfuncs.c,v 1.347 2006/08/12 02:52:04 tgl Exp $
+ *	  $PostgreSQL: pgsql/src/backend/nodes/copyfuncs.c,v 1.348 2006/08/21 00:57:24 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -1641,7 +1641,6 @@ _copyColumnDef(ColumnDef *from)
 	COPY_NODE_FIELD(raw_default);
 	COPY_STRING_FIELD(cooked_default);
 	COPY_NODE_FIELD(constraints);
-	COPY_NODE_FIELD(support);
 
 	return newnode;
 }
diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c
index 665d4833be3..b4d0632c037 100644
--- a/src/backend/nodes/equalfuncs.c
+++ b/src/backend/nodes/equalfuncs.c
@@ -18,7 +18,7 @@
  * Portions Copyright (c) 1994, Regents of the University of California
  *
  * IDENTIFICATION
- *	  $PostgreSQL: pgsql/src/backend/nodes/equalfuncs.c,v 1.281 2006/08/12 02:52:04 tgl Exp $
+ *	  $PostgreSQL: pgsql/src/backend/nodes/equalfuncs.c,v 1.282 2006/08/21 00:57:24 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -1665,7 +1665,6 @@ _equalColumnDef(ColumnDef *a, ColumnDef *b)
 	COMPARE_NODE_FIELD(raw_default);
 	COMPARE_STRING_FIELD(cooked_default);
 	COMPARE_NODE_FIELD(constraints);
-	COMPARE_NODE_FIELD(support);
 
 	return true;
 }
diff --git a/src/backend/nodes/makefuncs.c b/src/backend/nodes/makefuncs.c
index 784dd57831d..7555cbd0dd0 100644
--- a/src/backend/nodes/makefuncs.c
+++ b/src/backend/nodes/makefuncs.c
@@ -9,7 +9,7 @@
  *
  *
  * IDENTIFICATION
- *	  $PostgreSQL: pgsql/src/backend/nodes/makefuncs.c,v 1.50 2006/03/14 22:48:19 tgl Exp $
+ *	  $PostgreSQL: pgsql/src/backend/nodes/makefuncs.c,v 1.51 2006/08/21 00:57:24 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -322,3 +322,17 @@ makeFuncExpr(Oid funcid, Oid rettype, List *args, CoercionForm fformat)
 
 	return funcexpr;
 }
+
+/*
+ * makeDefElem -
+ *	build a DefElem node
+ */
+DefElem *
+makeDefElem(char *name, Node *arg)
+{
+	DefElem *res = makeNode(DefElem);
+
+	res->defname = name;
+	res->arg = arg;
+	return res;
+}
diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.c
index a1ed403d79b..a4b4044385d 100644
--- a/src/backend/nodes/outfuncs.c
+++ b/src/backend/nodes/outfuncs.c
@@ -8,7 +8,7 @@
  *
  *
  * IDENTIFICATION
- *	  $PostgreSQL: pgsql/src/backend/nodes/outfuncs.c,v 1.282 2006/08/12 02:52:04 tgl Exp $
+ *	  $PostgreSQL: pgsql/src/backend/nodes/outfuncs.c,v 1.283 2006/08/21 00:57:24 tgl Exp $
  *
  * NOTES
  *	  Every node type that can appear in stored rules' parsetrees *must*
@@ -1444,7 +1444,6 @@ _outColumnDef(StringInfo str, ColumnDef *node)
 	WRITE_NODE_FIELD(raw_default);
 	WRITE_STRING_FIELD(cooked_default);
 	WRITE_NODE_FIELD(constraints);
-	WRITE_NODE_FIELD(support);
 }
 
 static void
diff --git a/src/backend/parser/analyze.c b/src/backend/parser/analyze.c
index 39c7372733f..8eb50fb573e 100644
--- a/src/backend/parser/analyze.c
+++ b/src/backend/parser/analyze.c
@@ -6,7 +6,7 @@
  * Portions Copyright (c) 1996-2006, PostgreSQL Global Development Group
  * Portions Copyright (c) 1994, Regents of the University of California
  *
- *	$PostgreSQL: pgsql/src/backend/parser/analyze.c,v 1.346 2006/08/12 20:05:55 tgl Exp $
+ *	$PostgreSQL: pgsql/src/backend/parser/analyze.c,v 1.347 2006/08/21 00:57:24 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -980,6 +980,14 @@ transformCreateStmt(ParseState *pstate, CreateStmt *stmt,
 		}
 	}
 
+	/*
+	 * transformIndexConstraints wants cxt.alist to contain only index
+	 * statements, so transfer anything we already have into extras_after
+	 * immediately.
+	 */
+	*extras_after = list_concat(cxt.alist, *extras_after);
+	cxt.alist = NIL;
+
 	Assert(stmt->constraints == NIL);
 
 	/*
@@ -1052,6 +1060,8 @@ transformColumnDefinition(ParseState *pstate, CreateStmtContext *cxt,
 		A_Const    *snamenode;
 		FuncCall   *funccallnode;
 		CreateSeqStmt *seqstmt;
+		AlterSeqStmt *altseqstmt;
+		List	   *attnamelist;
 
 		/*
 		 * Determine namespace and name to use for the sequence.
@@ -1088,10 +1098,19 @@ transformColumnDefinition(ParseState *pstate, CreateStmtContext *cxt,
 		cxt->blist = lappend(cxt->blist, seqstmt);
 
 		/*
-		 * Mark the ColumnDef so that during execution, an appropriate
-		 * dependency will be added from the sequence to the column.
+		 * Build an ALTER SEQUENCE ... OWNED BY command to mark the sequence
+		 * as owned by this column, and add it to the list of things to be
+		 * done after this CREATE/ALTER TABLE.
 		 */
-		column->support = makeRangeVar(snamespace, sname);
+		altseqstmt = makeNode(AlterSeqStmt);
+		altseqstmt->sequence = makeRangeVar(snamespace, sname);
+		attnamelist = list_make3(makeString(snamespace),
+								 makeString(cxt->relation->relname),
+								 makeString(column->colname));
+		altseqstmt->options = list_make1(makeDefElem("owned_by",
+													 (Node *) attnamelist));
+
+		cxt->alist = lappend(cxt->alist, altseqstmt);
 
 		/*
 		 * Create appropriate constraints for SERIAL.  We do this in full,
@@ -1349,7 +1368,6 @@ transformInhRelation(ParseState *pstate, CreateStmtContext *cxt,
 		def->raw_default = NULL;
 		def->cooked_default = NULL;
 		def->constraints = NIL;
-		def->support = NULL;
 
 		/*
 		 * Add to column list
@@ -1604,7 +1622,7 @@ transformIndexConstraints(ParseState *pstate, CreateStmtContext *cxt)
 	 * XXX in ALTER TABLE case, it'd be nice to look for duplicate
 	 * pre-existing indexes, too.
 	 */
-	cxt->alist = NIL;
+	Assert(cxt->alist == NIL);
 	if (cxt->pkey != NULL)
 	{
 		/* Make sure we keep the PKEY index in preference to others... */
@@ -3041,6 +3059,14 @@ transformAlterTableStmt(ParseState *pstate, AlterTableStmt *stmt,
 		}
 	}
 
+	/*
+	 * transformIndexConstraints wants cxt.alist to contain only index
+	 * statements, so transfer anything we already have into extras_after
+	 * immediately.
+	 */
+	*extras_after = list_concat(cxt.alist, *extras_after);
+	cxt.alist = NIL;
+
 	/* Postprocess index and FK constraints */
 	transformIndexConstraints(pstate, &cxt);
 
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index c0744a74a42..60761ae6bca 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -11,7 +11,7 @@
  *
  *
  * IDENTIFICATION
- *	  $PostgreSQL: pgsql/src/backend/parser/gram.y,v 2.556 2006/08/12 18:58:54 tgl Exp $
+ *	  $PostgreSQL: pgsql/src/backend/parser/gram.y,v 2.557 2006/08/21 00:57:25 tgl Exp $
  *
  * HISTORY
  *	  AUTHOR			DATE			MAJOR EVENT
@@ -95,7 +95,6 @@ static Node *makeIntConst(int val);
 static Node *makeFloatConst(char *str);
 static Node *makeAConst(Value *v);
 static Node *makeRowNullTest(NullTestType test, RowExpr *row);
-static DefElem *makeDefElem(char *name, Node *arg);
 static A_Const *makeBoolAConst(bool state);
 static FuncCall *makeOverlaps(List *largs, List *rargs, int location);
 static void check_qualified_name(List *names);
@@ -2275,7 +2274,6 @@ CreateAsElement:
 					n->raw_default = NULL;
 					n->cooked_default = NULL;
 					n->constraints = NIL;
-					n->support = NULL;
 					$$ = (Node *)n;
 				}
 		;
@@ -2346,6 +2344,10 @@ OptSeqElem: CACHE NumericOnly
 				{
 					$$ = makeDefElem("minvalue", NULL);
 				}
+			| OWNED BY any_name
+				{
+					$$ = makeDefElem("owned_by", (Node *)$3);
+				}
 			| START opt_with NumericOnly
 				{
 					$$ = makeDefElem("start", (Node *)$3);
@@ -8977,19 +8979,6 @@ makeAConst(Value *v)
 	return n;
 }
 
-/* makeDefElem()
- * Create a DefElem node and set contents.
- * Could be moved to nodes/makefuncs.c if this is useful elsewhere.
- */
-static DefElem *
-makeDefElem(char *name, Node *arg)
-{
-	DefElem *f = makeNode(DefElem);
-	f->defname = name;
-	f->arg = arg;
-	return f;
-}
-
 /* makeBoolAConst()
  * Create an A_Const node and initialize to a boolean constant.
  */
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index e814310120a..a88f5ef93c6 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -2,7 +2,7 @@
  * ruleutils.c	- Functions to convert stored expressions/querytrees
  *				back to source text
  *
- *	  $PostgreSQL: pgsql/src/backend/utils/adt/ruleutils.c,v 1.231 2006/08/12 02:52:05 tgl Exp $
+ *	  $PostgreSQL: pgsql/src/backend/utils/adt/ruleutils.c,v 1.232 2006/08/21 00:57:25 tgl Exp $
  **********************************************************************/
 
 #include "postgres.h"
@@ -1287,12 +1287,12 @@ pg_get_serial_sequence(PG_FUNCTION_ARGS)
 		Form_pg_depend deprec = (Form_pg_depend) GETSTRUCT(tup);
 
 		/*
-		 * We assume any internal dependency of a relation on a column must be
+		 * We assume any auto dependency of a relation on a column must be
 		 * what we are looking for.
 		 */
 		if (deprec->classid == RelationRelationId &&
 			deprec->objsubid == 0 &&
-			deprec->deptype == DEPENDENCY_INTERNAL)
+			deprec->deptype == DEPENDENCY_AUTO)
 		{
 			sequenceId = deprec->objid;
 			break;