Prepare async index creation for issues.correct_work_item_type_id
What does this MR do and why?
These indexes need to be created over the weekend. They will be created sync with !170005
We need these indexes as part of the larger effort &15272. They will just be added temporarily while the code relies on the issues.correct_work_item_type_id column. Once we go back to issues.work_item_type_id (after the ID values are fixed), we can drop these indexes.
Migration output
UP
main: == [advisory_lock_connection] object_id: 130080, pg_backend_pid: 55463
main: == 20241021221146 PrepareAsyncIndexOnIssuesByProjectCorrectTypeWhereClosed: migrating
main: -- index_exists?(:issues, [:project_id, :correct_work_item_type_id, :closed_at], {:where=>"state_id = 2", :name=>"tmp_idx_issues_on_project_correct_type_closed_at_where_closed", :algorithm=>:concurrently})
main: -> 0.0082s
main: -- add_index_options(:issues, [:project_id, :correct_work_item_type_id, :closed_at], {:where=>"state_id = 2", :name=>"tmp_idx_issues_on_project_correct_type_closed_at_where_closed", :algorithm=>:concurrently})
main: -> 0.0001s
main: == 20241021221146 PrepareAsyncIndexOnIssuesByProjectCorrectTypeWhereClosed: migrated (0.0399s)
main: == [advisory_lock_connection] object_id: 130080, pg_backend_pid: 55463
ci: == [advisory_lock_connection] object_id: 130520, pg_backend_pid: 55467
ci: == 20241021221146 PrepareAsyncIndexOnIssuesByProjectCorrectTypeWhereClosed: migrating
ci: -- index_exists?(:issues, [:project_id, :correct_work_item_type_id, :closed_at], {:where=>"state_id = 2", :name=>"tmp_idx_issues_on_project_correct_type_closed_at_where_closed", :algorithm=>:concurrently})
ci: -> 0.0093s
ci: -- add_index_options(:issues, [:project_id, :correct_work_item_type_id, :closed_at], {:where=>"state_id = 2", :name=>"tmp_idx_issues_on_project_correct_type_closed_at_where_closed", :algorithm=>:concurrently})
ci: -> 0.0001s
ci: == 20241021221146 PrepareAsyncIndexOnIssuesByProjectCorrectTypeWhereClosed: migrated (0.0251s)
ci: == [advisory_lock_connection] object_id: 130520, pg_backend_pid: 55467
main: == [advisory_lock_connection] object_id: 130820, pg_backend_pid: 55470
main: == 20241021221626 PrepareAsyncIndexOnIssuesProjectHealthIdDescStateCorrectType: migrating
main: -- index_exists?(:issues, [:project_id, :health_status, :id, :state_id, :correct_work_item_type_id], {:order=>{:health_status=>"DESC NULLS LAST", :id=>:desc}, :name=>"tmp_idx_issues_on_project_health_id_desc_state_correct_type", :algorithm=>:concurrently})
main: -> 0.0086s
main: -- add_index_options(:issues, [:project_id, :health_status, :id, :state_id, :correct_work_item_type_id], {:order=>{:health_status=>"DESC NULLS LAST", :id=>:desc}, :name=>"tmp_idx_issues_on_project_health_id_desc_state_correct_type", :algorithm=>:concurrently})
main: -> 0.0001s
main: == 20241021221626 PrepareAsyncIndexOnIssuesProjectHealthIdDescStateCorrectType: migrated (0.0189s)
main: == [advisory_lock_connection] object_id: 130820, pg_backend_pid: 55470
ci: == [advisory_lock_connection] object_id: 175540, pg_backend_pid: 55472
ci: == 20241021221626 PrepareAsyncIndexOnIssuesProjectHealthIdDescStateCorrectType: migrating
ci: -- index_exists?(:issues, [:project_id, :health_status, :id, :state_id, :correct_work_item_type_id], {:order=>{:health_status=>"DESC NULLS LAST", :id=>:desc}, :name=>"tmp_idx_issues_on_project_health_id_desc_state_correct_type", :algorithm=>:concurrently})
ci: -> 0.0102s
ci: -- add_index_options(:issues, [:project_id, :health_status, :id, :state_id, :correct_work_item_type_id], {:order=>{:health_status=>"DESC NULLS LAST", :id=>:desc}, :name=>"tmp_idx_issues_on_project_health_id_desc_state_correct_type", :algorithm=>:concurrently})
ci: -> 0.0002s
ci: == 20241021221626 PrepareAsyncIndexOnIssuesProjectHealthIdDescStateCorrectType: migrated (0.0260s)
ci: == [advisory_lock_connection] object_id: 175540, pg_backend_pid: 55472
main: == [advisory_lock_connection] object_id: 196700, pg_backend_pid: 55475
main: == 20241021221755 PrepareAsyncIndexOnIssuesProjectHealthIdAscStateCorrectType: migrating
main: -- index_exists?(:issues, [:project_id, :health_status, :id, :state_id, :correct_work_item_type_id], {:order=>{:health_status=>"ASC NULLS LAST", :id=>:desc}, :name=>"tmp_idx_issues_on_project_health_id_asc_state_correct_type", :algorithm=>:concurrently})
main: -> 0.0093s
main: -- add_index_options(:issues, [:project_id, :health_status, :id, :state_id, :correct_work_item_type_id], {:order=>{:health_status=>"ASC NULLS LAST", :id=>:desc}, :name=>"tmp_idx_issues_on_project_health_id_asc_state_correct_type", :algorithm=>:concurrently})
main: -> 0.0001s
main: == 20241021221755 PrepareAsyncIndexOnIssuesProjectHealthIdAscStateCorrectType: migrated (0.0206s)
main: == [advisory_lock_connection] object_id: 196700, pg_backend_pid: 55475
ci: == [advisory_lock_connection] object_id: 227460, pg_backend_pid: 55477
ci: == 20241021221755 PrepareAsyncIndexOnIssuesProjectHealthIdAscStateCorrectType: migrating
ci: -- index_exists?(:issues, [:project_id, :health_status, :id, :state_id, :correct_work_item_type_id], {:order=>{:health_status=>"ASC NULLS LAST", :id=>:desc}, :name=>"tmp_idx_issues_on_project_health_id_asc_state_correct_type", :algorithm=>:concurrently})
ci: -> 0.0104s
ci: -- add_index_options(:issues, [:project_id, :health_status, :id, :state_id, :correct_work_item_type_id], {:order=>{:health_status=>"ASC NULLS LAST", :id=>:desc}, :name=>"tmp_idx_issues_on_project_health_id_asc_state_correct_type", :algorithm=>:concurrently})
ci: -> 0.0001s
ci: == 20241021221755 PrepareAsyncIndexOnIssuesProjectHealthIdAscStateCorrectType: migrated (0.0263s)
ci: == [advisory_lock_connection] object_id: 227460, pg_backend_pid: 55477
main: == [advisory_lock_connection] object_id: 259740, pg_backend_pid: 55480
main: == 20241021221938 PrepareAsyncIndexOnIssuesCorrectTypeProjectCreatedAtState: migrating
main: -- index_exists?(:issues, [:correct_work_item_type_id, :project_id, :created_at, :state_id], {:name=>"tmp_idx_issues_on_correct_type_project_created_at_state", :algorithm=>:concurrently})
main: -> 0.0084s
main: -- add_index_options(:issues, [:correct_work_item_type_id, :project_id, :created_at, :state_id], {:name=>"tmp_idx_issues_on_correct_type_project_created_at_state", :algorithm=>:concurrently})
main: -> 0.0001s
main: == 20241021221938 PrepareAsyncIndexOnIssuesCorrectTypeProjectCreatedAtState: migrated (0.0187s)
main: == [advisory_lock_connection] object_id: 259740, pg_backend_pid: 55480
ci: == [advisory_lock_connection] object_id: 288940, pg_backend_pid: 55482
ci: == 20241021221938 PrepareAsyncIndexOnIssuesCorrectTypeProjectCreatedAtState: migrating
ci: -- index_exists?(:issues, [:correct_work_item_type_id, :project_id, :created_at, :state_id], {:name=>"tmp_idx_issues_on_correct_type_project_created_at_state", :algorithm=>:concurrently})
ci: -> 0.0100s
ci: -- add_index_options(:issues, [:correct_work_item_type_id, :project_id, :created_at, :state_id], {:name=>"tmp_idx_issues_on_correct_type_project_created_at_state", :algorithm=>:concurrently})
ci: -> 0.0001s
ci: == 20241021221938 PrepareAsyncIndexOnIssuesCorrectTypeProjectCreatedAtState: migrated (0.0255s)
ci: == [advisory_lock_connection] object_id: 288940, pg_backend_pid: 55482
DOWN
main: == [advisory_lock_connection] object_id: 129660, pg_backend_pid: 61967
main: == 20241021221938 PrepareAsyncIndexOnIssuesCorrectTypeProjectCreatedAtState: reverting
main: == 20241021221938 PrepareAsyncIndexOnIssuesCorrectTypeProjectCreatedAtState: reverted (0.0448s)
main: == 20241021221755 PrepareAsyncIndexOnIssuesProjectHealthIdAscStateCorrectType: reverting
main: == 20241021221755 PrepareAsyncIndexOnIssuesProjectHealthIdAscStateCorrectType: reverted (0.0093s)
main: == 20241021221626 PrepareAsyncIndexOnIssuesProjectHealthIdDescStateCorrectType: reverting
main: == 20241021221626 PrepareAsyncIndexOnIssuesProjectHealthIdDescStateCorrectType: reverted (0.0100s)
main: == 20241021221146 PrepareAsyncIndexOnIssuesByProjectCorrectTypeWhereClosed: reverting
main: == 20241021221146 PrepareAsyncIndexOnIssuesByProjectCorrectTypeWhereClosed: reverted (0.0078s)
main: == [advisory_lock_connection] object_id: 129660, pg_backend_pid: 61967
ci: == [advisory_lock_connection] object_id: 142360, pg_backend_pid: 62219
ci: == 20241021221938 PrepareAsyncIndexOnIssuesCorrectTypeProjectCreatedAtState: reverting
ci: == 20241021221938 PrepareAsyncIndexOnIssuesCorrectTypeProjectCreatedAtState: reverted (0.0131s)
ci: == 20241021221755 PrepareAsyncIndexOnIssuesProjectHealthIdAscStateCorrectType: reverting
ci: == 20241021221755 PrepareAsyncIndexOnIssuesProjectHealthIdAscStateCorrectType: reverted (0.0106s)
ci: == 20241021221626 PrepareAsyncIndexOnIssuesProjectHealthIdDescStateCorrectType: reverting
ci: == 20241021221626 PrepareAsyncIndexOnIssuesProjectHealthIdDescStateCorrectType: reverted (0.0107s)
ci: == 20241021221146 PrepareAsyncIndexOnIssuesByProjectCorrectTypeWhereClosed: reverting
ci: == 20241021221146 PrepareAsyncIndexOnIssuesByProjectCorrectTypeWhereClosed: reverted (0.0094s)
ci: == [advisory_lock_connection] object_id: 142360, pg_backend_pid: 62219
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 #498271
Edited by Mario Celi