Re: foreign keys for array/period contains relationships

On Mon, 2010-10-25 at 17:57 -0700, Greg Stark wrote:
> On Mon, Oct 25, 2010 at 5:24 PM, Jeff Davis <pgsql(at)j-davis(dot)com> wrote:
> > I think that's easier when the PK must contain the FK, because then you
> > only need to lock one record. Even when you need to lock multiple
> > records, it seems feasible, and is just an index lookup, right? Do you
> > see a particular problem?
>
> Well if you lock multiple records then it's not clear what operations
> you should conflict with. Removing any one of them wouldn't actually
> invalidate the foreign key reference unless you remove the last one.

I didn't word my statement clearly. If the PK contains the FK, and you
have an Exclusion Constraint on the PK (as Peter suggested), then you
only need to lock one record. I think that logic would be pretty much
the same as a normal FK.

The case where you might need to lock multiple records is when the FK
contains the PK (case #1 in Peter's original email). But in that case,
you would still have a UNIQUE constraint on the PK (right?) and removing
any referenced element should cause a conflict.

Case #2 is the strange one, I think. It's not actually just an
adaptation of #1. #1 requires that all elements of the array have a
corresponding PK value; but #2 just requires that one of them does.
Peter, can you clarify case #2? Did you have a use case in mind?

Regards,
Jeff Davis