On 09/16/2012 11:37 PM, Tom Lane wrote:
Craig Ringer <ringerc@xxxxxxxxxxxxx> 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
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
