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

On Tue, Mar 20, 2012 at 2:48 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> 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 tend to think that's exactly what we should do, and it's what that
patch did, although as you point out my commit message was the product
of confused thinking.

> I'd always figured that we'd want to try to fix that
> omission eventually, though.

It's mildly tempting, but as Merlin points out, it's hard to know
exactly when you'd apply those rules. We talked a while back about
domains with NOT NULL constraints; if someone does a left join with a
domain-typed column on the outer side, what are you going to put there
if you don't put NULL? This case seems somewhat similar. Defaults
make sense when applied to table columns, because the semantics are
clear: columns not explicitly mentioned get their default value if
any, else NULL. But if we rule that a composite type with no default
gets the composite type's default values for each column, then we're
overriding the general SQL presumption that unspecified columns are
NULL. And similarly for temps created by uninitialized variables or,
worse, LEFT JOINs. In languages like C++ or even Perl, there's always
a very clear notion of when an object gets created, and constructors
and so on run at that time. Defaults logically should run at the same
time that a constructor would, but that concept doesn't really exist
in SQL, which is seemingly deliberately quite murky about when values
spring into existence.

Does the SQL standard say anything on this topic?

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