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

Hi,

On Wed, Aug 03, 2022 at 07:30:31PM -0500, Ron wrote:
>
> [quote]
> |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]
>
> [/quote]
>
> But yet a |DEFERRABLE| FK constraint in a transaction immediately failed on
> a FK constraint violation.
>
> [quote]
> |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.*
>
> [/quote]
>
> INITIALLY DEFERRED solved my problem.  Why do both clauses exist?

Because a deferred constraint needs to keep references around in memory until
the constraint is evaluated. The sooner it's done, the sooner you release that
memory and therefore can avoid, or minimize, memory-related problems.

The typical naive example for INITIALLY IMMEDIATE deferred constraint is a
primary key for which you want to do something like UPDATE ... SET pk = pk + 1

Postponing the evaluation at the end of the UPDATE command is enough, no need
to wait for the end of the transaction.