Message-ID: <50569208.8050408@ringerc.id.au>
Date: Mon, 17 Sep 2012 10:59:20 +0800
From: Craig Ringer <ringerc@ringerc.id.au>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
	rv:14.0) Gecko/20120717 Thunderbird/14.0
MIME-Version: 1.0
To: Tom Lane <tgl@sss.pgh.pa.us>
CC: "McKinzie, Alan (Alan)" <alanmck@avaya.com>,
	"pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Subject: Re: Are there known performance issues with defining all
	Foreign Keys as deferrable initially immediate
References: 
 <E151F470685C15418AEA609E825A171022EC8EA823@DC-US1MBEX4.global.avaya.com>
	<5055D7FF.2070908@ringerc.id.au> <5055DE3D.4040603@ringerc.id.au>
	<11998.1347809835@sss.pgh.pa.us>
In-Reply-To: <11998.1347809835@sss.pgh.pa.us>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

On 09/16/2012 11:37 PM, Tom Lane wrote:
> Craig Ringer <ringerc@ringerc.id.au> writes:
>> Found it, it's in the NOTES for CREATE TABLE.
>> http://www.postgresql.org/docs/current/static/sql-createtable.html:
>
>> When a UNIQUE or PRIMARY KEY constraint is not deferrable, PostgreSQL
>> checks for uniqueness immediately whenever a row is inserted or
>> modified. The SQL standard says that uniqueness should be enforced only
>> at the end of the statement; this makes a difference when, for example,
>> a single command updates multiple key values. To obtain
>> standard-compliant behavior, declare the constraint as DEFERRABLE but
>> not deferred (i.e., INITIALLY IMMEDIATE). Be aware that this can be
>> significantly slower than immediate uniqueness checking.
>
> Note that that is addressing uniqueness constraints, and *only*
> uniqueness constraints.  Foreign key constraints are implemented
> differently.  There is no equivalent to an immediate check of a foreign
> key constraint --- it's checked either at end of statement or end of
> transaction, depending on the DEFERRED property.  So there's really no
> performance difference for FKs, unless you let a large number of pending
> checks accumulate over multiple commands within a transaction.

Ah, thanks. I missed that detail.

--
Craig Ringer