Re: pg_dump partitions can lead to inconsistent state after restore

On 2019/04/24 10:19, David Rowley wrote:
> On Wed, 24 Apr 2019 at 06:50, Alvaro Herrera <alvherre@2ndquadrant.com> wrote:
>> Per my comment at https://postgr.es/m/20190422225129.GA6126@alvherre.pgsql
>> I think that pg_dump can possibly cause bogus partition definitions,
>> when the users explicitly decide to join tables as partitions that have
>> different column ordering than the parent table.  Any COPY or INSERT
>> command without an explicit column list that tries to put tuples in the
>> table will fail after the restore.
> 
> Yeah, pg_dump itself is broken here, never mind dreaming up some other
> user command.
> 
> We do use a column list when doing COPY, but with --inserts (not
> --column-inserts) we don't include a column list.
> 
> All it takes is:
> 
> postgres=# create table listp (a int, b text) partition by list(a);
> CREATE TABLE
> postgres=# create table listp1 (b text, a int);
> CREATE TABLE
> postgres=# alter table listp attach partition listp1 for values in(1);
> ALTER TABLE
> postgres=# insert into listp values(1,'One');
> INSERT 0 1
> postgres=# \q
> 
> $ createdb test1
> $ pg_dump --inserts postgres | psql test1
> ...
> ERROR:  invalid input syntax for type integer: "One"
> LINE 1: INSERT INTO public.listp1 VALUES ('One', 1);
> 
> That settles the debate on the other thread...

+1 to fixing this, although +0.5 to back-patching.

The reason no one has complained so far of being bitten by this may be
that, as each of one us has said at least once on the other thread, users
are not very likely to create partitions with different column orders to
begin with.  Maybe, that isn't a reason to leave it as is though.

Thanks,
Amit