Re: Primary key gist index?

On 03/14/2018 11:44 AM, Tom Lane wrote:> Also, as you mention, 
extrapolating behavior that's not really equality
> to situations like foreign keys gets pretty interesting pretty fast.
> An exclusion constraint using && might ensure that no two values in the
> column are identical, but it would not be enough to ensure that a proposed
> FK row can't && with more than one PK row.  So what then?

This is (perhaps) a digression from the OP's question, but in temporal 
databases it is fine if the child record's as_of_time overlaps with 
multiple records from the parent table. In fact that would be pretty 
normal. What's required is that the child's as_of_time is completely 
covered by the "sum" of the as_of_times of the parent records with a 
matching ID. For example:

houses
id  as_of_time                appraisal
1   [2016-01-01, 2017-01-01)  $200k
1   [2017-01-01, 2018-01-01)  $230k

rooms
id  as_of_time                house_id
1   [2016-01-01, 2018-01-01)  1

If you had a "temporal foreign key" from rooms to houses, that example 
should be valid. I understand that's not possible today with normal 
foreign keys, and maybe it's too specific to be desirable as a 
general-purpose feature, but that's what the github extension I linked 
to tries to offer.

-- 
Paul              ~{:-)
pj@illuminatedcomputing.com