Re: Code bug or doc bug?

Folks,

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.

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

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

On Wed, Aug 27, 2014 at 09:05:41AM -0400, Robert Haas wrote:
> 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.

Is there a doc patch to make here?

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ Everyone has their own god. +

Bruce Momjian wrote
> On Wed, Aug 27, 2014 at 09:05:41AM -0400, Robert Haas wrote:
>> 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.
>
> Is there a doc patch to make here?

1. Last sentence change suggestion: "The target tablespace must be empty."

2. Based on Robert's comments it sounds like a "You cannot change the
default tablespace of the current database." comment should be added as
well.

Side note: I have no clue what the "mapped relations" Robert refers to
are...

If the locking problem is unsolvable, which seems to be the only realistic
reason why updating pg_class cannot be done somewhere in the process, could
we make it so that the same physical tablespace location can have multiple
pointers? The problem here would be that a subsequent move would only grab
those relations that are in the current tablespace by default and would
leave the ones that were present originally - unless they get moved in the
interim to the default tablespace (in this case by changing their oid to 0
manually first).

David J.

--
View this message in context: http://postgresql.1045698.n5.nabble.com/Code-bug-or-doc-bug-tp5816052p5816550.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.

On Wed, Aug 27, 2014 at 06:39:21AM -0700, David G Johnston wrote:
> > Is there a doc patch to make here?
>
> 1. Last sentence change suggestion: "The target tablespace must be empty."
>
> 2. Based on Robert's comments it sounds like a "You cannot change the
> default tablespace of the current database." comment should be added as
> well.
>
> Side note: I have no clue what the "mapped relations" Robert refers to
> are...

I have created the attached doc patch for this. Should we backpatch
this through 9.0, or just 9.4?

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ Everyone has their own god. +

On Mon, Oct 13, 2014 at 12:17:54PM -0400, Bruce Momjian wrote:
> On Wed, Aug 27, 2014 at 06:39:21AM -0700, David G Johnston wrote:
> > > Is there a doc patch to make here?
> >
> > 1. Last sentence change suggestion: "The target tablespace must be empty."
> >
> > 2. Based on Robert's comments it sounds like a "You cannot change the
> > default tablespace of the current database." comment should be added as
> > well.
> >
> > Side note: I have no clue what the "mapped relations" Robert refers to
> > are...
>
> I have created the attached doc patch for this. Should we backpatch
> this through 9.0, or just 9.4?

Applied.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ Everyone has their own god. +