Re: Are there known performance issues with defining all Foreign Keys as deferrable initially immediate

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