sql - Constraint defined DEFERRABLE INITIALLY IMMEDIATE is still DEFERRED?

Question

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

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');

1) UPDATE statement modifying multiple rows:

UPDATE t
SET    id = t_old.id
FROM   t t_old
WHERE (t.id, t_old.id) IN ((1,2), (2,1));

The above UPDATE works, though it expected 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?

2) Data modifying CTE

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.

3) Multiple UPDATE statements in one transaction

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;

Answer 1

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!

Summary of the status quo since PostgreSQL 9.1

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

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.