Thread: Weird behaviour with the new MOVE clause of ALTER TABLESPACE

Weird behaviour with the new MOVE clause of ALTER TABLESPACE

From
Guillaume Lelarge
Date:
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




Re: Weird behaviour with the new MOVE clause of ALTER TABLESPACE

From
Stephen Frost
Date:
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

Re: Weird behaviour with the new MOVE clause of ALTER TABLESPACE

From
Guillaume Lelarge
Date:
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




Re: Weird behaviour with the new MOVE clause of ALTER TABLESPACE

From
Stephen Frost
Date:
* 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