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

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?

(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.

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

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.

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.

On 8/3/22 18:13, Ron wrote:
> On 8/3/22 20:02, Adrian Klaver wrote:
>> On 8/3/22 17:30, Ron wrote:
>>> AWS RDS Postgresql 12.10

> 
> 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?

The way I think of it is:

1) CONSTRAINTS are something that is supposed to constrain an action.

2) Setting DEFERRABLE is a cheat code to push 1) out to the end of a 
transaction by using INITIALLY DEFERRED.

3) If the constraint was created/altered to DEFERRABLE INITIALLY 
DEFERRED the SET CONSTRAINTS IMMEDIATE unsets the cheat code in a given 
transaction. Maybe for most cases you want the check done at the end  of 
the transaction, but in a specific case you need that check to happen 
before another statement occurs prior to the end of the transaction.


-- 
Adrian Klaver
adrian.klaver@aklaver.com

Ron:

On Thu, 4 Aug 2022 at 02:30, Ron <ronljohnsonjr@gmail.com> wrote:
> DEFERRABLE
> NOT DEFERRABLE
> This controls whether the constraint can be deferred. A constraint that is not deferrable will be checked immediately
afterevery command. Checking of constraints that are deferrable can be postponed until the end of the transaction
[/quote]

> But yet a DEFERRABLE FK constraint in a transaction immediately failed on a FK constraint violation.

Because, as the name hints, it is DEFERRABLE not DEFERRED.

> INITIALLY IMMEDIATE
> INITIALLY DEFERRED
> If a constraint is deferrable, this clause specifies the default time to check the constraint. If the constraint is
INITIALLYIMMEDIATE, it is checked after each statement. This is the default. If the constraint is INITIALLY DEFERRED,
itis checked only at the end of the transaction. 
> INITIALLY DEFERRED solved my problem.  Why do both clauses exist?

Note DEFAULT TIME. Both claused exists because you can change that
with SET CONSTRAINTS.

Not knowing your case I cannot comment on it, but one use case I've
found is when your system NORMALLY holds constraints valid across
statements, so you have DEFERRABLE INITIALLY IMMEDIATE to check it and
fail early and loudly on any bug. But you have some SPECIAL_OP (tm),
which only holds constraints at transaction end, because it does some
carefully controlled manipulations. You do this one with SET
CONSTRAINTS DEFERRED ( and triple testing as you have weakened your
safety net ).You can even toggle it according to the docs, i.e. toglle
a constraint to deferred, do several statements which end up in a
valid state, toggle to immediate to check it is really valid. It's all
in the set constraints docs.

> (A naive interpretation just by looking at the clause words led me to think that INITIALLY DEFERRED would not check
recordvalidity when a constraint is added to a table, but obviously that's wrong too.) 

Not that obvious, but true. In the effort to make it read nice, like
natural languages,  SQL is difficult to interpret precisely, like
natural languages.

FOS.

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