Purpose of DEFERRABLE _and_ INITIALLY DEFERRED foreign key constraint checking?

Поиск
Список
Период
Сортировка
От Ron
Тема Purpose of DEFERRABLE _and_ INITIALLY DEFERRED foreign key constraint checking?
Дата
Msg-id d7529945-a3a3-f5fe-9c63-08703f2c3825@gmail.com
обсуждение исходный текст
Ответы Re: Purpose of DEFERRABLE _and_ INITIALLY DEFERRED foreign key constraint checking?  (Adrian Klaver <adrian.klaver@aklaver.com>)
Re: Purpose of DEFERRABLE _and_ INITIALLY DEFERRED foreign key constraint checking?  (Julien Rouhaud <rjuju123@gmail.com>)
Re: Purpose of DEFERRABLE _and_ INITIALLY DEFERRED foreign key constraint checking?  (Francisco Olarte <folarte@peoplecall.com>)
Re: Purpose of DEFERRABLE _and_ INITIALLY DEFERRED foreign key constraint checking?  (Jan Wieck <jan@wi3ck.info>)
Список 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.

В списке pgsql-general по дате отправления:

Предыдущее
От: Tom Lane
Дата:
Сообщение: Re: PostgreSQL 14.4 ERROR: out of memory issues
Следующее
От: Adrian Klaver
Дата:
Сообщение: Re: Purpose of DEFERRABLE _and_ INITIALLY DEFERRED foreign key constraint checking?