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

Matthias Nagel <matthias(dot)h(dot)nagel(at)gmail(dot)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