Re: ALTER TABLE ... ADD COLUMN

Alvaro Herrera <alvherre(at)atentus(dot)com> writes:
> I'm thinking about ALTER TABLE ... ADD COLUMN working properly when
> child tables already contain the column.
> There are two cases: one when specifying ALTER TABLE ONLY, and other
> when specifying recursive (not ONLY).

I think ALTER TABLE ONLY ... ADD COLUMN is nonsensical and should be
rejected out of hand. That solves that part of the problem easily ;-)

The comparison case in my mind is ALTER TABLE ONLY ... RENAME COLUMN,
which has to be rejected. (Surely you're not going to say we should
support this by allowing the parent column to become associated with
some other child columns than before...) ALTER ONLY ADD COLUMN cannot
add any functionality that's not perfectly well available with
ALTER ADD COLUMN.

> In the second case, child tables are checked for existance of the
> column. If the column doesn't exist, the procedure is called
> recursively. If the column exists but has a different atttypid o
> atttypmod, the request is aborted. Else, the attribute has its
> attinhcount incremented and attislocal reset.

I don't like resetting attislocal here. If you do that, then DROPping
the parent column doesn't return you to the prior state. I think I gave
this example before, but consider

CREATE TABLE p (f1 int);
CREATE TABLE c (f2 int) INHERITS (p);
ALTER TABLE p ADD COLUMN f2 int;
-- sometime later, realize that the ADD was a mistake, so:
ALTER TABLE p DROP COLUMN f2;

If you reset attislocal then the ending state will be that c.f2 is gone.
That seems clearly wrong to me.

> The second may seems odd, but consider the following scenario:

> CREATE TABLE p1 (f1 int);
> CREATE TABLE p2 (f2 int);
> CREATE TABLE c (f1 int) INHERITS (p1, p2);

> In this case, c.f1.attislocal is true. Now suppose the user wants to
> create p2.f1. If the recursive version is used, attislocal will be
> reset and the scenario will be equivalent to

> CREATE TABLE p1 (f1 int);
> CREATE TABLE p2 (f1 int, f2 int);
> CREATE TABLE c () INHERITS (p1, p2);

... which is wrong also. c had a local definition before and should
still, IMHO. What's the argument for taking away its local definition?

regards, tom lane