Re: pg_dump is broken for partition tablespaces

On 2019-Apr-10, Andres Freund wrote:

> Hi,
>
> On 2019-04-10 09:28:21 -0400, Alvaro Herrera wrote:
> > So I think that apart from David's patch, we should just document all
> > these things carefully.
>
> Yea, I think that's the most important part.
>
> I'm not convinced that we should have any inheriting behaviour btw - it
> seems like there's a lot of different ways to think this should behave,
> with different good reason each.

So, I ended up with the attached patch. I think it works pretty well,
and it passes all my POLA tests.

But it doesn't pass pg_upgrade tests! And investigating closer, it
seems closely related to what David was complaining elsewhere about the
tablespace being improperly set by some rewrite operations. Here's the
setup as created by regress' create_table.sql:

create table at_partitioned (a int, b text) partition by range (a);
create table at_part_1 partition of at_partitioned for values from (0) to (1000);
insert into at_partitioned values (512, '0.123');
create table at_part_2 (b text, a int);
insert into at_part_2 values ('1.234', 1024);
create index on at_partitioned (b);
create index on at_partitioned (a);

If you examine state at this point, it's all good:
alvherre=# select relname, reltablespace from pg_class where relname like 'at_partitioned%';
relname | reltablespace
----------------------+---------------
at_partitioned | 0
at_partitioned_a_idx | 0
at_partitioned_b_idx | 0

but the test immediately does this:

alter table at_partitioned alter column b type numeric using b::numeric;

and watch what happens! (1663 is pg_default)

alvherre=# select relname, reltablespace from pg_class where relname like 'at_partitioned%';
relname | reltablespace
----------------------+---------------
at_partitioned | 0
at_partitioned_a_idx | 0
at_partitioned_b_idx | 1663
(3 filas)

Outrageous!

I'm going to have a look at this behavior now. IMO it's a separate bug,
but with that obviously we cannot fix the other one.

--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services