Fix orphaned promoted issues
What does this MR do?
Migration to populate promoted issues missing promoted_to_epic_id
field.
There are 411 promoted issues to epic with nil promoted_to_epic_id
, the problem here is that
the only way to find those issues is through a system note which contains the epic_iid
for example: promoted to epic &99
This merge request schedules a background migration using temporary indexes:
there are only 3 orphan issues in the context where these queries ran
Scheduling queries
SELECT "notes"."id"
FROM "notes"
WHERE "notes"."noteable_type" = $1
AND "notes"."system" = $2
AND ( notes.note LIKE 'promoted to epic%' )
ORDER BY "notes"."id" ASC
LIMIT $3
SELECT "notes"."id"
FROM "notes"
WHERE "notes"."noteable_type" = $1
AND "notes"."system" = $2
AND ( notes.note LIKE 'promoted to epic%' )
AND "notes"."id" >= 1
ORDER BY "notes"."id" ASC
LIMIT $3 offset $4
SELECT Min(id),
Max(id)
FROM "notes"
WHERE "notes"."noteable_type" = $1
AND "notes"."system" = $2
AND ( notes.note LIKE 'promoted to epic%' )
AND "notes"."id" >= 1
Update query on background migration
WITH promotion_notes AS (
SELECT noteable_id, note as promotion_note, projects.namespace_id as epic_group_id FROM notes
INNER JOIN projects ON notes.project_id = projects.id
WHERE notes.noteable_type = 'Issue' AND notes.system = true AND notes.note like 'promoted to epic%'
AND notes.id BETWEEN 1 AND 3
), promoted_epics AS (
SELECT epics.id as promoted_epic_id, promotion_notes.noteable_id as issue_id FROM epics
INNER JOIN promotion_notes on epics.group_id = promotion_notes.epic_group_id
WHERE concat('promoted to epic &', epics.iid) = promotion_notes.promotion_note
)
UPDATE issues
SET promoted_to_epic_id = promoted_epic_id
FROM promoted_epics
WHERE issues.id = promoted_epics.issue_id
AND issues.promoted_to_epic_id IS NULL
Related to #194177 (closed)
Edited by Felipe Cardozo