Add migration for new index on project id and lower(name) for tags table
What does this MR do and why?
This MR removes old index on project_id
and name
on incident_management_timeline_event_tags
table and
adds new index for project_id
and lower(name)
on the same table.
Also, added back the post_creation_worker
that was disabled in !103086 (merged) for truncating data to add the new column. But since we decided to just add the new index, I am enabling it back.
Migration
UP
main: == 20221110150942 AddProjectIdLowerNameIndexRemoveOldIndex: migrating =========
main: -- transaction_open?()
main: -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main: -> 0.0007s
main: -- indexes(:incident_management_timeline_event_tags)
main: -> 0.0017s
main: -- execute("SET statement_timeout TO 0")
main: -> 0.0001s
main: -- remove_index(:incident_management_timeline_event_tags, {:algorithm=>:concurrently, :name=>"index_im_timeline_event_tags_name_project_id"})
main: -> 0.0008s
main: -- execute("RESET statement_timeout")
main: -> 0.0001s
main: -- transaction_open?()
main: -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main: -> 0.0003s
main: -- index_exists?(:incident_management_timeline_event_tags, "project_id, LOWER(name)", {:unique=>true, :name=>"index_im_timeline_event_tags_lower_name_project_id", :algorithm=>:concurrently})
main: -> 0.0006s
main: -- add_index(:incident_management_timeline_event_tags, "project_id, LOWER(name)", {:unique=>true, :name=>"index_im_timeline_event_tags_lower_name_project_id", :algorithm=>:concurrently})
main: -> 0.0009s
main: == 20221110150942 AddProjectIdLowerNameIndexRemoveOldIndex: migrated (0.0144s)
ci: == 20221110150942 AddProjectIdLowerNameIndexRemoveOldIndex: migrating =========
ci: -- transaction_open?()
ci: -> 0.0000s
ci: -- view_exists?(:postgres_partitions)
ci: -> 0.0004s
ci: -- indexes(:incident_management_timeline_event_tags)
ci: -> 0.0016s
ci: -- execute("SET statement_timeout TO 0")
ci: -> 0.0001s
ci: -- remove_index(:incident_management_timeline_event_tags, {:algorithm=>:concurrently, :name=>"index_im_timeline_event_tags_name_project_id"})
ci: -> 0.0009s
ci: -- execute("RESET statement_timeout")
ci: -> 0.0001s
ci: -- transaction_open?()
ci: -> 0.0000s
ci: -- view_exists?(:postgres_partitions)
ci: -> 0.0003s
ci: -- index_exists?(:incident_management_timeline_event_tags, "project_id, LOWER(name)", {:unique=>true, :name=>"index_im_timeline_event_tags_lower_name_project_id", :algorithm=>:concurrently})
ci: -> 0.0007s
ci: -- add_index(:incident_management_timeline_event_tags, "project_id, LOWER(name)", {:unique=>true, :name=>"index_im_timeline_event_tags_lower_name_project_id", :algorithm=>:concurrently})
ci: -> 0.0013s
ci: == 20221110150942 AddProjectIdLowerNameIndexRemoveOldIndex: migrated (0.0157s)
DOWN
main: == 20221110150942 AddProjectIdLowerNameIndexRemoveOldIndex: reverting =========
main: -- transaction_open?()
main: -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main: -> 0.0050s
main: -- indexes(:incident_management_timeline_event_tags)
main: -> 0.0019s
main: -- execute("SET statement_timeout TO 0")
main: -> 0.0001s
main: -- remove_index(:incident_management_timeline_event_tags, {:algorithm=>:concurrently, :name=>"index_im_timeline_event_tags_lower_name_project_id"})
main: -> 0.0011s
main: -- execute("RESET statement_timeout")
main: -> 0.0001s
main: -- transaction_open?()
main: -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main: -> 0.0003s
main: -- index_exists?(:incident_management_timeline_event_tags, [:project_id, :name], {:name=>"index_im_timeline_event_tags_name_project_id", :algorithm=>:concurrently})
main: -> 0.0008s
main: -- add_index(:incident_management_timeline_event_tags, [:project_id, :name], {:name=>"index_im_timeline_event_tags_name_project_id", :algorithm=>:concurrently})
main: -> 0.0013s
main: == 20221110150942 AddProjectIdLowerNameIndexRemoveOldIndex: reverted (0.0213s)
How to set up and validate locally
- Run the migration and check the schema changes in structure.sql
MR acceptance checklist
This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.
-
I have evaluated the MR acceptance checklist for this MR.
Related to #373853 (closed)
Edited by Rajendra Kadam