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

From: Noah Misch <noah(at)leadboat(dot)com>
To: Bruce Momjian <bruce(at)momjian(dot)us>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>, majid(at)apsalar(dot)com, PostgreSQL-development <pgsql-hackers(at)postgreSQL(dot)org>
Subject: Re: [BUGS] Re: BUG #9555: pg_dump for tables with inheritance recreates the table with the wrong order of columns
Date: 2014-09-01 22:24:34
Message-ID: 20140901222434.GA906981@tornado.leadboat.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs pgsql-hackers

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.

In response to

Browse pgsql-bugs by date

  From Date Subject
Next Message David G Johnston 2014-09-01 23:54:15 Re: BUG #11325: Documentation Bug / RFE
Previous Message David G Johnston 2014-09-01 21:20:14 Re: BUG #11325: Documentation Bug / RFE

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2014-09-01 22:59:51 Re: Patch for psql History Display on MacOSX
Previous Message Álvaro Hernández Tortosa 2014-09-01 22:11:46 Re: PL/pgSQL 2