Re: CREATE DATABASE with tablespace fix

Gavin Sherry wrote:
> 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?)

OK, I am finally following this issue. I couldn't figure out why we
were throwing any error, let alone a strange one. Then I read the
command in dbcommands.c:

/*
* 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.
*/

I am a little confused why we can't just merge the directories? The
relfilenodes would guarantee that the files are unique, right?

Seems we should apply this patch with improved wording removing the "in
use" mention because it isn't an "in use" issue as much as a references
issue, and add a TODO to allow tablespaces to merge directories.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073