Contradicting information for DEFERRABLE constraints - Mailing list pgsql-docs
From | Erwin Brandstetter |
---|---|
Subject | Contradicting information for DEFERRABLE constraints |
Date | |
Msg-id | 519FAC78.7090603@falter.at Whole thread Raw |
Responses |
Re: Contradicting information for DEFERRABLE constraints
|
List | pgsql-docs |
The manual states here:
http://www.postgresql.org/docs/current/static/sql-createtable.html
- DEFERRABLE
NOT DEFERRABLEThis 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 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 DEFERREDIf 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 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 DEFERRABLEThis 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 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
pgsql-docs by date: