In connection with this answer I stumbled upon a phenomenon I cannot explain.
Version:
PostgreSQL 9.1.2 on x86_64-unknown-linux-gnu, compiled by gcc-4.4.real (Debian 4.4.5-8) 4.4.5, 64-bit
Consider the following demo. Testbed:
CREATE TEMP TABLE t (
id integer
,txt text
,CONSTRAINT t_pkey PRIMARY KEY (id) DEFERRABLE INITIALLY IMMEDIATE
);
INSERT INTO t VALUES
(1, 'one')
,(2, 'two');
UPDATE t
SET id = t_old.id
FROM t t_old
WHERE (t.id, t_old.id) IN ((1,2), (2,1));
It seems there is a bug in the current implementation? The above UPDATE works though it should not. The constraint is defined INITIALLY IMMEDIATE
and I did not use SET CONSTRAINTS
.
Am I missing something or is this a (rather harmless) bug?
Consequently, a data modifying CTE works, too, though it fails with a NOT DEFERRED
pk:
WITH x AS (
UPDATE t SET id = 1 WHERE id = 2
)
UPDATE t SET id = 2 WHERE id = 1;
I quote the manual on CTEs:
The sub-statements in WITH are executed concurrently with each other and with the main query. Therefore, when using data-modifying statements in WITH, the order in which the specified updates actually happen is unpredictable. All the statements are executed with the same snapshot (see Chapter 13), so they cannot "see" each others' effects on the target tables.
Without SET CONSTRAINTS
, this fails with a UNIQUE violation - as expected:
BEGIN;
-- SET CONSTRAINTS t_pkey DEFERRED;
UPDATE t SET id = 2 WHERE txt = 'one';
UPDATE t SET id = 1 WHERE txt = 'two';
COMMIT;
I remember having raised an almost identical point when PG9 was in alpha state. Here was the answer from Tom Lane (high-profile PG core developer): http://archives.postgresql.org/pgsql-general/2010-01/msg00221.php
In short: won't fix.
Not to say that I agree with your suggestion that the current behavior is a bug. Look at it from the opposite angle: it's the behavior of NOT DEFERRABLE
that is incorrect.
In fact, the constraint violation in this UPDATE should never happen in any case, since at the end of the UPDATE the constraint is satisfied. The state at the end of the command is what matters. The intermediate states during the execution of a single statement should not be exposed to the user.
It seems like the PostgreSQL implements the non deferrable constraint by checking for duplicates after every row updated and failing immediately upon the first duplicate, which is essentially flawed. But this is a known problem, probably as old as PostgreSQL. Nowadays the workaround for this is precisely to use a DEFERRABLE constraint. And there is some irony in that you're looking at it as deficient because it fails to fail, while somehow it's supposed to be the solution to the failure in the first place!
NOT DEFERRABLE
UNIQUE
or PRIMARY KEY
constraints are checked after each row.
DEFERRABLE
constraints set to IMMEDIATE
(INITIALLY IMMEDIATE
or via SET CONSTRAINTS
) are checked after each statement.
DEFERRABLE
constraints set to DEFERRED
(INITIALLY DEFERRED
or via SET CONSTRAINTS
) are checked after each transaction.
Note the special treatment of UNIQUE
/ PRIMARY KEY
constraints.
Quoting the manual page for CREATE TABLE
:
A constraint that is not deferrable will be checked immediately after every command.
While it states further down in the Compatibility section under Non-deferred uniqueness constraints
:
When a
UNIQUE
orPRIMARY 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 asDEFERRABLE
but not deferred (i.e.,INITIALLY IMMEDIATE
). Be aware that this can be significantly slower than immediate uniqueness checking.
Bold emphasis mine.
If you need any FOREIGN KEY
constraints to reference the column(s), DEFERRABLE
is not an option because (per documentation):
The referenced columns must be the columns of a non-deferrable unique or primary key constraint in the referenced table.
There may be a slight documentation bug here, but not for the case you're showing. If you BEGIN a transaction and try the updates one at time, they fail, but if a single statement leaves things in a good state, it doesn't complain. The docs say:
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.
Which is exactly what seems to be happening. What is the surprise to me, given the documentation of DEFERRABLE
, which says in part:
A constraint that is not deferrable will be checked immediately after every command.
Without the DEFERRABLE INITIALLY IMMEDIATE
options, the example update fails, even though the UPDATE
statement (presumably constituting the "command") leaves things in a good state. Perhaps the docs should be modified to say that a NOT DEFERRABLE
constraint is enforced as each row is modified by a statement?