Обсуждение: Composite types as columns used in production?

Folks,

Composite types look so seductive for mapping application-level
non-scalar objects to columns:

    o) Directly mappable to a client-side datatype via oid w/o any
heavyweight O/R code.
    o) Such mapping persists through any use of views, joins, etc from
ad-hoc queries.
    o) Using Domains as composite type members seem to work nicely, with
the domain checks firing.

I can see a few drawbacks towards their use, however:

    o) A bit of a pain to extend / manage. Cannot just add a column to the
type. Gotta create a new type, write a procedure to crossover all
instances in all tables from the old type to the new type, then,
finally, drop the old type. Not insurmountable, but not nearly as easy
as 'alter table add column ...; update table set newcolumn=value; alter
table alter column newcolumn set not null;'

    Hmm -- I see that if the composite type was created via a table
definition, then you _can_ actually add and drop columns, and the
tables using the composite type seem to get updated, as in
http://archives.postgresql.org/pgsql-hackers/2005-03/msg00621.php .
That's sweet, and if this is supportable functionality as opposed to an
odd side-effect, then the 'ALTER TYPE' and 'CREATE TYPE' doc pages may
well want to reference this feature.

    o) Query syntax is a bit odd, but can still use contained fields in
where clauses.

    o) As currently implemented, constraints are not carried forth from
type definition to column usage. This can be manually corrected through
constraints on the tables which use 'em:

        alter table testtable add check ((val).v2 in (1,2,3));

    o) Convincing the system to create an index on a scalar member of a
composite type is not quite intuitive. I was going to write that it was
not possible, but it seems that you can do it as an expression:

---
create type testtype as (
    v1 int,
    v2 int
);

create table testtable (
    id int not null primary key,
    val testtype not null
);

create index t on testtable(((val).v2));

insert into testtable (id, val) values (1, (1,1));
insert into testtable (id, val) values (2, (1,2));

set enable_seqscan=f;

explain select  * from testtable where (val).v2 = 2;


  Index Scan using t on testtable  (cost=0.00..3.35 rows=1 width=36)
(actual time=0.370..0.375 rows=1 loops=1)
    Index Cond: ((val).v2 = 2)

---

Heck, even multi-column indexes work:

test=# create index t2 on testtable((((val).v1)), ((val).v2));
CREATE INDEX
test=# \d t2
      Index "public.t2"
      Column      |  Type
-----------------+---------
  pg_expression_1 | integer
  pg_expression_2 | integer
btree, for table "public.testtable"

test=# explain select * from testtable where (val).v1=1 and (val).v2=1;
                              QUERY PLAN
---------------------------------------------------------------------
  Index Scan using t2 on testtable  (cost=0.00..3.35 rows=1 width=36)
    Index Cond: (((val).v1 = 1) AND ((val).v2 = 1))
(2 rows)

----
    Anyway, it seems that a good bit of work has happened in the past two
release to bring composite types up-to-snuff, but are folks actually
using 'em in production databases? Any known show-stopping drawbacks or
creepy feelings about 'em? I think I remember seeing posts that they
just aren't true members of the relational model, but, aside from
constraint inheritance, what is known to be lacking, aside from the
plethora of parenthesis they inflict?

----
James Robinson
Socialserve.com

James Robinson <jlrobins@socialserve.com> writes:
>     Hmm -- I see that if the composite type was created via a table
> definition, then you _can_ actually add and drop columns, and the
> tables using the composite type seem to get updated, as in
> http://archives.postgresql.org/pgsql-hackers/2005-03/msg00621.php .
> That's sweet, and if this is supportable functionality as opposed to an
> odd side-effect, then the 'ALTER TYPE' and 'CREATE TYPE' doc pages may
> well want to reference this feature.

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

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

James Robinson <jlrobins@socialserve.com> writes:
> That said -- anyone stepping up to claiming using 'em? Are these things
> seen as against the data normalization grain?

One strike against 'em is the fairly high overhead involved --- a
composite value is stored as effectively its own tuple, so there's 28 or
so bytes of overhead for each one.

            regards, tom lane