Re: pg_dump partitions can lead to inconsistent state after restore

On Wed, 24 Apr 2019 at 06:50, Alvaro Herrera <alvherre(at)2ndquadrant(dot)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...

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