Reschedule task system note renaming migration
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.
-
I have evaluated the MR acceptance checklist for this MR.
Related to #369930 (closed)