Re: [BUGS] Re: BUG #9555: pg_dump for tables with inheritance recreates the table with the wrong order of columns

On Wed, Aug 27, 2014 at 11:24:53AM -0400, Tom Lane wrote:
> On Wed, Aug 27, 2014 at 10:40:53AM -0400, Bruce Momjian wrote:
> > I looked at this issue from March and I think we need to do something.
> > In summary, the problem is that tables using inheritance can be dumped
> > and reloaded with columns in a different order from the original
> > cluster.
>
> Yeah ... this has been a well-understood issue for a dozen years, and
> pg_dump goes to considerable trouble to get it right.

pg_dump goes to trouble to preserve attislocal but not to preserve inherited
column order. Hence this thread about pg_dump getting column order wrong.

> > I think we have several options:
> >
> > 1. document this behavior
>
> That one.

+1; certainly reasonable as a first step.

> > 2. have ALTER TABLE INHERIT issue a warning about future reordering

That warning would summarize as "WARNING: this object is now subject to a
known bug". -0; I'm not strongly opposed, but it's not our norm.

> > 3. use the pg_dump binary-upgrade code when such cases happen

+1. We have the convention that, while --binary-upgrade can inject catalog
hacks, regular pg_dump uses standard, documented DDL. I like that convention
on general aesthetic grounds and for its benefit to non-superusers. Let's
introduce the DDL needed to fix this bug while preserving that convention,
namely DDL to toggle attislocal.

> > My crude approach for #3 would be for pg_dump to loop over the columns
> > and, where pg_attribute.attinhcount == 0, check to see if there is a
> > matching column name in any inherited table.

That doesn't look right. attinhcount is essentially a cache; it shall equal
the number of parents having a matching column. The approach we use in binary
upgrade mode ought to suffice.

> > Will such tables load fine
> > because pg_dump binary-upgrade mode doesn't do any data loading?

We're now talking about changes to pg_dump's normal (non-binary-upgrade) mode,
right? pg_dump always gives COPY a column list, so I don't expect trouble on
the data load side of things.

Thanks,
nm