Thread: 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.nagel@gmail.com>
An: pgsql-sql@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
managea 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)
REFERENCEScontainer ( 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
thatonly 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
containerswhose 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
laterthe 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 =,
lifetimeWITH <@ )
 

(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.5090708@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
withinclusion) 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.nagel@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.nagel@gmail.com
ICQ: 499797758
Skype: nagmat84

Matthias Nagel <matthias.h.nagel@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