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_index
i | pg_toast_12624_index
i | pg_toast_12629_index
i | pg_toast_12634_index
i | pg_toast_12639_index
i | pg_toast_12644_index
i | pg_toast_12649_index
r | sql_features
r | sql_implementation_info
r | sql_languages
r | sql_packages
r | sql_parts
r | sql_sizing
r | sql_sizing_profiles
t | pg_toast_12619
t | pg_toast_12624
t | pg_toast_12629
t | pg_toast_12634
t | pg_toast_12639
t | pg_toast_12644
t | 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
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
