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(at)illuminatedcomputing(dot)com