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 <alvhe...@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
> > 
> > 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);

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  <br...@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + Everyone has their own god. +

Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:

Reply via email to