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

On Sat, Feb 25, 2012 at 7:23 AM, <rikard(dot)pavelic(at)zg(dot)htnet(dot)hr> wrote:
> The following bug has been logged on the website:
>
> Bug reference:      6489
> Logged by:          Rikard Pavelic
> Email address:      rikard(dot)pavelic(at)zg(dot)htnet(dot)hr
> PostgreSQL version: 9.1.2
> Operating system:   Windows 7
> Description:
>
> I'm trying to push types in Postgres and have run into some
> limitations/inconsistent behaviors.
>
> Currently I'm declaring types and using them in other types and tables as
> composites.
> But types don't support inheritance so I'm thinking about declaring tables
> and using it's types instead of just declaring types.
>
> I've run into problems with adding new columns:
>
> create table t1(i int, j int);
> create table t2(i int, j t1);
> insert into t2 values(1,(2,3));
>
> This works:
> alter table t1 add x float not null;
> This doesn't work:
> alter table t1 add x float not null default 0;
> It fails with ERROR:  cannot alter table "t1" because column "t2.j" uses its
> row type
>
> While first alter table will not do as someone would expect (t2.x will be
> null) I'm fine with this behavior as it is consistent with types not
> allowing not null on attributes.
>
> But I would expect second alter to pass and enforcing not null and default
> when adding this column in table and not enforcing not null and default when
> adding into composite type for another table.
>
> Is this by design, oversight or a TODO?

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

The server is blocking the alter-not-null-with-default because it's
assuming that the default should be applied to dependent (foreign)
tables implementing the type as a field. I think this assumption is
totally bogus because composite types defaults get applied to the
type, not to member fields and therefore a default has no meaning in
that context. I think the TODO should read to relax the check
essentially.

merlin