Re: [HACKERS] Code bug or doc bug?
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 br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Code bug or doc bug?
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 br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. + diff --git a/doc/src/sgml/ref/alter_database.sgml b/doc/src/sgml/ref/alter_database.sgml new file mode 100644 index 3724c05..4af441e *** a/doc/src/sgml/ref/alter_database.sgml --- b/doc/src/sgml/ref/alter_database.sgml *** ALTER DATABASE replaceable class=PARAM *** 77,84 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. /para para --- 77,86 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. The new tablespace for !this database must be empty, and no one can be connected to the !database. Tables and indexes in non-default tablespaces are not !affected. /para para -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Code bug or doc bug?
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 (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Code bug or doc bug?
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 br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Code bug or doc bug?
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. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] 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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
