Skip to content

Remove sync index idx_merge_requests_on_state_id_and_merge_status

Dylan Griffith requested to merge 402491-remove-index-sync-mrs-state-and-status into master

What does this MR do and why?

This was already removed async in !115926 (merged) . This follows the instructions in https://docs.gitlab.com/ee/development/database/adding_database_indexes.html#add-a-migration-to-destroy-the-index-synchronously to follow up by removing the index synchronously.

I have also verified in postgres.ai that this index was removed:

\d idx_merge_requests_on_state_id_and_merge_status
Session: webui-i17728
ERROR: psql error: psql:/tmp/psql-query-699078273:1: error: Did not find any relation named "idx_merge_requests_on_state_id_and_merge_status".

Screenshots or screen recordings

11:36:47 dylangriffith@Dylan-GitLab-MBP:~/workspace/gitlab-development-kit/gitlab$ bin/rake db:migrate
main: == [advisory_lock_connection] object_id: 312220, pg_backend_pid: 96397
main: == 20230413012807 RemoveIndexSyncIndexOnMergeRequestsOnStateIdAndMergeStatus: migrating
main: -- transaction_open?()
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0419s
main: -- indexes(:merge_requests)
main:    -> 0.0099s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0003s
main: -- remove_index(:merge_requests, {:algorithm=>:concurrently, :name=>"idx_merge_requests_on_state_id_and_merge_status"})
main:    -> 0.0020s
main: -- execute("RESET statement_timeout")
main:    -> 0.0001s
main: == 20230413012807 RemoveIndexSyncIndexOnMergeRequestsOnStateIdAndMergeStatus: migrated (0.0639s)

main: == [advisory_lock_connection] object_id: 312220, pg_backend_pid: 96397
ci: == [advisory_lock_connection] object_id: 312420, pg_backend_pid: 96400
ci: == 20230413012807 RemoveIndexSyncIndexOnMergeRequestsOnStateIdAndMergeStatus: migrating
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- view_exists?(:postgres_partitions)
ci:    -> 0.0004s
ci: -- indexes(:merge_requests)
ci:    -> 0.0099s
ci: -- execute("SET statement_timeout TO 0")
ci:    -> 0.0002s
ci: -- remove_index(:merge_requests, {:algorithm=>:concurrently, :name=>"idx_merge_requests_on_state_id_and_merge_status"})
ci:    -> 0.0024s
ci: -- execute("RESET statement_timeout")
ci:    -> 0.0001s
ci: == 20230413012807 RemoveIndexSyncIndexOnMergeRequestsOnStateIdAndMergeStatus: migrated (0.0222s)

ci: == [advisory_lock_connection] object_id: 312420, pg_backend_pid: 96400

11:38:35 dylangriffith@Dylan-GitLab-MBP:~/workspace/gitlab-development-kit/gitlab$ bin/rake db:rollback:main
main: == [advisory_lock_connection] object_id: 312300, pg_backend_pid: 97111
main: == 20230413012807 RemoveIndexSyncIndexOnMergeRequestsOnStateIdAndMergeStatus: reverting
main: -- transaction_open?()
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0592s
main: -- index_exists?(:merge_requests, [:state_id, :merge_status], {:where=>"((state_id = 1) AND ((merge_status)::text = 'can_be_merged'::text))", :name=>"idx_merge_requests_on_state_id_and_merge_status", :algorithm=>:concurrently})
main:    -> 0.0093s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0002s
main: -- add_index(:merge_requests, [:state_id, :merge_status], {:where=>"((state_id = 1) AND ((merge_status)::text = 'can_be_merged'::text))", :name=>"idx_merge_requests_on_state_id_and_merge_status", :algorithm=>:concurrently})
main:    -> 0.0020s
main: -- execute("RESET statement_timeout")
main:    -> 0.0001s
main: == 20230413012807 RemoveIndexSyncIndexOnMergeRequestsOnStateIdAndMergeStatus: reverted (0.0815s)

main: == [advisory_lock_connection] object_id: 312300, pg_backend_pid: 97111
11:39:01 dylangriffith@Dylan-GitLab-MBP:~/workspace/gitlab-development-kit/gitlab$ bin/rake db:rollback:ci
ci: == [advisory_lock_connection] object_id: 277280, pg_backend_pid: 97572
ci: == 20230413012807 RemoveIndexSyncIndexOnMergeRequestsOnStateIdAndMergeStatus: reverting
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- view_exists?(:postgres_partitions)
ci:    -> 0.0597s
ci: -- index_exists?(:merge_requests, [:state_id, :merge_status], {:where=>"((state_id = 1) AND ((merge_status)::text = 'can_be_merged'::text))", :name=>"idx_merge_requests_on_state_id_and_merge_status", :algorithm=>:concurrently})
ci:    -> 0.0113s
ci: -- execute("SET statement_timeout TO 0")
ci:    -> 0.0002s
ci: -- add_index(:merge_requests, [:state_id, :merge_status], {:where=>"((state_id = 1) AND ((merge_status)::text = 'can_be_merged'::text))", :name=>"idx_merge_requests_on_state_id_and_merge_status", :algorithm=>:concurrently})
ci:    -> 0.0031s
ci: -- execute("RESET statement_timeout")
ci:    -> 0.0003s
ci: == 20230413012807 RemoveIndexSyncIndexOnMergeRequestsOnStateIdAndMergeStatus: reverted (0.1121s)

ci: == [advisory_lock_connection] object_id: 277280, pg_backend_pid: 97572

How to set up and validate locally

Numbered steps to set up and validate the change are strongly suggested.

MR acceptance checklist

This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.

Closes #402491 (closed)

Merge request reports