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 > > 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 <br...@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. + -- Sent via pgsql-hackers mailing list (firstname.lastname@example.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers