Re: [HACKERS] Column ADDing issues

Peter Eisentraut wrote: >Thirdly, about TODO item > >* ALTER TABLE ADD COLUMN to inherited table put column in wrong
place> >Actually, according to what I would expect out of the blue, it puts it >into the *right* place. Even good ol'
SQL,although they naturally do not >know about inheritance, seems to agree: > >"... the degree of [the table] is
increasedby 1 and the ordinal position >of that [new] column is equal to the new degree of [the table] ..." >(11.11) >
>Whatthat says to me is that if you add a column to a table (during create >or alter) then the new column gets placed
afterall the others. Thus, >we're in compliance without even knowing it.  > >Or maybe look at it this way: >create
tabletest1 (a int4); >create table test2 (b int4) inherits (test1); >                    ^ col #1          ^ col #2
>altertable test1* add column c int4; >                              ^ col #3 > >Everything has its order and it's not
likethe inheritance as such is >broken. > >Surely, trying to stick the column in between is going to be three times >as
muchwork as dropping columns would be, whichever way you do it. (And >making attributes invisible ain't gonna help you.
;)

It is:
create table test1 (a int4);
create table test2 (b int4) inherits (test1);                   ^ col #2          ^ col #1
alter table test1* add column c int4;                             ^ col #3 but needs to be #2, since it _is_
                  #2 of test1
 

As far as inheritance goes, all the descendants are treated as one table,
including those created on a different branch from test2.  All of them
have to return the right columns for a single query; the two options for
dealing with this seem to be logical column numbering, or rewriting the
descendant tables.  (But I haven't spent enough time in the code to be
sure of that.)

-- 
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver              PGP key from public servers; key
ID32B8FAA1                ========================================    "My little children, let us not love in word,
neither     in tongue; but in deed and in truth."                                                     I John 3:18