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

On Wed, Mar 7, 2012 at 3:49 PM, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:
> On Wed, Mar 7, 2012 at 2:31 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> 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.
>
> I'm not buying that..it implies no such thing.  In particular, for
> table-as-rowtype columns, there's no way that I can see to have
> default values be generated.  So why does it follow that the dependent
> table has to be rewritten?  Column constraints are not enforced on the
> rowtype, so it follows that default shouldn't be either considering
> there's no way to get the default to fire.  Composite type (or table
> based composite) defaults are applied to the composite as a whole, not
> to specific fields.

I think Tom's correct about what the right behavior would be if
composite types supported defaults, but they don't, never have, and
maybe never will. I had a previous argument about this with Tom, and
lost, though I am not sure that anyone other than Tom thinks that the
current behavior is for the best. But see commits
a06e41deebdf74b8b5109329dc75b2e9d9057962 and
a40b1e0bf32b1da46c1baa9bc7da87f207cd37d8.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company