Tom Lane a écrit :
> Bernd Helmle <mailings@oopsware.de> writes:
>> * We really should error out when trying to copy into the same tablespace
>> the database already lives in.
>
> No, I think that should just be a no-op. We don't for instance throw
> error when you ALTER OWNER to the existing owner.
>
Moreover, ALTER TABLE SET TABLESPACE is silent when a user tries to move
an object to the tablespace it already belongs to.
>> * The current implementation cannot merge a tablespace used by some
>> database objects already, for example:
>
> Hmm --- there's more there than meets the eye. To handle that case
> correctly, you'd have to go into the DB's pg_class and change the
> recorded tablespace for those objects to zero. (Fail to do so, and
> you've got a mess when you move the DB to yet another tablespace.)
>
> I tend to agree that throwing an error is sufficient, as long as it's
> a clear error message.
>
OK. I added a code that checks the existence of the target tablespace
directory before executing copydir. If it found an empty directory, it
deletes it.
The error message looks like this:
postgres=# alter database test set tablespace db2;
ERROR: some relations are already in the target tablespace "db2"
HINT: You need to move them back to the default tablespace before using
this command.
Here is the complete test case:
postgres=# create database bernd;
CREATE DATABASE
postgres=# create database test;
CREATE DATABASE
postgres=# create tablespace db1 location
'/home/guillaume/postgresql_tblspc/db1';
CREATE TABLESPACE
postgres=# create tablespace db2 location
'/home/guillaume/postgresql_tblspc/db2';
CREATE TABLESPACE
postgres=# \c test
psql (8.4devel)
You are now connected to database "test".
test=# create table foo(id integer) tablespace db2;
CREATE TABLE
test=# \c bernd
psql (8.4devel)
You are now connected to database "bernd".
bernd=# alter database test set tablespace db2;
ERROR: some relations are already in the target tablespace "db2"
HINT: You need to move them back to the default tablespace before using
this command.
bernd=# \c test
psql (8.4devel)
You are now connected to database "test".
test=# alter table foo set tablespace pg_default;
ALTER TABLE
test=# \c bernd
psql (8.4devel)
You are now connected to database "bernd".
bernd=# alter database test set tablespace db2;
ALTER DATABASE
v4 patch attached.
Thanks.
--
Guillaume.
http://www.postgresqlfr.org
http://dalibo.com