Thread: Fwd: Range types (DATERANGE, TSTZRANGE) in a foreign key with "inclusion" logic

Fwd: Range types (DATERANGE, TSTZRANGE) in a foreign key with "inclusion" logic

From
Matthias Nagel
Date:
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




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



--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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