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

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> 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:
>>> 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.

Um, did I say I thought it was for the best? I thought I said we don't
have support for doing better.

If we are willing to legislate that column defaults are not and never
will be applied to composite types, then I think Merlin might be right
that we could just let an ALTER ADD with DEFAULT ignore the existence of
composite columns. I'd always figured that we'd want to try to fix that
omission eventually, though.

> But see commits
> a06e41deebdf74b8b5109329dc75b2e9d9057962 and
> a40b1e0bf32b1da46c1baa9bc7da87f207cd37d8.

Note that the actual problem with the original commit was that it
depended on a misreading of the SQL standard. Per spec, ALTER ADD with
DEFAULT is *not* the same thing as ALTER ADD followed by ALTER SET
DEFAULT; the contents of the table end up different.

regards, tom lane