Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29])
	by malur.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <ringerc@ringerc.id.au>) id 1TDFaB-0001F4-0Z
	for pgsql-performance@postgresql.org; Sun, 16 Sep 2012 14:12:23 +0000
Received: from mail-pb0-f46.google.com ([209.85.160.46])
	by magus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <ringerc@ringerc.id.au>) id 1TDFa8-0007al-LQ
	for pgsql-performance@postgresql.org; Sun, 16 Sep 2012 14:12:21 +0000
Received: by pbbrr13 with SMTP id rr13so7827786pbb.19
	for <pgsql-performance@postgresql.org>;
	Sun, 16 Sep 2012 07:12:19 -0700 (PDT)
X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed;
	d=google.com; s=20120113;
	h=message-id:date:from:user-agent:mime-version:to:cc:subject
	:references:in-reply-to:content-type:x-gm-message-state;
	bh=uuoFZl99VH3ZUOeHK1OA4g+6AKJmCMoH43TmKDPD3kQ=;
	b=fQhhYou9H4YkaLxmYFOijrV2GbetUwAaIs0tMNIYxG3y/l7N428BQUFD38i55gzFmJ
	3Hz6wzGBnw3FrTKBnUB09KwoJ9bV4x6II+s/guAgglLFdpxUHra94UcicAuJ9+liM1LV
	JubEDKbKfJMs3rhyDtM+NPOhHLdv1vfyXyeX59phGo6sRO8mVc2DUkMstYlOCo8mIwtJ
	4K2e4WNoq5+VbrDdMtBncubVqTNtltA78Z2VxuAaPlWqEZqD94oye9a0JRSeRPhL6Y6r
	wasMU9mpzBn8e3AADy4LtqdNOQArywF5SIlNzSUdamBVAlsoeqSSiLw5LZtOytf3c+Kb
	l0hA==
Received: by 10.68.203.200 with SMTP id ks8mr16395582pbc.142.1347804739322;
	Sun, 16 Sep 2012 07:12:19 -0700 (PDT)
Received: from ayaki.localdomain (124-169-33-9.dyn.iinet.net.au.
	[124.169.33.9])
	by mx.google.com with ESMTPS id qf4sm5105918pbc.1.2012.09.16.07.12.16
	(version=SSLv3 cipher=OTHER); Sun, 16 Sep 2012 07:12:17 -0700 (PDT)
Message-ID: <5055DE3D.4040603@ringerc.id.au>
Date: Sun, 16 Sep 2012 22:12:13 +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: "McKinzie, Alan (Alan)" <alanmck@avaya.com>
CC: "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>
In-Reply-To: <5055D7FF.2070908@ringerc.id.au>
Content-Type: multipart/alternative;
	boundary="------------030503040400080707090102"
X-Gm-Message-State: 
 ALoCoQltqbE1CkLR+zvriwnBHMS0PPgh4dDrI2fPl5fkypVY4Ii91xZyCh4C+JM5sBoqUBSp2sVa
X-Pg-Spam-Score: -1.9 (-)
X-Archive-Number: 201209/79
X-Sequence-Number: 47825

This is a multi-part message in MIME format.
--------------030503040400080707090102
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

On 09/16/2012 09:45 PM, Craig Ringer wrote:

> This seems under-documented and I haven't found much good info on it, 
> so the best thing to do is test it.

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.


--
Craig Ringer

--------------030503040400080707090102
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

<html>
  <head>
    <meta content="text/html; charset=ISO-8859-1"
      http-equiv="Content-Type">
  </head>
  <body bgcolor="#FFFFFF" text="#000000">
    <div class="moz-cite-prefix">On 09/16/2012 09:45 PM, Craig Ringer
      wrote:<br>
      <br>
    </div>
    <blockquote cite="mid:5055D7FF.2070908@ringerc.id.au" type="cite">This
      seems under-documented and I haven't found much good info on it,
      so the best thing to do is test it.
      <br>
    </blockquote>
    <br>
    Found it, it's in the NOTES for CREATE TABLE.<br>
    <br>
    <a
href="http://www.postgresql.org/docs/current/static/sql-createtable.html">http://www.postgresql.org/docs/current/static/sql-createtable.html</a>:<br>
    <br>
    <meta http-equiv="content-type" content="text/html;
      charset=ISO-8859-1">
    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.<br>
    <br>
    <br>
    --<br>
    Craig Ringer<br>
  </body>
</html>

--------------030503040400080707090102--