From: Craig Ringer <ringerc@ringerc.id.au>
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
Date: Sun, 16 Sep 2012 22:12:13 +0800
Message-ID: <5055DE3D.4040603@ringerc.id.au> (raw)
In-Reply-To: <5055D7FF.2070908@ringerc.id.au>
References: <E151F470685C15418AEA609E825A171022EC8EA823@DC-US1MBEX4.global.avaya.com>
<5055D7FF.2070908@ringerc.id.au>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: ringerc@ringerc.id.au, alanmck@avaya.com
Subject: Re: Are there known performance issues with defining all Foreign Keys as deferrable initially immediate
In-Reply-To: <5055DE3D.4040603@ringerc.id.au>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox