Re: Code bug or doc bug?

On Sun, Aug 24, 2014 at 6:26 PM, Josh Berkus <josh(at)agliodbs(dot)com> wrote:
> Quoth our docs
> (http://www.postgresql.org/docs/9.3/static/sql-alterdatabase.html)
>
> "The fourth form changes the default tablespace of the database. Only
> the database owner or a superuser can do this; you must also have create
> privilege for the new tablespace. This command physically moves any
> tables or indexes in the database's old default tablespace to the new
> tablespace. Note that tables and indexes in non-default tablespaces are
> not affected."
>
> Yet:
>
> jberkus=# alter database phc set tablespace ssd;
> ERROR: some relations of database "phc" are already in tablespace "ssd"
> HINT: You must move them back to the database's default tablespace
> before using this command.
>
> Aside from being a stupid limitation (I need to copy the tables back to
> the old tablespace so that I can recopy them to the new one?), the above
> seems to be in direct contradiction to the docs.

I think that it works OK to move objects from tablespace A to table B
while there are also objects in tablespace C, where B != C, but not to
move objects from tablespace A to tablespace B while there are already
objects in tablespace B. So I think the documentation is right as far
as it goes, but there's an undocumented limitation there.

The reasons for the limitation are:

1. We can't move a database while there are users connected to it.
This means that we can't modify any of the data in the database in the
process of relocating it. In particular, we can't update it's copy of
pg_class.

2. By convention, pg_class.reltablespace = 0 when the relation is in
the database's default tablespace, and only contains a non-zero OID
when the relation is in some other tablespace. This is what lets this
feature work at all: the pg_class.reltablespace value for every
relation we're moving is guaranteed to be 0 before the move, and is
still correctly valued as 0 after the move. But it also means there
can't be any relations from that database in the new tablespace,
because any such relations would need pg_class.reltablespace to get
updated from the OID of that tablespace to 0.

I don't see any easy way to lift this limitation. If it were possible
to move a database while users are connected to it, then of course you
could connect to the database to move it and update pg_class, but
you'd have to take an exclusive lock on every relation in the database
simultaneously, which might blow out the lock table, deadlock against
other sessions, and other messy things.

Another idea is to have a command that you can run, while connected to
a particular database, that updates the default tablespace for that
database without actually moving any data on disk - i.e. it sets
pg_database.dattablespace, and then updates every pg_class row where
reltablespace = 0 to the old default tablespace, and pg_class row
where reltablespace = the new tablespace ID to 0. Then you can move
individual relations afterwards if you feel like it. But that might
still require a lot of locks, and I think we also have a limitation
that some relations (the mapped ones?) have to be in the database's
default tablespace, which obviously wouldn't work here.

So it's a tricky problem.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company