Attached is a patch resolving the issue raised here:
http://groups.google.com.au/groups?q=tablespaces+group:comp.databases.postgresql.hackers&hl=en&lr=&ie=UTF-8&group=comp.databases.postgresql.hackers&scoring=d&selm=Pine.LNX.4.58.0407281411470.17889%40linuxworld.com.au&rnum=4
When I was testing this, I noticed the following:
template1=# create tablespace blah location '/home/gavins/pgsql/blah';
CREATE TABLESPACE
template1=# create table foo(i int) tablespace blah;
CREATE TABLE
template1=# create database bar tablespace blah;
ERROR: template database "template1" is already using tablespace "blah"
DETAIL: The default tablespace for a database cannot be in use by the
template
database
template1=# drop table foo;
DROP TABLE
template1=# create database bar tablespace blah;
ERROR: template database "template1" is already using tablespace "blah"
DETAIL: The default tablespace for a database cannot be in use by the
template database
This happens because even though we drop the only entry in the tablespace
we keep the empty database directory around.
Should be test if the directory is empty and if so, not copy it (perhaps
only if it is in the tablespace which will be the default tablespace of
the new database?)
Gavin
Index: doc/src/sgml/ref/create_database.sgml
===================================================================
RCS file: /usr/local/cvsroot/pgsql-server/doc/src/sgml/ref/create_database.sgml,v
retrieving revision 1.41
diff -2 -c -r1.41 create_database.sgml
*** doc/src/sgml/ref/create_database.sgml 17 Jul 2004 16:33:31 -0000 1.41
--- doc/src/sgml/ref/create_database.sgml 8 Aug 2004 08:21:45 -0000
***************
*** 115,118 ****
--- 115,121 ----
<para>
Specifies the default tablespace for the new database.
+ The tablespace specified must not be in use by the template database.
+ </para>
+ <para>
If not specified, the same tablespace that is default for
the template database is used. See
Index: src/backend/commands/dbcommands.c
===================================================================
RCS file: /usr/local/cvsroot/pgsql-server/src/backend/commands/dbcommands.c,v
retrieving revision 1.139
diff -2 -c -r1.139 dbcommands.c
*** src/backend/commands/dbcommands.c 1 Aug 2004 20:30:48 -0000 1.139
--- src/backend/commands/dbcommands.c 8 Aug 2004 08:25:41 -0000
***************
*** 266,271 ****
{
char *tablespacename;
! AclResult aclresult;
!
tablespacename = strVal(dtablespacename->arg);
dst_deftablespace = get_tablespace_oid(tablespacename);
--- 266,272 ----
{
char *tablespacename;
! AclResult aclresult;
! char *srcpath;
! struct stat st;
tablespacename = strVal(dtablespacename->arg);
dst_deftablespace = get_tablespace_oid(tablespacename);
***************
*** 276,284 ****
tablespacename)));
/* check permissions */
! aclresult = pg_tablespace_aclcheck(dst_deftablespace, GetUserId(),
ACL_CREATE);
! if (aclresult != ACLCHECK_OK)
! aclcheck_error(aclresult, ACL_KIND_TABLESPACE,
! tablespacename);
}
else
--- 277,305 ----
tablespacename)));
/* check permissions */
! aclresult = pg_tablespace_aclcheck(dst_deftablespace, GetUserId(),
ACL_CREATE);
! if (aclresult != ACLCHECK_OK)
! aclcheck_error(aclresult, ACL_KIND_TABLESPACE,
! tablespacename);
!
! /* If we are trying to change the default tablespace of the template,
! * we require that the template not have any files in the new default
! * tablespace. This avoids the need to merge two subdirectories.
! * We can deal with the default tablespace, however.
! *
! * This could probably be improved later.
! */
!
! if(dst_deftablespace != DEFAULTTABLESPACE_OID)
! {
! srcpath = GetDatabasePath(src_dboid, dst_deftablespace);
!
! if(stat(srcpath, &st) == 0 || errno != ENOENT)
! ereport(ERROR,
! (errmsg("template database \"%s\" is already
using tablespace \"%s\"",
! dbtemplate, tablespacename),
! (errdetail("The default tablespace for a
database cannot be in use by the template database"))));
! pfree(srcpath);
! }
}
else
***************
*** 311,319 ****
* Iterate through all tablespaces of the template database, and
* copy each one to the new database.
- *
- * If we are trying to change the default tablespace of the template,
- * we require that the template not have any files in the new default
- * tablespace. This avoids the need to merge two subdirectories.
- * This could probably be improved later.
*/
rel = heap_openr(TableSpaceRelationName, AccessShareLock);
--- 333,336 ----
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
