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
