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(at)commandprompt(dot)com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support