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;?

marc_s · Accepted Answer · 2024-08-23 19:23:18Z

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}

The mid-transaction scenario made sense to me, but the mid-statement check was confusing me until I realized that you can have an update statement that affects multiple rows, and that you either want the check to wait until the entire statement is done, or after each row gets modified. Of course this applies to INSERT, UPDATE, and DELETE as well.

Laurenz Albe · Accepted Answer · 2024-06-27 05:02:42Z

There are two things at play here:

DEFERRABLE is not DEFERRED. The INITIALLY IMMEDIATE part means that the constraint is not checked at the end of the transaction, but right after each statement. You'd need to write DEFERRABLE INITIALLY DEFERRED to have PostgreSQL check the constraint at the end of the transaction.
DEFERRABLE constraints are implemented differently from NOT DEFERRABLE constraints (the default) in PostgreSQL.

NOT DEFERRABLE unique constraints are verified at the moment when a row gets inserted into the unique index, so when you swap the two values, changing the first row triggers the constraint violation.

DEFERRABLE unique constraints are implemented with a system trigger that is executed at the end of the statement, so you get no constraint violation with a DEFERRABLE constraint, because the data are consistent after the statement has swapped the values.

The default NOT DEFERRABLE behavior is not in line with the SQL standard's definition, which demands that constraints are checked after the statement. PostgreSQL's default behavior is the way it is for performance reasone; if you need strict standard compliance, use a DEFERRABLE constraint.

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related