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

On Fri, Mar 14, 2014 at 12:33:04PM -0300, Alvaro Herrera wrote:
> Tom Lane wrote:
> > Alvaro Herrera <alvherre(at)2ndquadrant(dot)com> writes:
> > > majid(at)apsalar(dot)com wrote:
> > >> Reproduction case:
> > >>
> > >> create table A(a int, b int, c int);
> > >> create table B(a int, c int);
> > >> alter table A inherit B;
> >
> > > I wonder if the real fix here is to have ALTER / INHERIT error out of
> > > the columns in B are not a prefix of those in A.
> >
> > Years ago, we sweated quite a lot of blood to make these cases work.
> > I'm not thrilled about throwing away all that effort because one person
> > doesn't like the behavior.

Agreed. That also makes the current pg_dump behavior a bug. Column order
matters; pg_dump is failing to recreate a semantically-equivalent database.

> Hm, well in that case it makes sense to consider the original
> suggestion: if the columns in the parent are not a prefix of those of
> the child, use ALTER INHERIT after creating both tables rather than
> CREATE TABLE INHERITS.
>
> It'd be a lot of new code in pg_dump though. I am not volunteering ...

"pg_dump --binary-upgrade" already gets this right. Perhaps it won't take too
much code to make dumpTableSchema() reuse that one part of its binary-upgrade
approach whenever the columns of B are not a prefix of those in A.

nm

--
Noah Misch
EnterpriseDB http://www.enterprisedb.com