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

On 29.8.2012. 21:02, Merlin Moncure wrote:
> On Wed, Aug 29, 2012 at 10:44 AM, Chris Travers <chris(at)metatrontech(dot)com> wrote:
>
>> Again, the question is simply this:
>>
>> Are the table constraints for storage complete in themselves (and assuming
>> full knowledge of all changes of internal data types) or are they
>> encapsulated within the types?
>>
>> A nice middle ground might be domains over complex types. However, we don't
>> currently support that. Otherwise you end up with really awkward constructs
>> like:
> I don't think domains are the answer. Domains are the 'other way' --
> type default and constraints are conveyed strictly though the type.
> Superficially cool but difficult to deal with on the implementation
> side...perhaps a design error of the SQL language.
>
>> CREATE FUNCTION is_valid (mytype) RETURNS BOOL
>>
>> and then:
>>
>> check ((mytypetest).is_valid)
> You can certainly do that (as of today it's the best way). Syntax
> sugar is still sweet though. If I had a choice, I'd prefer to enforce
> constraints with CHECK vs writing a special function to do that,
> especially for trivial constraints. Either way though that's the
> behavior that should be formalized IMO.
>
>>> If you do want
>>> defaults and constraints to propagate, then I think we need new
>>> conventions to do that strictly on compatibility grounds. Maybe if
>>> you did want propagating behaviors you could explicitly ask for them:
>>
>> Given the current mess in this area, I think backwards-compatibility
>> settings on a per-database level would be sufficient.
> Disagree: compatibility .conf settings should only be introduced in
> the most dire of needs -- for example when a bad but popular behavior
> has to be taken away. So the right behavior has to bolt on, and if
> that's not possible, we are stuck with the status quo.
>
>
>> CREATE TABLE cidr_type (
>> cidr_inet_address text,
>> cidr_netmask_bits int,
>> CHECK ((cidr_inet_address IS NULL AND cidr_netmask_bits IS NULL) or
>> (cidr_inet_address IS NOT NULL AND cidr_netmask_bits IS NOT
>> NULL)
>> );
>>
>> Then I can write my functions about cidr_type and inherit it on other tables
>> and I get proper constraints, but I *cannot* use this as a column type
>> without adding functions to manage the check constraints and explicitly
>> checking them. Again if you had domains available you could create a domain
>> that would presumably be expanded in table storage.
> yes -- as noted above domains are the alternative approach -- maybe
> the better one, but I'm not sure. maybe the sql standard (which I
> don't have) might give some clues.
>
> merlin
>

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.

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).

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).

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
}

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.

Regards,
Rikard