Thread: Weird behaviour with the new MOVE clause of ALTER TABLESPACE
Hey, I was working on adding support to the new MOVE clause of the ALTER TABLESPACE statement to pgAdmin when I noticed this issue. See this example: Fresh git compilation, and new database on a new cluster: $ createdb b1 $ psql b1 psql (9.4devel) Type "help" for help. b1=# CREATE TABLESPACE ts1 LOCATION '/opt/postgresql/tablespaces/ts94'; CREATE TABLESPACE b1=# SELECT count(*) FROM pg_class c JOIN pg_tablespace t ON c.reltablespace=t.oid AND spcname='pg_default';count ------- 0 (1 row) b1=# SELECT count(*) FROM pg_class c JOIN pg_tablespace t ON c.reltablespace=t.oid AND spcname='ts1';count ------- 0 (1 row) b1=# SELECT count(*) FROM pg_class c WHERE c.reltablespace=0;count ------- 268 (1 row) So, 268 objects in the default tablespace (which happens to be pg_default) and none in ts1 (that's correct, it was just created). Now, we move all objects from pg_default to ts1. My expectation was that all user objects would be afterwards in the ts1 tablespace. And here is what happens: b1=# ALTER TABLESPACE pg_default MOVE ALL TO ts1; ALTER TABLESPACE b1=# SELECT count(*) FROM pg_class c JOIN pg_tablespace t ON c.reltablespace=t.oid AND spcname='pg_default';count ------- 0 (1 row) b1=# SELECT count(*) FROM pg_class c JOIN pg_tablespace t ON c.reltablespace=t.oid AND spcname='ts1';count ------- 21 (1 row) b1=# SELECT count(*) FROM pg_class c WHERE c.reltablespace=0;count ------- 247 (1 row) I have 21 objects in ts1 and 247 stayed in the default tablespace. I'm not sure what I should find weird: that some objects were moved, or that not all objects were moved :) What's weirder is the objects themselves: b1=# SELECT relkind, relname FROM pg_class c JOIN pg_tablespace t ON c.reltablespace=t.oid AND spcname='ts1' ORDER BY 1,2;relkind | relname ---------+-------------------------i | pg_toast_12619_indexi | pg_toast_12624_indexi | pg_toast_12629_indexi | pg_toast_12634_indexi | pg_toast_12639_indexi | pg_toast_12644_indexi | pg_toast_12649_indexr | sql_featuresr | sql_implementation_infor | sql_languagesr | sql_packagesr | sql_partsr | sql_sizingr | sql_sizing_profilest | pg_toast_12619t | pg_toast_12624t | pg_toast_12629t | pg_toast_12634t | pg_toast_12639t | pg_toast_12644t | pg_toast_12649 (21 rows) In other words, all information_schema tables (and their toast tables and the toast indexes) were moved. Why only them? AFAICT, there are no other information_schema tables, only views which obviously are not concerned by the ALTER TABLESPACE statement. Should information_schema tables be moved and not pg_catalog ones? it doesn't seem consistent to me. I probably miss something obvious. Thanks for any pointer. -- Guillaume http://blog.guillaume.lelarge.info http://www.dalibo.com
Guillaume,
* Guillaume Lelarge (guillaume@lelarge.info) wrote:
> Should information_schema tables be moved and not pg_catalog ones? it
> doesn't seem consistent to me.
The catalog tables are moved by changing the database's tablespace, eg:
ALTER DATABASE ... SET TABLESPACE
That also moves any objects which are not assigned to a specific
tablespace.
The question ends up being just which side of "is it part of the
catalog, or not?" the information schema falls on to. For this case, I
had considered those to *not* be part of the catalog as they can be
moved independently of the ALTER DATABASE ... SET TABLESPACE.
This is happily documented:
System catalogs will not be moved by this command- individuals wishing to move a whole database should use ALTER
DATABASE,or call ALTER TABLE on the individual system catalogs. Note that relations in
<literal>information_schema</literal> will be moved, just as any other normal database objects, if the user is the
superuseror considered an owner of the relations in <literal>information_schema</literal>.
Thanks,
Stephen
On Fri, 2014-05-09 at 17:16 -0400, Stephen Frost wrote: > Guillaume, > > * Guillaume Lelarge (guillaume@lelarge.info) wrote: > > Should information_schema tables be moved and not pg_catalog ones? it > > doesn't seem consistent to me. > > The catalog tables are moved by changing the database's tablespace, eg: > > ALTER DATABASE ... SET TABLESPACE > > That also moves any objects which are not assigned to a specific > tablespace. > > The question ends up being just which side of "is it part of the > catalog, or not?" the information schema falls on to. For this case, I > had considered those to *not* be part of the catalog as they can be > moved independently of the ALTER DATABASE ... SET TABLESPACE. > > This is happily documented: > > System catalogs will not be moved by this command- individuals wishing to > move a whole database should use ALTER DATABASE, or call ALTER TABLE on the > individual system catalogs. Note that relations in <literal>information_schema</literal> > will be moved, just as any other normal database objects, if the user is the > superuser or considered an owner of the relations in <literal>information_schema</literal>. > Thanks for the explanation. I should have RTFM before complaining. Sorry for the noise :) -- Guillaume http://blog.guillaume.lelarge.info http://www.dalibo.com
* Guillaume Lelarge (guillaume@lelarge.info) wrote:
> Thanks for the explanation. I should have RTFM before complaining. Sorry
> for the noise :)
No prob. If people don't feel that makes sense then we can still change
it.. I don't feel particularly strongly either way, though I seem to
recall my testing showing that we already treated information schema
differently from catalog tables in some ways (though I don't recall
offhand exactly how).
Thanks,
Stephen