Purpose of DEFERRABLE _and_ INITIALLY DEFERRED foreign key constraint checking?

From: Ron <ronljohnsonjr(at)gmail(dot)com>
To: "pgsql-generallists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org>
Subject: Purpose of DEFERRABLE _and_ INITIALLY DEFERRED foreign key constraint checking?
Date: 2022-08-04 00:30:31
Message-ID: d7529945-a3a3-f5fe-9c63-08703f2c3825@gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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?

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Adrian Klaver 2022-08-04 01:02:11 Re: Purpose of DEFERRABLE _and_ INITIALLY DEFERRED foreign key constraint checking?
Previous Message Tom Lane 2022-08-03 23:05:52 Re: PostgreSQL 14.4 ERROR: out of memory issues