Re: DROP COLUMN misbehaviour with multiple inheritance - Mailing list pgsql-hackers

Alvaro Herrera kirjutas E, 23.09.2002 kell 10:06:
> Hannu Krosing dijo: 
> 
> > Tom Lane kirjutas P, 22.09.2002 kell 18:56:
> 
> > > It seems to me that DROP ONLY should set attislocal true on each child
> > > for which it decrements the inherit count, whether the count reaches
> > > zero or not.
> > 
> > Would it then not produce a situation, which can't be reproduced using
> > just CREATEs ? i.e. same column in bot parent (p2.f1) and child (c.f1)
> > but _not_ inherited ?? 
> 
> No, you cannot do that.  For example,
> create table p1 (f1 int, f2 int);
> create table p2 (f1 int, f3 int);
> create table c () inherits (p1, p2);
> 
> alter table only p1 drop column f1;
> alter table only p2 drop column f1;
> 
> In this case, f1 is kept on c, and this situation can be recreated as:
> create table p1 (f2 int);
> create table p2 (f3 int);
> create table c (f1 int) inherits (p2, p3);
> 
> If you drop it on only one parent it is exactly the same.


I meant 

create table p1 (f1 int, f2 int);
create table p2 (f1 int, f3 int);
create table c () inherits (p1, p2);
alter table only p1 drop column f1;

If you now set c.f1.attislocal = 1 as suggested by Tom , it seems like
you have a local p1.f1 _and_ local c.f1 , for which there is no way to
create without DROP's.

If I understand the meaning of attislocal correctly, the after the
above, I could do ALTER TABLE c DROP COLUMN f1, which would break 
SELECT * FROM p2.

> The next question is whether pg_dump knows how to do such things.  The
> answer is that it doesn't know that it must locally define f1 on c if
> you drop the column on only one parent.  Oddly enough, the following
> 
> create table p (f1 int);
> create table c (f1 int not null);

Did you mean

create table c (f1 int not null) inherits (p);

?

> produces the right behavior in pg_dump, but
> 
> create table p (f1 int);
> create table c () inherits (p);
> alter table c alter f1 set not null;
> 
> produces exactly the same as the former.  I don't know if it's right.
>
> > Then there would be no way to move a field from one parent table to
> > another and still have it as an inherited column in child.
> 
> You cannot add a column to a table that is inherited by another table
> that has a column with the same name:
> 
> inhtest=# alter table p1 add column f1 int;
> ERROR:  ALTER TABLE: column name "f1" already exists in table "c"
> inhtest=# alter table only p1 add column f1 int;
> ERROR:  Attribute must be added to child tables too
> inhtest=# 
> 
> IOW: there's no way to "move" a column, unless you drop it in the whole
> inheritance tree first.  Maybe this is a bug, and adding a column that
> exists in all childs (with the same name and type) should be allowed.

It should be symmetric to DROP behaviour.

So we should first check, if there are no childs with columns with the
same name but different type, then add it to all children where it is
missing and just make it inherited, where it is already present.


-----------
Hannu