search

riverqueue / river

Fast and reliable background jobs in Go
https://riverqueue.com
Mozilla Public License 2.0
3.59k stars 94 forks source link

migration 6 down failure #650

Open wydengyre opened 1 month ago

wydengyre commented 1 month ago

River migration 6 down fails in river 0.13.0, at least when using the riverdatabasesql driver, when there are periodic jobs in the job table. It fails with the following message:

ERROR: could not create unique index "river_job_kind_unique_key_idx" (SQLSTATE 23505))

The relevant line from the migration is here: https://github.com/riverqueue/river/blob/56ddf095ab3b7ca183425641e84022a194d1111e/riverdriver/riverdatabasesql/migration/main/006_bulk_unique.down.sql#L11

It appears that the periodic jobs, rather than have their unique key set to NULL, instead just have an empty byte array (\x). As a result, they violate the unique constraint. Perhaps this was different in past versions of river?

bgentry commented 1 month ago

Dang, I think this ends up being a case where the migration isn't fully reversible if there's existing data conflicting with the index. 🤔 For performing the reverse migration, it's probably only going to be possible if you first clear or finish processing the existing unique jobs.

I'm not sure if there's anything that can be done to make this cleaner—really I wish we had just gotten this more flexible unique jobs implementation in the first place.