Constraint defined DEFERRABLE INITIALLY IMMEDIATE is still DEFERRED?

Question

At what point is a DEFERRED / DEFERRABLE/ IMMEDIATE unique / primary key constraint enforced exactly?
I am asking in connection with this answer.

Tried with this testbed in PostgreSQL 9.1.2:

CREATE TABLE tbl (
  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 tbl t
SET    id = t_old.id
FROM   tbl t_old
WHERE (t.id, t_old.id) IN ((1,2), (2,1));

The above UPDATE works, though I expected it to fail. The constraint is defined INITIALLY IMMEDIATE and no SET CONSTRAINTS was issued. What am I missing?

2) Data modifying CTE

A data modifying CTE works the same. Though either fails with a NOT DEFERRED PK:

WITH upd1 AS (
   UPDATE tbl
   SET    id = 1
   WHERE  id = 2
   )
UPDATE tbl
SET    id = 2
WHERE  id = 1;

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 tbl SET id = 2 WHERE txt = 'one';
UPDATE tbl SET id = 1 WHERE txt = 'two';
COMMIT;

fiddle

Belfry

Same with 9.1.3 on Windows. You should report that as a bug to the PostgreSQL team. – Belay

The way I read the documentation, I think the update should fail. I'd call it a bug. – Corum

Can you refer to the documentation that shows why "The above UPDATE works though it should not." - why that update should not work? – Moose

@AlexKuznetsov: Look at the answers below. Also follow the link in the comments and consider Peter Eisentraut's answer there. – Belfry

@ErwinBrandstetter which says "The SQL standard says that uniqueness should be enforced only at the end of the statement ... To obtain standard-compliant behavior, declare the constraint as DEFERRABLE but not deferred (i.e., INITIALLY IMMEDIATE)" - this is exactly what you are doing, so your update should succeed, because at the end of the statement all IDs are unique. – Moose

I was surprised to see the famous Erwin Brandstetter asking a question rather than answering it, then I saw that it was 8 years ago and was only confusing due to a bug in Postgres at the time, and it all adds up. – Crosson

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.

Bugger

Answer 2

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?

Boarish

With some delay I proposed a fix like you suggested to [email protected]. – Belfry