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

On Sat, Aug 30, 2014 at 07:32:26PM -0400, Bruce Momjian wrote:
> On Wed, Aug 27, 2014 at 09:40:30PM -0400, Noah Misch wrote:
> > > > 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.
>
> I have spend some time researching this, and I am not sure what to
> recommend. The basic issue is that CREATE TABLE INHERITS always puts
> the inherited columns first, so to preserve column ordering, you have to
> use CREATE TABLE and then ALTER TABLE INHERIT. The problem there is
> that ALTER TABLE INHERIT doesn't preserve attislocal, and it also has
> problems with constraints not being marked local. I am just not sure we
> want to add SQL-level code to do that. Would it be documented?

Yes; I value the fact that ordinary pg_dump emits only documented SQL. In a
similar vein, we added ALTER TABLE OF for the benefit of pg_dump.

> I have developed the attached patch to warn about column reordering in
> this odd case. The patch mentions the reordering of c:

This, as amended downthread, seems useful.