Date: Tue, 23 Apr 2019 14:50:07 -0400
From: Alvaro Herrera <alvherre@2ndquadrant.com>
To: Pg Hackers <pgsql-hackers@lists.postgresql.org>
Subject: pg_dump partitions can lead to inconsistent state after restore
Message-ID: <20190423185007.GA27954@alvherre.pgsql>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
User-Agent: Mutt/1.9.4 (2018-02-28)
Precedence: bulk

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.

Tom Lane said:

> I haven't looked at the partitioning code, but I am quite sure that that's
> always happened for old-style inheritance children, and I imagine pg_dump
> is just duplicating that old behavior.

Actually, the new code is unrelated to the old one; for legacy
inheritance, the children are always created exactly as they were
created at definition time.  If you use ALTER TABLE ... INHERITS
(attach a table as a children after creation) then obviously the child
table cannot be modified to match its new parent; and pg_dump reproduces
the exact column ordering that the table originally had.  If you use
"CREATE TABLE ... INHERITS (parent)" then the child columns are reordered
*at that point* (creation time); the dump will, again, reproduce the
exact same definition.


I think failing to reproduce the exact same definition is a pg_dump bug
that should be fixed and backpatched to pg10.  It's just sheer luck that
nobody has complained of being bitten by it.

-- 
Álvaro Herrera                                http://www.twitter.com/alvherre