migration 6 down failure

[wydengyre]

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:

river/riverdriver/riverdatabasesql/migration/main/006_bulk_unique.down.sql

Line 11 in 56ddf09

CREATE UNIQUE INDEX IF NOT EXISTS river_job_kind_unique_key_idx ON river_job (kind, unique_key) WHERE unique_key IS NOT NULL;

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]

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.