Thread: Nonrecursive ALTER TABLE ADD/RENAME COLUMN is wrong

Fernando Nasser of Red Hat reminded me that it really makes no sense
for ALTER TABLE ADD COLUMN and ALTER TABLE RENAME COLUMN to behave
non-recursively --- that is, they should *always* affect inheritance
children of the named table, never just the named table itself.

After a non-recursive ADD/RENAME, you'd have a situation wherein
"SELECT * FROM foo" would fail, because there'd be no corresponding
columns in the child table(s).  This seems clearly bogus to me.
(On the other hand, non-recursive DROP COLUMN, if we had one, would
be okay ... the orphaned child columns would effectively become
non-inherited added columns.  Similarly, non-recursive alterations of
defaults, constraints, etc seem reasonable.)

As of 7.2 we do accept "ALTER TABLE ONLY foo" forms of these commands,
but I think that's a mistake arising from thoughtless cut-and-paste
from the other forms of ALTER.  I believe it is better to give an error
if such a command is given.  Any objections?

Also, in the case where neither "ONLY foo" nor "foo*" is written, the
behavior currently depends on the SQL_INHERITANCE variable.  There's
no problem when SQL_INHERITANCE has its default value of TRUE, but what
if it is set to FALSE?  Seems to me we have two plausible choices:
* Give an error, same as if "ONLY foo" had been written.
* Assume the user really wants recursion, and do it anyway.

The second seems more user-friendly but also seems to violate the
principle of least surprise.  Anyone have an opinion about what to do?
        regards, tom lane

>     * Give an error, same as if "ONLY foo" had been written.
> 
>     * Assume the user really wants recursion, and do it anyway.
> 
> The second seems more user-friendly but also seems to violate the
> principle of least surprise.  Anyone have an opinion about what to do?

I really prefer the former.  If for some reason it were to become 
available that they could alter only foo for some strange reason we 
haven't come up with yet (statistics related perhaps?), we would 
certainly need to throw an error on the other 'alter table' statements 
at that point in time.

> Fernando Nasser of Red Hat reminded me that it really makes no sense
> for ALTER TABLE ADD COLUMN and ALTER TABLE RENAME COLUMN to behave
> non-recursively --- that is, they should *always* affect inheritance
> children of the named table, never just the named table itself.

Hmm. Good point. Anything else would lead to structural breakage.

> The second seems more user-friendly but also seems to violate the
> principle of least surprise.  Anyone have an opinion about what to do?

Same point as for the main issue: the solution should not introduce
structural breakage, especially only on the otherwise benign setting of
a GUC variable.

The case you are worried about already *has* structural inheritance, so
the GUC setting could reasonably have no effect. But if one is mixing a
database with inheritance structures with command settings that hide it,
they shouldn't be too suprised at whatever they get. The Right Thing
imho is to respect the underlying structures and definitions, not the
command facade. But would not dig in my heels on either choice after
more discussion.
                    - Thomas

> The second seems more user-friendly but also seems to violate the
> principle of least surprise.  Anyone have an opinion about what to do?

Sounds like a logical argument, given normal OO behaviour.

Hope it inspires someone to implement DROP COLUMN :)

Chris