Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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.





[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux