Re: pg_dump is broken for partition tablespaces

On Wed, 10 Apr 2019 at 11:05, Alvaro Herrera <alvherre@2ndquadrant.com> wrote:
>
> On 2019-Apr-09, Alvaro Herrera wrote:
>
> > There is one deficiency that needs to be solved in order for this to
> > work fully: currently there is no way to reset "reltablespace" to 0.
>
> Therefore I propose to add
> ALTER TABLE tb ... RESET TABLESPACE;
> which sets reltablespace to 0, and it would work only for partitioned
> tables and indexes.
>
> That, together with the initial proposal by David, seems to me to solve
> the issue at hand.
>
> If no objections, I'll try to come up with a patch tomorrow.

I'm starting to wonder if maintaining two separate behaviours here
isn't just to complex.

For example, if I do:

CREATE TABLE a (a INT PRIMARY KEY) TABLESPACE mytablespace;

then a_pkey goes into the default_tablespace, not mytablespace.

Also, is it weird that CLUSTER can move a table into another
tablespace if the database's tablespace has changed?

postgres=# CREATE TABLE a (a INT PRIMARY KEY) TABLESPACE pg_default;
CREATE TABLE
postgres=# SELECT pg_relation_filepath('a'::regclass);
 pg_relation_filepath
----------------------
 base/12702/16444
(1 row)

postgres=# \c n
n=# ALTER DATABASE postgres TABLESPACE mytablespace;
ALTER DATABASE
n=# \c postgres
postgres=# CLUSTER a USING a_pkey;
CLUSTER
postgres=# SELECT pg_relation_filepath('a'::regclass);
            pg_relation_filepath
---------------------------------------------
 pg_tblspc/16415/PG_12_201904072/12702/16449
(1 row)

This one seems very strange to me.

I think to make it work we'd need to modify heap_create() and
heap_create_with_catalog() to add a new bool argument that controls if
the TABLESPACE was defined the calling command then only set the
reltablespace to InvalidOid if the tablespace was not defined and it
matches the database's tablespace.  If we want to treat table
partitions and index partitions in a special way then we'll need to
add a condition to not set InvalidOid if the relkind is one of those.
That feels a bit dirty, but if the above two cases were also deemed
wrong then we wouldn't need the special case.

Another option would be instead of adding a new bool flag, just pass
InvalidOid for the tablespace to heap_create() when TABLESPACE was not
specified then have it lookup GetDefaultTablespace() but keep
pg_class.reltablespace set to InvalidOId. Neither of these would be a
back-patchable fix for index partitions in PG11. Not sure what to do
about that...

Making constraints follow the tablespace specified during CREATE TABLE
would require a bit more work.

-- 
 David Rowley                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services