What are the consequences of making a foreign key constraint
DEFERRABLE INITIALLY IMMEDIATE instead of
This answer on StackOverflow mentions the performance impact that comes with no longer using a unique index for a deferrable unique constraint and I can relate to that. However, what are disadvantages or side-effects of making a foreign key deferrable?
I can see only one disadvantage: inconsistencies introduced by earlier writes may go undetected until a
COMMIT occurs. However, our application's storage layer delays all DML statements until the end of the transaction, so an inconsistent DB state is never read (not even by the code causing it) and only detected upon
Are there others?
If it matters, we are thinking about making all foreign keys in a certain table family
DEFERRABLE INITIALLY IMMEDIATE, but only make them deferred in one specific job (out of 20 or so that access these tables) that performs a large amount of mutually dependent inserts/updates/deletes.
TL;DR: What do we have to be aware of when making foreign keys
DEFERRABLE INITIALLY DEFERRED?