I'd like to provide a way for DBAs to specify that the temporary files
needed to for sorting, holdable cursors and similar operations should be
created in a particular tablespace. (Right now these files are created
in the tablespace associated with the current database.)
Two ways to do this come to mind: via a GUC variable, or by setting a
property of CREATE DATABASE (that could be altered via ALTER DATABASE).
I think using a GUC variable is probably the better bet: it is more
flexible, since ALTER DATABASE ... SET and ALTER USER ... SET can be
used to define the GUC variable automatically for particular users and
databases.
So I'd like to add a GUC variable called something like
"scratch_tablespace". If undefined (the default), temporary files for
sorting/etc. will be created in the current database's tablespace. If
set to the name of an existent tablespace, that tablespace will be used
for temporary storage. If set to a nonexistent tablespace, a warning
will be printed and we'll fallback to using the current database's
tablespace.
Regarding naming, I considered calling the GUC variable
"temporary_tablespace" or something similar, but it seems to me that
this might cause confusion with temporary tables -- furthermore, it
might be an interesting feature to define a "temporary table tablespace"
in the future, leading to yet more confusion. I think "scratch
tablespace" is a pretty decent name for this concept, but I'm open to
suggestions.
Comments?
-Neil