Composite types or composite keys?

On Fri, Nov 15, 2013 at 2:01 AM, Tony Theodore <tony.theodore@gmail.com> wrote:

Hi,

I was reading about composite types and wondering if I should use them instead of composite keys. I currently have tables like this:

create table products (
source_system text,
product_id text,
description text,
...
primary key (source_system, product_id)
);
create table inventory (
source_system text,
product_id text,
qty int,
...
foreign key (source_system, product_id) references products
);

and it means having to add the “source_system" column to many queries. Would something like:

create type product as (
source_system text,
product_id text
);
create table products (
product product,
description text,
...
primary key(product)
);
create table inventory (
product product,
qty numeric,
...
foreign key (product) references products
);

be a correct use of composite types? I rarely need to see the columns separately, so having to write “(product).product_id” won’t happen much in practice.

Well, here are the downsides. Composite types:
*) are more than the sum of their parts performance-wise. So there is
a storage penalty in both the heap and the index
*) can't leverage indexes that are querying only part of the key
*) will defeat the implicit 'per column NOT NULL constraint' of the primary keys
*) are not very well supported in certain clients -- for example JAVA.
you can always deal with them as text, but that can be a headache.

...plus some other things I didn't think about. If you can deal with
those constraints, it might be interesting to try a limited
experiment. The big upside of composite types is that you can add
attributes on the fly without rebuilding the index. Test carefully.

merlin

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 15 Nov 2013, at 8:04 pm, Chris Travers <chris.travers@gmail.com> wrote:

In general, if you don't know you need composite types, you don't want them. You have basically three options and the way you are doing it is the most typical solution to the problem

The current way is much easier since I discovered the “JOIN ... USING(..)” syntax and I’m tempted to try natural joins.

Having experience with table inheritance and composite types in tuples, I will say the former has fewer sharp corners than the latter.

Where composite types really work well is where you want to add functions which take the type as input. In essence you can develop some very sophisticated models with them, but you probably would not use them for storage unless you have other considerations in mind.

Thanks for that, I’ve done some reading on inheritance and it looks like I can create an empty parent table that acts like a column definition template. This also automatically creates a type that can be used in functions which sounds like what I’m after. There are also scenarios where “product” is a combination of “level" and “id” (where “level” can be things like brand/category/sku) and I’d like to use the same calculations regardless of where it sits in the hierarchy.

Cheers,

Tony

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Sun, Nov 17, 2013 at 6:57 PM, Tony Theodore <tony.theodore@gmail.com>wrote:

On 15 Nov 2013, at 8:04 pm, Chris Travers <chris.travers@gmail.com> wrote:

In general, if you don't know you need composite types, you don't want

them. You have basically three options and the way you are doing it is the
most typical solution to the problem

The current way is much easier since I discovered the “JOIN ... USING(..)”
syntax and I’m tempted to try natural joins.

Having experience with table inheritance and composite types in tuples,

I will say the former has fewer sharp corners than the latter.

Where composite types really work well is where you want to add

functions which take the type as input. In essence you can develop some
very sophisticated models with them, but you probably would not use them
for storage unless you have other considerations in mind.

Thanks for that, I’ve done some reading on inheritance and it looks like I
can create an empty parent table that acts like a column definition
template. This also automatically creates a type that can be used in
functions which sounds like what I’m after. There are also scenarios where
“product” is a combination of “level" and “id” (where “level” can be things
like brand/category/sku) and I’d like to use the same calculations
regardless of where it sits in the hierarchy.

I haven't done work with this so I am not 100% sure but it seems to me
based on other uses I have for table inheritance that it might work well
for enforcing interfaces for natural joins. The one caveat I can imagine
is that there are two issues that occur to me there.

1. If you have two child tables which add a column of the same name, then
your centralized enforcement gets messed up and you have a magic join which
could take a while to debug....

2. The same goes if you have two child tables which also inherit a
different parent table for a different natural join....

To be honest I think being explicit about joins is usually a very good
thing.

Best Wishes,
Chris Travers

Cheers,

Tony

--
Best Wishes,
Chris Travers

Efficito: Hosted Accounting and ERP. Robust and Flexible. No vendor
lock-in.
http://www.efficito.com/learn_more.shtml

On 16 Nov 2013, at 3:01 am, Merlin Moncure <mmoncure@gmail.com> wrote:

Well, here are the downsides. Composite types:
*) are more than the sum of their parts performance-wise. So there is
a storage penalty in both the heap and the index
*) can't leverage indexes that are querying only part of the key
*) will defeat the implicit 'per column NOT NULL constraint' of the primary keys

Thanks, I didn’t see any of those - I was thinking that they were like pseudo tables or column templates.

*) are not very well supported in certain clients -- for example JAVA.
you can always deal with them as text, but that can be a headache.

...plus some other things I didn't think about. If you can deal with
those constraints, it might be interesting to try a limited
experiment. The big upside of composite types is that you can add
attributes on the fly without rebuilding the index. Test carefully.

I’ll give it a try - I might stick to using plain or inherited tables for the main storage and then experiment with composite types for functions and other aggregate tables that are used internally.

Cheers,

Tony

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 18 Nov 2013, at 2:24 pm, Chris Travers <chris.travers@gmail.com> wrote:

I haven't done work with this so I am not 100% sure but it seems to me based on other uses I have for table inheritance that it might work well for enforcing interfaces for natural joins. The one caveat I can imagine is that there are two issues that occur to me there.

1. If you have two child tables which add a column of the same name, then your centralized enforcement gets messed up and you have a magic join which could take a while to debug....

2. The same goes if you have two child tables which also inherit a different parent table for a different natural join....

To be honest I think being explicit about joins is usually a very good thing.

I can see how debugging a magic join would quickly outweigh any benefits and the “USING()” clause nicely reflects the foreign key definition, so I’ll stick with explicit joins.

Thanks,

Tony

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general