I’ve got a nearly-complete implementation for Cockroach in the migrate schema migration tool/library.
The only missing piece is implementing the
Unlock functions, to acquire a lock for performing schema changes. In the postgres implementation, they take advantage of the
pg_advisory_unlock function. These functions exist in the Cockroach grammar (presumably for compatibility), but merely return
true and don’t do any locking.
So, I wanted to pick your brains to see if you had any thoughts on the best way to implement this.
I came up with two possible options
- Maintain a separate ‘advisory lock’ table that is a simple key-value (key = lock id, value = bool (locked/unlocked)). Read and set this value in a transaction to atomically acquire the lock, and release it at the end of the migrations.
- Perform the entire series of migrations in the context of a single transaction, which is effectively a lock and will work so long as the current schema version is checked within the transaction block.
The first option has the potential to be a bit messy in the event of an error during migration (possibly creating a lock that is never released), and the latter would involve some pretty large changes to the inner workings of the migrate library to maintain the transaction context across all migrations, and also then has the limitations of schema changes in a Cockroach transaction.
Any suggestions/ideas on alternate implementations?
I’ve also got a work-in-progress PR open on my fork in case anyone wants to look over the code and make any other suggestions before I make a PR to the mainline repo