self-managed 14.5 upgrade failed - PG::UndefinedObject: ERROR: index "index_for_resource_group" does not exist

Summary

!74018 (merged) introduced a database migration to remove the redundant index_for_resource_group index in ci_builds that is not being used anymore.

Some self-managed instances do not have this index owing to schema drift.

We have a large premium customer whose upgrade to %14.5 in production failed because of this. GitLab team members can read more in the ticket.

Please can this migration be fixed along the lines of !71606 (merged) to DROP INDEX IF EXISTS and Pick into 14.5

Steps to reproduce

%14.4 instance
drop index_for_resource_group index from ci_builds
attempt to upgrade to %14.5

Example Project

What is the current bug behavior?

instances without this index fail to upgrade to %14.5

What is the expected correct behavior?

instances without the index execute the migration successfully

Relevant logs and/or screenshots

Caused by:
PG::UndefinedObject: ERROR:  index "index_for_resource_group" does not exist
/opt/gitlab/embedded/lib/ruby/gems/2.7.0/gems/activerecord-6.1.4.1/lib/active_record/connection_adapters/postgresql/database_statements.rb:47:in `exec'
/opt/gitlab/embedded/lib/ruby/gems/2.7.0/gems/activerecord-6.1.4.1/lib/active_record/connection_adapters/postgresql/database_statements.rb:47:in `block (2 levels) in execute'
/opt/gitlab/embedded/lib/ruby/gems/2.7.0/gems/activesupport-6.1.4.1/lib/active_support/dependencies/interlock.rb:48:in `block in permit_concurrent_loads'
/opt/gitlab/embedded/lib/ruby/gems/2.7.0/gems/activesupport-6.1.4.1/lib/active_support/concurrency/share_lock.rb:187:in `yield_shares'
/opt/gitlab/embedded/lib/ruby/gems/2.7.0/gems/activesupport-6.1.4.1/lib/active_support/dependencies/interlock.rb:47:in `permit_concurrent_loads'
/opt/gitlab/embedded/lib/ruby/gems/2.7.0/gems/activerecord-6.1.4.1/lib/active_record/connection_adapters/postgresql/database_statements.rb:46:in `block in execute'
/opt/gitlab/embedded/lib/ruby/gems/2.7.0/gems/activerecord-6.1.4.1/lib/active_record/connection_adapters/abstract_adapter.rb:696:in `block (2 levels) in log'
/opt/gitlab/embedded/lib/ruby/gems/2.7.0/gems/activesupport-6.1.4.1/lib/active_support/concurrency/load_interlock_aware_monitor.rb:26:in `block (2 levels) in synchronize'
/opt/gitlab/embedded/lib/ruby/gems/2.7.0/gems/activesupport-6.1.4.1/lib/active_support/concurrency/load_interlock_aware_monitor.rb:25:in `handle_interrupt'
/opt/gitlab/embedded/lib/ruby/gems/2.7.0/gems/activesupport-6.1.4.1/lib/active_support/concurrency/load_interlock_aware_monitor.rb:25:in `block in synchronize'
/opt/gitlab/embedded/lib/ruby/gems/2.7.0/gems/activesupport-6.1.4.1/lib/active_support/concurrency/load_interlock_aware_monitor.rb:21:in `handle_interrupt'
/opt/gitlab/embedded/lib/ruby/gems/2.7.0/gems/activesupport-6.1.4.1/lib/active_support/concurrency/load_interlock_aware_monitor.rb:21:in `synchronize'
/opt/gitlab/embedded/lib/ruby/gems/2.7.0/gems/activerecord-6.1.4.1/lib/active_record/connection_adapters/abstract_adapter.rb:695:in `block in log'
/opt/gitlab/embedded/lib/ruby/gems/2.7.0/gems/activesupport-6.1.4.1/lib/active_support/notifications/instrumenter.rb:24:in `instrument'
/opt/gitlab/embedded/lib/ruby/gems/2.7.0/gems/activerecord-6.1.4.1/lib/active_record/connection_adapters/abstract_adapter.rb:687:in `log'
/opt/gitlab/embedded/lib/ruby/gems/2.7.0/gems/activerecord-6.1.4.1/lib/active_record/connection_adapters/postgresql/database_statements.rb:45:in `execute'
/opt/gitlab/embedded/lib/ruby/gems/2.7.0/gems/marginalia-1.10.0/lib/marginalia.rb:71:in `execute_with_marginalia'
/opt/gitlab/embedded/lib/ruby/gems/2.7.0/gems/activerecord-6.1.4.1/lib/active_record/connection_adapters/postgresql/schema_statements.rb:472:in `remove_index'
/opt/gitlab/embedded/lib/ruby/gems/2.7.0/gems/activerecord-6.1.4.1/lib/active_record/migration.rb:929:in `block in method_missing'
/opt/gitlab/embedded/lib/ruby/gems/2.7.0/gems/activerecord-6.1.4.1/lib/active_record/migration.rb:897:in `block in say_with_time'
/opt/gitlab/embedded/lib/ruby/2.7.0/benchmark.rb:293:in `measure'
/opt/gitlab/embedded/lib/ruby/gems/2.7.0/gems/activerecord-6.1.4.1/lib/active_record/migration.rb:897:in `say_with_time'
/opt/gitlab/embedded/lib/ruby/gems/2.7.0/gems/activerecord-6.1.4.1/lib/active_record/migration.rb:918:in `method_missing'
/opt/gitlab/embedded/service/gitlab-rails/db/post_migrate/20210907211557_finalize_ci_builds_bigint_conversion.rb:180:in `block in rename_secondary_indexes'
/opt/gitlab/embedded/service/gitlab-rails/db/post_migrate/20210907211557_finalize_ci_builds_bigint_conversion.rb:179:in `each'
/opt/gitlab/embedded/service/gitlab-rails/db/post_migrate/20210907211557_finalize_ci_builds_bigint_conversion.rb:179:in `rename_secondary_indexes'
/opt/gitlab/embedded/service/gitlab-rails/db/post_migrate/20210907211557_finalize_ci_builds_bigint_conversion.rb:155:in `block in swap_columns'

Output of checks

Instances upgrading from %14.0 or %14.1 (or later) to %14.5 (or later ..)

Possible fixes

Workaround for affected customers:

Start database console

sudo gitlab-psql
# or
sudo gitlab-rails dbconsole

Create the index

CREATE INDEX index_for_resource_group ON ci_builds USING btree (resource_group_id, id) WHERE (resource_group_id IS NOT NULL);
\q

manually complete migrations
```
sudo gitlab-rake db:migrate
```

Edited Jan 10, 2022 by Ben Prescott (ex-GitLab)