Ron: On Thu, 4 Aug 2022 at 02:30, Ron <ronljohnsonjr@xxxxxxxxx> 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.
