Remove MR locked state index synchronously

What does this MR do and why?

idx_merge_requests_on_target_project_id_and_locked_state index has already been removed asynchronously on GitLab.com and needs to be also ran for other installations synchronously.

Verification that the index has been removed from Gitlab.com

It would've printed out the detail of the index instead if it existed.

Database Migrations

UP

(454457-idx_merge_requests_on_target_project_id_and_locked_state-synchronous-database-index-removal ✗)  ➜  rake db:migrate                                                                [14:34 23/04]
main: == [advisory_lock_connection] object_id: 124200, pg_backend_pid: 41722
main: == 20240423022641 DropIdxMergeRequestsOnTargetProjectIdAndLockedState: migrating
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0141s
main: -- indexes(:merge_requests)
main:    -> 0.0110s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0002s
main: -- remove_index(:merge_requests, {:algorithm=>:concurrently, :name=>"idx_merge_requests_on_target_project_id_and_locked_state"})
main:    -> 0.0015s
main: -- execute("RESET statement_timeout")
main:    -> 0.0004s
main: == 20240423022641 DropIdxMergeRequestsOnTargetProjectIdAndLockedState: migrated (0.0396s)

main: == [advisory_lock_connection] object_id: 124200, pg_backend_pid: 41722
ci: == [advisory_lock_connection] object_id: 124660, pg_backend_pid: 41724
ci: == 20240423022641 DropIdxMergeRequestsOnTargetProjectIdAndLockedState: migrating
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- view_exists?(:postgres_partitions)
ci:    -> 0.0006s
ci: -- indexes(:merge_requests)
ci:    -> 0.0101s
ci: -- execute("SET statement_timeout TO 0")
ci:    -> 0.0002s
ci: -- remove_index(:merge_requests, {:algorithm=>:concurrently, :name=>"idx_merge_requests_on_target_project_id_and_locked_state"})
ci:    -> 0.0020s
ci: -- execute("RESET statement_timeout")
ci:    -> 0.0003s
ci: == 20240423022641 DropIdxMergeRequestsOnTargetProjectIdAndLockedState: migrated (0.0254s)

ci: == [advisory_lock_connection] object_id: 124660, pg_backend_pid: 41724

DOWN

(454457-idx_merge_requests_on_target_project_id_and_locked_state-synchronous-database-index-removal ✗)  ➜  rake db:migrate:down:main VERSION=20240423022641                               [14:52 23/04]
main: == [advisory_lock_connection] object_id: 128320, pg_backend_pid: 53952
main: == 20240423022641 DropIdxMergeRequestsOnTargetProjectIdAndLockedState: reverting
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0101s
main: -- index_exists?(:merge_requests, :target_project_id, {:where=>"state_id = 4", :name=>"idx_merge_requests_on_target_project_id_and_locked_state", :algorithm=>:concurrently})
main:    -> 0.0080s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0002s
main: -- add_index(:merge_requests, :target_project_id, {:where=>"state_id = 4", :name=>"idx_merge_requests_on_target_project_id_and_locked_state", :algorithm=>:concurrently})
main:    -> 0.0016s
main: -- execute("RESET statement_timeout")
main:    -> 0.0002s
main: == 20240423022641 DropIdxMergeRequestsOnTargetProjectIdAndLockedState: reverted (0.0304s)

main: == [advisory_lock_connection] object_id: 128320, pg_backend_pid: 53952
dskim@dskim-gitlab-mbp: ~/forge/gitlab/gitlab-development-kit/gitlab [3.2.3]
(454457-idx_merge_requests_on_target_project_id_and_locked_state-synchronous-database-index-removal ✗)  ➜  rake db:migrate:down:ci VERSION=20240423022641                                 [14:52 23/04]
ci: == [advisory_lock_connection] object_id: 123820, pg_backend_pid: 54591
ci: == 20240423022641 DropIdxMergeRequestsOnTargetProjectIdAndLockedState: reverting
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- view_exists?(:postgres_partitions)
ci:    -> 0.0125s
ci: -- index_exists?(:merge_requests, :target_project_id, {:where=>"state_id = 4", :name=>"idx_merge_requests_on_target_project_id_and_locked_state", :algorithm=>:concurrently})
ci:    -> 0.0095s
ci: -- execute("SET statement_timeout TO 0")
ci:    -> 0.0001s
ci: -- add_index(:merge_requests, :target_project_id, {:where=>"state_id = 4", :name=>"idx_merge_requests_on_target_project_id_and_locked_state", :algorithm=>:concurrently})
ci:    -> 0.0020s
ci: -- execute("RESET statement_timeout")
ci:    -> 0.0004s
ci: == 20240423022641 DropIdxMergeRequestsOnTargetProjectIdAndLockedState: reverted (0.0418s)

ci: == [advisory_lock_connection] object_id: 123820, pg_backend_pid: 54591

MR acceptance checklist

Please evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.

Related to #454457