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

On 8/3/22 20:30, Ron wrote:
> AWS RDS Postgresql 12.10
>
> https://www.postgresql.org/docs/12/sql-createtable.html
>
> [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?

This is as per the Standard.

The default is NOT DEFERRABLE and when DEFERRABLE is specified then the
default is INITIALLY DEFERRED. This can then be overriden inside a
transaction with SET CONSTRAINT so that one or more (or all) DEFERRABLE
constraints will be deferred until the end of transaction OR until they
are explicitly set to IMMEDIATE again. Setting a previously DEFERRED
constraint to IMMEDIATE will immediately run all the queued up checks.

This gives the application absolute fine control as to when constraints
are checked.

The purpose of deferrable constraints is to do things that normally are
impossible. Like for example a circular constraint because you want
table A and table B to have a guaranteed 1:1 content on their primary
key. For every row in A there must be a corresponding row in B and vice
versa. This is implemented with two constraints where A and B point at
each other. Without deferring those constraints it would be impossible
to ever get a single row into either of them.

Regards, Jan