Cockroach DB Alter not supported

[taciturnaxolotl]

When starting up migrations will fail because of the ALTER command not being implemented in Cockroach DB

{"time":"2024-09-11T21:59:45.834109427Z","level":"ERROR","msg":"migration failed","error":"ERROR: unimplemented: ALTER COLUMN TYPE requiring rewrite of on-disk data is currently not supported for columns that are part of an index (SQLSTATE 0A000)"}

System information

• Wakapi version: 2.12.0
• Operating system: Ubuntu 22.04.3 LTS
• Database: Cockroach DB

https://github.com/cockroachdb/cockroach/issues/47636

[muety]

Hi @kcoderhtml, thanks for reporting this. We had issues with Cockroach compatibility in the past, see #442, #90. I'm tempted to drop Cockroach support entirely again, as only very few users actually seem to benefit from it, while maintaining support seems to be quite a hassle.

However, if you just started using Wakapi recently, chances are high that you don't actually need the migrations, because you're schemas are already at the latest version anyway. In that case, a potential workaround could be to set WAKAPI_DB_AUTOMIGRATE_FAIL_SILENTLY=true. You may just give it a try. But keep in mind that the underlying problem will persist and you might run into trouble in the future once we actually change our database schema again.

For custom migrations, what we could try on our end is to create special migrations for Cockroach (we already have similar checks, e.g. here) where we first remove the column from the index, then alter it, and then add it back. Didn't fully think through this, yet, so not sure if it's a feasible solution. For auto-migrations, we'd have to rely on Gorm's driver to add support for this.

Apart from that, there's not much we can do at this point unless Cockroach implements alter column for indexed columns (very unlikely?). Will keep the issue open to keep track of this, but with low prio. If anyone is eager to go for a deep-dive on the issue, feel free!

[taciturnaxolotl]

Thank you! I’m very much interested in cockroach as a db, but I think for now I'll just disable migrations and pin the version.

[taciturnaxolotl]

following up on this; we ended up migrating away from cockroach because of how expensive it started being at scale and migrated to postgres. once we switched over to postgres it worked insanely well!!! by now we have tracked a tad bit over a quarter million hours across 21.6k users and had very few issues.