Re: pg_dump is broken for partition tablespaces

On 2019-Mar-06, Tom Lane wrote:

> David Rowley <david(dot)rowley(at)2ndquadrant(dot)com> writes:
> > As far as I can see, the biggest fundamental difference with doing
> > things this way will be that the column order of partitions will be
> > preserved, where before it would inherit the order of the partitioned
> > table. I'm a little unsure if doing this column reordering was an
> > intended side-effect or not.
>
> Well, if the normal behavior results in changing the column order,
> it'd be necessary to do things differently in --binary-upgrade mode
> anyway, because there we *must* preserve column order. I don't know
> if what you're describing represents a separate bug for pg_upgrade runs,
> but it might. Is there any test case for the situation left behind by
> the core regression tests?

Now that I re-read this complaint once again, I wonder if a mismatching
column order in partitions isn't a thing we ought to preserve anyhow.
Robert, Amit -- is it by design that pg_dump loses the original column
order for partitions, when not in binary-upgrade mode? To me, it sounds
unintuitive to accept partitions that don't exactly match the order of
the parent table; but it's been supported all along. In the statu quo,
if users dump and restore such a database, the restored partition ends
up with the column order of the parent instead of its own column order
(by virtue of being created as CREATE TABLE PARTITION OF). Isn't that
wrong? It'll cause an INSERT/COPY direct to the partition that worked
prior to the restore to fail after the restore, if the column list isn't
specified.

--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services