"20221114142044 delete experiments foreign keys" migration always fails

The migration 20221114142044_delete_experiments_foreign_keys always fails for me, both on our production system and also in an unrelated test VM.

I can only upgrade to version 15.6.8. Any version past that seems to fail, I tried 15.9.2 and 15.8.4.

Anyone an idea what is going on here?

main: == 20221110080748 AddPartitionIdToCiBuildPendingStates: migrating =============
main: -- add_column(:ci_build_pending_states, :partition_id, :bigint, {:default=>100, :null=>false})rake aborted!
StandardError: An error has occurred, all later migrations canceled:

PG::UndefinedColumn: ERROR:  column postgres_foreign_keys.constrained_table_name does not exist
LINE 1: ...ELECT 1 AS one FROM "postgres_foreign_keys" WHERE "postgres_...
                                                             ^
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/load_balancing/connection_proxy.rb:100:in `block in read_using_load_balancer'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/load_balancing/load_balancer.rb:129:in `block in read_write'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/load_balancing/load_balancer.rb:200:in `retry_with_backoff'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/load_balancing/load_balancer.rb:118:in `read_write'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/load_balancing/connection_proxy.rb:99:in `read_using_load_balancer'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/load_balancing/connection_proxy.rb:54:in `block (2 levels) in <class:ConnectionProxy>'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/migration_helpers.rb:389:in `foreign_key_exists?'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/migration_helpers.rb:1028:in `remove_foreign_key_if_exists'
/opt/gitlab/embedded/service/gitlab-rails/db/post_migrate/20221114142044_delete_experiments_foreign_keys.rb:8:in `block in up'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/with_lock_retries.rb:123:in `run_block'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/with_lock_retries.rb:134:in `block in run_block_with_lock_timeout'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/with_lock_retries.rb:129:in `run_block_with_lock_timeout'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/with_lock_retries.rb:97:in `run'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/migrations/lock_retries_helpers.rb:52:in `with_lock_retries'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/migration_helpers/v2.rb:117:in `with_lock_retries'
/opt/gitlab/embedded/service/gitlab-rails/db/post_migrate/20221114142044_delete_experiments_foreign_keys.rb:7:in `up'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/migration_helpers/restrict_gitlab_schema.rb:33:in `block in exec_migration'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/query_analyzer.rb:37:in `within'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/migration_helpers/restrict_gitlab_schema.rb:30:in `exec_migration'
/opt/gitlab/embedded/service/gitlab-rails/lib/gitlab/database/migrations/lock_retry_mixin.rb:36:in `ddl_transaction'
/opt/gitlab/embedded/service/gitlab-rails/lib/tasks/gitlab/db.rake:115:in `configure_database'
/opt/gitlab/embedded/service/gitlab-rails/lib/tasks/gitlab/db.rake:95:in `block (3 levels) in <top (required)>'
/opt/gitlab/embedded/bin/bundle:23:in `load'
/opt/gitlab/embedded/bin/bundle:23:in `<main>'

So after spending more time on this, it turns out that it actually is possible to get past this broken migration.

The upgrade path tool suggests:

15.3.3 => 15.4.6 => 15.6.8 => 15.9.2

What actually ended up working is:

15.3.3 => 15.4.6 => 15.6.8 => 15.7.0 => 15.9.2

While I do think that the upgrade path website is very helpful, I think the whole migration part is becoming more and more problematic and every upgrade feels like rolling the dice.

thanks, @jwfx
I just downgraded to 15.6.8 from 15.9.2 (unusable).
I will wait for a few weeks until this becomes more stable

Thanks for flagging. I have created an issue for the upgrade path helper tool in Upgrade path 15.4 to 15.9 suggests 15.6.x but misses 15.7.x as required db migration (#27) · Issues · GitLab.com / GitLab Support Team / toolbox / upgrade-path · GitLab

Was able to reproduce and have added a 15.7 as a upgrade step. It looks like its due to a migration bug here: