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(dot)klaver(at)aklaver(dot)com