Re: pg_dump is broken for partition tablespaces

On Wed, Apr 17, 2019 at 6:06 PM Alvaro Herrera <alvherre(at)2ndquadrant(dot)com> wrote:
> > > 3. Partitioned relations can have the database tablespace in
> > > pg_class.reltablespace, as opposed to storage-bearing relations which
> > > cannot. This is useful to be able to put partitions in the database
> > > tablespace even if the default_tablespace is set to something else.
> >
> > I still feel that this is a darn bad idea. It goes against the rule
> > that's existed for pg_class.reltablespace since its beginning, and
> > I think it's inevitable that that's going to break something.
>
> Yes, this deviates from current practice, and while I tested this in as
> many ways as I could think of, I cannot deny that it might break
> something unexpectedly.

Like Tom, I think this has got to be broken.

Suppose that you have a partitioned table which has reltablespace =
dattablespace. It has a bunch of children with reltablespace = 0.
So far so good: new children of the partitioned table go into the
database tablespace regardless of default_tablespace.

Now somebody changes the default tablespace using ALTER DATABASE ..
SET TABLESPACE. All the existing children end up in the new default
tablespace, but new children of the partitioned table end up going
into the tablespace that used to be the default but is no longer.
That's pretty odd, because the whole point of setting a tablespace on
the children was to get all of the children into the same tablespace.

The same thing happens if you clone the database using CREATE DATABASE
.. TEMPLATE .. TABLESPACE, as Tom mentioned.

PostgreSQL has historically and very deliberately *not made a
distinction* between "this object is in the default tablespace" and
"this object is in tablespace X which happens to be the default." I
think that it's too late to invent such a distinction for reasons of
backward compatibility -- and if we were going to do it, surely it
would need to exist for both partitioned tables and the partitions
themselves. Otherwise it just produces more strange inconsistencies.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company