When AI Gets Migration Wrong: Our Failure Cases and Fixes

Failure Cases, Written Down

Most tooling posts describe successes. This one describes the ways automated migration goes wrong, because those failure modes are stable across every tool — ours included — and knowing them is most of the defence.

The common thread: the dangerous failures aren’t the loud ones. A conversion that errors out gets fixed the same afternoon. A conversion that succeeds and is subtly wrong gets found in UAT, or in production.

Case 1: Output That Cannot Compile

We shipped this one ourselves, so it is a fair place to start. Our PL/SQL transpiler wraps a converted routine in a dollar-quoted body. For a class of input — routines with no parameter list — the header rewrite fell through, while the footer still appended a closing $$. The result opened a body it never closed, or closed one it never opened.

The tell was mechanical and easy to check once we looked: an odd number of $$ in the output. The reason it survived a large test suite is that every test asserted on fragments — “this warning fired”, “that name got rewritten” — and none asserted the emitted routine was structurally whole.

The fix that matters is the test, not the patch. Structural gates now run over every fixture: balanced dollar quotes, an argument list, a RETURNS clause, LANGUAGE plpgsql, and no leftover Oracle IS. Deep semantic correctness is hard to assert; “did we emit a routine at all” is not, and it catches a whole class of regressions.

Case 2: The Empty String

Oracle treats an empty string as NULL. PostgreSQL does not — ’’ is a real, distinct value. Data copies across without complaint and every row count matches. Then application code that relied on col IS NULL silently stops matching rows.

-- Oracle: both of these are NULL
INSERT INTO t VALUES ('');      -- stored as NULL
SELECT count(*) FROM t WHERE c IS NULL;   -- 1

-- PostgreSQL: they are different values
INSERT INTO t VALUES ('');      -- stored as ''
SELECT count(*) FROM t WHERE c IS NULL;   -- 0

No tool can decide this for you — it is a per-column business decision. What a tool can do is refuse to be silent about it, and assert the chosen mapping during validation.

Case 3: DATE Is Not a Date

Oracle’s DATE carries a time component. Map it to PostgreSQL DATE — the obvious-looking choice — and you silently truncate hours, minutes and seconds on every row. Row counts match. Checksums on that column don’t, which is exactly why column-level validation exists. The correct mapping is TIMESTAMP.

Case 4: Sequences at Cutover

The single most common go-live failure, and it happens after everything looks green. Data loads with explicit ids; the target sequence still sits at its initial value; the first application insert collides.

-- run for every sequence before the app writes
SELECT setval('hr.employees_id_seq',
              (SELECT max(employee_id) FROM hr.employees));

Case 5: Confident Nonsense

The failure mode specific to LLM-assisted tooling: a model asked to convert an unfamiliar construct will produce fluent, well-formatted, wrong code rather than admit the gap. It will invent a PostgreSQL function that does not exist and use it with total confidence.

Our mitigations are structural rather than hopeful. Deterministic rules handle the constructs we have encoded — type mapping, function swaps, DDL shape — so the model is not the thing deciding them. Generated SQL is checked mechanically before it is offered. Destructive operations require explicit confirmation. And the assistant runs against a curated knowledge floor, so “I don’t cover that” is an available answer.

The Pattern

Every case above shares a shape: the migration appeared to succeed. Row counts matched. Nothing errored. That is why validation has to go deeper than counts — column-level checksums, and cryptographic proof per partition when the volume is large enough that a full comparison isn’t practical.