Create a GUC parameter temp_tablespaces that allows selection of the

tablespace(s) in which to store temp tables and temporary files.  This is a
list to allow spreading the load across multiple tablespaces (a random list
element is chosen each time a temp object is to be created).  Temp files are
not stored in per-database pgsql_tmp/ directories anymore, but per-tablespace
directories.

Jaime Casanova and Albert Cervera, with review by Bernd Helmle and Tom Lane.

18 files changed, 380 insertions, 123 deletions

diff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/indexcmds.c
index 18047a53889..24cb898b6a0 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.158 2007/05/02 21:08:45 tgl Exp $
+ *	  $PostgreSQL: pgsql/src/backend/commands/indexcmds.c,v 1.159 2007/06/03 17:06:16 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -194,7 +194,7 @@ DefineIndex(RangeVar *heapRelation,
 	}
 
 	/*
-	 * Select tablespace to use.  If not specified, use default_tablespace
+	 * Select tablespace to use.  If not specified, use default tablespace
 	 * (which may in turn default to database's default).
 	 */
 	if (tableSpaceName)
@@ -208,7 +208,7 @@ DefineIndex(RangeVar *heapRelation,
 	}
 	else
 	{
-		tablespaceId = GetDefaultTablespace();
+		tablespaceId = GetDefaultTablespace(rel->rd_istemp);
 		/* note InvalidOid is OK in this case */
 	}
 
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index aafc4d1b2ee..f5bdc35615d 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.225 2007/05/18 23:19:41 alvherre Exp $
+ *	  $PostgreSQL: pgsql/src/backend/commands/tablecmds.c,v 1.226 2007/06/03 17:06:25 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -319,7 +319,7 @@ DefineRelation(CreateStmt *stmt, char relkind)
 	}
 
 	/*
-	 * Select tablespace to use.  If not specified, use default_tablespace
+	 * Select tablespace to use.  If not specified, use default tablespace
 	 * (which may in turn default to database's default).
 	 */
 	if (stmt->tablespacename)
@@ -333,17 +333,10 @@ DefineRelation(CreateStmt *stmt, char relkind)
 	}
 	else
 	{
-		tablespaceId = GetDefaultTablespace();
+		tablespaceId = GetDefaultTablespace(stmt->relation->istemp);
 		/* note InvalidOid is OK in this case */
 	}
 
-	/*
-	 * Parse and validate reloptions, if any.
-	 */
-	reloptions = transformRelOptions((Datum) 0, stmt->options, true, false);
-
-	(void) heap_reloptions(relkind, reloptions, true);
-
 	/* Check permissions except when using database's default */
 	if (OidIsValid(tablespaceId))
 	{
@@ -357,6 +350,13 @@ DefineRelation(CreateStmt *stmt, char relkind)
 	}
 
 	/*
+	 * Parse and validate reloptions, if any.
+	 */
+	reloptions = transformRelOptions((Datum) 0, stmt->options, true, false);
+
+	(void) heap_reloptions(relkind, reloptions, true);
+
+	/*
 	 * Look up inheritance ancestors and generate relation schema, including
 	 * inherited attributes.
 	 */
diff --git a/src/backend/commands/tablespace.c b/src/backend/commands/tablespace.c
index adceda634de..8201a4f3341 100644
--- a/src/backend/commands/tablespace.c
+++ b/src/backend/commands/tablespace.c
@@ -37,7 +37,7 @@
  *
  *
  * IDENTIFICATION
- *	  $PostgreSQL: pgsql/src/backend/commands/tablespace.c,v 1.46 2007/05/31 15:13:02 petere Exp $
+ *	  $PostgreSQL: pgsql/src/backend/commands/tablespace.c,v 1.47 2007/06/03 17:06:59 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -65,12 +65,14 @@
 #include "utils/lsyscache.h"
 
 
-/* GUC variable */
+/* GUC variables */
 char	   *default_tablespace = NULL;
+char	   *temp_tablespaces = NULL;
 
 
 static bool remove_tablespace_directories(Oid tablespaceoid, bool redo);
 static void set_short_version(const char *path);
+static Oid	getTempTablespace(void);
 
 
 /*
@@ -903,16 +905,26 @@ assign_default_tablespace(const char *newval, bool doit, GucSource source)
 /*
  * GetDefaultTablespace -- get the OID of the current default tablespace
  *
- * May return InvalidOid to indicate "use the database's default tablespace"
+ * Regular objects and temporary objects have different default tablespaces,
+ * hence the forTemp parameter must be specified.
+ *
+ * May return InvalidOid to indicate "use the database's default tablespace".
+ *
+ * Note that caller is expected to check appropriate permissions for any
+ * result other than InvalidOid.
  *
  * This exists to hide (and possibly optimize the use of) the
  * default_tablespace GUC variable.
  */
 Oid
-GetDefaultTablespace(void)
+GetDefaultTablespace(bool forTemp)
 {
 	Oid			result;
 
+	/* The temp-table case is handled by getTempTablespace() */
+	if (forTemp)
+		return getTempTablespace();
+
 	/* Fast path for default_tablespace == "" */
 	if (default_tablespace == NULL || default_tablespace[0] == '\0')
 		return InvalidOid;
@@ -937,6 +949,179 @@ GetDefaultTablespace(void)
 
 
 /*
+ * Routines for handling the GUC variable 'temp_tablespaces'.
+ */
+
+/* assign_hook: validate new temp_tablespaces, do extra actions as needed */
+const char *
+assign_temp_tablespaces(const char *newval, bool doit, GucSource source)
+{
+	char	   *rawname;
+	List	   *namelist;
+	ListCell   *l;
+
+	/* Need a modifiable copy of string */
+	rawname = pstrdup(newval);
+
+	/* Parse string into list of identifiers */
+	if (!SplitIdentifierString(rawname, ',', &namelist))
+	{
+		/* syntax error in name list */
+		pfree(rawname);
+		list_free(namelist);
+		return NULL;
+	}
+
+	/*
+	 * If we aren't inside a transaction, we cannot do database access so
+	 * cannot verify the individual names.	Must accept the list on faith.
+	 */
+	if (source >= PGC_S_INTERACTIVE && IsTransactionState())
+	{
+		foreach(l, namelist)
+		{
+			char	   *curname = (char *) lfirst(l);
+
+			/* Allow an empty string (signifying database default) */
+			if (curname[0] == '\0')
+				continue;
+
+			/* Else verify that name is a valid tablespace name */
+			if (get_tablespace_oid(curname) == InvalidOid)
+				ereport(ERROR,
+						(errcode(ERRCODE_UNDEFINED_OBJECT),
+						 errmsg("tablespace \"%s\" does not exist",
+								curname)));
+		}
+	}
+
+	pfree(rawname);
+	list_free(namelist);
+
+	return newval;
+}
+
+/*
+ * GetTempTablespace -- get the OID of the next temp tablespace to use
+ *
+ * May return InvalidOid to indicate "use the database's default tablespace".
+ *
+ * This is different from GetDefaultTablespace(true) in just two ways:
+ * 1. We check privileges here instead of leaving it to the caller.
+ * 2. It's safe to call this outside a transaction (we just return InvalidOid).
+ * The transaction state check is used so that this can be called from
+ * low-level places that might conceivably run outside a transaction.
+ */
+Oid
+GetTempTablespace(void)
+{
+	Oid			result;
+
+	/* Can't do catalog access unless within a transaction */
+	if (!IsTransactionState())
+		return InvalidOid;
+
+	/* OK, select a temp tablespace */
+	result = getTempTablespace();
+
+	/* Check permissions except when using database's default */
+	if (OidIsValid(result))
+	{
+		AclResult	aclresult;
+
+		aclresult = pg_tablespace_aclcheck(result, GetUserId(),
+										   ACL_CREATE);
+		if (aclresult != ACLCHECK_OK)
+			aclcheck_error(aclresult, ACL_KIND_TABLESPACE,
+						   get_tablespace_name(result));
+	}
+
+	return result;
+}
+
+/*
+ * getTempTablespace -- get the OID of the next temp tablespace to use
+ *
+ * This has exactly the API defined for GetDefaultTablespace(true),
+ * in particular that caller is responsible for permissions checks.
+ *
+ * This exists to hide (and possibly optimize the use of) the
+ * temp_tablespaces GUC variable.
+ */
+static Oid
+getTempTablespace(void)
+{
+	Oid			result;
+	char	   *rawname;
+	List	   *namelist;
+	int			nnames;
+	char	   *curname;
+
+	if (temp_tablespaces == NULL)
+		return InvalidOid;
+
+	/*
+	 * We re-parse the string on each call; this is a bit expensive, but
+	 * we don't expect this function will be called many times per query,
+	 * so it's probably not worth being tenser.
+	 */
+
+	/* Need a modifiable copy of string */
+	rawname = pstrdup(temp_tablespaces);
+
+	/* Parse string into list of identifiers */
+	if (!SplitIdentifierString(rawname, ',', &namelist))
+	{
+		/* syntax error in name list */
+		pfree(rawname);
+		list_free(namelist);
+		return InvalidOid;
+	}
+	nnames = list_length(namelist);
+
+	/* Fast path for temp_tablespaces == "" */
+	if (nnames == 0)
+	{
+		pfree(rawname);
+		list_free(namelist);
+		return InvalidOid;
+	}
+
+	/* Select a random element */
+	if (nnames == 1)			/* no need for a random() call */
+		curname = (char *) linitial(namelist);
+	else
+		curname = (char *) list_nth(namelist, random() % nnames);
+
+	/*
+	 * Empty string means "database's default", else look up the tablespace.
+	 *
+	 * 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.
+	 */
+	if (curname[0] == '\0')
+		result = InvalidOid;
+	else
+		result = get_tablespace_oid(curname);
+
+	/*
+	 * Allow explicit specification of database's default tablespace in
+	 * temp_tablespaces without triggering permissions checks.
+	 */
+	if (result == MyDatabaseTableSpace)
+		result = InvalidOid;
+
+	pfree(rawname);
+	list_free(namelist);
+
+	return result;
+}
+
+
+/*
  * get_tablespace_oid - given a tablespace name, look up the OID
  *
  * Returns InvalidOid if tablespace name not found.
@@ -950,7 +1135,11 @@ get_tablespace_oid(const char *tablespacename)
 	HeapTuple	tuple;
 	ScanKeyData entry[1];
 
-	/* Search pg_tablespace */
+	/*
+	 * Search pg_tablespace.  We use a heapscan here even though there is an
+	 * index on name, on the theory that pg_tablespace will usually have just
+	 * a few entries and so an indexed lookup is a waste of effort.
+	 */
 	rel = heap_open(TableSpaceRelationId, AccessShareLock);
 
 	ScanKeyInit(&entry[0],
@@ -960,6 +1149,7 @@ get_tablespace_oid(const char *tablespacename)
 	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 = HeapTupleGetOid(tuple);
 	else
@@ -985,7 +1175,11 @@ get_tablespace_name(Oid spc_oid)
 	HeapTuple	tuple;
 	ScanKeyData entry[1];
 
-	/* Search pg_tablespace */
+	/*
+	 * Search pg_tablespace.  We use a heapscan here even though there is an
+	 * index on oid, on the theory that pg_tablespace will usually have just
+	 * a few entries and so an indexed lookup is a waste of effort.
+	 */
 	rel = heap_open(TableSpaceRelationId, AccessShareLock);
 
 	ScanKeyInit(&entry[0],
diff --git a/src/backend/executor/execMain.c b/src/backend/executor/execMain.c
index 298d0c69c9d..82b17b19f01 100644
--- a/src/backend/executor/execMain.c
+++ b/src/backend/executor/execMain.c
@@ -26,7 +26,7 @@
  *
  *
  * IDENTIFICATION
- *	  $PostgreSQL: pgsql/src/backend/executor/execMain.c,v 1.293 2007/04/27 22:05:47 tgl Exp $
+ *	  $PostgreSQL: pgsql/src/backend/executor/execMain.c,v 1.294 2007/06/03 17:07:00 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -2430,7 +2430,7 @@ OpenIntoRel(QueryDesc *queryDesc)
 					   get_namespace_name(namespaceId));
 
 	/*
-	 * Select tablespace to use.  If not specified, use default_tablespace
+	 * Select tablespace to use.  If not specified, use default tablespace
 	 * (which may in turn default to database's default).
 	 */
 	if (into->tableSpaceName)
@@ -2444,7 +2444,7 @@ OpenIntoRel(QueryDesc *queryDesc)
 	}
 	else
 	{
-		tablespaceId = GetDefaultTablespace();
+		tablespaceId = GetDefaultTablespace(into->rel->istemp);
 		/* note InvalidOid is OK in this case */
 	}
 
diff --git a/src/backend/executor/nodeHash.c b/src/backend/executor/nodeHash.c
index 3f13b199c9e..348606e88bb 100644
--- a/src/backend/executor/nodeHash.c
+++ b/src/backend/executor/nodeHash.c
@@ -8,7 +8,7 @@
  *
  *
  * IDENTIFICATION
- *	  $PostgreSQL: pgsql/src/backend/executor/nodeHash.c,v 1.112 2007/06/01 17:38:44 tgl Exp $
+ *	  $PostgreSQL: pgsql/src/backend/executor/nodeHash.c,v 1.113 2007/06/03 17:07:14 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -24,6 +24,7 @@
 #include <math.h>
 #include <limits.h>
 
+#include "commands/tablespace.h"
 #include "executor/execdebug.h"
 #include "executor/hashjoin.h"
 #include "executor/instrument.h"
@@ -266,6 +267,7 @@ ExecHashTableCreate(Hash *node, List *hashOperators)
 	hashtable->totalTuples = 0;
 	hashtable->innerBatchFile = NULL;
 	hashtable->outerBatchFile = NULL;
+	hashtable->hashTblSpc = InvalidOid;
 	hashtable->spaceUsed = 0;
 	hashtable->spaceAllowed = work_mem * 1024L;
 
@@ -325,6 +327,8 @@ ExecHashTableCreate(Hash *node, List *hashOperators)
 		hashtable->outerBatchFile = (BufFile **)
 			palloc0(nbatch * sizeof(BufFile *));
 		/* The files will not be opened until needed... */
+		/* ... but we want to choose the tablespace only once */
+		hashtable->hashTblSpc = GetTempTablespace();
 	}
 
 	/*
@@ -506,6 +510,8 @@ ExecHashIncreaseNumBatches(HashJoinTable hashtable)
 			palloc0(nbatch * sizeof(BufFile *));
 		hashtable->outerBatchFile = (BufFile **)
 			palloc0(nbatch * sizeof(BufFile *));
+		/* time to choose the tablespace, too */
+		hashtable->hashTblSpc = GetTempTablespace();
 	}
 	else
 	{
@@ -558,7 +564,8 @@ ExecHashIncreaseNumBatches(HashJoinTable hashtable)
 			{
 				/* dump it out */
 				Assert(batchno > curbatch);
-				ExecHashJoinSaveTuple(HJTUPLE_MINTUPLE(tuple),
+				ExecHashJoinSaveTuple(hashtable,
+									  HJTUPLE_MINTUPLE(tuple),
 									  tuple->hashvalue,
 									  &hashtable->innerBatchFile[batchno]);
 				/* and remove from hash table */
@@ -650,7 +657,8 @@ ExecHashTableInsert(HashJoinTable hashtable,
 		 * put the tuple into a temp file for later batches
 		 */
 		Assert(batchno > hashtable->curbatch);
-		ExecHashJoinSaveTuple(tuple,
+		ExecHashJoinSaveTuple(hashtable,
+							  tuple,
 							  hashvalue,
 							  &hashtable->innerBatchFile[batchno]);
 	}
diff --git a/src/backend/executor/nodeHashjoin.c b/src/backend/executor/nodeHashjoin.c
index f75a09e717f..495548ca821 100644
--- a/src/backend/executor/nodeHashjoin.c
+++ b/src/backend/executor/nodeHashjoin.c
@@ -8,7 +8,7 @@
  *
  *
  * IDENTIFICATION
- *	  $PostgreSQL: pgsql/src/backend/executor/nodeHashjoin.c,v 1.89 2007/02/02 00:07:03 tgl Exp $
+ *	  $PostgreSQL: pgsql/src/backend/executor/nodeHashjoin.c,v 1.90 2007/06/03 17:07:26 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -223,7 +223,8 @@ ExecHashJoin(HashJoinState *node)
 				 * in the corresponding outer-batch file.
 				 */
 				Assert(batchno > hashtable->curbatch);
-				ExecHashJoinSaveTuple(ExecFetchSlotMinimalTuple(outerTupleSlot),
+				ExecHashJoinSaveTuple(hashtable,
+									  ExecFetchSlotMinimalTuple(outerTupleSlot),
 									  hashvalue,
 									  &hashtable->outerBatchFile[batchno]);
 				node->hj_NeedNewOuter = true;
@@ -754,7 +755,8 @@ start_over:
  * will get messed up.
  */
 void
-ExecHashJoinSaveTuple(MinimalTuple tuple, uint32 hashvalue,
+ExecHashJoinSaveTuple(HashJoinTable hashtable,
+					  MinimalTuple tuple, uint32 hashvalue,
 					  BufFile **fileptr)
 {
 	BufFile    *file = *fileptr;
@@ -763,7 +765,7 @@ ExecHashJoinSaveTuple(MinimalTuple tuple, uint32 hashvalue,
 	if (file == NULL)
 	{
 		/* First write to this batch file, so open it. */
-		file = BufFileCreateTemp(false);
+		file = BufFileCreateTemp(false, hashtable->hashTblSpc);
 		*fileptr = file;
 	}
 
diff --git a/src/backend/storage/file/buffile.c b/src/backend/storage/file/buffile.c
index 4f3a8a311bf..6bb40558961 100644
--- a/src/backend/storage/file/buffile.c
+++ b/src/backend/storage/file/buffile.c
@@ -7,7 +7,7 @@
  * Portions Copyright (c) 1994, Regents of the University of California
  *
  * IDENTIFICATION
- *	  $PostgreSQL: pgsql/src/backend/storage/file/buffile.c,v 1.26 2007/06/01 23:43:11 tgl Exp $
+ *	  $PostgreSQL: pgsql/src/backend/storage/file/buffile.c,v 1.27 2007/06/03 17:07:30 tgl Exp $
  *
  * NOTES:
  *
@@ -60,6 +60,7 @@ struct BufFile
 	 * offsets[i] is the current seek position of files[i].  We use this to
 	 * avoid making redundant FileSeek calls.
 	 */
+	Oid			tblspcOid;		/* tablespace to use (InvalidOid = default) */
 
 	bool		isTemp;			/* can only add files if this is TRUE */
 	bool		isInterXact;	/* keep open over transactions? */
@@ -85,7 +86,7 @@ static int	BufFileFlush(BufFile *file);
 
 /*
  * Create a BufFile given the first underlying physical file.
- * NOTE: caller must set isTemp true if appropriate.
+ * NOTE: caller must set tblspcOid, isTemp, isInterXact if appropriate.
  */
 static BufFile *
 makeBufFile(File firstfile)
@@ -97,7 +98,9 @@ makeBufFile(File firstfile)
 	file->files[0] = firstfile;
 	file->offsets = (long *) palloc(sizeof(long));
 	file->offsets[0] = 0L;
+	file->tblspcOid = InvalidOid;
 	file->isTemp = false;
+	file->isInterXact = false;
 	file->dirty = false;
 	file->curFile = 0;
 	file->curOffset = 0L;
@@ -116,7 +119,7 @@ extendBufFile(BufFile *file)
 	File		pfile;
 
 	Assert(file->isTemp);
-	pfile = OpenTemporaryFile(file->isInterXact);
+	pfile = OpenTemporaryFile(file->isInterXact, file->tblspcOid);
 	Assert(pfile >= 0);
 
 	file->files = (File *) repalloc(file->files,
@@ -133,19 +136,24 @@ extendBufFile(BufFile *file)
  * multiple temporary files if more than MAX_PHYSICAL_FILESIZE bytes are
  * written to it).
  *
+ * If interXact is true, the temp file will not be automatically deleted
+ * at end of transaction.  If tblspcOid is not InvalidOid, the temp file
+ * is created in the specified tablespace instead of the default one.
+ *
  * Note: if interXact is true, the caller had better be calling us in a
  * memory context that will survive across transaction boundaries.
  */
 BufFile *
-BufFileCreateTemp(bool interXact)
+BufFileCreateTemp(bool interXact, Oid tblspcOid)
 {
 	BufFile    *file;
 	File		pfile;
 
-	pfile = OpenTemporaryFile(interXact);
+	pfile = OpenTemporaryFile(interXact, tblspcOid);
 	Assert(pfile >= 0);
 
 	file = makeBufFile(pfile);
+	file->tblspcOid = tblspcOid;
 	file->isTemp = true;
 	file->isInterXact = interXact;
 
diff --git a/src/backend/storage/file/fd.c b/src/backend/storage/file/fd.c
index 1d04caad4f2..5c1be83ed04 100644
--- a/src/backend/storage/file/fd.c
+++ b/src/backend/storage/file/fd.c
@@ -7,7 +7,7 @@
  * Portions Copyright (c) 1994, Regents of the University of California
  *
  * IDENTIFICATION
- *	  $PostgreSQL: pgsql/src/backend/storage/file/fd.c,v 1.137 2007/03/06 02:06:14 momjian Exp $
+ *	  $PostgreSQL: pgsql/src/backend/storage/file/fd.c,v 1.138 2007/06/03 17:07:31 tgl Exp $
  *
  * NOTES:
  *
@@ -48,6 +48,7 @@
 
 #include "miscadmin.h"
 #include "access/xact.h"
+#include "catalog/pg_tablespace.h"
 #include "storage/fd.h"
 #include "storage/ipc.h"
 #include "utils/guc.h"
@@ -225,7 +226,7 @@ static File AllocateVfd(void);
 static void FreeVfd(File file);
 
 static int	FileAccess(File file);
-static char *make_database_relative(const char *filename);
+static File OpenTemporaryFileInTablespace(Oid tblspcOid, bool rejectError);
 static void AtProcExit_Files(int code, Datum arg);
 static void CleanupTempFiles(bool isProcExit);
 static void RemovePgTempFilesInDir(const char *tmpdirname);
@@ -721,23 +722,6 @@ FreeVfd(File file)
 	VfdCache[0].nextFree = file;
 }
 
-/*
- * make_database_relative()
- *		Prepend DatabasePath to the given file name.
- *
- * Result is a palloc'd string.
- */
-static char *
-make_database_relative(const char *filename)
-{
-	char	   *buf;
-
-	Assert(!is_absolute_path(filename));
-	buf = (char *) palloc(strlen(DatabasePath) + strlen(filename) + 2);
-	sprintf(buf, "%s/%s", DatabasePath, filename);
-	return buf;
-}
-
 /* returns 0 on success, -1 on re-open failure (with errno set) */
 static int
 FileAccess(File file)
@@ -845,24 +829,6 @@ PathNameOpenFile(FileName fileName, int fileFlags, int fileMode)
 }
 
 /*
- * open a file in the database directory ($PGDATA/base/DIROID/)
- *
- * The passed name MUST be a relative path.  Effectively, this
- * prepends DatabasePath to it and then acts like PathNameOpenFile.
- */
-File
-FileNameOpenFile(FileName fileName, int fileFlags, int fileMode)
-{
-	File		fd;
-	char	   *fname;
-
-	fname = make_database_relative(fileName);
-	fd = PathNameOpenFile(fname, fileFlags, fileMode);
-	pfree(fname);
-	return fd;
-}
-
-/*
  * Open a temporary file that will disappear when we close it.
  *
  * This routine takes care of generating an appropriate tempfile name.
@@ -874,62 +840,110 @@ FileNameOpenFile(FileName fileName, int fileFlags, int fileMode)
  * that created them, so this should be false -- but if you need
  * "somewhat" temporary storage, this might be useful. In either case,
  * the file is removed when the File is explicitly closed.
+ *
+ * tblspcOid: the Oid of the tablespace where the temp file should be created.
+ * If InvalidOid, or if the tablespace can't be found, we silently fall back
+ * to the database's default tablespace.
  */
 File
-OpenTemporaryFile(bool interXact)
+OpenTemporaryFile(bool interXact, Oid tblspcOid)
 {
+	File		file = 0;
+
+	/*
+	 * If caller specified a tablespace, try to create there.
+	 */
+	if (OidIsValid(tblspcOid))
+		file = OpenTemporaryFileInTablespace(tblspcOid, false);
+
+	/*
+	 * If not, or if tablespace is bad, create in database's default
+	 * tablespace.  MyDatabaseTableSpace should normally be set before we get
+	 * here, but just in case it isn't, fall back to pg_default tablespace.
+	 */
+	if (file <= 0)
+		file = OpenTemporaryFileInTablespace(MyDatabaseTableSpace ?
+											 MyDatabaseTableSpace :
+											 DEFAULTTABLESPACE_OID,
+											 true);
+
+	/* Mark it for deletion at close */
+	VfdCache[file].fdstate |= FD_TEMPORARY;
+
+	/* Mark it for deletion at EOXact */
+	if (!interXact)
+	{
+		VfdCache[file].fdstate |= FD_XACT_TEMPORARY;
+		VfdCache[file].create_subid = GetCurrentSubTransactionId();
+	}
+
+	return file;
+}
+
+/*
+ * Open a temporary file in a specific tablespace.
+ * Subroutine for OpenTemporaryFile, which see for details.
+ */
+static File
+OpenTemporaryFileInTablespace(Oid tblspcOid, bool rejectError)
+{
+	char		tempdirpath[MAXPGPATH];
 	char		tempfilepath[MAXPGPATH];
 	File		file;
 
 	/*
+	 * Identify the tempfile directory for this tablespace.
+	 *
+	 * If someone tries to specify pg_global, use pg_default instead.
+	 */
+	if (tblspcOid == DEFAULTTABLESPACE_OID ||
+		tblspcOid == GLOBALTABLESPACE_OID)
+	{
+		/* The default tablespace is {datadir}/base */
+		snprintf(tempdirpath, sizeof(tempdirpath), "base/%s",
+				 PG_TEMP_FILES_DIR);
+	}
+	else
+	{
+		/* All other tablespaces are accessed via symlinks */
+		snprintf(tempdirpath, sizeof(tempdirpath), "pg_tblspc/%u/%s",
+				 tblspcOid, PG_TEMP_FILES_DIR);
+	}
+
+	/*
 	 * Generate a tempfile name that should be unique within the current
 	 * database instance.
 	 */
-	snprintf(tempfilepath, sizeof(tempfilepath),
-			 "%s/%s%d.%ld", PG_TEMP_FILES_DIR, PG_TEMP_FILE_PREFIX,
-			 MyProcPid, tempFileCounter++);
+	snprintf(tempfilepath, sizeof(tempfilepath), "%s/%s%d.%ld",
+			 tempdirpath, PG_TEMP_FILE_PREFIX, MyProcPid, tempFileCounter++);
 
 	/*
 	 * Open the file.  Note: we don't use O_EXCL, in case there is an orphaned
 	 * temp file that can be reused.
 	 */
-	file = FileNameOpenFile(tempfilepath,
+	file = PathNameOpenFile(tempfilepath,
 							O_RDWR | O_CREAT | O_TRUNC | PG_BINARY,
 							0600);
 	if (file <= 0)
 	{
-		char	   *dirpath;
-
 		/*
-		 * We might need to create the pg_tempfiles subdirectory, if no one
-		 * has yet done so.
+		 * We might need to create the tablespace's tempfile directory,
+		 * if no one has yet done so.
 		 *
 		 * Don't check for error from mkdir; it could fail if someone else
 		 * just did the same thing.  If it doesn't work then we'll bomb out on
 		 * the second create attempt, instead.
 		 */
-		dirpath = make_database_relative(PG_TEMP_FILES_DIR);
-		mkdir(dirpath, S_IRWXU);
-		pfree(dirpath);
+		mkdir(tempdirpath, S_IRWXU);
 
-		file = FileNameOpenFile(tempfilepath,
+		file = PathNameOpenFile(tempfilepath,
 								O_RDWR | O_CREAT | O_TRUNC | PG_BINARY,
 								0600);
-		if (file <= 0)
+		if (file <= 0 && rejectError)
 			elog(ERROR, "could not create temporary file \"%s\": %m",
 				 tempfilepath);
 	}
 
-	/* Mark it for deletion at close */
-	VfdCache[file].fdstate |= FD_TEMPORARY;
-
-	/* Mark it for deletion at EOXact */
-	if (!interXact)
-	{
-		VfdCache[file].fdstate |= FD_XACT_TEMPORARY;
-		VfdCache[file].create_subid = GetCurrentSubTransactionId();
-	}
-
 	return file;
 }
 
@@ -1643,27 +1657,32 @@ void
 RemovePgTempFiles(void)
 {
 	char		temp_path[MAXPGPATH];
-	DIR		   *db_dir;
-	struct dirent *db_de;
+	DIR		   *spc_dir;
+	struct dirent *spc_de;
+
+	/*
+	 * First process temp files in pg_default ($PGDATA/base)
+	 */
+	snprintf(temp_path, sizeof(temp_path), "base/%s", PG_TEMP_FILES_DIR);
+	RemovePgTempFilesInDir(temp_path);
 
 	/*
-	 * Cycle through pgsql_tmp directories for all databases and remove old
-	 * temp files.
+	 * Cycle through temp directories for all non-default tablespaces.
 	 */
-	db_dir = AllocateDir("base");
+	spc_dir = AllocateDir("pg_tblspc");
 
-	while ((db_de = ReadDir(db_dir, "base")) != NULL)
+	while ((spc_de = ReadDir(spc_dir, "pg_tblspc")) != NULL)
 	{
-		if (strcmp(db_de->d_name, ".") == 0 ||
-			strcmp(db_de->d_name, "..") == 0)
+		if (strcmp(spc_de->d_name, ".") == 0 ||
+			strcmp(spc_de->d_name, "..") == 0)
 			continue;
 
-		snprintf(temp_path, sizeof(temp_path), "base/%s/%s",
-				 db_de->d_name, PG_TEMP_FILES_DIR);
+		snprintf(temp_path, sizeof(temp_path), "pg_tblspc/%s/%s",
+				 spc_de->d_name, PG_TEMP_FILES_DIR);
 		RemovePgTempFilesInDir(temp_path);
 	}
 
-	FreeDir(db_dir);
+	FreeDir(spc_dir);
 
 	/*
 	 * In EXEC_BACKEND case there is a pgsql_tmp directory at the top level of
diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c
index b8622328757..ec547a66706 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.392 2007/06/02 23:36:35 petere Exp $
+ *	  $PostgreSQL: pgsql/src/backend/utils/misc/guc.c,v 1.393 2007/06/03 17:07:34 tgl Exp $
  *
  *--------------------------------------------------------------------
  */
@@ -103,6 +103,7 @@ extern bool Log_disconnections;
 extern int	CommitDelay;
 extern int	CommitSiblings;
 extern char *default_tablespace;
+extern char *temp_tablespaces;
 extern bool fullPageWrites;
 
 #ifdef TRACE_SORT
@@ -1968,6 +1969,16 @@ static struct config_string ConfigureNamesString[] =
 	},
 
 	{
+		{"temp_tablespaces", PGC_USERSET, CLIENT_CONN_STATEMENT,
+			gettext_noop("Sets the tablespace(s) to use for temporary tables and sort files."),
+			NULL,
+			GUC_LIST_INPUT | GUC_LIST_QUOTE
+		},
+		&temp_tablespaces,
+		"", assign_temp_tablespaces, 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 e7d8e41b0e1..c56f2fd7092 100644
--- a/src/backend/utils/misc/postgresql.conf.sample
+++ b/src/backend/utils/misc/postgresql.conf.sample
@@ -408,6 +408,8 @@
 #search_path = '"$user",public'		# schema names
 #default_tablespace = ''		# a tablespace name, '' uses
 					# the default
+#temp_tablespaces = ''			# a list of tablespace names,
+					# '' uses only default tablespace
 #check_function_bodies = on
 #default_transaction_isolation = 'read committed'
 #default_transaction_read_only = off
diff --git a/src/backend/utils/sort/logtape.c b/src/backend/utils/sort/logtape.c
index 1592bb30e9f..8ded3ccf4fd 100644
--- a/src/backend/utils/sort/logtape.c
+++ b/src/backend/utils/sort/logtape.c
@@ -70,13 +70,14 @@
  * Portions Copyright (c) 1994, Regents of the University of California
  *
  * IDENTIFICATION
- *	  $PostgreSQL: pgsql/src/backend/utils/sort/logtape.c,v 1.23 2007/01/05 22:19:47 momjian Exp $
+ *	  $PostgreSQL: pgsql/src/backend/utils/sort/logtape.c,v 1.24 2007/06/03 17:08:23 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
 
 #include "postgres.h"
 
+#include "commands/tablespace.h"
 #include "storage/buffile.h"
 #include "utils/logtape.h"
 
@@ -528,7 +529,7 @@ LogicalTapeSetCreate(int ntapes)
 	Assert(ntapes > 0);
 	lts = (LogicalTapeSet *) palloc(sizeof(LogicalTapeSet) +
 									(ntapes - 1) *sizeof(LogicalTape));
-	lts->pfile = BufFileCreateTemp(false);
+	lts->pfile = BufFileCreateTemp(false, GetTempTablespace());
 	lts->nFileBlocks = 0L;
 	lts->forgetFreeSpace = false;
 	lts->blocksSorted = true;	/* a zero-length array is sorted ... */
diff --git a/src/backend/utils/sort/tuplestore.c b/src/backend/utils/sort/tuplestore.c
index d25bb122b58..6370232bc14 100644
--- a/src/backend/utils/sort/tuplestore.c
+++ b/src/backend/utils/sort/tuplestore.c
@@ -38,7 +38,7 @@
  * Portions Copyright (c) 1994, Regents of the University of California
  *
  * IDENTIFICATION
- *	  $PostgreSQL: pgsql/src/backend/utils/sort/tuplestore.c,v 1.31 2007/05/21 17:57:34 tgl Exp $
+ *	  $PostgreSQL: pgsql/src/backend/utils/sort/tuplestore.c,v 1.32 2007/06/03 17:08:26 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -46,6 +46,7 @@
 #include "postgres.h"
 
 #include "access/heapam.h"
+#include "commands/tablespace.h"
 #include "executor/executor.h"
 #include "storage/buffile.h"
 #include "utils/memutils.h"
@@ -424,8 +425,14 @@ tuplestore_puttuple_common(Tuplestorestate *state, void *tuple)
 
 			/*
 			 * Nope; time to switch to tape-based operation.
+			 *
+			 * If the temp table is slated to outlive the current transaction,
+			 * force it into my database's default tablespace, so that it will
+			 * not pose a threat to possible tablespace drop attempts.
 			 */
-			state->myfile = BufFileCreateTemp(state->interXact);
+			state->myfile = BufFileCreateTemp(state->interXact,
+											  state->interXact ? InvalidOid :
+											  GetTempTablespace());
 			state->status = TSS_WRITEFILE;
 			dumptuples(state);
 			break;
diff --git a/src/include/commands/tablespace.h b/src/include/commands/tablespace.h
index 47e4beb255c..78b86ffb689 100644
--- a/src/include/commands/tablespace.h
+++ b/src/include/commands/tablespace.h
@@ -7,7 +7,7 @@
  * Portions Copyright (c) 1996-2007, PostgreSQL Global Development Group
  * Portions Copyright (c) 1994, Regents of the University of California
  *
- * $PostgreSQL: pgsql/src/include/commands/tablespace.h,v 1.16 2007/03/06 02:06:15 momjian Exp $
+ * $PostgreSQL: pgsql/src/include/commands/tablespace.h,v 1.17 2007/06/03 17:08:29 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -40,7 +40,8 @@ extern void AlterTableSpaceOwner(const char *name, Oid newOwnerId);
 
 extern void TablespaceCreateDbspace(Oid spcNode, Oid dbNode, bool isRedo);
 
-extern Oid	GetDefaultTablespace(void);
+extern Oid	GetDefaultTablespace(bool forTemp);
+extern Oid	GetTempTablespace(void);
 
 extern Oid	get_tablespace_oid(const char *tablespacename);
 extern char *get_tablespace_name(Oid spc_oid);
diff --git a/src/include/executor/hashjoin.h b/src/include/executor/hashjoin.h
index 8056cd88e92..70db576ebee 100644
--- a/src/include/executor/hashjoin.h
+++ b/src/include/executor/hashjoin.h
@@ -7,7 +7,7 @@
  * Portions Copyright (c) 1996-2007, PostgreSQL Global Development Group
  * Portions Copyright (c) 1994, Regents of the University of California
  *
- * $PostgreSQL: pgsql/src/include/executor/hashjoin.h,v 1.45 2007/06/01 17:38:44 tgl Exp $
+ * $PostgreSQL: pgsql/src/include/executor/hashjoin.h,v 1.46 2007/06/03 17:08:32 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -102,6 +102,8 @@ typedef struct HashJoinTableData
 	BufFile   **innerBatchFile; /* buffered virtual temp file per batch */
 	BufFile   **outerBatchFile; /* buffered virtual temp file per batch */
 
+	Oid			hashTblSpc;		/* tablespace to put temp files in */
+
 	/*
 	 * Info about the datatype-specific hash functions for the datatypes being
 	 * hashed. These are arrays of the same length as the number of hash join
diff --git a/src/include/executor/nodeHashjoin.h b/src/include/executor/nodeHashjoin.h
index 6950bd300a0..2de661b8c12 100644
--- a/src/include/executor/nodeHashjoin.h
+++ b/src/include/executor/nodeHashjoin.h
@@ -7,7 +7,7 @@
  * Portions Copyright (c) 1996-2007, PostgreSQL Global Development Group
  * Portions Copyright (c) 1994, Regents of the University of California
  *
- * $PostgreSQL: pgsql/src/include/executor/nodeHashjoin.h,v 1.34 2007/01/05 22:19:54 momjian Exp $
+ * $PostgreSQL: pgsql/src/include/executor/nodeHashjoin.h,v 1.35 2007/06/03 17:08:32 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -23,7 +23,8 @@ extern TupleTableSlot *ExecHashJoin(HashJoinState *node);
 extern void ExecEndHashJoin(HashJoinState *node);
 extern void ExecReScanHashJoin(HashJoinState *node, ExprContext *exprCtxt);
 
-extern void ExecHashJoinSaveTuple(MinimalTuple tuple, uint32 hashvalue,
-					  BufFile **fileptr);
+extern void ExecHashJoinSaveTuple(HashJoinTable hashtable,
+								  MinimalTuple tuple, uint32 hashvalue,
+								  BufFile **fileptr);
 
 #endif   /* NODEHASHJOIN_H */
diff --git a/src/include/storage/buffile.h b/src/include/storage/buffile.h
index 45e4137ac3b..cee7eeb934b 100644
--- a/src/include/storage/buffile.h
+++ b/src/include/storage/buffile.h
@@ -18,7 +18,7 @@
  * Portions Copyright (c) 1996-2007, PostgreSQL Global Development Group
  * Portions Copyright (c) 1994, Regents of the University of California
  *
- * $PostgreSQL: pgsql/src/include/storage/buffile.h,v 1.20 2007/01/05 22:19:57 momjian Exp $
+ * $PostgreSQL: pgsql/src/include/storage/buffile.h,v 1.21 2007/06/03 17:08:32 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -34,7 +34,7 @@ typedef struct BufFile BufFile;
  * prototypes for functions in buffile.c
  */
 
-extern BufFile *BufFileCreateTemp(bool interXact);
+extern BufFile *BufFileCreateTemp(bool interXact, Oid tblspcOid);
 extern void BufFileClose(BufFile *file);
 extern size_t BufFileRead(BufFile *file, void *ptr, size_t size);
 extern size_t BufFileWrite(BufFile *file, void *ptr, size_t size);
diff --git a/src/include/storage/fd.h b/src/include/storage/fd.h
index fe7419a3d30..bb5772f64d3 100644
--- a/src/include/storage/fd.h
+++ b/src/include/storage/fd.h
@@ -7,7 +7,7 @@
  * Portions Copyright (c) 1996-2007, PostgreSQL Global Development Group
  * Portions Copyright (c) 1994, Regents of the University of California
  *
- * $PostgreSQL: pgsql/src/include/storage/fd.h,v 1.57 2007/01/05 22:19:57 momjian Exp $
+ * $PostgreSQL: pgsql/src/include/storage/fd.h,v 1.58 2007/06/03 17:08:33 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -59,9 +59,8 @@ extern int	max_files_per_process;
  */
 
 /* Operations on virtual Files --- equivalent to Unix kernel file ops */
-extern File FileNameOpenFile(FileName fileName, int fileFlags, int fileMode);
 extern File PathNameOpenFile(FileName fileName, int fileFlags, int fileMode);
-extern File OpenTemporaryFile(bool interXact);
+extern File OpenTemporaryFile(bool interXact, Oid tblspcOid);
 extern void FileClose(File file);
 extern void FileUnlink(File file);
 extern int	FileRead(File file, char *buffer, int amount);
diff --git a/src/include/utils/guc.h b/src/include/utils/guc.h
index e3315c72257..d1f2eb43f43 100644
--- a/src/include/utils/guc.h
+++ b/src/include/utils/guc.h
@@ -7,7 +7,7 @@
  * Copyright (c) 2000-2007, PostgreSQL Global Development Group
  * Written by Peter Eisentraut <peter_e@gmx.net>.
  *
- * $PostgreSQL: pgsql/src/include/utils/guc.h,v 1.81 2007/04/12 06:53:48 neilc Exp $
+ * $PostgreSQL: pgsql/src/include/utils/guc.h,v 1.82 2007/06/03 17:08:34 tgl Exp $
  *--------------------------------------------------------------------
  */
 #ifndef GUC_H
@@ -225,6 +225,8 @@ extern void read_nondefault_variables(void);
 /* in commands/tablespace.c */
 extern const char *assign_default_tablespace(const char *newval,
 						  bool doit, GucSource source);
+extern const char *assign_temp_tablespaces(const char *newval,
+						  bool doit, GucSource source);
 
 /* in utils/adt/regexp.c */
 extern const char *assign_regex_flavor(const char *value,