"Peter J. Holzer" <hjp-pgsql@hjp.at> writes:
> On 2019-07-16 19:20:24 +0000, Alex Williams wrote:
>> 1. postgres=# ALTER DATABASE mydatabase SET TABLESPACE pg_default;
>> ERROR: some relations of database "mydatabase" are already in tablespace "pg_default"
>> HINT: You must move them back to the database's default tablespace before using this command.
> What's the reason for this error? Wouldn't it be simpler to leave
> relations alone which are already in the correct tablespace?
It'd be simpler, it'd also be wrong.
The difficulty here is that ALTER DATABASE executes from a different
database, so it can't examine or change pg_class.reltablespace, so
it can't fix values that it would make inconsistent.
So imagine that we have a database in tablespace X, containing one
table T1 in tablespace Y. All the other entries in that database's
reltablespace column are zero signifying "it's in the database's default
tablespace". The one reference to tablespace X is in the shared catalog
pg_database, where we can see and change it during ALTER DATABASE.
Now let's do ALTER DATABASE ... SET TABLESPACE Y, and suppose that the
error check is not there. We move everything in X into Y, and we update
pg_database.dattablespace, but we don't change the reltablespace column.
Things are not actually broken at this point; T1's reltablespace says
Y and indeed that's where it is.
Now let's do ALTER DATABASE ... SET TABLESPACE Z. From outside the
database, all we can see is that all its files are physically in Y,
so we move them all to Z. Including T1. But its reltablespace entry
still says Y, so now things *are* broken.
(Another small issue here is that table relfilenodes are only
guaranteed unique per tablespace per database, so that there is
a hazard of OID collision between T1 and some other table being
moved into tablespace Y. We could fix that, but only if we
can change pg_class.relfilenode...)
So this can't be fixed without having a way to access and change
catalogs from a different database, which is a large can of worms
we don't want to open for such a small feature.
regards, tom lane
