Comment by perrygeo
10 hours ago
Locksmith is awesome, how am I just now discovering this?
Your comments re: database state are spot on. DDL can fail in subtle ways. It's not even enough to take a snapshot of the current state and validate; things can change under your feet.
Take adding a unique index on a column: a simple CREATE UNIQUE INDEX statement, right? But you realize it will fail if the values aren't unique already, so you run a SELECT query to confirm. Yep, all unique. Deploy the app which runs the migration on startup - fail. A non-unique key arrived in the time between your queries.
Even more fun if you CREATE UNIQUE INDEX CONCURRENTLY and a non-unique key arrives in the middle of the DDL execution.
Wouldn't that indicate an issue in your business logic attempting to do this in the first place?
Or if you are relying on DB to fail and your business side to detect and react, you'd still have that built into the business logic so you can just keep retrying the schema migration until it succeeds (if it's rare this happens).
So while I can see how this can happen, it basically is a bug and it means you are doing the migration yet the invariants are not going to be satisfied. Basically, even if it succeeds, you will have future inserts fail with unique constraint being broken.
It's definitely a bug. It's often distributed systems timing where things are "eventually consistent", just not at the moment you indexed. It can also be manual sql clients or other applications connected but not coordinating with the app. Not that I recommend that microservice madness style of development, but I've had to support databases in those scenarios. Pitfalls abound if you want to enforce uniqueness only at the application layer.
In the worst case bugs, systems can hum along for years with silent consistency problems. Database columns that are assumed to be unique but aren't - the truth only shakes out when you CREATE INDEX. Then once you fix that, you've got to find why the app was doing it in the first place! Generally its better for the app to crash than to silently corrupt the database as it had been doing all along.