Re: [HACKERS] Fwd: Range types (DATERANGE, TSTZRANGE) in a foreign key with inclusion logic
Matthias Nagel matthias.h.na...@gmail.com wrote: I would like to do someting like: FOREIGN KEY ( container_id, lifetime ) REFERENCES other_table (id, lifetime ) USING gist ( container_id WITH =, lifetime WITH @ ) (Of course, this is PosgreSQL-pseudo-code, but it hopefully make clear what I want.) So, now my questions: 1) Does this kind of feature already exist in 9.2? No. 2) If this feature does not directly exist, has anybody a good idea how to mimic the intended behaviour? I would probably do it by using only SERIALIZABLE transactions to modify data, and enforce the constraint in triggers. That would not be as convenient or as performant as the feature you're requesting would probably be, but it would work as long as you have a systematic way to retry transactions which are rolled back with a serialization failure. 3) If neither 1) or 2) applies, are there any plans to integrate such a feature? I have heard Jeff Davis talk about the possibility of such a feature, that he figures would be called inclusion constraints. I have not heard of any actual development on the idea yet. Having range types and exclusion contraints are nice, as I said in the introdruction. But if the reverse (foreign key with inclusion) would also work, the range type feature would really be amazing. Agreed. -- Kevin Grittner EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Fwd: Range types (DATERANGE, TSTZRANGE) in a foreign key with inclusion logic
Hi, Far as I am aware - there is not yet described FK feature... But should be possible to ensure that rule via trigger.. 1 after update on container, and one after insert/update on item... Kind Regards, Misa On Saturday, April 6, 2013, Matthias Nagel wrote: Hello, this is a re-post from the SQL user list 2 month ago, because I assume only a developer can answer the questions below. Thanks, Matthias Nagel -- Weitergeleitete Nachricht -- Betreff: Range types (DATERANGE, TSTZRANGE) in a foreign key with inclusion logic Datum: Mittwoch 23 Januar 2013, 11:28:10 Von: Matthias Nagel matthias.h.na...@gmail.com javascript:; An: pgsql-...@postgresql.org javascript:; Hello everybody, first a big thank you to all that make the range types possible. They are great, especially if one runs a database to manage a student's university dormitory with a lot of temporal information like rental agreements, room allocations, etc. At the moment we are redesigning our database scheme for PosgreSQL 9.2, because the new range types and especially the EXCLUSION constraints allow to put a lot more (business) logic into the database scheme than before. But there is one feature missing (or I am too stupid to find it). Let's say we have some kind of container with a lifetime attribute, i.e. something like that CREATE TABLE container ( id SERIAL PRIMARY KEY, lifetime DATERANGE ); Further, there are items that must be part of the container and these items have a lifetime, too. CREATE TABLE item ( id SERIAL PRIMARY KEY, container_id INTEGER, lifetime DATERANGE, FOREIGN KEY (container_id) REFERENCES container ( id ), EXCLUDE USING gist ( container_id WITH =, lifetime WITH ) ); The foreign key ensures that items are only put into containers that really exist and the exclude constraint ensure that only one item is member of the same container at any point of time. But actually I need a little bit more logic. The additional contraint is that items must only be put into those containers whose lifetime covers the lifetime of the item. If an item has a lifetime that exceeds the lifetime of the container, the item cannot be put into that container. If an item is already in a container (with valid lifetimes) and later the container or the item is updated such that either lifetime is modified and the contraint is not fullfilled any more, this update must fail. I would like to do someting like: FOREIGN KEY ( container_id, lifetime ) REFERENCES other_table ( id, lifetime ) USING gist ( container_id WITH =, lifetime WITH @ ) (Of course, this is PosgreSQL-pseudo-code, but it hopefully make clear what I want.) So, now my questions: 1) Does this kind of feature already exist in 9.2? If yes, a link to the documentation would be helpful. 2) If this feature does not directly exist, has anybody a good idea how to mimic the intended behaviour? 3) If neither 1) or 2) applies, are there any plans to integrate such a feature? I found this discussion http://www.postgresql.org/message-id/4f8bb9b0.5090...@darrenduncan.net . Does anybody know about the progress? Having range types and exclusion contraints are nice, as I said in the introdruction. But if the reverse (foreign key with inclusion) would also work, the range type feature would really be amazing. Best regards, Matthias Nagel -- Matthias Nagel Willy-Andreas-Allee 1, Zimmer 506 76131 Karlsruhe Telefon: +49-721-8695-1506 Mobil: +49-151-15998774 e-Mail: matthias.h.na...@gmail.com javascript:; ICQ: 499797758 Skype: nagmat84 - -- Matthias Nagel Willy-Andreas-Allee 1, Zimmer 506 76131 Karlsruhe Telefon: +49-721-8695-1506 Mobil: +49-151-15998774 e-Mail: matthias.h.na...@gmail.com javascript:; ICQ: 499797758 Skype: nagmat84 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.orgjavascript:; ) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Fwd: Range types (DATERANGE, TSTZRANGE) in a foreign key with inclusion logic
Hello, this is a re-post from the SQL user list 2 month ago, because I assume only a developer can answer the questions below. Thanks, Matthias Nagel -- Weitergeleitete Nachricht -- Betreff: Range types (DATERANGE, TSTZRANGE) in a foreign key with inclusion logic Datum: Mittwoch 23 Januar 2013, 11:28:10 Von: Matthias Nagel matthias.h.na...@gmail.com An: pgsql-...@postgresql.org Hello everybody, first a big thank you to all that make the range types possible. They are great, especially if one runs a database to manage a student's university dormitory with a lot of temporal information like rental agreements, room allocations, etc. At the moment we are redesigning our database scheme for PosgreSQL 9.2, because the new range types and especially the EXCLUSION constraints allow to put a lot more (business) logic into the database scheme than before. But there is one feature missing (or I am too stupid to find it). Let's say we have some kind of container with a lifetime attribute, i.e. something like that CREATE TABLE container ( id SERIAL PRIMARY KEY, lifetime DATERANGE ); Further, there are items that must be part of the container and these items have a lifetime, too. CREATE TABLE item ( id SERIAL PRIMARY KEY, container_id INTEGER, lifetime DATERANGE, FOREIGN KEY (container_id) REFERENCES container ( id ), EXCLUDE USING gist ( container_id WITH =, lifetime WITH ) ); The foreign key ensures that items are only put into containers that really exist and the exclude constraint ensure that only one item is member of the same container at any point of time. But actually I need a little bit more logic. The additional contraint is that items must only be put into those containers whose lifetime covers the lifetime of the item. If an item has a lifetime that exceeds the lifetime of the container, the item cannot be put into that container. If an item is already in a container (with valid lifetimes) and later the container or the item is updated such that either lifetime is modified and the contraint is not fullfilled any more, this update must fail. I would like to do someting like: FOREIGN KEY ( container_id, lifetime ) REFERENCES other_table ( id, lifetime ) USING gist ( container_id WITH =, lifetime WITH @ ) (Of course, this is PosgreSQL-pseudo-code, but it hopefully make clear what I want.) So, now my questions: 1) Does this kind of feature already exist in 9.2? If yes, a link to the documentation would be helpful. 2) If this feature does not directly exist, has anybody a good idea how to mimic the intended behaviour? 3) If neither 1) or 2) applies, are there any plans to integrate such a feature? I found this discussion http://www.postgresql.org/message-id/4f8bb9b0.5090...@darrenduncan.net . Does anybody know about the progress? Having range types and exclusion contraints are nice, as I said in the introdruction. But if the reverse (foreign key with inclusion) would also work, the range type feature would really be amazing. Best regards, Matthias Nagel -- Matthias Nagel Willy-Andreas-Allee 1, Zimmer 506 76131 Karlsruhe Telefon: +49-721-8695-1506 Mobil: +49-151-15998774 e-Mail: matthias.h.na...@gmail.com ICQ: 499797758 Skype: nagmat84 - -- Matthias Nagel Willy-Andreas-Allee 1, Zimmer 506 76131 Karlsruhe Telefon: +49-721-8695-1506 Mobil: +49-151-15998774 e-Mail: matthias.h.na...@gmail.com ICQ: 499797758 Skype: nagmat84 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
