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@xxxxxxxxxxx
