Re: WIP : change tablespace for a database

Hi,

I'm currently working on a patch for the TODO item :

Allow databases to be moved to different tablespaces

I already changed the syntax, added some code to move the relations of
the specific database to the target tablespace. It works. But I have
three issues I would like to discuss.

To get the list of relations to move, the user needs to be connected to
the database. It seems awkward to launch an ALTER DATABASE statement on
the currently open database. I mean, this is what I do know:

guillaume(at)laptop$ psql db1
psql (8.4devel)
Type "help" for help.

db1=# ALTER DATABASE db1 TABLESPACE ts1;

I don't think we can do another way, do you?

One other thing, a much worse one. It seems I can't move sys objects.
There's this comment in ATExecSetTableSpace function:

/*
* We can never allow moving of shared or nailed-in-cache relations,
* because we can't support changing their reltablespace values.
*/

I do understand we forbid moving a system relation when using the ALTER
TABLE SET TABLESPACE statement. But I wonder if it could be done with an
ALTER DATABASE TABLESPACE statement.

And last issue, when I do my ALTER DATABASE TABLESPACE statement, all
relations are moved on the target tablespace, and the dattablespace
field (in pg_database catalog) is updated with the new value. When this
is done, a few moment after, I get messages telling me PG_VERSION file
is not available in the pg_tblspc/<tablespace oid>/<database oid>
directory. I know about the set_short_version() function in
backend/commands/tablespace.c but I'm not sure I should use this
function. In fact, I wonder why this file is not created at the first
move of a table.

Comments and suggestions welcome!

--
Guillaume.
http://www.postgresqlfr.org
http://dalibo.com

Guillaume Lelarge <guillaume(at)lelarge(dot)info> writes:
> To get the list of relations to move, the user needs to be connected to
> the database.

Why? If what you are doing is changing the database's default
tablespace (which IMHO is what such a command ought to do) then
all you have to do is bulk-copy the per-DB subdirectory from
the old default tablespace to the new one. There's no reason to
think about it at the individual-relation level, and there won't be
any change to the contents of any catalog in the DB either (only
its pg_database row will change).

> One other thing, a much worse one. It seems I can't move sys objects.

The nailed relations have to stay in the DB's default tablespace,
yes. This is one of the reasons for my opinion above about what
the command's behavior should be.

regards, tom lane

Tom Lane a écrit :
> Guillaume Lelarge <guillaume(at)lelarge(dot)info> writes:
>> To get the list of relations to move, the user needs to be connected to
>> the database.
>
> Why? If what you are doing is changing the database's default
> tablespace (which IMHO is what such a command ought to do)

That's exactly what I'm trying to do.

> then
> all you have to do is bulk-copy the per-DB subdirectory from
> the old default tablespace to the new one. There's no reason to
> think about it at the individual-relation level, and there won't be
> any change to the contents of any catalog in the DB either (only
> its pg_database row will change).
>

So, I should be doing something like this:

* check various stuff (like permission and the fact that
no-one is connected on the target database)
* lock the database
* read the default tablespace dir (AllocateDir, ReadDir)
* move each file in it to the target tablespace (copydir, rmtree)
* change the default tablespace in pg_database

My current patch works well with this simple script:

guillaume(at)laptop$ psql postgres
psql (8.4devel)
Type "help" for help.

postgres=# create database db1;
CREATE DATABASE
postgres=# \c db1
psql (8.4devel)
You are now connected to database "db1".
db1=# create tablespace ts1
db1-# location '/home/guillaume/postgresql_tblspc';
CREATE TABLESPACE
db1=# create table t1(id int4);
CREATE TABLE
db1=# insert into t1 values (1);
INSERT 0 1
db1=# \c postgres
psql (8.4devel)
You are now connected to database "postgres".
postgres=# alter database db1 tablespace ts1;
NOTICE: alter tablespace db1 set tablespace ts1!
NOTICE: move base/16384 to pg_tblspc/16385/16384
NOTICE: remove base/16384
ALTER DATABASE
postgres=# \c db1
psql (8.4devel)
You are now connected to database "db1".
db1=# \d
List of relations
Schema | Name | Type | Owner
--------+------+-------+-----------
public | t1 | table | guillaume
(1 row)
db1=# select datname, dattablespace from pg_database
db1-# where datname='db1';
datname | dattablespace
---------+---------------
db1 | 16385
(1 row)
db1=# select relname, relfilenode, reltablespace from pg_class
db1-# where relname='t1';
relname | relfilenode | reltablespace
---------+-------------+---------------
t1 | 16386 | 0
(1 row)

So, it seems to work. I say "seems" because there's no XLOG record that
says I moved all relations from one tablespace to another. Am I right in
thinking I need to insert a new XLOG record? should I create a new one?

Thanks.

--
Guillaume.
http://www.postgresqlfr.org
http://dalibo.com

Guillaume Lelarge <guillaume(at)lelarge(dot)info> writes:
> So, it seems to work. I say "seems" because there's no XLOG record that
> says I moved all relations from one tablespace to another. Am I right in
> thinking I need to insert a new XLOG record? should I create a new one?

You certainly need to do *something* about that. But are you sure there
aren't any existing record types that will work? Look at CREATE/DROP
DATABASE.

regards, tom lane

Tom Lane a écrit :
> Guillaume Lelarge <guillaume(at)lelarge(dot)info> writes:
>> So, it seems to work. I say "seems" because there's no XLOG record that
>> says I moved all relations from one tablespace to another. Am I right in
>> thinking I need to insert a new XLOG record? should I create a new one?
>
> You certainly need to do *something* about that. But are you sure there
> aren't any existing record types that will work? Look at CREATE/DROP
> DATABASE.
>

You're right. I found what I needed. I will send my patch in a few moments.

Thanks a lot.

--
Guillaume.
http://www.postgresqlfr.org
http://dalibo.com