Re: Re: Adding foreign key constraint holds exclusive lock for too long (on production database) - Mailing list pgsql-performance

From Tom Lane
Subject Re: Re: Adding foreign key constraint holds exclusive lock for too long (on production database)
Date
Msg-id 13822.1383093005@sss.pgh.pa.us
Whole thread Raw
In response to Re: Adding foreign key constraint holds exclusive lock for too long (on production database)  (David Johnston <polobo@yahoo.com>)
Responses Re: Re: Adding foreign key constraint holds exclusive lock for too long (on production database)  (Ben Hoyt <benhoyt@gmail.com>)
List pgsql-performance
David Johnston <polobo@yahoo.com> writes:
> As noted in the referenced thread (and never contradicted) the current
> algorithm is "for each record does the value in the FK column exist in the
> PK table?" not "do all of the values currently found on the FK table exist
> in the PK table?".

Well, apparently nobody who knows the code was paying attention, because
that hasn't been true for some time.  ALTER TABLE ADD FOREIGN KEY will
actually validate the constraint using a query constructed like this
(cf RI_Initial_Check() in ri_triggers.c):

     *    SELECT fk.keycols FROM ONLY relname fk
     *     LEFT OUTER JOIN ONLY pkrelname pk
     *     ON (pk.pkkeycol1=fk.keycol1 [AND ...])
     *     WHERE pk.pkkeycol1 IS NULL AND
     * For MATCH SIMPLE:
     *     (fk.keycol1 IS NOT NULL [AND ...])
     * For MATCH FULL:
     *     (fk.keycol1 IS NOT NULL [OR ...])

It appears the possible explanations for Ben's problem are:

1. For some reason this query is a lot slower than the one he came up
with;

2. The code isn't using this query but is falling back to a row-at-a-time
check.

Case 2 would apply if the user attempting to do the ALTER TABLE doesn't
have read permission on both tables ... though that seems rather unlikely.

            regards, tom lane


pgsql-performance by date:

Previous
From: David Johnston
Date:
Subject: Re: Adding foreign key constraint holds exclusive lock for too long (on production database)
Next
From: si24
Date:
Subject: Re: postgres connections