Re: BUG #6489: Alter table with composite type/table - Mailing list pgsql-bugs

On Fri, Aug 31, 2012 at 12:32 AM, Rikard Pavelic <rikard.pavelic@zg.htnet.hr
> wrote:

>
>
> I'm doing something most DBA would probably think it's a bad idea, but at
> least
> I can provide you with use case of Postgres usage.
>

There are bad ideas and there are bad ideas.  The question of course is
what you get and what it costs.   I think there are two big costs.  The
first is that this area is full of inconsistencies in assumptions about
correct behavior and inconsistencies as you have found out.  The second is
that composite types as columns make it harder for a lot of add-on
reporting tools to extract data out (which is why I think that multiple
inheritance is cleaner).  But those have to be weighed against what you are
doing, naturally.

>
> We are trying to support DDD programming paradigm on top of Postgres.
> DDD modeling blocks are entities (and aggregates) and values.
> We map entities to tables and values to types.
> This fits mostly very nicely (values don't have identity, so they are
> inlined with
> other values and entities which uses them).
>
> I won't pretend to be an expert on DDD.



> What I think would be a great goal for Postgres is if lot of constraints
> would
> move from tables to types.
> This include NOT NULL, CHECK and even FOREIGN KEY (somewhere in the far
> future).
>

BTW, you can do NOT NULL and CHECK at the domain level fwiw and those will
pass through when composite types are stored.

>
> It would make me very happy if Postgres could check all constraints for
> model like this:
>
> aggregate country(code) {
>   string code;
>   string local_name;
> }
> aggregate person {
>   string name;
>   address[] addresses;
> }
> value address {
>   string? street;
>   string town;
>   country *country;  //this will create surrogate country_code field in
> address,
>                                     //function country(address) which
> returns country
>                                     //and it would be great if it could
> maintain relationship with country
> }
>

Take a look at recent blog entries in my blog for how to do the foreign key
dereferencing: http://ledgersmbdev.blogspot.com

Basically:

CREATE TABLE country_ref (
    country_id int,
);

CREATE FUNCTION country(country_ref) RETURNS COUNTRY
STABLE LANGUAGE SQL AS $$
SELECT * FROM COUNTRY WHERE id = $1.country_id $$;

Then inherit from country and define the fkey in the child table.


>
> Currently Postgres can't declare NOT NULL for town and reference from
> address to country.
> I would be happy if direction Postgres takes would allow design like this
> to be enforced by database.
>

Sure it can:

CREATE DOMAIN not_null_string as text not null;

use not_null_string in place of text in your parent tables and it will be
enforced when pull these into the column.  This is one of those
inconsistencies I mentioned above.

This is one of those reasons I don't see the backwards-compatibility
reasons so convincing.  We can't create some modicum of consistency in
behavior without breaking *something.*  I think the big issue is that
nobody has figured out exactly what we want to break.

Best Wishes,
Chris Travers