What Parse Without Executing Actually Covers in a Rollback Script

We had just finished a cleanup on a live database: a gate to stop a background scanner from creating ownerless records, plus a one-time pass to clean up the ones that had already piled up. Standard discipline says a change like that gets a paired rollback script, written and staged before the real thing runs, in case any part of it needs undoing later. We wrote one. Then we made a mistake trying to prove the rollback script itself was safe.

The dry run that ran

Before running the rollback script for real, we wanted to confirm its syntax was correct without executing it. So we switched on a mode built for exactly that: parse the statements, don’t run them. We pointed it at the live database and treated it as a harmless no-op.

It is a no-op for statements inside a stored procedure body. It is not a no-op for plain schema and data statements sitting outside one, and most of a rollback script is exactly that: drop this constraint, drop this table, restore these rows.

So the “safety check” ran the script. For real. A constraint we’d just added got dropped. A small reference table the cleanup depended on got dropped with it. The rows we’d just finished cleaning reverted to their messy prior state, as if the cleanup pass had never run.

Noticing it right away

This stayed a non-event because we were already running verification queries as part of the same validation pass, and they came back wrong immediately. A constraint that should exist didn’t, and a table that should have rows in it didn’t exist at all. There was no gap between causing the problem and seeing it.

We restored the constraint with its correct definition and recreated and reseeded the reference table. Then we ran the cleanup step again, since it was written to be safe to repeat, and checked the end state against what the cleanup was supposed to produce. It matched, with one exception: the audit log now carries a duplicate pair of removal entries for those rows. We left them in place because that log is never edited after the fact. Nothing reached anyone using the actual product. The whole arc happened inside a validation step meant to catch problems before they could.

The second bug in the rollback script

While putting the pieces back, we found another problem in the same script. It hadn’t fired yet, because nothing had needed the rollback for real. The step that re-narrows a constraint after undoing a change assumed the underlying log table would only ever contain the values it had at the start. But that log is append-only. Records get added, never removed, and the gate we’d shipped that same day had already logged a new kind of entry.

Run the rollback as written, and that step would fail outright the moment it hit a single one of those new rows. We fixed it to let existing rows through without re-validating them, which is the correct behavior for an append-only log. Then we confirmed the corrected version handles both the untouched case and the case where new rows already exist.

What the check actually covered

We did not skip a safety step. We added one, on purpose, specifically to avoid running something risky against a live system before we were sure it was correct. The mistake was trusting what the feature’s name implied instead of checking what it covers. “Parse without executing” sounds like a blanket guarantee. It is a guarantee about one specific case, stored procedure bodies, and says nothing about everything else, including the exact kind of statement a rollback script is mostly made of.

A mode’s name is a claim, and a claim about safety is exactly the kind of thing worth testing on something that isn’t live before trusting it on something that is. We wrote the rollback script and tried to validate it before use, but we never checked that our validation method validated anything. The verification queries running alongside it are what turned a damaging mistake into a quick repair.

dxdev LESSON · paste into your AI coding agent
LESSON: A Mode Named 'Safe' Is Only Safe For the Statements It Actually Covers
SOURCE: dxdev.com/blog/the-safety-check-i-trusted-wasnt-one
WHAT HAPPENED: I had just shipped a cleanup against a live database: a gate that stopped a background process from creating orphaned records, plus a one-time cleanup of the records that had already piled up. Standard practice, I wrote a paired rollback script in case anything needed undoing. Before touching production with it, I wanted a safe way to confirm the rollback script's syntax was correct, so I switched on a mode that is supposed to check statements without executing them and ran it against the live database, believing that was a no-op. It was not. That mode suppresses execution inside stored procedure bodies, but plain schema and data statements outside a procedure run exactly as normal. The rollback script's real drop-constraint, drop-table, and data-restore statements executed for real: a constraint got dropped, a small reference table got dropped, and the cleanup I'd just finished got reverted back to its messy prior state. I caught it within the same validation pass, via a direct query against the live state, not because anyone downstream reported anything. I restored the constraint, recreated and reseeded the table, redid the cleanup, and reverified the end state matched the intended outcome exactly. While in there I also found the rollback script itself had a second latent bug: the step that re-narrows the constraint would fail outright the first time the gate had logged even one new kind of record, because that log table only ever adds rows and never deletes them. Fixed that too, before the script was needed for real.
THE RULE: "syntax-only" and "safe to run against production" are not the same claim unless you have verified they cover the exact statements in front of you. A feature's name describes what it was built for, not the boundary of what it actually does. Before trusting a "won't execute anything" mode against a live system, check what kinds of statements it actually suppresses, because the gap between the two is usually invisible until something outside the covered scope runs for real.
CHECK MY CODE, then report PASS or FAIL with file:line for each:
  1. Any script that uses a "dry run," "parse only," or "syntax check" mode against a live system, where the scope of what that mode actually suppresses has not been verified against every statement in the script.
  2. Any rollback or undo script that re-applies a narrower constraint or schema rule without accounting for rows already written under the wider rule it's rolling back from.
  3. Any production validation step whose safety depends on a single assumption about what a database feature does, rather than a verified test of that assumption on a non-production target first.
THEN PRINT: a table (check, PASS/FAIL, evidence, fix) + a verdict (applies / partially / OUT_OF_SCOPE / no) + the single most important next action.