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.
