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

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.

Best Wishes,
Chris Travers