Skip to content

Reschedule task system note renaming migration

Mario Celi requested to merge 369930-reschedule-background-migration into master

What does this MR do and why?

In !95631 (merged) we introduced a background migration that renames every system note related to a system_note_metadata of action = 'task'. We are changing the wording from task into checklist item as described in #368028 (closed)

Failure Investigation

Many batches failed in staging while none failed in production (but we did have multiple attempts for some batches in production). From what I can tell about the failing batches in staging, the most probable reason for failure is that staging has test data with very long notes. We also have long notes in production, but in staging I believe they appear more often so we might see multiple long ones in a single update batch. The text we are trying to replace comes from user input, so we cannot tell how long some notes.note fields might be.

In staging reducing the sub_batch_size to 25 still saw some failures in the failing batches, so I reduced the sub batch size to 10 to be sure they won't fail and effectively it worked, no more failures. I also reduced the batch size from 10k to 1k to reduce the number of updates in a single batch, but still left the max_batch_size set to 20k in the event the optimizer thinks the updates are running fast enough.

DB Review

UP

bin/rails db:migrate
main: == 20220929215504 CleanUpBackgroundMigrationRenameTaskSysteNotes: migrating ===
main: == 20220929215504 CleanUpBackgroundMigrationRenameTaskSysteNotes: migrated (0.0606s)

main: == 20220929215527 RescheduleTaskSystemNoteRenaming: migrating =================
main: == 20220929215527 RescheduleTaskSystemNoteRenaming: migrated (0.0553s) ========

ci: == 20220929215504 CleanUpBackgroundMigrationRenameTaskSysteNotes: 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: == 20220929215504 CleanUpBackgroundMigrationRenameTaskSysteNotes: migrated (0.0001s)

ci: == 20220929215527 RescheduleTaskSystemNoteRenaming: 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: == 20220929215527 RescheduleTaskSystemNoteRenaming: migrated (0.0001s) ========

DOWN

br db:rollback:main STEP=2
main: == 20220929215527 RescheduleTaskSystemNoteRenaming: reverting =================
main: == 20220929215527 RescheduleTaskSystemNoteRenaming: reverted (0.0257s) ========

main: == 20220929215504 CleanUpBackgroundMigrationRenameTaskSysteNotes: reverting ===
main: == 20220929215504 CleanUpBackgroundMigrationRenameTaskSysteNotes: reverted (0.0001s)

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/df65f144-a1b0-4450-ad76-d0a7e91ed372 
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 10
https://console.postgres.ai/shared/6ec191d3-2f51-4d3b-8425-ca330297877f 
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" < 641
  ) AS metadata_fields(note_id) 
WHERE 
  notes.id = note_id
https://console.postgres.ai/shared/453b2653-5c4d-4cfb-86b1-facfd1487a6a 
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" >= 641 
ORDER BY 
  "system_note_metadata"."id" ASC 
LIMIT 
  1 OFFSET 10
https://console.postgres.ai/shared/262c2c6a-b457-444b-b37a-fd0774795417 
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" >= 641 
      AND "system_note_metadata"."id" < 1278
  ) 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.

Related to #369930 (closed)

Edited by Mario Celi

Merge request reports