undefined

points

[-]

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.

by perrygeo38 minutes ago|

parent|

[-]

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.