Re: Problem with foreign keys (performance and deadlocks)

On Tue, 10 Dec 2002, Brian Walker wrote:

> As a test I went into "backend/utils/adt/ri_triggers.c" and removed
> the "FOR UPDATE OF X" from the foreign key checks and the concurrency
> issues disappeared. This still make check 1 happen but removed the
> safety net of check 2.

Right, at which point you lose the guarantees of the constraint.

> The "FOR UPDATE OF X" seems to grab a lock that cannot be shared so
> the second foreign key select must wait until the first one releases.
> Is there a weaker lock that can applied to the foreign key check in
> ri_triggers.c? Is a new type of lock "FOR FKEY OF X" required?

Not in the same way and sort of (it's more involved than a new kind of
lock).

> I saw a discussion on this from March:
>
> http://archives.postgresql.org/pgsql-hackers/2002-03/msg01156.php
>
> These does not seem to be a resolution to this yet.
>
> Are there any plans to fix this soon? This is a serious problem for us.

There are plans to fix it. Right now, soon is relative to how much time I
can spend on it. I think a version of my very early testing patch ended
up on -general (with one bug that was mentioned afterwards that has an
easy fix) that I was asking for information on. As I say in that message,
it's not a prime time patch since at the last it lets through bad data
(less so than removing the for update from the original patch) and it
really needs better management of its local data and it's really ugly. I
haven't had any time recently, but I'm hoping to get a new testing patch
out by early January.