Rename existing task system notes (!95631) · Merge requests · GitLab.org / GitLab

Mario Celi requested to merge 369930-fix-task-system-notes-use-checklist-item-alt into master Aug 17, 2022

What does this MR do and why?

We changed the name of task in Issues to checklist item. Most changes were done in the frontend, but we have the old name on system notes when me mark a checklist item as complete or incomplete. Another MR made sure new system notes use the new name and also that the returned value in our APIs is the new name by overriding the model method. This migration needs to go through all system notes related to tasks and change to the new name so we can remove the overridden value.

DB Review

Approximate Run Time of the Migration

4.01 days (more details in note #369930 (comment 1102403527))

This is a very rough and conservative estimate, it will probably take less that the calculated 1 min in each batch. Batched migration optimization might increase the batch size to a max of 20_000 that might reduce the runtime a lot

Temporary index creation

CREATE INDEX tmp_index_system_note_metadata_on_id_where_task ON system_note_metadata USING btree (id, action) WHERE ((action)::text = 'task'::text)

24.585 min

Migration Output

UP

bin/rails db:migrate
main: == 20220809214730 AddNoteMetadataTempIndexOnIdWhereTask: migrating ============
main: -- transaction_open?()
main:    -> 0.0000s
main: -- index_exists?(:system_note_metadata, [:id, :action], {:where=>"action = 'task'", :name=>"tmp_index_system_note_metadata_on_id_where_task", :algorithm=>:concurrently})
main:    -> 0.0062s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0003s
main: -- add_index(:system_note_metadata, [:id, :action], {:where=>"action = 'task'", :name=>"tmp_index_system_note_metadata_on_id_where_task", :algorithm=>:concurrently})
main:    -> 0.0043s
main: -- execute("RESET statement_timeout")
main:    -> 0.0004s
main: == 20220809214730 AddNoteMetadataTempIndexOnIdWhereTask: migrated (0.0187s) ===

ci: == 20220809214730 AddNoteMetadataTempIndexOnIdWhereTask: migrating ============
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- index_exists?(:system_note_metadata, [:id, :action], {:where=>"action = 'task'", :name=>"tmp_index_system_note_metadata_on_id_where_task", :algorithm=>:concurrently})
ci:    -> 0.0050s
ci: -- execute("SET statement_timeout TO 0")
ci:    -> 0.0004s
ci: -- add_index(:system_note_metadata, [:id, :action], {:where=>"action = 'task'", :name=>"tmp_index_system_note_metadata_on_id_where_task", :algorithm=>:concurrently})
ci:    -> 0.0058s
ci: -- execute("RESET statement_timeout")
ci:    -> 0.0004s
ci: == 20220809214730 AddNoteMetadataTempIndexOnIdWhereTask: migrated (0.0155s) ===

main: == 20220809223215 ChangeTaskSystemNoteWordingToChecklistItem: migrating =======
main: == 20220809223215 ChangeTaskSystemNoteWordingToChecklistItem: migrated (0.0757s)

ci: == 20220809223215 ChangeTaskSystemNoteWordingToChecklistItem: migrating =======
ci: -- The migration is skipped since it modifies the schemas: [:gitlab_main].
ci: -- This database can only apply migrations in one of the following schemas: [:gitlab_ci, :gitlab_shared, :gitlab_internal].
ci: == 20220809223215 ChangeTaskSystemNoteWordingToChecklistItem: migrated (0.0001s)

DOWN

bin/rails db:migrate:down:main VERSION=20220809223215
main: == 20220809223215 ChangeTaskSystemNoteWordingToChecklistItem: reverting =======
main: == 20220809223215 ChangeTaskSystemNoteWordingToChecklistItem: reverted (0.0492s)

bin/rails db:migrate:down:main VERSION=20220809214730
ci: == 20220809214730 AddNoteMetadataTempIndexOnIdWhereTask: reverting =================
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- indexes(:system_note_metadata)
ci:    -> 0.0103s
ci: -- execute("SET statement_timeout TO 0")
ci:    -> 0.0004s
ci: -- remove_index(:system_note_metadata, {:algorithm=>:concurrently, :name=>"tmp_index_system_note_metadata_on_id_where_task"})
ci:    -> 0.0036s
ci: -- execute("RESET statement_timeout")
ci:    -> 0.0004s
ci: == 20220809214730 AddNoteMetadataTempIndexOnIdWhereTask: reverted (0.0297s) ========

Query Plans

Queries in order for the first two sub batches

https://console.postgres.ai/shared/53a0d21e-23ce-4614-b870-f7190b9c8592

SELECT 
  "system_note_metadata"."id" 
FROM 
  "system_note_metadata" 
WHERE 
  ("id" >= 1) 
  AND "system_note_metadata"."action" = 'task' 
ORDER BY 
  "system_note_metadata"."id" ASC 
LIMIT 
  1

https://console.postgres.ai/shared/6a3ea060-f6ab-4962-8909-b344a32ad2a6

SELECT 
  "system_note_metadata"."id" 
FROM 
  "system_note_metadata" 
WHERE 
  ("id" >= 1) 
  AND "system_note_metadata"."action" = 'task' 
  AND "system_note_metadata"."id" >= 62 
ORDER BY 
  "system_note_metadata"."id" ASC 
LIMIT 
  1 OFFSET 1000

https://console.postgres.ai/shared/218d0699-e460-49ff-ab5e-0f2574900bc5

SELECT 
  MIN("id"), 
  MAX("id") 
FROM 
  "system_note_metadata" 
WHERE 
  ("id" >= 1) 
  AND "system_note_metadata"."action" = 'task' 
  AND "system_note_metadata"."id" >= 62 
  AND "system_note_metadata"."id" < 41043 
LIMIT 
  1

https://console.postgres.ai/shared/aeeafd2e-79d3-47c7-a21f-fadbc5950ccc

SELECT 
  "system_note_metadata"."id" 
FROM 
  "system_note_metadata" 
WHERE 
  "system_note_metadata"."id" BETWEEN 62 
  AND 41040 
  AND "system_note_metadata"."action" = 'task' 
ORDER BY 
  "system_note_metadata"."id" ASC 
LIMIT 
  1

https://console.postgres.ai/shared/464fe313-a625-415c-8e90-5e3e912650f6

SELECT 
  "system_note_metadata"."id" 
FROM 
  "system_note_metadata" 
WHERE 
  "system_note_metadata"."id" BETWEEN 62 
  AND 41040 
  AND "system_note_metadata"."action" = 'task'
  AND "system_note_metadata"."id" >= 116 
ORDER BY 
  "system_note_metadata"."id" ASC 
LIMIT 
  1 OFFSET 100

https://console.postgres.ai/shared/14b3ecfb-94b6-46fd-b945-537ba28c38a1

UPDATE 
  notes 
SET 
  note = REGEXP_REPLACE(
    notes.note, '\Amarked\sthe\stask', 
    'marked the checklist item'
  ) 
FROM 
  (
    SELECT 
      "system_note_metadata"."note_id" 
    FROM 
      "system_note_metadata" 
    WHERE 
      "system_note_metadata"."id" BETWEEN 62 
      AND 41040 
      AND "system_note_metadata"."action" = 'task' 
      AND "system_note_metadata"."id" >= 62 
      AND "system_note_metadata"."id" < 3535
  ) AS metadata_fields(note_id) 
WHERE 
  notes.id = note_id

https://console.postgres.ai/shared/f3404ec2-8d8e-4138-b8db-776c6bbfa6f6

SELECT 
  "system_note_metadata"."id" 
FROM 
  "system_note_metadata" 
WHERE 
  "system_note_metadata"."id" BETWEEN 62 
  AND 41040
  AND "system_note_metadata"."action" = 'task' 
  AND "system_note_metadata"."id" >= 3535 
ORDER BY 
  "system_note_metadata"."id" ASC 
LIMIT 
  1 OFFSET 100

https://console.postgres.ai/shared/cdf6c625-3b98-43cf-8f65-4f3ee62b29db

UPDATE 
  notes 
SET 
  note = REGEXP_REPLACE(
    notes.note, '\Amarked\sthe\stask', 
    'marked the checklist item'
  ) 
FROM 
  (
    SELECT 
      "system_note_metadata"."note_id" 
    FROM 
      "system_note_metadata" 
    WHERE 
      "system_note_metadata"."id" BETWEEN 62 
      AND 41040
      AND "system_note_metadata"."action" = 'task' 
      AND "system_note_metadata"."id" >= 3535 
      AND "system_note_metadata"."id" < 5156
  ) AS metadata_fields(note_id) 
WHERE 
  notes.id = note_id

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 #369930 (closed)

Edited Sep 15, 2022 by Mario Celi

Rename existing task system notes