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

On Mon, Mar 17, 2014 at 07:12:12PM -0400, Noah Misch wrote:
> On Fri, Mar 14, 2014 at 12:33:04PM -0300, Alvaro Herrera wrote:
> > Tom Lane wrote:
> > > Alvaro Herrera <alvherre(at)2ndquadrant(dot)com> writes:
> > > > 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.

[thread moved to hackers]

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. What is a basically happening is that these queries:

CREATE TABLE A(a int, b int, c int);
CREATE TABLE B(a int, c int);
ALTER TABLE A INHERIT B;

cause pg_dump to generate this:

CREATE TABLE b (
a integer,
c integer
);
CREATE TABLE a (
a integer,
b integer,
c integer
)
INHERITS (b);

which issues these warnings when run:

NOTICE: merging column "a" with inherited definition
NOTICE: merging column "c" with inherited definition

and produces this table "a":

test2=> \d a
Table "public.a"
Column | Type | Modifiers
--------+---------+-----------
a | integer |
--> c | integer |
b | integer |

Notice the column reordering. The logic is that a CREATE TABLE INHERITS
should place the inherited parent columns _first_. This can't be done
by ALTER TABLE INHERIT because the table might already contain data.

I think we have several options:

1. document this behavior
2. have ALTER TABLE INHERIT issue a warning about future reordering
3. use the pg_dump binary-upgrade code when such cases happen

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. Will such tables load fine
because pg_dump binary-upgrade mode doesn't do any data loading?

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ Everyone has their own god. +