9

What are the consequences of making a foreign key constraint DEFERRABLE INITIALLY IMMEDIATE instead of NONDEFERRABLE?

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 COMMIT anyway.

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?

2
  • 1
    This is a good question but there is no an answer though
    – deFreitas
    Aug 6, 2018 at 4:31
  • This article may be of help to some. I know it's about Postgres, but still, there are some interesting points in it.
    – cavpollo
    May 17, 2021 at 11:25

2 Answers 2

2

Deferrable constraints can prevent optimizer transformations. Here's a simple example validating percentages must be between zero and one hundred:

create table exam_results (
  student_id integer, exam_id integer,
  percentage_correct number,
  primary key ( student_id, exam_id )
);

alter table exam_results
  add constraint exre_pct_correct_c
  check ( percentage_correct between 0 and 100 );

With a not deferrable constraint the optimizer knows if you search for values outside this range the query must return no rows. So if you do it can apply a transformation to bypass the table:

set serveroutput off

select * from exam_results
where  percentage_correct < 0;

select * 
from   dbms_xplan.display_cursor ( format => 'BASIC +PREDICATE' );

---------------------------------------------------
| Id  | Operation                  | Name         |
---------------------------------------------------
|   0 | SELECT STATEMENT           |              |
|*  1 |  FILTER                    |              |
|*  2 |   TABLE ACCESS STORAGE FULL| EXAM_RESULTS |
---------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter(NULL IS NOT NULL)
   2 - storage("PERCENTAGE_CORRECT"<0)
       filter("PERCENTAGE_CORRECT"<0)

Notice the filter operation NULL IS NOT NULL is a parent of the full scan operation. Because it's false, there's no need to run the scan. The optimizer has stopped the query accessing the table at all!

If you re-create the constraint as deferrable, then you may temporarily have invalid data. This applies even if you declare it initially immediate because you can change the state to deferred within sessions/transactions.

So the optimizer can no longer rely on the constraint:

alter table exam_results
  drop constraint exre_pct_correct_c;
  
alter table exam_results
  add constraint exre_pct_correct_c
  check ( percentage_correct between 0 and 100 )
  deferrable initially immediate;

select * from exam_results
where  percentage_correct < 0;

select * 
from   dbms_xplan.display_cursor ( format => 'BASIC +PREDICATE' );

--------------------------------------------------
| Id  | Operation                 | Name         |
--------------------------------------------------
|   0 | SELECT STATEMENT          |              |
|*  1 |  TABLE ACCESS STORAGE FULL| EXAM_RESULTS |
--------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - storage("PERCENTAGE_CORRECT"<0)
       filter("PERCENTAGE_CORRECT"<0)

The plan no longer has filter operation. The database reads every row in the table, checking to see if the percentage is below zero.

TL;DR Without the guarantees validated, not deferrable constraints bring, you limit the optimizer's ability to generate better plans.

-4

Deferrable constraints are usually wrong. It's a proof that your ORM does something wrong. Usual problems are

  • commit might fail. This is usally not expexted by various tools and also by developers
  • commit might last very long. This might be problem with distributed(XA) transactions.
  • also troubleshooting might be problematic, especially when using Java(JDBC). When commit fails you can hardly say which particular constraint failed the transaction
3
  • 1
    I'm sorry, I can't relate to this. It's too vague to answer the question, and it doesn't say anything about the foreign key part of it.
    – blubb
    May 26, 2015 at 16:49
  • 1
    Does that mean that developers are tools? ;) I would give +1 if I could for that - but I disagree with the "Deferrable constraints are usually wrong." May 26, 2015 at 17:04
  • Developers are creating tools. I saw many faulty SW where developers forgot to wrap commit into begin ... exception when ... end; block or into try-catch. Simply because nobody expected that commit can throw an exception. When you do ETL then you usualy do some DDL (disable constraints, disable indexes, drop unique indexes, ...) and then you load data. Whenever I saw deffered constraints in some schema it was implemented because company was using some crazy in-house ORM tool, which was unable serialize SQL properly.
    – ibre5041
    May 26, 2015 at 17:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.