Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id 4387F1336B42 for ; Tue, 22 Feb 2011 23:34:31 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024) with ESMTP id 37691-01-6 for ; Wed, 23 Feb 2011 03:34:25 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by mail.postgresql.org (Postfix) with ESMTP id 57FC81337C08 for ; Tue, 22 Feb 2011 23:34:10 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id p1N3Y9VS012632; Tue, 22 Feb 2011 22:34:09 -0500 (EST) To: Robert Haas cc: Noah Misch , Nikolas Everett , pgsql-performance@postgresql.org Subject: Re: Exhaustive list of what takes what locks In-reply-to: References: <20110202052004.GA26098@tornado.gateway.2wire.net> Comments: In-reply-to Robert Haas message dated "Tue, 22 Feb 2011 22:18:36 -0500" Date: Tue, 22 Feb 2011 22:34:08 -0500 Message-ID: <12631.1298432048@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.91 tagged_above=-10 required=5 tests=BAYES_00=-1.9, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201102/451 X-Sequence-Number: 42589 Robert Haas writes: > The problem is that constraints can affect the query plan. If a > transaction sees the constraint in the system catalogs (under > SnapshotNow) but the table data doesn't conform (under some earlier > snapshot) and if the chosen plan depends on the validity of the > constraint, then we've got trouble. At least when running at READ > COMMITTED, taking an AccessExclusiveLock protects us against that > hazard (I'm not exactly sure what if anything protects us at higher > isolation levels... but I hope there is something). Interesting point. If we really wanted to make that work "right", we might have to do something like the hack that's in place for CREATE INDEX CONCURRENTLY, wherein there's a notion that an index can't be used by a transaction with xmin before some horizon. Not entirely convinced it's worth the trouble, but ... > Now, it's true that in the specific case of a foreign key constraint, > we don't currently have anything in the planner that depends on that. > But I'm hoping to get around to working on inner join removal again > one of these days. Yeah, that sort of thing will certainly be there eventually. regards, tom lane