Re: Patch for ALTER DATABASE WITH TABLESPACE

Tom Lane a écrit :
> Bernd Helmle <mailings(at)oopsware(dot)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