Re: BUG #6489: Alter table with composite type/table

On Tue, Aug 28, 2012 at 11:09 PM, Chris Travers <chris(at)metatrontech(dot)com> wrote:
> here's my sense from what I've done in this area so far.
>
> On Tue, Aug 28, 2012 at 9:54 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>
>> Bruce Momjian <bruce(at)momjian(dot)us> writes:
>> > On Wed, Mar 14, 2012 at 07:19:14PM +0100, Rikard Pavelic wrote:
>> >> On 13.3.2012. 20:49, Merlin Moncure wrote:
>> >>> I personally think it's an oversight. This was just discussed a
>> >>> couple of days ago here:
>> >>>
>> >>> http://postgresql.1045698.n5.nabble.com/Altering-a-table-with-a-rowtype-column-td5544844.html
>>
>> >> TODO: alter table-type columns according to attribute type rules.
>> >> Enforce only TYPE features and ignore TABLE features when altering
>> >> composite table-types.
>>
>> > Should we add this TODO? I am confused by the text above though.
>>
>> I think this is making an assumption that we have consensus on what
>> are "type" properties and what are only "table" properties; that is,
>> is it a feature or a bug that column defaults don't work for instances
>> of composite types?
>
>
> I think right now the fact is that multiple inheritance is usually a cleaner
> way to incorporate multiple table types in a single table. There are some
> giant gotchas here of course, but nothing like the area of using composite
> types in columns. This is an area where we may do well to work towards
> consensus. Right now tables and composite types work almost the same but
> there are so many odd cases where they don't that it is somewhat
> disorienting.
>
>>
>>
>> The ALTER code is rejecting the case on the assumption that we think
>> this is a bug that should get fixed eventually. I'd only want to relax
>> the check if we have consensus that that will never happen.
>
>
> But at the same time, you can create the table with a not null constraint
> and then insert nulls, so I am not sure what the difference is. Again this
> is a case where different assumptions are followed partway through and
> consequently you run into very unexpected sharp corners.
>>
>>
>> The thread linked to via nabble above covers a lot of the background and
>> issues here. It didn't seem to me that there was clear consensus.
>
>
> I have some blog posts written (to be published next week) on the sharp
> corners of these sorts of things and how to avoid them. My overall
> recommendation actually is to use table inheritance as an alternative if you
> can (prefixing column names to avoid collisions) but reserve these mostly
> for views. Maybe it would be a good idea to re-hash this on -general at
> that point.
>>
>>
>> In any case, if we do do this, ISTM the TODO is much less about removing
>> one test in ALTER TABLE and much more about documenting the chosen
>> behavior. I think the reason you're confused by the proposed TODO
>> wording is exactly that it uses the phrases "TYPE features" and "TABLE
>> features" as if those concepts were defined or documented anywhere.
>
>
> To be honest, having worked with these a bit, I think we need to choose the
> behavior before we can document or even implement it.

Yeah -- the question at hand is whether certain table-ish mechanisms
apply to a table's type when it's used in non-table-storage ways. In
particular, defaults and constraints are interesting.

I'm arguing that they don't apply: defaults and constraints only make
sense when explicitly set by for the table and only when a record is
inserted into the table. In other words, they apply to the storage of
the table, not the type. I think there's a lot of circumstantial
support for that argument; consider the case of plpgsql declared
record variables for example...what happens to them? If you do want
defaults and constraints to propagate, then I think we need new
conventions to do that strictly on compatibility grounds. Maybe if
you did want propagating behaviors you could explicitly ask for them:

create table foo(a int default 1);
create table bar(f foo default row(2)::foo); --- this works fine now
create table bar(f foo default type_defaults(f)); --- what about this
(returns default populated type)?
create table bar(f foo check (type_constraints(f))); --- or this?

A second undecided point is whether adding attributes to a type should
require default/not null checks to occur as when adding columns to
table along with table rebuild if necessary. Here again I'm arguing
no: previously inserted types to the table have already been defaulted
and if you buy my argument above, it seems to fit in pretty well. So,
you wouldn't have to go around looking for type_defaults() in the
event you added a defaulted column to a table (or, if we going in this
direction, a type). If adding a constraint, you'd probably have to go
looking around for type_constraints() though.

merlin