Re: DROP COLUMN misbehaviour with multiple inheritance

Hannu Krosing <hannu(at)tm(dot)ee> writes:
> 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.

Uh, no, you don't have a p1.f1 at all.

> 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.

No you could not, because c.f1 still has attinhcount = 1 due to the
inheritance from p2. As long as c.f1.attinhcount > 0, you won't be
allowed to drop c.f1. attislocal does not override that.

>> 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.

That's a good point. It could be fixed easily though (pg_dump would
just have to take attislocal into consideration when deciding whether
to emit a column definition in the child table).

>> ... 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.

I think this is fine. Having done something to the field in c (and not
recursively from p) means that you are attaching special new meaning
to c.f1; I'm okay with equating this action to "c is now locally defined".
Maybe the backend should make that equation too, and actively set
attislocal in the top level when doing an ALTER COLUMN.

BTW, do we prohibit ALTER DROP NOT NULL on inherited columns? We
probably should.

>> 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.

Yeah, this is an implementation shortcoming in ALTER ADD COLUMN: if it
finds an existing column of the same name in a child table, it should
test whether it's okay to "merge" the columns (same types, no conflict
in constraints/defaults, cf CREATE's behavior); if so, it should
increment the child column's attinhcount instead of failing.

I had noticed that yesterday, and meant to ask Bruce to put it on TODO,
but got distracted with other stuff.

regards, tom lane