Thread: BUG #6489: Alter table with composite type/table
The following bug has been logged on the website: Bug reference: 6489 Logged by: Rikard Pavelic Email address: rikard.pavelic@zg.htnet.hr PostgreSQL version: 9.1.2 Operating system: Windows 7 Description:=20=20=20=20=20=20=20=20 I'm trying to push types in Postgres and have run into some limitations/inconsistent behaviors. Currently I'm declaring types and using them in other types and tables as composites. But types don't support inheritance so I'm thinking about declaring tables and using it's types instead of just declaring types. I've run into problems with adding new columns: create table t1(i int, j int); create table t2(i int, j t1); insert into t2 values(1,(2,3)); This works: alter table t1 add x float not null; This doesn't work: alter table t1 add x float not null default 0; It fails with ERROR: cannot alter table "t1" because column "t2.j" uses its row type While first alter table will not do as someone would expect (t2.x will be null) I'm fine with this behavior as it is consistent with types not allowing not null on attributes. But I would expect second alter to pass and enforcing not null and default when adding this column in table and not enforcing not null and default when adding into composite type for another table. Is this by design, oversight or a TODO? Regards, Rikard
On Sat, Feb 25, 2012 at 15:23, <rikard.pavelic@zg.htnet.hr> wrote: > This works: > alter table t1 add x float not null; > This doesn't work: > alter table t1 add x float not null default 0; > It fails with ERROR: =C2=A0cannot alter table "t1" because column "t2.j" = uses its > row type > But I would expect second alter to pass and enforcing not null and default > when adding this column in table and not enforcing not null and default w= hen > adding into composite type for another table. > > Is this by design, oversight or a TODO? Peeking at the code, currently any modifications that cause a rewrite of the original table are disallowed. Adding a nullable column without a default is allowed since it can be done without a rewrite -- all rows magically get the value NULL. I'm not sure whether it's worth complicating code for this. If you want to store composite types in tables, I think you're better off using CREATE TYPE/ALTER TYPE. Regards, Marti
On 28.2.2012. 15:01, Marti Raudsepp wrote: > On Sat, Feb 25, 2012 at 15:23, <rikard.pavelic@zg.htnet.hr> wrote: >> This works: >> alter table t1 add x float not null; > Peeking at the code, currently any modifications that cause a rewrite > of the original table are disallowed. > > Adding a nullable column without a default is allowed since it can be > done without a rewrite -- all rows magically get the value NULL. As shown in example above, I was adding not null column (but table was empty so query passed) > I'm not sure whether it's worth complicating code for this. If you > want to store composite types in tables, I think you're better off > using CREATE TYPE/ALTER TYPE. I guess this falls under advanced type usage (like recursive types - which can be used for lists) which are not even recognized as important ;( Regards, Rikard
Excerpts from rikard.pavelic's message of s=C3=A1b feb 25 10:23:18 -0300 20= 12: > But I would expect second alter to pass and enforcing not null and default > when adding this column in table and not enforcing not null and default w= hen > adding into composite type for another table. >=20 > Is this by design, oversight or a TODO? I think this is more a TODO than anything else. Last year we discussed something similar to this -- twice, even; IIRC, one was buried somewhere in the discussion about "variant" types, if you want to search the pgsql-hackers archives. As far as I recall, discussion died mainly because no one had the time and/or energy to pursue it, not because it was impossible. --=20 =C3=81lvaro Herrera <alvherre@commandprompt.com> The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support
On Wed, Feb 29, 2012 at 11:40:31AM -0300, Alvaro Herrera wrote: > > Excerpts from rikard.pavelic's message of sáb feb 25 10:23:18 -0300 2012: > > > But I would expect second alter to pass and enforcing not null and default > > when adding this column in table and not enforcing not null and default when > > adding into composite type for another table. > > > > Is this by design, oversight or a TODO? > > I think this is more a TODO than anything else. Last year we discussed > something similar to this -- twice, even; IIRC, one was buried somewhere > in the discussion about "variant" types, if you want to search the > pgsql-hackers archives. As far as I recall, discussion died mainly > because no one had the time and/or energy to pursue it, not because it > was impossible. Can you suggest some TODo text? -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. +
On Sat, Feb 25, 2012 at 7:23 AM, <rikard.pavelic@zg.htnet.hr> wrote: > The following bug has been logged on the website: > > Bug reference: =A0 =A0 =A06489 > Logged by: =A0 =A0 =A0 =A0 =A0Rikard Pavelic > Email address: =A0 =A0 =A0rikard.pavelic@zg.htnet.hr > PostgreSQL version: 9.1.2 > Operating system: =A0 Windows 7 > Description: > > I'm trying to push types in Postgres and have run into some > limitations/inconsistent behaviors. > > Currently I'm declaring types and using them in other types and tables as > composites. > But types don't support inheritance so I'm thinking about declaring tables > and using it's types instead of just declaring types. > > I've run into problems with adding new columns: > > create table t1(i int, j int); > create table t2(i int, j t1); > insert into t2 values(1,(2,3)); > > This works: > alter table t1 add x float not null; > This doesn't work: > alter table t1 add x float not null default 0; > It fails with ERROR: =A0cannot alter table "t1" because column "t2.j" use= s its > row type > > While first alter table will not do as someone would expect (t2.x will be > null) I'm fine with this behavior as it is consistent with types not > allowing not null on attributes. > > But I would expect second alter to pass and enforcing not null and default > when adding this column in table and not enforcing not null and default w= hen > adding into composite type for another table. > > Is this by design, oversight or a TODO? I personally think it's an oversight. This was just discussed a couple of days ago here: http://postgresql.1045698.n5.nabble.com/Altering-a-table-with-a-rowtype-col= umn-td5544844.html The server is blocking the alter-not-null-with-default because it's assuming that the default should be applied to dependent (foreign) tables implementing the type as a field. I think this assumption is totally bogus because composite types defaults get applied to the type, not to member fields and therefore a default has no meaning in that context. I think the TODO should read to relax the check essentially. merlin
On 13.3.2012. 20:49, Merlin Moncure wrote: > I personally think it's an oversight. This was just discussed a > couple of days ago here: > http://postgresql.1045698.n5.nabble.com/Altering-a-table-with-a-rowtype-column-td5544844.html > > The server is blocking the alter-not-null-with-default because it's > assuming that the default should be applied to dependent (foreign) > tables implementing the type as a field. I think this assumption is > totally bogus because composite types defaults get applied to the > type, not to member fields and therefore a default has no meaning in > that context. I think the TODO should read to relax the check > essentially. > > merlin > I agree. TODO: alter table-type columns according to attribute type rules. Enforce only TYPE features and ignore TABLE features when altering composite table-types. While I'm making up TODO's, my favorite one: support recursive types. Regards, Rikard
On Wed, Mar 14, 2012 at 07:19:14PM +0100, Rikard Pavelic wrote: > On 13.3.2012. 20:49, Merlin Moncure wrote: > > I personally think it's an oversight. This was just discussed a > > couple of days ago here: > > http://postgresql.1045698.n5.nabble.com/Altering-a-table-with-a-rowtype-column-td5544844.html > > > > The server is blocking the alter-not-null-with-default because it's > > assuming that the default should be applied to dependent (foreign) > > tables implementing the type as a field. I think this assumption is > > totally bogus because composite types defaults get applied to the > > type, not to member fields and therefore a default has no meaning in > > that context. I think the TODO should read to relax the check > > essentially. > > > > merlin > > > > I agree. > TODO: alter table-type columns according to attribute type rules. > Enforce only TYPE features and ignore TABLE features when altering composite table-types. > > While I'm making up TODO's, my favorite one: support recursive types. Should we add this TODO? I am confused by the text above though. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. +
On Mon, Aug 27, 2012 at 9:40 PM, Bruce Momjian <bruce@momjian.us> wrote: > On Wed, Mar 14, 2012 at 07:19:14PM +0100, Rikard Pavelic wrote: >> On 13.3.2012. 20:49, Merlin Moncure wrote: >> > I personally think it's an oversight. This was just discussed a >> > couple of days ago here: >> > http://postgresql.1045698.n5.nabble.com/Altering-a-table-with-a-rowtype-column-td5544844.html >> > >> > The server is blocking the alter-not-null-with-default because it's >> > assuming that the default should be applied to dependent (foreign) >> > tables implementing the type as a field. I think this assumption is >> > totally bogus because composite types defaults get applied to the >> > type, not to member fields and therefore a default has no meaning in >> > that context. I think the TODO should read to relax the check >> > essentially. >> > >> > merlin >> > >> >> I agree. >> TODO: alter table-type columns according to attribute type rules. >> Enforce only TYPE features and ignore TABLE features when altering composite table-types. >> >> While I'm making up TODO's, my favorite one: support recursive types. > > Should we add this TODO? I am confused by the text above though. I think so, but you should read the referenced thread for some background -- especially Tom's comments. What's missing is a consensus on how defaults and tables-as-rowtypes interact; and before working on a TODO that should be established. merlin
Bruce Momjian <bruce@momjian.us> writes: > On Wed, Mar 14, 2012 at 07:19:14PM +0100, Rikard Pavelic wrote: >> On 13.3.2012. 20:49, Merlin Moncure wrote: >>> I personally think it's an oversight. This was just discussed a >>> couple of days ago here: >>> http://postgresql.1045698.n5.nabble.com/Altering-a-table-with-a-rowtype-column-td5544844.html >> TODO: alter table-type columns according to attribute type rules. >> Enforce only TYPE features and ignore TABLE features when altering composite table-types. > Should we add this TODO? I am confused by the text above though. I think this is making an assumption that we have consensus on what are "type" properties and what are only "table" properties; that is, is it a feature or a bug that column defaults don't work for instances of composite types? The ALTER code is rejecting the case on the assumption that we think this is a bug that should get fixed eventually. I'd only want to relax the check if we have consensus that that will never happen. The thread linked to via nabble above covers a lot of the background and issues here. It didn't seem to me that there was clear consensus. In any case, if we do do this, ISTM the TODO is much less about removing one test in ALTER TABLE and much more about documenting the chosen behavior. I think the reason you're confused by the proposed TODO wording is exactly that it uses the phrases "TYPE features" and "TABLE features" as if those concepts were defined or documented anywhere. regards, tom lane
here's my sense from what I've done in this area so far. On Tue, Aug 28, 2012 at 9:54 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Bruce Momjian <bruce@momjian.us> writes: > > On Wed, Mar 14, 2012 at 07:19:14PM +0100, Rikard Pavelic wrote: > >> On 13.3.2012. 20:49, Merlin Moncure wrote: > >>> I personally think it's an oversight. This was just discussed a > >>> couple of days ago here: > >>> > http://postgresql.1045698.n5.nabble.com/Altering-a-table-with-a-rowtype-column-td5544844.html > > >> TODO: alter table-type columns according to attribute type rules. > >> Enforce only TYPE features and ignore TABLE features when altering > composite table-types. > > > Should we add this TODO? I am confused by the text above though. > > I think this is making an assumption that we have consensus on what > are "type" properties and what are only "table" properties; that is, > is it a feature or a bug that column defaults don't work for instances > of composite types? > I think right now the fact is that multiple inheritance is usually a cleaner way to incorporate multiple table types in a single table. There are some giant gotchas here of course, but nothing like the area of using composite types in columns. This is an area where we may do well to work towards consensus. Right now tables and composite types work almost the same but there are so many odd cases where they don't that it is somewhat disorienting. > > The ALTER code is rejecting the case on the assumption that we think > this is a bug that should get fixed eventually. I'd only want to relax > the check if we have consensus that that will never happen. > But at the same time, you can create the table with a not null constraint and then insert nulls, so I am not sure what the difference is. Again this is a case where different assumptions are followed partway through and consequently you run into very unexpected sharp corners. > > The thread linked to via nabble above covers a lot of the background and > issues here. It didn't seem to me that there was clear consensus. > I have some blog posts written (to be published next week) on the sharp corners of these sorts of things and how to avoid them. My overall recommendation actually is to use table inheritance as an alternative if you can (prefixing column names to avoid collisions) but reserve these mostly for views. Maybe it would be a good idea to re-hash this on -general at that point. > > In any case, if we do do this, ISTM the TODO is much less about removing > one test in ALTER TABLE and much more about documenting the chosen > behavior. I think the reason you're confused by the proposed TODO > wording is exactly that it uses the phrases "TYPE features" and "TABLE > features" as if those concepts were defined or documented anywhere. > To be honest, having worked with these a bit, I think we need to choose the behavior before we can document or even implement it. Best Wishes, Chris Travers
On Tue, Aug 28, 2012 at 11:09 PM, Chris Travers <chris@metatrontech.com> wrote: > here's my sense from what I've done in this area so far. > > On Tue, Aug 28, 2012 at 9:54 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: >> >> Bruce Momjian <bruce@momjian.us> writes: >> > On Wed, Mar 14, 2012 at 07:19:14PM +0100, Rikard Pavelic wrote: >> >> On 13.3.2012. 20:49, Merlin Moncure wrote: >> >>> I personally think it's an oversight. This was just discussed a >> >>> couple of days ago here: >> >>> >> >>> http://postgresql.1045698.n5.nabble.com/Altering-a-table-with-a-rowtype-column-td5544844.html >> >> >> TODO: alter table-type columns according to attribute type rules. >> >> Enforce only TYPE features and ignore TABLE features when altering >> >> composite table-types. >> >> > Should we add this TODO? I am confused by the text above though. >> >> I think this is making an assumption that we have consensus on what >> are "type" properties and what are only "table" properties; that is, >> is it a feature or a bug that column defaults don't work for instances >> of composite types? > > > I think right now the fact is that multiple inheritance is usually a cleaner > way to incorporate multiple table types in a single table. There are some > giant gotchas here of course, but nothing like the area of using composite > types in columns. This is an area where we may do well to work towards > consensus. Right now tables and composite types work almost the same but > there are so many odd cases where they don't that it is somewhat > disorienting. > >> >> >> The ALTER code is rejecting the case on the assumption that we think >> this is a bug that should get fixed eventually. I'd only want to relax >> the check if we have consensus that that will never happen. > > > But at the same time, you can create the table with a not null constraint > and then insert nulls, so I am not sure what the difference is. Again this > is a case where different assumptions are followed partway through and > consequently you run into very unexpected sharp corners. >> >> >> The thread linked to via nabble above covers a lot of the background and >> issues here. It didn't seem to me that there was clear consensus. > > > I have some blog posts written (to be published next week) on the sharp > corners of these sorts of things and how to avoid them. My overall > recommendation actually is to use table inheritance as an alternative if you > can (prefixing column names to avoid collisions) but reserve these mostly > for views. Maybe it would be a good idea to re-hash this on -general at > that point. >> >> >> In any case, if we do do this, ISTM the TODO is much less about removing >> one test in ALTER TABLE and much more about documenting the chosen >> behavior. I think the reason you're confused by the proposed TODO >> wording is exactly that it uses the phrases "TYPE features" and "TABLE >> features" as if those concepts were defined or documented anywhere. > > > To be honest, having worked with these a bit, I think we need to choose the > behavior before we can document or even implement it. Yeah -- the question at hand is whether certain table-ish mechanisms apply to a table's type when it's used in non-table-storage ways. In particular, defaults and constraints are interesting. I'm arguing that they don't apply: defaults and constraints only make sense when explicitly set by for the table and only when a record is inserted into the table. In other words, they apply to the storage of the table, not the type. I think there's a lot of circumstantial support for that argument; consider the case of plpgsql declared record variables for example...what happens to them? 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: create table foo(a int default 1); create table bar(f foo default row(2)::foo); --- this works fine now create table bar(f foo default type_defaults(f)); --- what about this (returns default populated type)? create table bar(f foo check (type_constraints(f))); --- or this? A second undecided point is whether adding attributes to a type should require default/not null checks to occur as when adding columns to table along with table rebuild if necessary. Here again I'm arguing no: previously inserted types to the table have already been defaulted and if you buy my argument above, it seems to fit in pretty well. So, you wouldn't have to go around looking for type_defaults() in the event you added a defaulted column to a table (or, if we going in this direction, a type). If adding a constraint, you'd probably have to go looking around for type_constraints() though. merlin
On Wed, Aug 29, 2012 at 6:55 AM, Merlin Moncure <mmoncure@gmail.com> wrote:
>
> Yeah -- the question at hand is whether certain table-ish mechanisms
> apply to a table's type when it's used in non-table-storage ways. In
> particular, defaults and constraints are interesting.
> I'm arguing that they don't apply: defaults and constraints only make
> sense when explicitly set by for the table and only when a record is
> inserted into the table. In other words, they apply to the storage of
> the table, not the type.
There are two places the line could be effectively drawn: At storage of
the type, or at storage of the table. These avoid the outer join problems
for example. My vote would be for storage of the type because once you get
into type methods, it is nice to be able to assume that if it is being run
in the standard use case that some columns are not null. This also is
useful when for column name collision reasons the type may have to be
re-used in a column. Also in cases of nested tables, it would be nice to
be able to do things other than iterating through every member of an array.
On the other hand, multiple inheritance gives us solutions for all these
problems. First it allows the sort of richness in modelling with central
constraint management that we get with drawing the line at storage of the
type, and we can always throw a few columns together, add deep constraints
and inherit from these in different contexts.
You can get a complete system at either set of assumptions. The current
system however draws different assumptions at awkward moments leaving the
DBA realize quite quickly that the developers are not on the same page.
I think there's a lot of circumstantial
> support for that argument; consider the case of plpgsql declared
> record variables for example...what happens to them?
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:
CREATE FUNCTION is_valid (mytype) RETURNS BOOL
and then:
check ((mytypetest).is_valid)
> 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.
The fact is that anyone doing significant work in this area is already
becoming very familiar with all sorts of ideosyncratic behavior. I don't
think that the as long as the choice was offered, there would be much
resistance towards moving to a more manageable toolkit.
>
> create table foo(a int default 1);
> create table bar(f foo default row(2)::foo); --- this works fine now
>
Sure.
> create table bar(f foo default type_defaults(f)); --- what about this
> (returns default populated type)?
>
The point though is that currently you end up having to write your own
constraint/defaults routines in sql, which then get brought into the table
schema.
create table bar(f foo check (type_constraints(f))); --- or this?
>
You can do this now, see above.
>
> A second undecided point is whether adding attributes to a type should
> require default/not null checks to occur as when adding columns to
> table along with table rebuild if necessary. Here again I'm arguing
> no: previously inserted types to the table have already been defaulted
> and if you buy my argument above, it seems to fit in pretty well.
But this basically means, if you want centralized management of your check
constraints and defaults, multiple inheritance is the only real tool
available, right? Personally I think from an object-relational
perspective, multiple inheritance (despite the gotchas) is currently a
*lot* cleaner than complex types in columns. Especially if you prefix
column names with something meaningful for the class, you can essentially
inline all the types you need, and collapse your table to each type as
needed,.
For example, suppose we didn't have a CIDR type and I wanted to create one
using composit types. I need the netmask bits to be set not null on all
valid entries. The best way to do this would be:
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.
> So,
> you wouldn't have to go around looking for type_defaults() in the
> event you added a defaulted column to a table (or, if we going in this
> direction, a type). If adding a constraint, you'd probably have to go
> looking around for type_constraints() though.
>
> merlin
>
I suppose this is yet another reason why multiple inheritance is an
absolutely killer feature in PostgreSQL is that currently you *can* model
your data in an equivalent way *and* have check constraints and not null
constraints enforced ;-)
Best Wishes,
Chris Travers
On Wed, Aug 29, 2012 at 10:44 AM, Chris Travers <chris@metatrontech.com> wrote: >> I think there's a lot of circumstantial >> support for that argument; consider the case of plpgsql declared >> record variables for example...what happens to them? > > > 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
On 29.8.2012. 21:02, Merlin Moncure wrote:
> On Wed, Aug 29, 2012 at 10:44 AM, Chris Travers <chris@metatrontech.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
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