Stop Disabling Foreign Keys: Use a Topological Sort Instead
You inherit a PostgreSQL cleanup script. The very first line is:
|
1 |
SET SESSION_REPLICATION_ROLE TO REPLICA; |
It works. Every table gets wiped clean, no FK errors, no complaints. Then one day somebody adds a new table to the schema, forgets to update the delete script, and you end up with orphan rows. No error. No warning. Just silent data corruption that you won’t find until something downstream breaks.
The “just turn off constraints” approach is a ticking time bomb.
What SESSION_REPLICATION_ROLE Actually Does
This setting was designed for logical replication. It tells PostgreSQL: “treat this session as a replica.” The practical side effects are:
- Foreign key constraint checks are disabled
- User-defined triggers are suppressed
- Row-level security policies are bypassed
People use it as a shortcut when they can’t (or don’t want to) figure out the correct deletion order for a set of FK-related tables. It feels clever. It is not. You’re trading a 30-minute ordering exercise for an invisible, accumulating debt.
The Real Problem Is a Dependency Graph
Think about what foreign keys actually represent: directed edges in a graph. If orders has a FK pointing to customers, that’s an edge from orders to customers. You must delete orders before customers.
When you have 136 tables with 229 FK relationships, figuring out the right order by hand is painful. But this is a solved problem in computer science. It’s called topological sorting, and the most intuitive version is Kahn’s algorithm[1]:
- Find all tables with no incoming FK edges (the “leaves” – tables nothing else depends on)
- Delete those first. Remove them from the graph.
- Some tables that previously had incoming edges now have zero. They become the new leaves.
- Repeat until the graph is empty.
If the graph is never empty (you can’t find a node with zero incoming edges but there are still nodes left), you have a circular FK dependency, and you know about it immediately instead of discovering it at 2 AM.
Extracting the FK Graph
We had the DDL files in a Git repo (as you should). The FK definitions looked like this:
|
1 2 3 4 |
ALTER TABLE schema_own.order_items ADD CONSTRAINT fk_order_items_orders FOREIGN KEY (order_id) REFERENCES schema_own.orders (order_id); |
A regex pass over the DDL repo pulled out all 229 FK edges as (child, parent) pairs. We then partitioned them:
- Internal edges: both tables are in our delete script (these affect ordering)
- External edges: the parent table is outside our scope (these don’t affect ordering since we’re not deleting the parent)
Only the internal edges matter for the topological sort.
Running the Sort
Here’s a simplified version of Kahn’s algorithm in Python. The real script read edges from a file; this version hardcodes a small example:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 |
from collections import defaultdict, deque def topological_sort(edges): """ edges: list of (child, parent) tuples Returns: deletion order (leaves first, roots last) """ in_degree = defaultdict(int) graph = defaultdict(list) nodes = set() for child, parent in edges: graph[parent].append(child) in_degree[child] += 0 # ensure child exists in_degree[parent] += 1 nodes.update([child, parent]) # NOTE: in_degree counts how many children # point TO this node, so we reverse the # direction: delete nodes with zero # dependents first queue = deque( n for n in nodes if in_degree[n] == 0 ) order = [] while queue: node = queue.popleft() order.append(node) for neighbor in graph[node]: in_degree[neighbor] -= 1 if in_degree[neighbor] == 0: queue.append(neighbor) if len(order) != len(nodes): raise ValueError( "Cycle detected - circular FK dependency" ) return order |
The output is a flat list: delete in this order, top to bottom, and every FK constraint is satisfied at every step.
The Safety Checklist
Before removing SESSION_REPLICATION_ROLE, we verified that a clean topological sort was actually sufficient. Three things can break the “just reorder the DELETEs” approach:
1. Self-referencing FKs – a table with a FK pointing to itself (e.g., employee.manager_id referencing employee.employee_id). You can’t delete a parent row before its child rows when they’re in the same table. We checked: none in our schema.
2. DELETE triggers – a trigger that fires on DELETE could cascade, block, or insert into other tables. We checked all triggers in the schema: five total, all BEFORE INSERT or AFTER UPDATE. None fired on DELETE.
3. DEFERRABLE constraints – constraints marked DEFERRABLE INITIALLY DEFERRED are checked at COMMIT, not at statement execution time. If you’re running the entire script in a transaction, these could let you delete in any order and only fail at the end. More importantly, if you’re relying on that behavior, reordering alone might surface errors you weren’t expecting. We checked: no deferrable constraints defined.
All clear. Safe to remove the crutch.
The Coverage Gap Bonus
Here’s the real payoff. While mapping the FK graph, we found 5 tables that were missing from the delete script entirely. They had FK relationships to tables we were deleting, but nobody had added DELETE statements for them.
Under SESSION_REPLICATION_ROLE TO REPLICA, this was invisible. The FK checks that would have screamed “you can’t delete from customers because customer_preferences still has rows pointing to it” were silenced. The script completed successfully, leaving orphan rows in those 5 tables.
This is the real argument against disabling constraints. Active constraints are a safety net, not a nuisance. They catch exactly the kind of mistake that humans make: forgetting to update a script when the schema changes.
The Takeaway
Constraints exist to catch mistakes. Disabling them to avoid thinking about delete order is trading a small upfront cost for an invisible, accumulating debt. A topological sort took an afternoon; finding orphan rows in production takes much longer.
If you have a script that starts with SET SESSION_REPLICATION_ROLE TO REPLICA and it’s not actually doing replication, it’s worth spending the time to figure out the right delete order. Your future self will thank you.
[1] Kahn, A. B. (1962). “Topological sorting of large networks.” Communications of the ACM, 5(11), 558-562. doi:10.1145/368996.369025
Have you untangled a similar FK mess? I’d love to hear about it. Find me on Bluesky or LinkedIn.