Thread: FK check implementation
I'm looking at the code behind the foreign key checks in ri_triggers.c, and something's got me a little confused.
In both cases (FK insert/update checking the PK, and PK update/delete checking the FK) the check is done with a SELECT ... FOR KEY SHARE.This makes perfect sense for PK checks, but in the FK check, it seems pointless at best; if it actually manages to find something to lock, it will fail the check and error out moments later. And in any case, I don't see how the key fields in the FK relation (to which the KEY SHARE lock applies) are even relevant to the constraint in question.
Nick Barnes <nickbarnes01@gmail.com> writes:
> I'm looking at the code behind the foreign key checks in ri_triggers.c, and
> something's got me a little confused.
> In both cases (FK insert/update checking the PK, and PK update/delete
> checking the FK) the check is done with a SELECT ... FOR KEY SHARE.
> This makes perfect sense for PK checks, but in the FK check, it seems
> pointless at best; if it actually manages to find something to lock, it
> will fail the check and error out moments later. And in any case, I don't
> see how the key fields in the FK relation (to which the KEY SHARE lock
> applies) are even relevant to the constraint in question.
> What am I missing?
Race conditions.
Example case: you're trying to delete the row for PK 'foo', while
concurrently somebody is inserting a row that references foo. With
no locking, neither of you will see the other action, hence both
will conclude their action is ok and commit. Presto: FK violation.
The point of the FOR SHARE lock (which also goes along with some
cute games played with the query's snapshot) is to make sure there
aren't uncommitted changes that would result in an FK violation.
We could possibly have done it another way but that would just have
resulted in two generally-similar mechanisms.
regards, tom lane
On Sat, Oct 11, 2014 at 1:32 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Race conditions.Nick Barnes <nickbarnes01@gmail.com> writes:
> I'm looking at the code behind the foreign key checks in ri_triggers.c, and
> something's got me a little confused.
> In both cases (FK insert/update checking the PK, and PK update/delete
> checking the FK) the check is done with a SELECT ... FOR KEY SHARE.
> This makes perfect sense for PK checks, but in the FK check, it seems
> pointless at best; if it actually manages to find something to lock, it
> will fail the check and error out moments later. And in any case, I don't
> see how the key fields in the FK relation (to which the KEY SHARE lock
> applies) are even relevant to the constraint in question.
> What am I missing?
Example case: you're trying to delete the row for PK 'foo', while
concurrently somebody is inserting a row that references foo. With
no locking, neither of you will see the other action, hence both
will conclude their action is ok and commit. Presto: FK violation.
The point of the FOR SHARE lock (which also goes along with some
cute games played with the query's snapshot) is to make sure there
aren't uncommitted changes that would result in an FK violation.
We could possibly have done it another way but that would just have
resulted in two generally-similar mechanisms.
regards, tom lane
I understand why the FK insert needs to lock on the PK row. But why is the PK delete trying to lock the FK row? If it finds one, won't the delete fail anyway? If it doesn't find one, what is there to lock?
On 10/10/2014 10:41 AM, Nick Barnes wrote: > > I understand why the FK insert needs to lock on the PK row. But why is > the PK delete trying to lock the FK row? If it finds one, won't the > delete fail anyway? If it doesn't find one, what is there to lock? > I would say this has to do with setting DEFERRABLE on a constraint. -- Adrian Klaver adrian.klaver@aklaver.com
On Sat, Oct 11, 2014 at 5:01 AM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
Any guesses why this might be? I would have thought that by this point, where we're actually performing the check, anything related to deferring the check would be behind us.On 10/10/2014 10:41 AM, Nick Barnes wrote:
I understand why the FK insert needs to lock on the PK row. But why is
the PK delete trying to lock the FK row? If it finds one, won't the
delete fail anyway? If it doesn't find one, what is there to lock?
I would say this has to do with setting DEFERRABLE on a constraint.
And even if we do require a lock, why FOR KEY SHARE? As I understand it, this won't lock the referencing field, which should be the only thing in the FK relation that we're interested in.