Обсуждение: Contradicting information for DEFERRABLE constraints

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 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 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 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

On 5/24/13 2:07 PM, Erwin Brandstetter wrote:
> 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.

That only applies to unique constraints, not to other kinds of
constraints.  That's why the unique constraint defer behavior is
described separately, but it's different from the normal constraint
defer behavior.