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@2ndquadrant.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 definitionNOTICE: 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@momjian.us> http://momjian.us EnterpriseDB
http://enterprisedb.com
+ Everyone has their own god. +