From: | Guillaume Lelarge <guillaume(at)lelarge(dot)info> |
---|---|
To: | PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Weird behaviour with the new MOVE clause of ALTER TABLESPACE |
Date: | 2014-05-09 19:24:04 |
Message-ID: | 1399663444.2941.32.camel@localhost.localdomain |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2014-05-09 20:37:49 | Re: pg_class.relpages/allvisible probably shouldn't be a int4 |
Previous Message | Jeff Janes | 2014-05-09 19:03:52 | Re: 9.4 checksum errors in recovery with gin index |