Re: Purpose of DEFERRABLE _and_ INITIALLY DEFERRED foreign key constraint checking?

Ron:

On Thu, 4 Aug 2022 at 02:30, Ron <ronljohnsonjr(at)gmail(dot)com> wrote:
> DEFERRABLE
> NOT DEFERRABLE
> This controls whether the constraint can be deferred. A constraint that is not deferrable will be checked immediately after every command. Checking of constraints that are deferrable can be postponed until the end of the transaction [/quote]

> But yet a DEFERRABLE FK constraint in a transaction immediately failed on a FK constraint violation.

Because, as the name hints, it is DEFERRABLE not DEFERRED.

> INITIALLY IMMEDIATE
> INITIALLY DEFERRED
> 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.
> INITIALLY DEFERRED solved my problem. Why do both clauses exist?

Note DEFAULT TIME. Both claused exists because you can change that
with SET CONSTRAINTS.

Not knowing your case I cannot comment on it, but one use case I've
found is when your system NORMALLY holds constraints valid across
statements, so you have DEFERRABLE INITIALLY IMMEDIATE to check it and
fail early and loudly on any bug. But you have some SPECIAL_OP (tm),
which only holds constraints at transaction end, because it does some
carefully controlled manipulations. You do this one with SET
CONSTRAINTS DEFERRED ( and triple testing as you have weakened your
safety net ).You can even toggle it according to the docs, i.e. toglle
a constraint to deferred, do several statements which end up in a
valid state, toggle to immediate to check it is really valid. It's all
in the set constraints docs.

> (A naive interpretation just by looking at the clause words led me to think that INITIALLY DEFERRED would not check record validity when a constraint is added to a table, but obviously that's wrong too.)

Not that obvious, but true. In the effort to make it read nice, like
natural languages, SQL is difficult to interpret precisely, like
natural languages.

FOS.