Why does PostgreSQL check a DEFERRABLE INITIALLY IMMEDIATE constraint after each statement?

Question

I run the following SQL that never commits the transaction:

rollback;
begin;
create table testing_stuff (
    id serial,
    num integer NOT NULL unique deferrable initially immediate
);

insert into testing_stuff (num) values (2), (1);

-- no issues with deferrable even though it swaps values
update testing_stuff set num = id;

-- fails even though I have not comitted;
update testing_stuff set num = 2;

-- this would have fixed it
update testing_stuff set num = id;

If I remove deferrable initially immediate then it fails on update testing_stuff set num = id; because each row is checked immediately. So deferrable initially immediate is definitely making something deferred.

My understanding is that since the transaction is not committed, no constraints should be checked at all here, based on the SET CONSTRAINTS documentation which says:

DEFERRED constraints are not checked until transaction commit.

If I remove the line update testing_stuff set num = 2; then everything works. Also, if I use deferrable initially deferred then I get the deferred behavior I expect.

What am I missing about transaction boundaries and/or initially immediate here? Does initially immediate also apply to all future statements in the transaction? But then why would deferrable initially immediate avoid errors on update testing_stuff set num = id;?

Answer 1

In case of constraint violation, the exception is raised

• at the end of transaction if the constraint is deferrable initially deferred
• at the end of statement if it's deferrable initially immediate
• mid-statement if it's not deferrable.

The option with immediate in the name is in fact the second most immediate in terms of constraint validation overall, first most immediate only among the two deferrables, which I guess is where that syntax is from.

All constraints are checked truly immediately, on the spot. The difference is that regular, non-deferrable ones raise the exception as soon as they spot the violation, while the deferrable ones only take note to later re-check that.

The constraint part of create table, alter table..disable trigger and set constraints doc entries aren't very clear about that and it's further made worse by the different meanings of immediate. 64.5. Index Uniqueness Checks is a bit more helpful (here, immediately really means immediately):

UNIQUE_CHECK_YES indicates that this is a non-deferrable unique index, and the uniqueness check must be done immediately, as described above.

UNIQUE_CHECK_PARTIAL indicates that the unique constraint is deferrable. PostgreSQL will use this mode to insert each row's index entry. The access method must allow duplicate entries into the index, and report any potential duplicates by returning false from aminsert. For each row for which false is returned, a deferred recheck will be scheduled.

UNIQUE_CHECK_EXISTING indicates that this is a deferred recheck of a row that was reported as a potential uniqueness violation.

The comments in the source are pretty helpful, too:

This may be an end-of-statement check, a commit-time check, or a check triggered by a SET CONSTRAINTS command.

But only if you remember that the default is the truly immediate, mid-statement and that it's referring to the scheduled re-check. The base check is still immediate.

_{demo at db<>fiddle}