Re: [GENERAL] Altering a table with a rowtype column

Merlin Moncure <mmoncure(at)gmail(dot)com> writes:
> On Wed, Mar 7, 2012 at 11:45 AM, Mike Blackwell <mike(dot)blackwell(at)rrd(dot)com> wrote:
>> alter table a add column even_more_stuff boolean not null default false;

> aha! that's not what you posted last time. you appended 'not null
> default false'; which inexplicably breaks the ALTER.

> try this:
> ALTER TABLE a ADD COLUMN even_more_stuff text not null;
> ALTER TABLE a ALTER even_more_stuff set default false;
> ALTER TABLE a DROP COLUMN even_more_stuff;
> ALTER TABLE a ADD COLUMN even_more_stuff boolean not null default false;

> (this really looks like a bug in postgres, cc-ing to bugs)

It is not a bug. The ALTER ADD ... DEFAULT ... form implies rewriting
every existing tuple of the rowtype to insert a non-null value in the
added column, and we don't have support for doing that to rowtype
columns, only to the target table and descendants. Without a default,
it's just a catalog adjustment and doesn't involve rewriting any data.
(This stems from the fact that columns beyond a tuple's natts value are
presumed null, so we can let ADD COLUMN without a default just change
the catalogs and a null column effectively springs into existence for
every existing tuple. ALTER ADD ... DEFAULT is specified to have a
different result, and it's not free.)

This probably could be done for rowtype columns as well, but nobody has
collected the necessary round tuits. I think there was some fear of
locking/deadlock issues, too.

regards, tom lane