On Sun, Aug 24, 2014 at 6:26 PM, Josh Berkus <j...@agliodbs.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 -- Sent via pgsql-hackers mailing list (email@example.com) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers