Re: Strange deadlock in foreign key check - Mailing list pgsql-general

From Sophia Wright
Subject Re: Strange deadlock in foreign key check
Date
Msg-id CAJTwwh17kwdjYRpq4SQBJOdMmOmiez4sP5tgax1Lh_0eg6nhJg@mail.gmail.com
Whole thread Raw
In response to Re: Strange deadlock in foreign key check  (Alvaro Herrera <alvherre@2ndquadrant.com>)
Responses Re: Strange deadlock in foreign key check
List pgsql-general
On Fri, Aug 7, 2015 at 1:11 AM, Alvaro Herrera <alvherre@2ndquadrant.com> wrote:
Sophia Wright wrote:
> I am seeing some odd locking behaviour when deleting a parent record
> (Postgres 9.4.4).

Somewhere in the triggers for FK checks we do "SELECT FOR KEY SHARE" of
the PK tuples when the FK tuples are altered; and conversely when we
remove tuples from the PK side we need to ensure that there are no
referencing tuples in the FK side.  The code doesn't distinguish between
indexes used in foreign keys from other indexes that *could* be used in
foreign keys.  Therefore your UNIQUE in the declaration for "x" may be
making it difficult for you.  I don't have the time to go through this
right now, but please try and see what happens if you remove the UNIQUE
from that column.

We discussed about only considering indexes actually referenced by
foreign keys instead of all of them, but there are some fine points to
keep in mind if you do that, so we never got around to implementing that
optimization.  I don't have any immediate suggestion for what to do to
work around this issue.

Thanks. Removing the UNIQUE constraint prevents this, but I'm still not clear on why it happens...

Based on your explanation, I can see how a UNIQUE index on the PK side would cause problems. But on the FK side, I'm not sure where this fits in. Why lock the UNIQUE field, but not lock the FK field itself? Isn't it the only part that's relevant here?

pgsql-general by date:

Previous
From: Adrian Klaver
Date:
Subject: Re: Strange deadlock in foreign key check
Next
From: Luca Ferrari
Date:
Subject: Re: Oracle baseline/baseplan/outplan in Postgres