Re: Composite types as columns used in production?

On Apr 29, 2005, at 12:21 PM, Tom Lane wrote:

> My recollection is that there are some pretty serious limitations on
> what you can do in this line, but basic cases do work.  I think the
> lack
> of an ALTER TYPE that can handle the same cases is just a matter of
> lack
> of round tuits.
>
>             regards, tom lane

I see, for example, that adding basic columns work:

test=# alter table testtype drop column v3;
ALTER TABLE
test=# alter table testtype add column v3 int;
ALTER TABLE

But domains cannot be added after the fact:

test=# create domain one_of_three as int check (VALUE in (1,2,3));
CREATE DOMAIN
test=# alter table testtype add column v4 one_of_three;
ERROR:  cannot alter table "testtype" because column "testtable"."val"
uses its rowtype

Likewise with something that has a default value, as in your example
listed
http://archives.postgresql.org/pgsql-hackers/2005-03/msg00623.php:

d=# alter table a add column qq timestamp default now() not null;
ERROR:  cannot alter table "a" because column "b"."z" uses its rowtype


But domains work well before the type gets used:

test=# create table testtype2 ( v1 one_of_three, v2 one_of_three );
CREATE TABLE
test=# create table uses_tt2 (id int, val testtype2);
CREATE TABLE
test=# insert into uses_tt2 values (1, (2, 3));
INSERT 0 1
test=# insert into uses_tt2 values (1, (3, 5));
ERROR:  value for domain one_of_three violates check constraint
"one_of_three_check"

Removing domain columns works, too:

test=# alter table testtype2 drop column v2;
ALTER TABLE
test=# select * from uses_tt2;
  id | val
----+-----
   1 | (2)
(1 row)


So, adding nontrivial columns to an in-use composite type amounts to
creating a new type and running crossover script(s) to convert the
in-use data, rebuilding any indices on the old type columns, then
dropping the old type. Inconvenient, not insurmountable.

That said -- anyone stepping up to claiming using 'em? Are these things
seen as against the data normalization grain?

----
James Robinson
Socialserve.com