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

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.


> 
> (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.)
> 
> -- 
> Angular momentum makes the world go 'round.


-- 
Adrian Klaver
adrian.klaver@aklaver.com