From: Alvaro Herrera <alvherre@2ndquadrant.com>
To: Andres Freund <andres@anarazel.de>
Cc: David Rowley <david.rowley@2ndquadrant.com>
Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Subject: Re: pg_dump is broken for partition tablespaces
Date: Wed, 10 Apr 2019 16:21:52 -0400
Message-ID: <20190410202152.GA10240@alvherre.pgsql> (raw)
In-Reply-To: <20190410161055.66wundhyxakt4fqo@alap3.anarazel.de>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: alvherre@2ndquadrant.com, andres@anarazel.de, david.rowley@2ndquadrant.com, pgsql-hackers@lists.postgresql.org
Subject: Re: pg_dump is broken for partition tablespaces
In-Reply-To: <20190410202152.GA10240@alvherre.pgsql>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox