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
Date: Tue, 23 Apr 2019 14:50:07 -0400
Message-ID: <20190423185007.GA27954@alvherre.pgsql> (raw)
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
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, pgsql-hackers@lists.postgresql.org
Subject: Re: pg_dump partitions can lead to inconsistent state after restore
In-Reply-To: <20190423185007.GA27954@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