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

On 8/3/22 20:02, Adrian Klaver wrote:
> On 8/3/22 17: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?
>
> Because from the same page:
>
> [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
>
> and
>
> 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 (using the SET CONSTRAINTS command). NOT DEFERRABLE is 
> the default. Currently, only UNIQUE, PRIMARY KEY, EXCLUDE, and REFERENCES 
> (foreign key) constraints accept this clause. NOT NULL and CHECK 
> constraints are not deferrable. Note that deferrable constraints cannot be 
> used as conflict arbitrators in an INSERT statement that includes an ON 
> CONFLICT DO UPDATE clause.
>
>
> 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. The 
> constraint check time can be altered with the SET CONSTRAINTS command.
>
>
> So the default
>
> NOT DEFERRABLE
>
> and:
>
> "A constraint that is not deferrable will be checked immediately after 
> every command."
>
> When you do
>
> DEFERRABLE
>
> the default is
>
> INITIALLY IMMEDIATE
>
> You have to explicitly set:
>
> INITIALLY DEFERRED.

And https://www.postgresql.org/docs/12/sql-set-constraints.html seems to say 
that SET CONSTRAINTS can make DEFERRABLE FKs temporarily not deferrable.

What's the point?


-- 
Angular momentum makes the world go 'round.