riverdatabasesql driver inserts empty unique key arrays instead of null

[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.

[brandur]

@bgentry Is this possibly indicative of a bug or at least unexpected behavior? If we were to be storing an empty byte array, that probably means there wasn't supposed to be any unique considerations, and I would've expected that to a NULL.

[bgentry]

@brandur most likely yes, wish I had thought about that before. As I've verified this extensively on the pgx driver (just checked a long running live installation locally) it's probably something specific to dbsql.

[bgentry]

@brandur yep, confirmed it's a dbsql specific thing with the extra driver test coverage here. Working on figuring out the cause/fix.

[bgentry]

@brandur it seems to be that the pq.Array type being used for the [][]byte input for the multiple unique key byte slices isn't handling nil slices correctly, inserting an empty slice instead of a NULL. Haven't yet been able to figure out a fix, might be related to sqlc-dev/sqlc#3021 or some other sqlc + pq issue.

[brandur]

Ah dang it. Yeah, that pq.Array is awful to work with and has caused problems before. Not surprised.