From: Erwin Brandstetter <brandstetter@falter.at>
To: pgsql-docs@postgresql.org
Subject: Contradicting information for DEFERRABLE constraints
Date: Fri, 24 May 2013 20:07:52 +0200
Message-ID: <519FAC78.7090603@falter.at> (raw)
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-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-sti...
Regards
Erwin
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-docs@postgresql.org
Cc: brandstetter@falter.at
Subject: Re: Contradicting information for DEFERRABLE constraints
In-Reply-To: <519FAC78.7090603@falter.at>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox