From: | Jeff Davis <pgsql(at)j-davis(dot)com> |
---|---|
To: | Greg Stark <gsstark(at)mit(dot)edu> |
Cc: | Peter Eisentraut <peter_e(at)gmx(dot)net>, pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: foreign keys for array/period contains relationships |
Date: | 2010-10-26 18:53:42 |
Message-ID: | 1288119222.15279.26.camel@jdavis-ux.asterdata.local |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Greg Stark | 2010-10-26 18:54:47 | Re: Extensible executor nodes for preparation of SQL/MED |
Previous Message | Jeff Davis | 2010-10-26 18:53:27 | Re: foreign keys for array/period contains relationships |