Hi!
The manual states here:
http://www.postgresql.org/docs/current/static/sql-createtable.html
DEFERRABLE
NOT DEFERRABLE
This controls whether the constraint can be deferred. A constraint that is
not deferrable will be checked immediately *after every command*.
Checking of constraints that are deferrable can be postponed until the end
of the transaction (using the SET CONSTRAINTS
<http://www.postgresql.org/docs/current/static/sql-set-constraints.html>
command). NOT DEFERRABLE is the default. Currently, only UNIQUE,
PRIMARY KEY, EXCLUDE, and REFERENCES (foreign key) constraints accept this
clause. NOT NULL and CHECK constraints are not deferrable.
INITIALLY IMMEDIATE
INITIALLY DEFERRED
If a constraint is deferrable, this clause specifies the default time to
check the constraint. If the constraint is INITIALLY IMMEDIATE, it is
checked *after each statement*. This is the default. If the constraint is
INITIALLY DEFERRED, it is checked only at the end of the transaction.
The constraint check time can be altered with the SET CONSTRAINTS
<http://www.postgresql.org/docs/current/static/sql-set-constraints.html>
command.
This directly contradicts the information further down:
Non-deferred Uniqueness Constraints
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.
Bold emphasis mine. My tests on Postgres 9.1 and 9.2 seem to confirm that the check for non-deferrable constraints happens *after every row*, not
after every command. So it should be:
DEFERRABLE
NOT DEFERRABLE
This controls whether the constraint can be deferred. A constraint that is
not deferrable will be checked immediately after every inserted or
modified row. Checking of constraints that are deferrable happens after
every statement and can be postponed until the end of the transaction
(using the SET CONSTRAINTS
<http://www.postgresql.org/docs/current/static/sql-set-constraints.html>
command). NOT DEFERRABLE is the default.
Currently, only UNIQUE, PRIMARY KEY, EXCLUDE, and REFERENCES (foreign key)
constraints accept this clause. NOT NULL and CHECK constraints are
not deferrable.
Also, this important difference is completely ignored in the documentation of SET CONSTRAINTS, where the behaviour of non-deferrable constraints is
treated as being equal to DEFERRABLE IMMEDATE (which it is not):
http://www.postgresql.org/docs/current/interactive/sql-set-constraints.html
> The third class is always IMMEDIATE.
Here is a test case to play with:
CREATE TEMP TABLE t1 (
id integer
,CONSTRAINT t_pkey PRIMARY KEY (id)
);
CREATE TEMP TABLE t2 (
id integer
,CONSTRAINT t2_pkey PRIMARY KEY (id) DEFERRABLE INITIALLY IMMEDIATE
);
CREATE TEMP TABLE t3 (
id integer
,CONSTRAINT t3_pkey PRIMARY KEY (id) DEFERRABLE INITIALLY DEFERRED
);
INSERT INTO t1 VALUES (1), (2);
INSERT INTO t2 VALUES (1), (2);
INSERT INTO t3 VALUES (1), (2);
UPDATE t1
SET id = tx.id
FROM t1 tx
WHERE t1.id <> tx.id; -- Fails. Contradicts manual.
UPDATE t2
SET id = tx.id
FROM t2 tx
WHERE t2.id <> tx.id; -- Succeeds
UPDATE t3
SET id = tx.id
FROM t3 tx
WHERE t3.id <> tx.id; -- Succeeds
I presented my case on stackoverflow in greater detail some time ago. Upon
revisiting I found the issue still unresolved.
http://stackoverflow.com/questions/10032272/constraint-defined-deferrable-initially-immediate-is-still-deferred
Regards
Erwin
