Orphaned Seeds from Partial Rollback Gaps

Most CI failures aren't bugs in the code — they're bugs in the test data. The specific failure mode that costs the most debugging time isn't a missing fixture or a bad foreign key; it's a partial rollback that silently leaves seed rows behind. The transaction rolls back, the test passes or fails, and the next test suite inherits a database with orphaned child rows whose parents no longer exist. Everything downstream is now running on corrupted state.

This happens at the intersection of two common patterns: seeding data across multiple tables with explicit INSERT statements, and relying on transaction rollback as the cleanup mechanism. When the rollback path doesn't mirror the insertion path — or when cascade deletes are absent or misconfigured — you get orphaned rows that FK constraints don't catch because the parent was deleted first, or not deleted at all.

By the end of this article you'll know exactly where partial rollbacks break cascade semantics, how to instrument your seed layer to detect orphans before they corrupt suite state, and which structural changes prevent the problem at the schema and test-harness level.

Why Partial Rollbacks Break Cascade Semantics

A partial rollback occurs when a test harness rolls back only a subset of the work performed during setup — typically because seeding spans multiple savepoints, multiple connections, or a mix of ORM operations and raw SQL that don't share the same transaction context. Postgres SAVEPOINT semantics are correct within a single connection, but factory_boy's create() calls, SQLAlchemy session flushes, and direct psycopg2 cursor executes are not always on the same connection, so a ROLLBACK TO SAVEPOINT only undoes what that connection saw.

Cascade deletes defined at the schema level (ON DELETE CASCADE) fire when a parent row is deleted via a DELETE statement. They do not fire during a rollback — rollback undoes the parent insert, but the child rows inserted on a different connection or after the savepoint are untouched. The result is child rows referencing a parent_id that no longer exists. If your FK is DEFERRABLE INITIALLY DEFERRED, the constraint check is postponed to commit time, which never comes — so the orphan slips through without any error. This is structurally identical to the phantom row problem that appears when bulk seeds bypass trigger-based audit chains, just triggered by rollback rather than insertion path.

Detecting and Eliminating Orphan Rows in Your Seed Layer

The first step is making orphans visible. Add a post-teardown assertion that runs after every test class and queries for referential violations directly:

-- Run after teardown; any rows returned are orphans
SELECT c.id, c.parent_id, c.created_at
FROM   child_records c
LEFT JOIN parent_records p ON p.id = c.parent_id
WHERE  p.id IS NULL
  AND  c.created_at > NOW() - INTERVAL '10 minutes';

The 10-minute window keeps this fast even on large datasets. Wire it into a pytest fixture at session scope so it runs once per suite rather than per test — and fail loudly if it returns rows. This query caught 14 orphaned order_line rows in a payment service suite that had been silently skewing aggregate coverage for two weeks.

The structural fix is to consolidate seed operations onto a single connection and a single explicit transaction, then truncate rather than rollback at teardown:

import pytest
import psycopg2

@pytest.fixture(scope="function")
def db_conn():
    conn = psycopg2.connect(dsn=TEST_DSN)
    conn.autocommit = False
    yield conn
    conn.rollback()   # only safe if ALL seeds used this conn
    conn.close()

@pytest.fixture(scope="function")
def clean_db(db_conn):
    yield db_conn
    # Truncate in reverse FK order; CASCADE handles children
    db_conn.execute(
        "TRUNCATE child_records, parent_records RESTART IDENTITY CASCADE;"
    )
    db_conn.commit()

TRUNCATE ... CASCADE is not the same as ON DELETE CASCADE — it truncates all referencing tables in one atomic DDL operation, which is exactly what you want for test teardown. Generation time for a 40-table seed graph dropped from 8 seconds (per-test rollback with savepoint bookkeeping) to under 1 second (truncate cascade at function scope) in one project, because Postgres doesn't have to reconstruct undo log entries for every inserted row.

For teams using factory_boy, enforce a single session by subclassing DjangoModelFactory or using factory_boy's _meta.sqlalchemy_session consistently. Never mix factory.create() with raw cursor inserts in the same test — the session boundary mismatch is where partial rollbacks originate. If you need raw SQL for performance (bulk inserts via COPY or execute_values), wrap them in the same connection object your ORM session is bound to, or accept that rollback won't cover them and use explicit truncation instead. Related ordering bugs — where child factories fire before parent rows are committed — are covered in detail in the article on cascade failures when foreign key seeds arrive out of insertion order.

Where Senior Engineers Still Get Burned

Assuming ORM session scope equals transaction scope. SQLAlchemy's Session and a raw psycopg2 connection are independent transaction participants. Engineers who know this in production code forget it in test harnesses because the test setup "looks" atomic. The fix: always inspect session.bind and confirm it matches the connection your raw cursors are using, or eliminate raw cursors from the seed path entirely.

Relying on deferred FK constraints as a safety net. DEFERRABLE INITIALLY DEFERRED constraints are useful in production for bulk loads, but in test environments they mask exactly the orphan condition you're trying to detect. If your test DB schema inherits deferred constraints from a migration that was applied without a backfill step, you'll see no constraint errors even when orphans exist. Set constraints to IMMEDIATE in your test schema, or run SET CONSTRAINTS ALL IMMEDIATE at the start of each test transaction so violations surface instantly rather than at a commit that never arrives.

Myths That Keep the Orphan Problem Alive

"ON DELETE CASCADE in the schema means rollback will clean up children." It won't. Cascade rules fire on explicit DELETE statements, not on transaction undo. A rollback reverses the parent insert on the connection that performed it; it has no knowledge of child rows inserted elsewhere. Teams that define ON DELETE CASCADE and then rely on rollback for cleanup are operating on a false mental model that survives code review because nobody tests the teardown path explicitly. The orphan detection query above is the fastest way to falsify this assumption.

"Truncating between tests is too slow for large seed graphs." This was true before Postgres 9.4 when TRUNCATE acquired better lock management. On modern Postgres (14+), truncating 20–30 tables with RESTART IDENTITY CASCADE takes under 100ms for typical test datasets. The perception of slowness usually comes from comparing against rollback on a small seed graph where rollback is fast — but rollback doesn't scale linearly with row count the way engineers expect. Measure both paths with EXPLAIN ANALYZE on your actual schema before choosing. Also note that soft-delete patterns introduce their own orphan variants; the orphaned audit rows from soft-delete seeds article covers that specific failure mode in depth.

Orphaned seed rows are a structural problem, not a discipline problem — they emerge from the gap between how transactions work and how test harnesses assume they work. The immediate action: add the orphan detection query as a post-suite assertion in your next sprint. The durable fix: consolidate seed operations onto a single connection, switch teardown from rollback to TRUNCATE ... CASCADE, and set FK constraints to IMMEDIATE in your test schema. That combination eliminates the entire class of failures, not just the instance you're debugging today.

Note: This article is for informational purposes only and is not a substitute for professional advice. If you need guidance on specific situations described in this article, consider consulting a qualified professional.