Re: [COMMITTERS] pgsql: Remove arbitrary ALTER TABLE .. ADD COLUMN restriction.

Excerpts from Robert Haas's message of mié ene 26 14:43:08 -0300 2011:

> For those following along at home who may wish to express an opinion,
> perhaps a brief review of the behavior change we're arguing about will
> be helpful.  Prior to this patch, if foo was used as a type in some
> other table, this would work:
> 
> ALTER TABLE foo ADD COLUMN bar integer;
> 
> And this would work:
> 
> ALTER TABLE foo ADD COLUMN bar integer DEFAULT null;
> 
> But this would fail:
> 
> ALTER TABLE foo ADD COLUMN bar integer DEFAULT 5;
> 
> ...and specifically, it would give you this error message:
> 
> cannot alter table "%s" because column "%s"."%s" uses its rowtype
> 
> Now, at the very least, that error message sucks, because clearly you
> *could* alter that table; you could even add that specific column, and
> you could subsequently set a default on it.  You just couldn't do both
> at the same time.  With this patch, the operation succeeds: the rows
> in the table are updated with the new default, but instances of the
> row type in other tables are not updated, so they effectively have a
> NULL in that column.

If you really want to do what you seem to want (i.e. add a column with a
default and not have it alter existing rows), you can already do it like
this:

ALTER TABLE foo ADD COLUMN bar INTEGER, ALTER COLUMN bar SET DEFAULT 5;

If there's an intention to improve ALTER TABLE so that it propagates the
new default to existing tuples in other tables, I have no problem with
it throwing an error now.  Perhaps suggest the above syntax in a hint or
something.

-- 
Álvaro Herrera <alvherre@commandprompt.com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support