pg.ddx.io pgsql-docs@postgresql.org mailing list archive
help / color / mirror / Atom feedContradicting information for DEFERRABLE constraints
2+ messages / 2 participants
[nested] [flat]
* Contradicting information for DEFERRABLE constraints
@ 2013-05-24 18:07 Erwin Brandstetter <brandstetter@falter.at>
0 siblings, 1 reply; 2+ messages in thread
From: Erwin Brandstetter @ 2013-05-24 18:07 UTC (permalink / raw)
To: 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-sti...
Regards
Erwin
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Contradicting information for DEFERRABLE constraints
@ 2013-05-30 20:35 Peter Eisentraut <peter_e@gmx.net>
parent: Erwin Brandstetter <brandstetter@falter.at>
0 siblings, 0 replies; 2+ messages in thread
From: Peter Eisentraut @ 2013-05-30 20:35 UTC (permalink / raw)
To: Erwin Brandstetter <brandstetter@falter.at>; +Cc: pgsql-docs
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.
--
Sent via pgsql-docs mailing list (pgsql-docs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-docs
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2013-05-30 20:35 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2013-05-24 18:07 Contradicting information for DEFERRABLE constraints Erwin Brandstetter <brandstetter@falter.at>
2013-05-30 20:35 ` Peter Eisentraut <peter_e@gmx.net>
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