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