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.