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

Tom Lane-2 wrote
> Alvaro Herrera <

> alvherre@

> > writes:
>>

> majid@

> wrote:
>>> Reproduction case:
>>>
>>> create table A(a int, b int, c int);
>>> create table B(a int, c int);
>>> alter table A inherit B;
>
>> 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.

At least a warning is in order so that the user at least has chance to know
they are doing something inconsistent and can affect a manual refactoring of
their create/alter table commands to make them consistent.

Adding something like the following after the first paragraph in the inherit
clause of alter table would seem wise.

"The columns need not be in the same order, or in the leading position, in
the child as in the parent but if they are not then during a dump-restore
the resultant CREATE TABLE will physically re-order the columns. This will
affect any code that references the columns of this table using * (implied -
via insert - or otherwise)."

I imagine the rules are a little more complicated when more than one parent
is involved. The wording in CREATE TABLE is goes into great detail as to
how column names must be presented but do not comment of column ordering at
all. Passing around and using table references is not that uncommon that
the impact of inherent on column ordering should be ignored.

In the case of create table are there cases, without an intervening "alter
table ...inherits" that the initial create and the one run after
dump/restore could result in a different physical order? The main risk area
is adding/removing columns from the parent(s).

Without actually predicting the order would it at least be possible to run a
check of any inheritance trees affected by any kind of change to see if the
current column order would match the order resulting from a dump/restore and
emit a warning if a discrepancy is found?

Pg_upgrade may work but at minimum recovery is still broken as is creating
copies for development purposes.

David J.

--
View this message in context: http://postgresql.1045698.n5.nabble.com/BUG-9555-pg-dump-for-tables-with-inheritance-recreates-the-table-with-the-wrong-order-of-columns-tp5795882p5795953.html
Sent from the PostgreSQL - bugs mailing list archive at Nabble.com.