Create 'default_tablespace' GUC variable that supplies a TABLESPACE

clause implicitly whenever one is not given explicitly.  Remove concept
of a schema having an associated tablespace, and simplify the rules for
selecting a default tablespace for a table or index.  It's now just
(a) explicit TABLESPACE clause; (b) default_tablespace if that's not an
empty string; (c) database's default.  This will allow pg_dump to use
SET commands instead of tablespace clauses to determine object locations
(but I didn't actually make it do so).  All per recent discussions.

26 files changed, 267 insertions, 470 deletions

diff --git a/src/backend/catalog/namespace.c b/src/backend/catalog/namespace.c
index f722b031e66..b83b62659d8 100644
--- a/src/backend/catalog/namespace.c
+++ b/src/backend/catalog/namespace.c
@@ -13,7 +13,7 @@
  * Portions Copyright (c) 1994, Regents of the University of California
  *
  * IDENTIFICATION
- *	  $PostgreSQL: pgsql/src/backend/catalog/namespace.c,v 1.71 2004/09/16 16:58:27 tgl Exp $
+ *	  $PostgreSQL: pgsql/src/backend/catalog/namespace.c,v 1.72 2004/11/05 19:15:54 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -1675,7 +1675,7 @@ InitTempTableNamespace(void)
 		 * that access the temp namespace for my own backend skip
 		 * permissions checks on it.
 		 */
-		namespaceId = NamespaceCreate(namespaceName, BOOTSTRAP_USESYSID, 0);
+		namespaceId = NamespaceCreate(namespaceName, BOOTSTRAP_USESYSID);
 		/* Advance command counter to make namespace visible */
 		CommandCounterIncrement();
 	}
diff --git a/src/backend/catalog/pg_namespace.c b/src/backend/catalog/pg_namespace.c
index c6ac46d7540..886392fa4ce 100644
--- a/src/backend/catalog/pg_namespace.c
+++ b/src/backend/catalog/pg_namespace.c
@@ -8,7 +8,7 @@
  *
  *
  * IDENTIFICATION
- *	  $PostgreSQL: pgsql/src/backend/catalog/pg_namespace.c,v 1.10 2004/08/29 04:12:29 momjian Exp $
+ *	  $PostgreSQL: pgsql/src/backend/catalog/pg_namespace.c,v 1.11 2004/11/05 19:15:54 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -27,7 +27,7 @@
  * ---------------
  */
 Oid
-NamespaceCreate(const char *nspName, int32 ownerSysId, Oid nspTablespace)
+NamespaceCreate(const char *nspName, int32 ownerSysId)
 {
 	Relation	nspdesc;
 	HeapTuple	tup;
@@ -59,7 +59,6 @@ NamespaceCreate(const char *nspName, int32 ownerSysId, Oid nspTablespace)
 	namestrcpy(&nname, nspName);
 	values[Anum_pg_namespace_nspname - 1] = NameGetDatum(&nname);
 	values[Anum_pg_namespace_nspowner - 1] = Int32GetDatum(ownerSysId);
-	values[Anum_pg_namespace_nsptablespace - 1] = Int32GetDatum(nspTablespace);
 	nulls[Anum_pg_namespace_nspacl - 1] = 'n';
 
 	nspdesc = heap_openr(NamespaceRelationName, RowExclusiveLock);
diff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/indexcmds.c
index bdbf8708b13..b3f80470c04 100644
--- a/src/backend/commands/indexcmds.c
+++ b/src/backend/commands/indexcmds.c
@@ -8,7 +8,7 @@
  *
  *
  * IDENTIFICATION
- *	  $PostgreSQL: pgsql/src/backend/commands/indexcmds.c,v 1.126 2004/09/13 20:06:29 tgl Exp $
+ *	  $PostgreSQL: pgsql/src/backend/commands/indexcmds.c,v 1.127 2004/11/05 19:15:57 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -24,6 +24,7 @@
 #include "catalog/namespace.h"
 #include "catalog/pg_opclass.h"
 #include "catalog/pg_proc.h"
+#include "catalog/pg_tablespace.h"
 #include "commands/dbcommands.h"
 #include "commands/defrem.h"
 #include "commands/tablecmds.h"
@@ -66,7 +67,7 @@ static bool relationHasPrimaryKey(Relation rel);
  *		that a nonconflicting default name should be picked.
  * 'accessMethodName': name of the AM to use.
  * 'tableSpaceName': name of the tablespace to create the index in.
- *		NULL specifies using the same tablespace as the parent relation.
+ *		NULL specifies using the appropriate default.
  * 'attributeList': a list of IndexElem specifying columns and expressions
  *		to index on.
  * 'predicate': the partial-index condition, or NULL if none.
@@ -157,32 +158,45 @@ DefineIndex(RangeVar *heapRelation,
 						   get_namespace_name(namespaceId));
 	}
 
-	/* Determine tablespace to use */
+	/*
+	 * Select tablespace to use.  If not specified, use default_tablespace
+	 * (which may in turn default to database's default).
+	 */
 	if (tableSpaceName)
 	{
-		AclResult	aclresult;
-
 		tablespaceId = get_tablespace_oid(tableSpaceName);
 		if (!OidIsValid(tablespaceId))
 			ereport(ERROR,
 					(errcode(ERRCODE_UNDEFINED_OBJECT),
 					 errmsg("tablespace \"%s\" does not exist",
 							tableSpaceName)));
-		/* check permissions */
+	}
+	else
+	{
+		tablespaceId = GetDefaultTablespace();
+		/* note InvalidOid is OK in this case */
+	}
+
+	/* Check permissions except when using database's default */
+	if (OidIsValid(tablespaceId))
+	{
+		AclResult	aclresult;
+
 		aclresult = pg_tablespace_aclcheck(tablespaceId, GetUserId(),
 										   ACL_CREATE);
 		if (aclresult != ACLCHECK_OK)
 			aclcheck_error(aclresult, ACL_KIND_TABLESPACE,
-						   tableSpaceName);
-	}
-	else
-	{
-		/* Use the parent rel's tablespace */
-		tablespaceId = get_rel_tablespace(relationId);
-		/* Note there is no additional permission check in this path */
+						   get_tablespace_name(tablespaceId));
 	}
 
 	/*
+	 * Force shared indexes into the pg_global tablespace.  This is a bit of
+	 * a hack but seems simpler than marking them in the BKI commands.
+	 */
+	if (rel->rd_rel->relisshared)
+		tablespaceId = GLOBALTABLESPACE_OID;
+
+	/*
 	 * Select name for index if caller didn't specify
 	 */
 	if (indexRelationName == NULL)
diff --git a/src/backend/commands/schemacmds.c b/src/backend/commands/schemacmds.c
index b4e1cd77983..fc2410bacc1 100644
--- a/src/backend/commands/schemacmds.c
+++ b/src/backend/commands/schemacmds.c
@@ -8,7 +8,7 @@
  *
  *
  * IDENTIFICATION
- *	  $PostgreSQL: pgsql/src/backend/commands/schemacmds.c,v 1.25 2004/09/02 00:22:16 tgl Exp $
+ *	  $PostgreSQL: pgsql/src/backend/commands/schemacmds.c,v 1.26 2004/11/05 19:15:57 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -23,7 +23,6 @@
 #include "catalog/pg_namespace.h"
 #include "commands/dbcommands.h"
 #include "commands/schemacmds.h"
-#include "commands/tablespace.h"
 #include "miscadmin.h"
 #include "parser/analyze.h"
 #include "tcop/utility.h"
@@ -42,7 +41,6 @@ CreateSchemaCommand(CreateSchemaStmt *stmt)
 	const char *schemaName = stmt->schemaname;
 	const char *authId = stmt->authid;
 	Oid			namespaceId;
-	Oid			tablespaceId;
 	List	   *parsetree_list;
 	ListCell   *parsetree_item;
 	const char *owner_name;
@@ -102,35 +100,8 @@ CreateSchemaCommand(CreateSchemaStmt *stmt)
 				 errmsg("unacceptable schema name \"%s\"", schemaName),
 		errdetail("The prefix \"pg_\" is reserved for system schemas.")));
 
-	/*
-	 * Select default tablespace for schema.  If not given, use zero which
-	 * implies the database's default tablespace.
-	 */
-	if (stmt->tablespacename)
-	{
-		AclResult	aclresult;
-
-		tablespaceId = get_tablespace_oid(stmt->tablespacename);
-		if (!OidIsValid(tablespaceId))
-			ereport(ERROR,
-					(errcode(ERRCODE_UNDEFINED_OBJECT),
-					 errmsg("tablespace \"%s\" does not exist",
-							stmt->tablespacename)));
-		/* check permissions */
-		aclresult = pg_tablespace_aclcheck(tablespaceId, GetUserId(),
-										   ACL_CREATE);
-		if (aclresult != ACLCHECK_OK)
-			aclcheck_error(aclresult, ACL_KIND_TABLESPACE,
-						   stmt->tablespacename);
-	}
-	else
-	{
-		tablespaceId = InvalidOid;
-		/* note there is no permission check in this path */
-	}
-
 	/* Create the schema's namespace */
-	namespaceId = NamespaceCreate(schemaName, owner_userid, tablespaceId);
+	namespaceId = NamespaceCreate(schemaName, owner_userid);
 
 	/* Advance cmd counter to make the namespace visible */
 	CommandCounterIncrement();
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 4593327d35d..e4001f0102a 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.138 2004/10/30 20:52:56 tgl Exp $
+ *	  $PostgreSQL: pgsql/src/backend/commands/tablecmds.c,v 1.139 2004/11/05 19:15:57 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -168,7 +168,6 @@ static void StoreCatalogInheritance(Oid relationId, List *supers);
 static int	findAttrByName(const char *attributeName, List *schema);
 static void setRelhassubclassInRelation(Oid relationId, bool relhassubclass);
 static bool needs_toast_table(Relation rel);
-static void check_tablespace_exists(Oid tablespaceId, Oid namespaceId);
 static int transformColumnNameList(Oid relId, List *colList,
 						int16 *attnums, Oid *atttypids);
 static int transformFkeyGetPrimaryKey(Relation pkrel, Oid *indexOid,
@@ -313,34 +312,34 @@ DefineRelation(CreateStmt *stmt, char relkind)
 	}
 
 	/*
-	 * Select tablespace to use.  If not specified, use containing
-	 * schema's default tablespace (which may in turn default to
-	 * database's default).
+	 * Select tablespace to use.  If not specified, use default_tablespace
+	 * (which may in turn default to database's default).
 	 */
 	if (stmt->tablespacename)
 	{
-		AclResult	aclresult;
-
 		tablespaceId = get_tablespace_oid(stmt->tablespacename);
 		if (!OidIsValid(tablespaceId))
 			ereport(ERROR,
 					(errcode(ERRCODE_UNDEFINED_OBJECT),
 					 errmsg("tablespace \"%s\" does not exist",
 							stmt->tablespacename)));
-		/* check permissions */
+	}
+	else
+	{
+		tablespaceId = GetDefaultTablespace();
+		/* note InvalidOid is OK in this case */
+	}
+
+	/* Check permissions except when using database's default */
+	if (OidIsValid(tablespaceId))
+	{
+		AclResult	aclresult;
+
 		aclresult = pg_tablespace_aclcheck(tablespaceId, GetUserId(),
 										   ACL_CREATE);
 		if (aclresult != ACLCHECK_OK)
 			aclcheck_error(aclresult, ACL_KIND_TABLESPACE,
-						   stmt->tablespacename);
-	}
-	else
-	{
-		tablespaceId = get_namespace_tablespace(namespaceId);
-		/* note no permission check on tablespace in this case */
-		/* check to see that schema's tablespace still exists */
-		if (OidIsValid(tablespaceId))
-			check_tablespace_exists(tablespaceId, namespaceId);
+						   get_tablespace_name(tablespaceId));
 	}
 
 	/*
@@ -5890,42 +5889,6 @@ needs_toast_table(Relation rel)
 	return (tuple_length > TOAST_TUPLE_THRESHOLD);
 }
 
-/*
- * Verify that a schema's tablespace still exists
- *
- * We need this because DROP TABLESPACE cannot check whether the target
- * tablespace is the default tablespace for any schemas.  (It could check
- * in the current database, but that doesn't seem very helpful.)  Subsequent
- * attempts to create tables in that tablespace will fail.  This code just
- * exists to ensure that we give a helpful error message.
- */
-static void
-check_tablespace_exists(Oid tablespaceId, Oid namespaceId)
-{
-	Relation	pg_tablespace;
-	ScanKeyData entry[1];
-	HeapScanDesc scan;
-	HeapTuple	tuple;
-
-	/* There's no syscache for pg_tablespace, so must look the hard way */
-	pg_tablespace = heap_openr(TableSpaceRelationName, AccessShareLock);
-	ScanKeyInit(&entry[0],
-				ObjectIdAttributeNumber,
-				BTEqualStrategyNumber, F_OIDEQ,
-				ObjectIdGetDatum(tablespaceId));
-	scan = heap_beginscan(pg_tablespace, SnapshotNow, 1, entry);
-	tuple = heap_getnext(scan, ForwardScanDirection);
-	if (!HeapTupleIsValid(tuple))
-		ereport(ERROR,
-				(errcode(ERRCODE_UNDEFINED_OBJECT),
-				 errmsg("tablespace with OID %u does not exist",
-						tablespaceId),
-				 errdetail("The default tablespace for schema \"%s\" has been dropped.",
-						   get_namespace_name(namespaceId))));
-	heap_endscan(scan);
-	heap_close(pg_tablespace, AccessShareLock);
-}
-
 
 /*
  * This code supports
diff --git a/src/backend/commands/tablespace.c b/src/backend/commands/tablespace.c
index 55f086d6609..4dd1120c9d4 100644
--- a/src/backend/commands/tablespace.c
+++ b/src/backend/commands/tablespace.c
@@ -3,7 +3,6 @@
  * tablespace.c
  *	  Commands to manipulate table spaces
  *
- *
  * Tablespaces in PostgreSQL are designed to allow users to determine
  * where the data file(s) for a given database object reside on the file
  * system.
@@ -26,18 +25,11 @@
  *			$PGDATA/global/relfilenode
  *			$PGDATA/base/dboid/relfilenode
  *
- * The implementation is designed to be backwards compatible. For this reason
- * (and also as a feature unto itself) when a user creates an object without
- * specifying a tablespace, we look at the object's parent and place
- * the object in the parent's tablespace. The hierarchy is as follows:
- *			database > schema > table > index
- *
  * To allow CREATE DATABASE to give a new database a default tablespace
  * that's different from the template database's default, we make the
  * provision that a zero in pg_class.reltablespace means the database's
  * default tablespace.	Without this, CREATE DATABASE would have to go in
- * and munge the system catalogs of the new database.  This special meaning
- * of zero also applies in pg_namespace.nsptablespace.
+ * and munge the system catalogs of the new database.
  *
  *
  * Portions Copyright (c) 1996-2004, PostgreSQL Global Development Group
@@ -45,7 +37,7 @@
  *
  *
  * IDENTIFICATION
- *	  $PostgreSQL: pgsql/src/backend/commands/tablespace.c,v 1.13 2004/11/05 17:11:05 petere Exp $
+ *	  $PostgreSQL: pgsql/src/backend/commands/tablespace.c,v 1.14 2004/11/05 19:15:57 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -69,10 +61,15 @@
 #include "utils/acl.h"
 #include "utils/builtins.h"
 #include "utils/fmgroids.h"
+#include "utils/guc.h"
 #include "utils/lsyscache.h"
 #include "utils/syscache.h"
 
 
+/* GUC variable */
+char   *default_tablespace = NULL;
+
+
 static bool remove_tablespace_directories(Oid tablespaceoid, bool redo);
 static void set_short_version(const char *path);
 
@@ -726,77 +723,6 @@ directory_is_empty(const char *path)
 }
 
 /*
- * get_tablespace_oid - given a tablespace name, look up the OID
- *
- * Returns InvalidOid if tablespace name not found.
- */
-Oid
-get_tablespace_oid(const char *tablespacename)
-{
-	Oid			result;
-	Relation	rel;
-	HeapScanDesc scandesc;
-	HeapTuple	tuple;
-	ScanKeyData entry[1];
-
-	/* Search pg_tablespace */
-	rel = heap_openr(TableSpaceRelationName, AccessShareLock);
-
-	ScanKeyInit(&entry[0],
-				Anum_pg_tablespace_spcname,
-				BTEqualStrategyNumber, F_NAMEEQ,
-				CStringGetDatum(tablespacename));
-	scandesc = heap_beginscan(rel, SnapshotNow, 1, entry);
-	tuple = heap_getnext(scandesc, ForwardScanDirection);
-
-	if (HeapTupleIsValid(tuple))
-		result = HeapTupleGetOid(tuple);
-	else
-		result = InvalidOid;
-
-	heap_endscan(scandesc);
-	heap_close(rel, AccessShareLock);
-
-	return result;
-}
-
-/*
- * get_tablespace_name - given a tablespace OID, look up the name
- *
- * Returns a palloc'd string, or NULL if no such tablespace.
- */
-char *
-get_tablespace_name(Oid spc_oid)
-{
-	char	   *result;
-	Relation	rel;
-	HeapScanDesc scandesc;
-	HeapTuple	tuple;
-	ScanKeyData entry[1];
-
-	/* Search pg_tablespace */
-	rel = heap_openr(TableSpaceRelationName, AccessShareLock);
-
-	ScanKeyInit(&entry[0],
-				ObjectIdAttributeNumber,
-				BTEqualStrategyNumber, F_OIDEQ,
-				ObjectIdGetDatum(spc_oid));
-	scandesc = heap_beginscan(rel, SnapshotNow, 1, entry);
-	tuple = heap_getnext(scandesc, ForwardScanDirection);
-
-	/* We assume that there can be at most one matching tuple */
-	if (HeapTupleIsValid(tuple))
-		result = pstrdup(NameStr(((Form_pg_tablespace) GETSTRUCT(tuple))->spcname));
-	else
-		result = NULL;
-
-	heap_endscan(scandesc);
-	heap_close(rel, AccessShareLock);
-
-	return result;
-}
-
-/*
  * Rename a tablespace
  */
 void
@@ -946,6 +872,143 @@ AlterTableSpaceOwner(const char *name, AclId newOwnerSysId)
 	heap_close(rel, NoLock);
 }
 
+
+/*
+ * Routines for handling the GUC variable 'default_tablespace'.
+ */
+
+/* assign_hook: validate new default_tablespace, do extra actions as needed */
+const char *
+assign_default_tablespace(const char *newval, bool doit, GucSource source)
+{
+	/*
+	 * If we aren't inside a transaction, we cannot do database access so
+	 * cannot verify the name.  Must accept the value on faith.
+	 */
+	if (IsTransactionState())
+	{
+		if (newval[0] != '\0' &&
+			!OidIsValid(get_tablespace_oid(newval)))
+		{
+			if (source >= PGC_S_INTERACTIVE)
+				ereport(ERROR,
+						(errcode(ERRCODE_UNDEFINED_OBJECT),
+						 errmsg("tablespace \"%s\" does not exist",
+								newval)));
+			return NULL;
+		}
+	}
+
+	return newval;
+}
+
+/*
+ * GetDefaultTablespace -- get the OID of the current default tablespace
+ *
+ * May return InvalidOid to indicate "use the database's default tablespace"
+ *
+ * This exists to hide (and possibly optimize the use of) the
+ * default_tablespace GUC variable.
+ */
+Oid
+GetDefaultTablespace(void)
+{
+	Oid			result;
+
+	/* Fast path for default_tablespace == "" */
+	if (default_tablespace == NULL || default_tablespace[0] == '\0')
+		return InvalidOid;
+	/*
+	 * It is tempting to cache this lookup for more speed, but then we would
+	 * fail to detect the case where the tablespace was dropped since the
+	 * GUC variable was set.  Note also that we don't complain if the value
+	 * fails to refer to an existing tablespace; we just silently return
+	 * InvalidOid, causing the new object to be created in the database's
+	 * tablespace.
+	 */
+	result = get_tablespace_oid(default_tablespace);
+	/*
+	 * Allow explicit specification of database's default tablespace in
+	 * default_tablespace without triggering permissions checks.
+	 */
+	if (result == MyDatabaseTableSpace)
+		result = InvalidOid;
+	return result;
+}
+
+
+/*
+ * get_tablespace_oid - given a tablespace name, look up the OID
+ *
+ * Returns InvalidOid if tablespace name not found.
+ */
+Oid
+get_tablespace_oid(const char *tablespacename)
+{
+	Oid			result;
+	Relation	rel;
+	HeapScanDesc scandesc;
+	HeapTuple	tuple;
+	ScanKeyData entry[1];
+
+	/* Search pg_tablespace */
+	rel = heap_openr(TableSpaceRelationName, AccessShareLock);
+
+	ScanKeyInit(&entry[0],
+				Anum_pg_tablespace_spcname,
+				BTEqualStrategyNumber, F_NAMEEQ,
+				CStringGetDatum(tablespacename));
+	scandesc = heap_beginscan(rel, SnapshotNow, 1, entry);
+	tuple = heap_getnext(scandesc, ForwardScanDirection);
+
+	if (HeapTupleIsValid(tuple))
+		result = HeapTupleGetOid(tuple);
+	else
+		result = InvalidOid;
+
+	heap_endscan(scandesc);
+	heap_close(rel, AccessShareLock);
+
+	return result;
+}
+
+/*
+ * get_tablespace_name - given a tablespace OID, look up the name
+ *
+ * Returns a palloc'd string, or NULL if no such tablespace.
+ */
+char *
+get_tablespace_name(Oid spc_oid)
+{
+	char	   *result;
+	Relation	rel;
+	HeapScanDesc scandesc;
+	HeapTuple	tuple;
+	ScanKeyData entry[1];
+
+	/* Search pg_tablespace */
+	rel = heap_openr(TableSpaceRelationName, AccessShareLock);
+
+	ScanKeyInit(&entry[0],
+				ObjectIdAttributeNumber,
+				BTEqualStrategyNumber, F_OIDEQ,
+				ObjectIdGetDatum(spc_oid));
+	scandesc = heap_beginscan(rel, SnapshotNow, 1, entry);
+	tuple = heap_getnext(scandesc, ForwardScanDirection);
+
+	/* We assume that there can be at most one matching tuple */
+	if (HeapTupleIsValid(tuple))
+		result = pstrdup(NameStr(((Form_pg_tablespace) GETSTRUCT(tuple))->spcname));
+	else
+		result = NULL;
+
+	heap_endscan(scandesc);
+	heap_close(rel, AccessShareLock);
+
+	return result;
+}
+
+
 /*
  * TABLESPACE resource manager's routines
  */
diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c
index fe74495781e..093965a5174 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.292 2004/08/29 05:06:43 momjian Exp $
+ *	  $PostgreSQL: pgsql/src/backend/nodes/copyfuncs.c,v 1.293 2004/11/05 19:15:59 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -2401,7 +2401,6 @@ _copyCreateSchemaStmt(CreateSchemaStmt *from)
 
 	COPY_STRING_FIELD(schemaname);
 	COPY_STRING_FIELD(authid);
-	COPY_STRING_FIELD(tablespacename);
 	COPY_NODE_FIELD(schemaElts);
 
 	return newnode;
diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c
index 22c94cb4af2..6099f42cca7 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.231 2004/08/29 05:06:43 momjian Exp $
+ *	  $PostgreSQL: pgsql/src/backend/nodes/equalfuncs.c,v 1.232 2004/11/05 19:15:59 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -1377,7 +1377,6 @@ _equalCreateSchemaStmt(CreateSchemaStmt *a, CreateSchemaStmt *b)
 {
 	COMPARE_STRING_FIELD(schemaname);
 	COMPARE_STRING_FIELD(authid);
-	COMPARE_STRING_FIELD(tablespacename);
 	COMPARE_NODE_FIELD(schemaElts);
 
 	return true;
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 5a12bf11891..12c1acac4d8 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.478 2004/10/01 16:39:59 tgl Exp $
+ *	  $PostgreSQL: pgsql/src/backend/parser/gram.y,v 2.479 2004/11/05 19:16:02 tgl Exp $
  *
  * HISTORY
  *	  AUTHOR			DATE			MAJOR EVENT
@@ -780,7 +780,7 @@ DropGroupStmt:
  *****************************************************************************/
 
 CreateSchemaStmt:
-			CREATE SCHEMA OptSchemaName AUTHORIZATION UserId OptTableSpace OptSchemaEltList
+			CREATE SCHEMA OptSchemaName AUTHORIZATION UserId OptSchemaEltList
 				{
 					CreateSchemaStmt *n = makeNode(CreateSchemaStmt);
 					/* One can omit the schema name or the authorization id. */
@@ -789,18 +789,16 @@ CreateSchemaStmt:
 					else
 						n->schemaname = $5;
 					n->authid = $5;
-					n->tablespacename = $6;
-					n->schemaElts = $7;
+					n->schemaElts = $6;
 					$$ = (Node *)n;
 				}
-			| CREATE SCHEMA ColId OptTableSpace OptSchemaEltList
+			| CREATE SCHEMA ColId OptSchemaEltList
 				{
 					CreateSchemaStmt *n = makeNode(CreateSchemaStmt);
 					/* ...but not both */
 					n->schemaname = $3;
 					n->authid = NULL;
-					n->tablespacename = $4;
-					n->schemaElts = $5;
+					n->schemaElts = $4;
 					$$ = (Node *)n;
 				}
 		;
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index bb0fc2a1794..36e7208dc1b 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -3,7 +3,7 @@
  *				back to source text
  *
  * IDENTIFICATION
- *	  $PostgreSQL: pgsql/src/backend/utils/adt/ruleutils.c,v 1.184 2004/10/27 18:09:38 tgl Exp $
+ *	  $PostgreSQL: pgsql/src/backend/utils/adt/ruleutils.c,v 1.185 2004/11/05 19:16:11 tgl Exp $
  *
  *	  This software is copyrighted by Jan Wieck - Hamburg.
  *
@@ -55,7 +55,6 @@
 #include "catalog/pg_operator.h"
 #include "catalog/pg_shadow.h"
 #include "catalog/pg_trigger.h"
-#include "commands/tablespace.h"
 #include "executor/spi.h"
 #include "lib/stringinfo.h"
 #include "miscadmin.h"
@@ -163,7 +162,6 @@ static char *pg_get_constraintdef_worker(Oid constraintId, bool fullCommand,
 							int prettyFlags);
 static char *pg_get_expr_worker(text *expr, Oid relid, char *relname,
 				   int prettyFlags);
-static Oid	get_constraint_index(Oid constraintRelOid, Oid constraintOid);
 static void make_ruledef(StringInfo buf, HeapTuple ruletup, TupleDesc rulettc,
 			 int prettyFlags);
 static void make_viewdef(StringInfo buf, HeapTuple ruletup, TupleDesc rulettc,
@@ -775,27 +773,6 @@ pg_get_indexdef_worker(Oid indexrelid, int colno, int prettyFlags)
 		appendStringInfoChar(&buf, ')');
 
 		/*
-		 * If the index is in a different tablespace from its parent, tell
-		 * about that
-		 */
-		if (idxrelrec->reltablespace != get_rel_tablespace(indrelid))
-		{
-			char	   *spcname;
-
-			if (OidIsValid(idxrelrec->reltablespace))
-				spcname = get_tablespace_name(idxrelrec->reltablespace);
-			else
-				spcname = get_tablespace_name(MyDatabaseTableSpace);
-
-			if (spcname)		/* just paranoia... */
-			{
-				appendStringInfo(&buf, " TABLESPACE %s",
-								 quote_identifier(spcname));
-				pfree(spcname);
-			}
-		}
-
-		/*
 		 * If it's a partial index, decompile and append the predicate
 		 */
 		if (!heap_attisnull(ht_idx, Anum_pg_index_indpred))
@@ -1023,7 +1000,6 @@ pg_get_constraintdef_worker(Oid constraintId, bool fullCommand,
 			{
 				Datum		val;
 				bool		isnull;
-				Oid			indexOid;
 
 				/* Start off the constraint definition */
 				if (conForm->contype == CONSTRAINT_PRIMARY)
@@ -1041,30 +1017,6 @@ pg_get_constraintdef_worker(Oid constraintId, bool fullCommand,
 				decompile_column_index_array(val, conForm->conrelid, &buf);
 
 				appendStringInfo(&buf, ")");
-
-				/* Add TABLESPACE if it's not default */
-				indexOid = get_constraint_index(RelationGetRelid(conDesc),
-												constraintId);
-				if (OidIsValid(indexOid))
-				{
-					Oid			reltablespace;
-					Oid			indtablespace;
-
-					reltablespace = get_rel_tablespace(conForm->conrelid);
-					indtablespace = get_rel_tablespace(indexOid);
-					if (OidIsValid(indtablespace) &&
-						indtablespace != reltablespace)
-					{
-						char	   *spcname = get_tablespace_name(indtablespace);
-
-						if (spcname)	/* just paranoia... */
-						{
-							appendStringInfo(&buf, " USING INDEX TABLESPACE %s",
-											 quote_identifier(spcname));
-							pfree(spcname);
-						}
-					}
-				}
 				break;
 			}
 		case CONSTRAINT_CHECK:
@@ -1377,67 +1329,6 @@ pg_get_serial_sequence(PG_FUNCTION_ARGS)
 	PG_RETURN_NULL();
 }
 
-/*
- * get_constraint_index
- *		Given the OID of a unique or primary-key constraint,
- *		look up the OID of the underlying index.
- *
- * We make the caller pass in the OID of pg_constraint, too, simply because
- * it's probably got it at hand already.
- *
- * Returns InvalidOid if index can't be found.
- */
-static Oid
-get_constraint_index(Oid constraintRelOid, Oid constraintOid)
-{
-	Oid			result = InvalidOid;
-	Relation	depRel;
-	ScanKeyData key[3];
-	SysScanDesc scan;
-	HeapTuple	tup;
-
-	/* Search the dependency table for the dependent index */
-	depRel = heap_openr(DependRelationName, AccessShareLock);
-
-	ScanKeyInit(&key[0],
-				Anum_pg_depend_refclassid,
-				BTEqualStrategyNumber, F_OIDEQ,
-				ObjectIdGetDatum(constraintRelOid));
-	ScanKeyInit(&key[1],
-				Anum_pg_depend_refobjid,
-				BTEqualStrategyNumber, F_OIDEQ,
-				ObjectIdGetDatum(constraintOid));
-	ScanKeyInit(&key[2],
-				Anum_pg_depend_refobjsubid,
-				BTEqualStrategyNumber, F_INT4EQ,
-				Int32GetDatum(0));
-
-	scan = systable_beginscan(depRel, DependReferenceIndex, true,
-							  SnapshotNow, 3, key);
-
-	while (HeapTupleIsValid(tup = systable_getnext(scan)))
-	{
-		Form_pg_depend deprec = (Form_pg_depend) GETSTRUCT(tup);
-
-		/*
-		 * We assume any internal dependency of a relation on the
-		 * constraint must be what we are looking for.
-		 */
-		if (deprec->classid == RelOid_pg_class &&
-			deprec->objsubid == 0 &&
-			deprec->deptype == DEPENDENCY_INTERNAL)
-		{
-			result = deprec->objid;
-			break;
-		}
-	}
-
-	systable_endscan(scan);
-	heap_close(depRel, AccessShareLock);
-
-	return result;
-}
-
 
 /* ----------
  * deparse_expression			- General utility for deparsing expressions
diff --git a/src/backend/utils/cache/lsyscache.c b/src/backend/utils/cache/lsyscache.c
index 2e3848947c8..3e63efcc4f6 100644
--- a/src/backend/utils/cache/lsyscache.c
+++ b/src/backend/utils/cache/lsyscache.c
@@ -7,7 +7,7 @@
  * Portions Copyright (c) 1994, Regents of the University of California
  *
  * IDENTIFICATION
- *	  $PostgreSQL: pgsql/src/backend/utils/cache/lsyscache.c,v 1.117 2004/10/20 16:04:49 tgl Exp $
+ *	  $PostgreSQL: pgsql/src/backend/utils/cache/lsyscache.c,v 1.118 2004/11/05 19:16:14 tgl Exp $
  *
  * NOTES
  *	  Eventually, the index information should go through here, too.
@@ -986,34 +986,6 @@ get_rel_namespace(Oid relid)
 }
 
 /*
- * get_rel_tablespace
- *		Returns the pg_tablespace OID associated with a given relation.
- *
- * Note: failure return is InvalidOid, which cannot be distinguished from
- * "default tablespace for this database", but that seems OK.
- */
-Oid
-get_rel_tablespace(Oid relid)
-{
-	HeapTuple	tp;
-
-	tp = SearchSysCache(RELOID,
-						ObjectIdGetDatum(relid),
-						0, 0, 0);
-	if (HeapTupleIsValid(tp))
-	{
-		Form_pg_class reltup = (Form_pg_class) GETSTRUCT(tp);
-		Oid			result;
-
-		result = reltup->reltablespace;
-		ReleaseSysCache(tp);
-		return result;
-	}
-	else
-		return InvalidOid;
-}
-
-/*
  * get_rel_type_id
  *
  *		Returns the pg_type OID associated with a given relation.
@@ -2044,34 +2016,6 @@ get_namespace_name(Oid nspid)
 		return NULL;
 }
 
-/*
- * get_namespace_tablespace
- *		Returns the default tablespace of a given namespace
- *
- * Note: failure return is InvalidOid, which cannot be distinguished from
- * "default tablespace for this database", but that seems OK.
- */
-Oid
-get_namespace_tablespace(Oid nspid)
-{
-	HeapTuple	tp;
-
-	tp = SearchSysCache(NAMESPACEOID,
-						ObjectIdGetDatum(nspid),
-						0, 0, 0);
-	if (HeapTupleIsValid(tp))
-	{
-		Form_pg_namespace nsptup = (Form_pg_namespace) GETSTRUCT(tp);
-		Oid			result;
-
-		result = nsptup->nsptablespace;
-		ReleaseSysCache(tp);
-		return result;
-	}
-	else
-		return InvalidOid;
-}
-
 /*				---------- PG_SHADOW CACHE ----------					 */
 
 /*
diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c
index c742ace1b5c..b59644397cf 100644
--- a/src/backend/utils/misc/guc.c
+++ b/src/backend/utils/misc/guc.c
@@ -10,7 +10,7 @@
  * Written by Peter Eisentraut <peter_e@gmx.net>.
  *
  * IDENTIFICATION
- *	  $PostgreSQL: pgsql/src/backend/utils/misc/guc.c,v 1.247 2004/11/04 19:08:42 tgl Exp $
+ *	  $PostgreSQL: pgsql/src/backend/utils/misc/guc.c,v 1.248 2004/11/05 19:16:16 tgl Exp $
  *
  *--------------------------------------------------------------------
  */
@@ -78,6 +78,7 @@ extern DLLIMPORT bool check_function_bodies;
 extern int	CommitDelay;
 extern int	CommitSiblings;
 extern int	DebugSharedBuffers;
+extern char *default_tablespace;
 
 static const char *assign_log_destination(const char *value,
 					   bool doit, GucSource source);
@@ -1507,6 +1508,15 @@ static struct config_string ConfigureNamesString[] =
 	},
 
 	{
+		{"default_tablespace", PGC_USERSET, CLIENT_CONN_STATEMENT,
+			gettext_noop("Sets the default tablespace to create tables and indexes in."),
+			gettext_noop("An empty string selects the database's default tablespace.")
+		},
+		&default_tablespace,
+		"", assign_default_tablespace, NULL
+	},
+
+	{
 		{"default_transaction_isolation", PGC_USERSET, CLIENT_CONN_STATEMENT,
 			gettext_noop("Sets the transaction isolation level of each new transaction."),
 			gettext_noop("Each SQL transaction has an isolation level, which "
diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample
index 3aed76a411e..86640efa7da 100644
--- a/src/backend/utils/misc/postgresql.conf.sample
+++ b/src/backend/utils/misc/postgresql.conf.sample
@@ -269,6 +269,7 @@
 # - Statement Behavior -
 
 #search_path = '$user,public'	# schema names
+#default_tablespace = ''	# a tablespace name, or '' for default
 #check_function_bodies = true
 #default_transaction_isolation = 'read committed'
 #default_transaction_read_only = false
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index 8b524c85613..45510a98794 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -12,7 +12,7 @@
  *	by PostgreSQL
  *
  * IDENTIFICATION
- *	  $PostgreSQL: pgsql/src/bin/pg_dump/pg_dump.c,v 1.390 2004/10/22 16:04:35 petere Exp $
+ *	  $PostgreSQL: pgsql/src/bin/pg_dump/pg_dump.c,v 1.391 2004/11/05 19:16:19 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -108,9 +108,6 @@ static const CatalogId nilCatalogId = {0, 0};
 static NamespaceInfo *g_namespaces;
 static int	g_numNamespaces;
 
-/* need the name of the database's default tablespace */
-static char *dbDefaultTableSpace;
-
 /* flag to turn on/off dollar quoting */
 static int	disable_dollar_quoting = 0;
 
@@ -1252,9 +1249,6 @@ dumpDatabase(Archive *AH)
 	encoding = PQgetvalue(res, 0, i_encoding);
 	tablespace = PQgetvalue(res, 0, i_tablespace);
 
-	/* save dattablespace name for later dump routines */
-	dbDefaultTableSpace = strdup(tablespace);
-
 	appendPQExpBuffer(creaQry, "CREATE DATABASE %s WITH TEMPLATE = template0",
 					  fmtId(datname));
 	if (strlen(encoding) > 0)
@@ -1482,7 +1476,6 @@ getNamespaces(int *numNamespaces)
 	int			i_oid;
 	int			i_nspname;
 	int			i_usename;
-	int			i_nsptablespace;
 	int			i_nspacl;
 
 	/*
@@ -1500,7 +1493,6 @@ getNamespaces(int *numNamespaces)
 		nsinfo[0].dobj.name = strdup("public");
 		nsinfo[0].usename = strdup("");
 		nsinfo[0].nspacl = strdup("");
-		nsinfo[0].nsptablespace = strdup("");
 
 		selectDumpableNamespace(&nsinfo[0]);
 
@@ -1511,7 +1503,6 @@ getNamespaces(int *numNamespaces)
 		nsinfo[1].dobj.name = strdup("pg_catalog");
 		nsinfo[1].usename = strdup("");
 		nsinfo[1].nspacl = strdup("");
-		nsinfo[0].nsptablespace = strdup("");
 
 		selectDumpableNamespace(&nsinfo[1]);
 
@@ -1530,21 +1521,9 @@ getNamespaces(int *numNamespaces)
 	 * we fetch all namespaces including system ones, so that every object
 	 * we read in can be linked to a containing namespace.
 	 */
-	if (g_fout->remoteVersion >= 80000)
-	{
-		appendPQExpBuffer(query, "SELECT tableoid, oid, nspname, "
-						  "(select usename from pg_user where nspowner = usesysid) as usename, "
-						  "nspacl, "
-						  "(SELECT spcname FROM pg_tablespace t WHERE t.oid = nsptablespace) AS nsptablespace "
-						  "FROM pg_namespace");
-	}
-	else
-	{
-		appendPQExpBuffer(query, "SELECT tableoid, oid, nspname, "
-						  "(select usename from pg_user where nspowner = usesysid) as usename, "
-						  "nspacl, NULL AS nsptablespace "
-						  "FROM pg_namespace");
-	}
+	appendPQExpBuffer(query, "SELECT tableoid, oid, nspname, "
+					  "(select usename from pg_user where nspowner = usesysid) as usename, "
+					  "nspacl FROM pg_namespace");
 
 	res = PQexec(g_conn, query->data);
 	check_sql_result(res, g_conn, query->data, PGRES_TUPLES_OK);
@@ -1558,7 +1537,6 @@ getNamespaces(int *numNamespaces)
 	i_nspname = PQfnumber(res, "nspname");
 	i_usename = PQfnumber(res, "usename");
 	i_nspacl = PQfnumber(res, "nspacl");
-	i_nsptablespace = PQfnumber(res, "nsptablespace");
 
 	for (i = 0; i < ntups; i++)
 	{
@@ -1569,7 +1547,6 @@ getNamespaces(int *numNamespaces)
 		nsinfo[i].dobj.name = strdup(PQgetvalue(res, i, i_nspname));
 		nsinfo[i].usename = strdup(PQgetvalue(res, i, i_usename));
 		nsinfo[i].nspacl = strdup(PQgetvalue(res, i, i_nspacl));
-		nsinfo[i].nsptablespace = strdup(PQgetvalue(res, i, i_nsptablespace));
 
 		/* Decide whether to dump this namespace */
 		selectDumpableNamespace(&nsinfo[i]);
@@ -4432,16 +4409,9 @@ dumpNamespace(Archive *fout, NamespaceInfo *nspinfo)
 	 */
 	appendPQExpBuffer(delq, "DROP SCHEMA %s;\n", qnspname);
 
-	appendPQExpBuffer(q, "CREATE SCHEMA %s AUTHORIZATION %s",
+	appendPQExpBuffer(q, "CREATE SCHEMA %s AUTHORIZATION %s;\n",
 					  qnspname, fmtId(nspinfo->usename));
 
-	/* Add tablespace qualifier, if not default for database */
-	if (strlen(nspinfo->nsptablespace) != 0)
-		appendPQExpBuffer(q, " TABLESPACE %s",
-						  fmtId(nspinfo->nsptablespace));
-
-	appendPQExpBuffer(q, ";\n");
-
 	ArchiveEntry(fout, nspinfo->dobj.catId, nspinfo->dobj.dumpId,
 				 nspinfo->dobj.name,
 				 NULL, strcmp(nspinfo->dobj.name, "public") == 0 ? nspinfo->usename : "",
@@ -6659,17 +6629,10 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo)
 			appendPQExpBuffer(q, ")");
 		}
 
-		/* Output tablespace clause if different from parent schema's */
-		if (strcmp(tbinfo->reltablespace,
-				   tbinfo->dobj.namespace->nsptablespace) != 0)
-		{
-			if (strlen(tbinfo->reltablespace) != 0)
-				appendPQExpBuffer(q, " TABLESPACE %s",
-								  fmtId(tbinfo->reltablespace));
-			else if (strlen(dbDefaultTableSpace) != 0)
-				appendPQExpBuffer(q, " TABLESPACE %s",
-								  fmtId(dbDefaultTableSpace));
-		}
+		/* Output tablespace clause if not database's default */
+		if (strlen(tbinfo->reltablespace) != 0)
+			appendPQExpBuffer(q, " TABLESPACE %s",
+							  fmtId(tbinfo->reltablespace));
 
 		appendPQExpBuffer(q, ";\n");
 
@@ -6957,17 +6920,10 @@ dumpConstraint(Archive *fout, ConstraintInfo *coninfo)
 
 		appendPQExpBuffer(q, ")");
 
-		/* Output tablespace clause if different from parent table's */
-		if (strcmp(indxinfo->tablespace,
-				   indxinfo->indextable->reltablespace) != 0)
-		{
-			if (strlen(indxinfo->tablespace) != 0)
-				appendPQExpBuffer(q, " USING INDEX TABLESPACE %s",
-								  fmtId(indxinfo->tablespace));
-			else if (strlen(dbDefaultTableSpace) != 0)
-				appendPQExpBuffer(q, " USING INDEX TABLESPACE %s",
-								  fmtId(dbDefaultTableSpace));
-		}
+		/* Output tablespace clause if not database's default */
+		if (strlen(indxinfo->tablespace) != 0)
+			appendPQExpBuffer(q, " USING INDEX TABLESPACE %s",
+							  fmtId(indxinfo->tablespace));
 
 		appendPQExpBuffer(q, ";\n");
 
diff --git a/src/bin/pg_dump/pg_dump.h b/src/bin/pg_dump/pg_dump.h
index 6c3c02707af..07798c90f47 100644
--- a/src/bin/pg_dump/pg_dump.h
+++ b/src/bin/pg_dump/pg_dump.h
@@ -6,7 +6,7 @@
  * Portions Copyright (c) 1996-2004, PostgreSQL Global Development Group
  * Portions Copyright (c) 1994, Regents of the University of California
  *
- * $PostgreSQL: pgsql/src/bin/pg_dump/pg_dump.h,v 1.112 2004/08/29 05:06:53 momjian Exp $
+ * $PostgreSQL: pgsql/src/bin/pg_dump/pg_dump.h,v 1.113 2004/11/05 19:16:19 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -98,7 +98,6 @@ typedef struct _namespaceInfo
 	DumpableObject dobj;
 	char	   *usename;		/* name of owner, or empty string */
 	char	   *nspacl;
-	char	   *nsptablespace;	/* default tablespace */
 	bool		dump;			/* true if need to dump definition */
 } NamespaceInfo;
 
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index 32c7bcbaf6b..21ae52b82b3 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -3,7 +3,7 @@
  *
  * Copyright (c) 2000-2004, PostgreSQL Global Development Group
  *
- * $PostgreSQL: pgsql/src/bin/psql/describe.c,v 1.108 2004/10/12 21:54:44 petere Exp $
+ * $PostgreSQL: pgsql/src/bin/psql/describe.c,v 1.109 2004/11/05 19:16:21 tgl Exp $
  */
 #include "postgres_fe.h"
 #include "describe.h"
@@ -112,7 +112,7 @@ describeTablespaces(const char *pattern, bool verbose)
 	PGresult   *res;
 	printQueryOpt myopt = pset.popt;
 
-	if (pset.sversion < 70500)
+	if (pset.sversion < 80000)
 	{
 		fprintf(stderr, _("The server version (%d) does not support tablespaces.\n"),
 				pset.sversion);
@@ -715,7 +715,7 @@ describeOneTableDetails(const char *schemaname,
 	"SELECT relhasindex, relkind, relchecks, reltriggers, relhasrules, \n"
 					  "relhasoids %s \n"
 					  "FROM pg_catalog.pg_class WHERE oid = '%s'",
-					  pset.sversion >= 70500 ? ", reltablespace" : "",
+					  pset.sversion >= 80000 ? ", reltablespace" : "",
 					  oid);
 	res = PSQLexec(buf.data, false);
 	if (!res)
@@ -737,7 +737,7 @@ describeOneTableDetails(const char *schemaname,
 	tableinfo.hasindex = strcmp(PQgetvalue(res, 0, 0), "t") == 0;
 	tableinfo.hasrules = strcmp(PQgetvalue(res, 0, 4), "t") == 0;
 	tableinfo.hasoids = strcmp(PQgetvalue(res, 0, 5), "t") == 0;
-	tableinfo.tablespace = (pset.sversion >= 70500) ?
+	tableinfo.tablespace = (pset.sversion >= 80000) ?
 		atooid(PQgetvalue(res, 0, 6)) : 0;
 	PQclear(res);
 
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index cd448b9d0f1..93f576a0bf4 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -3,7 +3,7 @@
  *
  * Copyright (c) 2000-2004, PostgreSQL Global Development Group
  *
- * $PostgreSQL: pgsql/src/bin/psql/tab-complete.c,v 1.117 2004/11/02 16:10:05 petere Exp $
+ * $PostgreSQL: pgsql/src/bin/psql/tab-complete.c,v 1.118 2004/11/05 19:16:22 tgl Exp $
  */
 
 /*----------------------------------------------------------------------
@@ -526,6 +526,7 @@ psql_completion(char *text, int start, int end)
 		"debug_print_plan",
 		"debug_print_rewritten",
 		"default_statistics_target",
+		"default_tablespace",
 		"default_transaction_isolation",
 		"default_transaction_read_only",
 		"default_with_oids",
diff --git a/src/include/catalog/catversion.h b/src/include/catalog/catversion.h
index 32c097eeb01..25e34f81537 100644
--- a/src/include/catalog/catversion.h
+++ b/src/include/catalog/catversion.h
@@ -37,7 +37,7 @@
  * Portions Copyright (c) 1996-2004, PostgreSQL Global Development Group
  * Portions Copyright (c) 1994, Regents of the University of California
  *
- * $PostgreSQL: pgsql/src/include/catalog/catversion.h,v 1.251 2004/10/11 17:24:40 tgl Exp $
+ * $PostgreSQL: pgsql/src/include/catalog/catversion.h,v 1.252 2004/11/05 19:16:32 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -53,6 +53,6 @@
  */
 
 /*							yyyymmddN */
-#define CATALOG_VERSION_NO	200410111
+#define CATALOG_VERSION_NO	200411041
 
 #endif
diff --git a/src/include/catalog/pg_namespace.h b/src/include/catalog/pg_namespace.h
index 795663931b1..33c8a59e273 100644
--- a/src/include/catalog/pg_namespace.h
+++ b/src/include/catalog/pg_namespace.h
@@ -8,7 +8,7 @@
  * Portions Copyright (c) 1996-2004, PostgreSQL Global Development Group
  * Portions Copyright (c) 1994, Regents of the University of California
  *
- * $PostgreSQL: pgsql/src/include/catalog/pg_namespace.h,v 1.14 2004/08/29 05:06:55 momjian Exp $
+ * $PostgreSQL: pgsql/src/include/catalog/pg_namespace.h,v 1.15 2004/11/05 19:16:33 tgl Exp $
  *
  * NOTES
  *	  the genbki.sh script reads this file and generates .bki
@@ -40,7 +40,6 @@ CATALOG(pg_namespace)
 {
 	NameData	nspname;
 	int4		nspowner;
-	Oid			nsptablespace;	/* default table space for name space */
 	aclitem		nspacl[1];		/* VARIABLE LENGTH FIELD */
 } FormData_pg_namespace;
 
@@ -56,11 +55,10 @@ typedef FormData_pg_namespace *Form_pg_namespace;
  * ----------------
  */
 
-#define Natts_pg_namespace				4
+#define Natts_pg_namespace				3
 #define Anum_pg_namespace_nspname		1
 #define Anum_pg_namespace_nspowner		2
-#define Anum_pg_namespace_nsptablespace 3
-#define Anum_pg_namespace_nspacl		4
+#define Anum_pg_namespace_nspacl		3
 
 
 /* ----------------
@@ -68,13 +66,13 @@ typedef FormData_pg_namespace *Form_pg_namespace;
  * ---------------
  */
 
-DATA(insert OID = 11 ( "pg_catalog" PGUID 0 _null_ ));
+DATA(insert OID = 11 ( "pg_catalog" PGUID _null_ ));
 DESCR("System catalog schema");
 #define PG_CATALOG_NAMESPACE 11
-DATA(insert OID = 99 ( "pg_toast" PGUID 0 _null_ ));
+DATA(insert OID = 99 ( "pg_toast" PGUID _null_ ));
 DESCR("Reserved schema for TOAST tables");
 #define PG_TOAST_NAMESPACE 99
-DATA(insert OID = 2200 ( "public" PGUID 0 _null_ ));
+DATA(insert OID = 2200 ( "public" PGUID _null_ ));
 DESCR("Standard public schema");
 #define PG_PUBLIC_NAMESPACE 2200
 
@@ -82,7 +80,6 @@ DESCR("Standard public schema");
 /*
  * prototypes for functions in pg_namespace.c
  */
-extern Oid NamespaceCreate(const char *nspName, int32 ownerSysId,
-				Oid nspTablespace);
+extern Oid NamespaceCreate(const char *nspName, int32 ownerSysId);
 
 #endif   /* PG_NAMESPACE_H */
diff --git a/src/include/commands/tablespace.h b/src/include/commands/tablespace.h
index 58ad23ae161..a6c21888245 100644
--- a/src/include/commands/tablespace.h
+++ b/src/include/commands/tablespace.h
@@ -7,7 +7,7 @@
  * Portions Copyright (c) 1996-2004, PostgreSQL Global Development Group
  * Portions Copyright (c) 1994, Regents of the University of California
  *
- * $PostgreSQL: pgsql/src/include/commands/tablespace.h,v 1.6 2004/10/17 20:47:21 tgl Exp $
+ * $PostgreSQL: pgsql/src/include/commands/tablespace.h,v 1.7 2004/11/05 19:16:36 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -32,6 +32,7 @@ typedef struct xl_tblspc_drop_rec
 	Oid			ts_id;
 }	xl_tblspc_drop_rec;
 
+
 extern void CreateTableSpace(CreateTableSpaceStmt *stmt);
 extern void DropTableSpace(DropTableSpaceStmt *stmt);
 extern void RenameTableSpace(const char *oldname, const char *newname);
@@ -39,6 +40,8 @@ extern void AlterTableSpaceOwner(const char *name, AclId newOwnerSysId);
 
 extern void TablespaceCreateDbspace(Oid spcNode, Oid dbNode, bool isRedo);
 
+extern Oid	GetDefaultTablespace(void);
+
 extern Oid	get_tablespace_oid(const char *tablespacename);
 extern char *get_tablespace_name(Oid spc_oid);
 
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 8b46366bd97..9e2dfd839ec 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -7,7 +7,7 @@
  * Portions Copyright (c) 1996-2004, PostgreSQL Global Development Group
  * Portions Copyright (c) 1994, Regents of the University of California
  *
- * $PostgreSQL: pgsql/src/include/nodes/parsenodes.h,v 1.269 2004/08/29 05:06:57 momjian Exp $
+ * $PostgreSQL: pgsql/src/include/nodes/parsenodes.h,v 1.270 2004/11/05 19:16:38 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -777,7 +777,6 @@ typedef struct CreateSchemaStmt
 	NodeTag		type;
 	char	   *schemaname;		/* the name of the schema to create */
 	char	   *authid;			/* the owner of the created schema */
-	char	   *tablespacename; /* default tablespace for schema, or NULL */
 	List	   *schemaElts;		/* schema components (list of parsenodes) */
 } CreateSchemaStmt;
 
diff --git a/src/include/utils/guc.h b/src/include/utils/guc.h
index 0559264dd10..19ae296559d 100644
--- a/src/include/utils/guc.h
+++ b/src/include/utils/guc.h
@@ -7,7 +7,7 @@
  * Copyright (c) 2000-2004, PostgreSQL Global Development Group
  * Written by Peter Eisentraut <peter_e@gmx.net>.
  *
- * $PostgreSQL: pgsql/src/include/utils/guc.h,v 1.54 2004/10/22 19:48:19 tgl Exp $
+ * $PostgreSQL: pgsql/src/include/utils/guc.h,v 1.55 2004/11/05 19:16:41 tgl Exp $
  *--------------------------------------------------------------------
  */
 #ifndef GUC_H
@@ -223,6 +223,10 @@ extern void read_nondefault_variables(void);
 /* in utils/adt/datetime.c */
 extern bool ClearDateCache(bool newval, bool doit, GucSource source);
 
+/* in commands/tablespace.c */
+extern const char *assign_default_tablespace(const char *newval,
+				   bool doit, GucSource source);
+
 /* in utils/adt/regexp.c */
 extern const char *assign_regex_flavor(const char *value,
 					bool doit, GucSource source);
diff --git a/src/include/utils/lsyscache.h b/src/include/utils/lsyscache.h
index 3a016b7a52d..01f6aa73644 100644
--- a/src/include/utils/lsyscache.h
+++ b/src/include/utils/lsyscache.h
@@ -6,7 +6,7 @@
  * Portions Copyright (c) 1996-2004, PostgreSQL Global Development Group
  * Portions Copyright (c) 1994, Regents of the University of California
  *
- * $PostgreSQL: pgsql/src/include/utils/lsyscache.h,v 1.91 2004/10/20 16:04:50 tgl Exp $
+ * $PostgreSQL: pgsql/src/include/utils/lsyscache.h,v 1.92 2004/11/05 19:16:41 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -72,7 +72,6 @@ extern Oid	get_relname_relid(const char *relname, Oid relnamespace);
 extern Oid	get_system_catalog_relid(const char *catname);
 extern char *get_rel_name(Oid relid);
 extern Oid	get_rel_namespace(Oid relid);
-extern Oid	get_rel_tablespace(Oid relid);
 extern Oid	get_rel_type_id(Oid relid);
 extern char get_rel_relkind(Oid relid);
 extern bool get_typisdefined(Oid typid);
@@ -116,7 +115,6 @@ extern void free_attstatsslot(Oid atttype,
 				  Datum *values, int nvalues,
 				  float4 *numbers, int nnumbers);
 extern char *get_namespace_name(Oid nspid);
-extern Oid	get_namespace_tablespace(Oid nspid);
 extern int32 get_usesysid(const char *username);
 
 #define is_array_type(typid)  (get_element_type(typid) != InvalidOid)
diff --git a/src/interfaces/ecpg/preproc/preproc.y b/src/interfaces/ecpg/preproc/preproc.y
index 0ded0cb6041..fa579afd6a2 100644
--- a/src/interfaces/ecpg/preproc/preproc.y
+++ b/src/interfaces/ecpg/preproc/preproc.y
@@ -1,4 +1,4 @@
-/* $PostgreSQL: pgsql/src/interfaces/ecpg/preproc/preproc.y,v 1.299 2004/11/01 13:17:12 davec Exp $ */
+/* $PostgreSQL: pgsql/src/interfaces/ecpg/preproc/preproc.y,v 1.300 2004/11/05 19:16:43 tgl Exp $ */
 
 /* Copyright comment */
 %{
@@ -1013,10 +1013,10 @@ DropGroupStmt: DROP GROUP_P UserId
  *
  *****************************************************************************/
 
-CreateSchemaStmt:  CREATE SCHEMA OptSchemaName AUTHORIZATION UserId OptTableSpace OptSchemaEltList
-			{ $$ = cat_str(6, make_str("create schema"), $3, make_str("authorization"), $5, $6, $7); }
-		| CREATE SCHEMA ColId OptTableSpace OptSchemaEltList
-			{ $$ = cat_str(4, make_str("create schema"), $3, $4, $5); }
+CreateSchemaStmt:  CREATE SCHEMA OptSchemaName AUTHORIZATION UserId OptSchemaEltList
+			{ $$ = cat_str(5, make_str("create schema"), $3, make_str("authorization"), $5, $6); }
+		| CREATE SCHEMA ColId OptSchemaEltList
+			{ $$ = cat_str(3, make_str("create schema"), $3, $4); }
 		;
 
 OptSchemaName: ColId		{ $$ = $1; }
diff --git a/src/test/regress/input/tablespace.source b/src/test/regress/input/tablespace.source
index 460d4433a84..d094bd70811 100644
--- a/src/test/regress/input/tablespace.source
+++ b/src/test/regress/input/tablespace.source
@@ -1,15 +1,11 @@
 -- create a tablespace we can use
 CREATE TABLESPACE testspace LOCATION '@testtablespace@';
 
--- create a schema in the tablespace
-CREATE SCHEMA testschema TABLESPACE testspace;
-
--- sanity check
-SELECT nspname, spcname FROM pg_catalog.pg_tablespace t, pg_catalog.pg_namespace n
-	where n.nsptablespace = t.oid and n.nspname = 'testschema';
+-- create a schema we can use
+CREATE SCHEMA testschema;
 
 -- try a table
-CREATE TABLE testschema.foo (i int);
+CREATE TABLE testschema.foo (i int) TABLESPACE testspace;
 SELECT relname, spcname FROM pg_catalog.pg_tablespace t, pg_catalog.pg_class c
     where c.reltablespace = t.oid AND c.relname = 'foo';
 
@@ -17,7 +13,7 @@ INSERT INTO testschema.foo VALUES(1);
 INSERT INTO testschema.foo VALUES(2);
 
 -- index
-CREATE INDEX foo_idx on testschema.foo(i);
+CREATE INDEX foo_idx on testschema.foo(i) TABLESPACE testspace;
 SELECT relname, spcname FROM pg_catalog.pg_tablespace t, pg_catalog.pg_class c
     where c.reltablespace = t.oid AND c.relname = 'foo_idx';
 
diff --git a/src/test/regress/output/tablespace.source b/src/test/regress/output/tablespace.source
index 43044a2bcf8..42c4bc628d5 100644
--- a/src/test/regress/output/tablespace.source
+++ b/src/test/regress/output/tablespace.source
@@ -1,17 +1,9 @@
 -- create a tablespace we can use
 CREATE TABLESPACE testspace LOCATION '@testtablespace@';
--- create a schema in the tablespace
-CREATE SCHEMA testschema TABLESPACE testspace;
--- sanity check
-SELECT nspname, spcname FROM pg_catalog.pg_tablespace t, pg_catalog.pg_namespace n
-	where n.nsptablespace = t.oid and n.nspname = 'testschema';
-  nspname   |  spcname  
-------------+-----------
- testschema | testspace
-(1 row)
-
+-- create a schema we can use
+CREATE SCHEMA testschema;
 -- try a table
-CREATE TABLE testschema.foo (i int);
+CREATE TABLE testschema.foo (i int) TABLESPACE testspace;
 SELECT relname, spcname FROM pg_catalog.pg_tablespace t, pg_catalog.pg_class c
     where c.reltablespace = t.oid AND c.relname = 'foo';
  relname |  spcname  
@@ -22,7 +14,7 @@ SELECT relname, spcname FROM pg_catalog.pg_tablespace t, pg_catalog.pg_class c
 INSERT INTO testschema.foo VALUES(1);
 INSERT INTO testschema.foo VALUES(2);
 -- index
-CREATE INDEX foo_idx on testschema.foo(i);
+CREATE INDEX foo_idx on testschema.foo(i) TABLESPACE testspace;
 SELECT relname, spcname FROM pg_catalog.pg_tablespace t, pg_catalog.pg_class c
     where c.reltablespace = t.oid AND c.relname = 'foo_idx';
  relname |  spcname