From: Tom Lane <tgl@sss.pgh.pa.us>
To: David Johnston <polobo@yahoo.com>
cc: pgsql-performance@postgresql.org
Subject: Re: Re: Adding foreign key constraint holds exclusive lock for too
 long (on production database)
In-reply-to: <1383092289576-5776315.post@n5.nabble.com>
References: 
 <CAL9jXCFwKXt67+fSGq2N4NDMEadAe=DtKdUGU2gFSS0t-crFxA@mail.gmail.com>
 <1383092289576-5776315.post@n5.nabble.com>
Comments: In-reply-to David Johnston <polobo@yahoo.com>
 message dated "Tue, 29 Oct 2013 17:18:09 -0700"
Date: Tue, 29 Oct 2013 20:30:05 -0400
Message-ID: <13822.1383093005@sss.pgh.pa.us>
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

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


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance