From: | Erwin Brandstetter <brandstetter(at)falter(dot)at> |
---|---|
To: | pgsql-docs(at)postgresql(dot)org |
Subject: | Contradicting information for DEFERRABLE constraints |
Date: | 2013-05-24 18:07:52 |
Message-ID: | 519FAC78.7090603@falter.at |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-docs |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Ian Lawrence Barwick | 2013-05-24 20:29:11 | The Parser Stage: "plain ASCII text" ? |
Previous Message | Robins Tharakan | 2013-05-22 21:33:54 | Re: CREATE OPERATOR query |