Skip to content

Spike 2.3: Cyclic FK with SET CONSTRAINTS ALL DEFERRED

Hypothesis

Tables in a mutual FK cycle can be loaded in one transaction when constraints are DEFERRABLE INITIALLY DEFERRED and the session runs SET CONSTRAINTS ALL DEFERRED before inserts.

Setup

  • tests/fixtures/sql/spikes/02_cyclic_fk.sql on source DB
  • Tables spike_cycle_a / spike_cycle_b reference each other

Procedure

pytest tests/spikes/test_cyclic_fk_spike.py -m "integration and spike" -v

Results (fill after local run)

Metric Value
Date YYYY-MM-DD
Rows in A / B after commit 1 / 1
Transaction committed yes/no

Conclusion

  • Status: PASS / FAIL / BLOCKED
  • Notes: Pre-flight should still detect non-deferrable cycles (risk R3) and emit SQL to alter constraints.

Impact on design

  • Supports streaming spec FK-cycle handling (ยง12.9).
  • Reinforces Open Question Q1: pre-check DEFERRABLE on source, do not rely on blind attempt.