Draft: [DO NOT MERGE] Backfill epic_issues into work item parent links (!147509) · Merge requests · GitLab.org / GitLab

Mario Celi requested to merge 451062-backfill-epic-issues into master Mar 21, 2024

What does this MR do and why?

While we migrate epics to work items, we also need to backfill epic relationships with their parents.

That means creating a record in the work_item_parent_links for every record in the epic_issues table. This migration needs to be scheduled after the initial backfill is done which will create a record in the issues table for every record in the epics table.

DB review

Console output

bin/rails db:migrate
main: == [advisory_lock_connection] object_id: 120160, pg_backend_pid: 50842
main: == 20240320193910 QueueBackfillEpicIssuesIntoWorkItemParentLinks: migrating ===
main: == 20240320193910 QueueBackfillEpicIssuesIntoWorkItemParentLinks: migrated (0.0395s)

main: == [advisory_lock_connection] object_id: 120160, pg_backend_pid: 50842
ci: == [advisory_lock_connection] object_id: 120600, pg_backend_pid: 50844
ci: == 20240320193910 QueueBackfillEpicIssuesIntoWorkItemParentLinks: 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_internal, :gitlab_shared].
ci: == 20240320193910 QueueBackfillEpicIssuesIntoWorkItemParentLinks: migrated (0.0054s)

ci: == [advisory_lock_connection] object_id: 120600, pg_backend_pid: 50844

DOWN

bin/rails db:rollback:main db:rollback:ci
main: == [advisory_lock_connection] object_id: 119700, pg_backend_pid: 49695
main: == 20240320193910 QueueBackfillEpicIssuesIntoWorkItemParentLinks: reverting ===
main: == 20240320193910 QueueBackfillEpicIssuesIntoWorkItemParentLinks: reverted (0.0393s)

main: == [advisory_lock_connection] object_id: 119700, pg_backend_pid: 49695
ci: == [advisory_lock_connection] object_id: 129500, pg_backend_pid: 49996
ci: == 20240320193910 QueueBackfillEpicIssuesIntoWorkItemParentLinks: reverting ===
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_internal, :gitlab_shared].
ci: == 20240320193910 QueueBackfillEpicIssuesIntoWorkItemParentLinks: reverted (0.0082s)

ci: == [advisory_lock_connection] object_id: 129500, pg_backend_pid: 49996

Time estimates

#451062 (comment 1832911727) (internal only)

DB plans

When no `group_id` is specified (ALL records, as scheduled in this MR)

https://console.postgres.ai/gitlab/gitlab-production-main/sessions/27212/commands/84634

SELECT 
  "epic_issues"."id" 
FROM 
  "epic_issues" 
WHERE 
  "epic_issues"."id" >= 1 
ORDER BY 
  "epic_issues"."id" ASC 
LIMIT 
  1

https://console.postgres.ai/gitlab/gitlab-production-main/sessions/27212/commands/84635

SELECT 
  "epic_issues"."id" 
FROM 
  "epic_issues" 
WHERE 
  "epic_issues"."id" >= 1 
  AND "epic_issues"."id" >= 2 
ORDER BY 
  "epic_issues"."id" ASC 
LIMIT 
  1 OFFSET 10000

https://console.postgres.ai/gitlab/gitlab-production-main/sessions/27212/commands/84636

SELECT 
  MIN("epic_issues"."id"), 
  MAX("epic_issues"."id") 
FROM 
  "epic_issues" 
WHERE 
  "epic_issues"."id" >= 1 
  AND "epic_issues"."id" >= 2 
  AND "epic_issues"."id" <= 13181 
LIMIT 
  1

https://console.postgres.ai/gitlab/gitlab-production-main/sessions/27212/commands/84637

SELECT 
  "epic_issues"."id" 
FROM 
  "epic_issues" 
WHERE 
  "epic_issues"."id" BETWEEN 2 
  AND 13181 
  AND "epic_issues"."id" >= 2 
ORDER BY 
  "epic_issues"."id" ASC 
LIMIT 
  1 OFFSET 100

https://console.postgres.ai/gitlab/gitlab-production-main/sessions/27212/commands/84638

SELECT 
  "epic_issues"."id", 
  "epic_issues"."epic_id", 
  "epic_issues"."issue_id", 
  "epic_issues"."relative_position", 
  epics.issue_id AS parent_issue_id 
FROM 
  "epic_issues" 
  INNER JOIN epics ON epics.id = epic_issues.epic_id 
WHERE 
  "epic_issues"."id" BETWEEN 2 
  AND 233 
  AND "epic_issues"."id" >= 2 FOR 
UPDATE

https://console.postgres.ai/gitlab/gitlab-production-main/sessions/27212/commands/84639

INSERT INTO "work_item_parent_links" (
  "work_item_parent_id", "work_item_id", 
  "relative_position", "created_at", 
  "updated_at"
) 
VALUES 
  (
    142568200, 142790846,-513, CURRENT_TIMESTAMP, 
    CURRENT_TIMESTAMP
  ), 
  (
    142753053, 141734976,-513, CURRENT_TIMESTAMP, 
    CURRENT_TIMESTAMP
  ), 
  (
    142753053, 142749798, 0, CURRENT_TIMESTAMP, 
    CURRENT_TIMESTAMP
  ), 
  (
    142754828, 74283466,-1154, CURRENT_TIMESTAMP, 
    CURRENT_TIMESTAMP
  ), 
  (
    142756459, 129132570,-5130, CURRENT_TIMESTAMP, 
    CURRENT_TIMESTAMP
  ), 
  (
    142756459, 140658114,-4617, CURRENT_TIMESTAMP, 
    CURRENT_TIMESTAMP
  ), 
  (
    142756459, 142756869,-6156, CURRENT_TIMESTAMP, 
    CURRENT_TIMESTAMP
  ), 
  (
    142756459, 142756915,-3847, CURRENT_TIMESTAMP, 
    CURRENT_TIMESTAMP
  ), 
  (
    142756459, 142756940,-513, CURRENT_TIMESTAMP, 
    CURRENT_TIMESTAMP
  ), 
  (
    142756459, 142957916,-1539, CURRENT_TIMESTAMP, 
    CURRENT_TIMESTAMP
  ), 
  (
    142756459, 143743513,-5899, CURRENT_TIMESTAMP, 
    CURRENT_TIMESTAMP
  ), 
  (
    142756459, 143744237,-3078, CURRENT_TIMESTAMP, 
    CURRENT_TIMESTAMP
  ), 
  (
    142756459, 143748435,-5643, CURRENT_TIMESTAMP, 
    CURRENT_TIMESTAMP
  ), 
  (
    142764587, 142240001, 0, CURRENT_TIMESTAMP, 
    CURRENT_TIMESTAMP
  ), 
  (
    142766824, 133238072,-513, CURRENT_TIMESTAMP, 
    CURRENT_TIMESTAMP
  ), 
  (
    142766824, 142766882, 0, CURRENT_TIMESTAMP, 
    CURRENT_TIMESTAMP
  ), 
  (
    142771187, 91006281,-7695, CURRENT_TIMESTAMP, 
    CURRENT_TIMESTAMP
  ), 
  (
    142771187, 91305679,-7182, CURRENT_TIMESTAMP, 
    CURRENT_TIMESTAMP
  ), 
  (
    142771187, 122267621,-1026, CURRENT_TIMESTAMP, 
    CURRENT_TIMESTAMP
  ), 
  (
    142771187, 132652888,-4104, CURRENT_TIMESTAMP, 
    CURRENT_TIMESTAMP
  ), 
  (
    142771187, 137381265,-1539, CURRENT_TIMESTAMP, 
    CURRENT_TIMESTAMP
  ), 
  (
    142771187, 137427283,-4617, CURRENT_TIMESTAMP, 
    CURRENT_TIMESTAMP
  ), 
  (
    142771187, 139348899,-5643, CURRENT_TIMESTAMP, 
    CURRENT_TIMESTAMP
  ), 
  (
    142771187, 139349136,-513, CURRENT_TIMESTAMP, 
    CURRENT_TIMESTAMP
  ), 
  (
    142771187, 139349141,-2052, CURRENT_TIMESTAMP, 
    CURRENT_TIMESTAMP
  ), 
  (
    142771187, 139349143,-2565, CURRENT_TIMESTAMP, 
    CURRENT_TIMESTAMP
  ), 
  (
    142771187, 139606036,-5130, CURRENT_TIMESTAMP, 
    CURRENT_TIMESTAMP
  ), 
  (
    142771187, 140348499,-3591, CURRENT_TIMESTAMP, 
    CURRENT_TIMESTAMP
  ), 
  (
    142771187, 141679899,-6669, CURRENT_TIMESTAMP, 
    CURRENT_TIMESTAMP
  ), 
  (
    142771187, 141905467,-3078, CURRENT_TIMESTAMP, 
    CURRENT_TIMESTAMP
  ), 
  (
    142771187, 142691373, 0, CURRENT_TIMESTAMP, 
    CURRENT_TIMESTAMP
  ), 
  (
    142771204, 101680876,-4617, CURRENT_TIMESTAMP, 
    CURRENT_TIMESTAMP
  ), 
  (
    142771204, 113164029,-4104, CURRENT_TIMESTAMP, 
    CURRENT_TIMESTAMP
  ), 
  (
    142771204, 113275970,-3591, CURRENT_TIMESTAMP, 
    CURRENT_TIMESTAMP
  ), 
  (
    142771204, 113813310,-3078, CURRENT_TIMESTAMP, 
    CURRENT_TIMESTAMP
  ), 
  (
    142771204, 114322035,-513, CURRENT_TIMESTAMP, 
    CURRENT_TIMESTAMP
  ), 
  (
    142771204, 119522876,-2565, CURRENT_TIMESTAMP, 
    CURRENT_TIMESTAMP
  ), 
  (
    142771204, 120170839,-2052, CURRENT_TIMESTAMP, 
    CURRENT_TIMESTAMP
  ), 
  (
    142771204, 120548887,-1539, CURRENT_TIMESTAMP, 
    CURRENT_TIMESTAMP
  ), 
  (
    142771204, 132348034,-1026, CURRENT_TIMESTAMP, 
    CURRENT_TIMESTAMP
  ), 
  (
    142771204, 138536341, 0, CURRENT_TIMESTAMP, 
    CURRENT_TIMESTAMP
  ), 
  (
    142784908, 143079148, 0, CURRENT_TIMESTAMP, 
    CURRENT_TIMESTAMP
  ), 
  (
    142784908, 143079162,-513, CURRENT_TIMESTAMP, 
    CURRENT_TIMESTAMP
  ), 
  (
    142784908, 143079181,-1026, CURRENT_TIMESTAMP, 
    CURRENT_TIMESTAMP
  ), 
  (
    142784908, 143079219,-769, CURRENT_TIMESTAMP, 
    CURRENT_TIMESTAMP
  ), 
  (
    142784908, 143079246,-2052, CURRENT_TIMESTAMP, 
    CURRENT_TIMESTAMP
  ), 
  (
    142784908, 143079265,-2565, CURRENT_TIMESTAMP, 
    CURRENT_TIMESTAMP
  ), 
  (
    142784908, 143079284,-3078, CURRENT_TIMESTAMP, 
    CURRENT_TIMESTAMP
  ), 
  (
    142784917, 143214025, 0, CURRENT_TIMESTAMP, 
    CURRENT_TIMESTAMP
  ), 
  (
    142784917, 143214054,-513, CURRENT_TIMESTAMP, 
    CURRENT_TIMESTAMP
  ), 
  (
    142784917, 143214079,-1026, CURRENT_TIMESTAMP, 
    CURRENT_TIMESTAMP
  ), 
  (
    142784917, 143214113,-1539, CURRENT_TIMESTAMP, 
    CURRENT_TIMESTAMP
  ), 
  (
    142784930, 143214531, 0, CURRENT_TIMESTAMP, 
    CURRENT_TIMESTAMP
  ), 
  (
    142784930, 143214561,-513, CURRENT_TIMESTAMP, 
    CURRENT_TIMESTAMP
  ), 
  (
    142784930, 143214581,-1026, CURRENT_TIMESTAMP, 
    CURRENT_TIMESTAMP
  ), 
  (
    142784930, 143214601,-1539, CURRENT_TIMESTAMP, 
    CURRENT_TIMESTAMP
  ), 
  (
    142789323, 142799375, 0, CURRENT_TIMESTAMP, 
    CURRENT_TIMESTAMP
  ), 
  (
    142789323, 142810053, 513, CURRENT_TIMESTAMP, 
    CURRENT_TIMESTAMP
  ), 
  (
    142798836, 142798858,-1539, CURRENT_TIMESTAMP, 
    CURRENT_TIMESTAMP
  ), 
  (
    142798845, 142572032, 0, CURRENT_TIMESTAMP, 
    CURRENT_TIMESTAMP
  ), 
  (
    143124934, 134636246, 0, CURRENT_TIMESTAMP, 
    CURRENT_TIMESTAMP
  ), 
  (
    143124934, 137919825,-513, CURRENT_TIMESTAMP, 
    CURRENT_TIMESTAMP
  ), 
  (
    143124934, 138875158,-1026, CURRENT_TIMESTAMP, 
    CURRENT_TIMESTAMP
  ), 
  (
    143124934, 139294411,-1539, CURRENT_TIMESTAMP, 
    CURRENT_TIMESTAMP
  ), 
  (
    143124934, 139667646,-2052, CURRENT_TIMESTAMP, 
    CURRENT_TIMESTAMP
  ), 
  (
    143124934, 140455969,-2565, CURRENT_TIMESTAMP, 
    CURRENT_TIMESTAMP
  ), 
  (
    143124934, 142747316,-3078, CURRENT_TIMESTAMP, 
    CURRENT_TIMESTAMP
  ), 
  (
    143125130, 134863521, 0, CURRENT_TIMESTAMP, 
    CURRENT_TIMESTAMP
  ), 
  (
    143125942, 139920651,-513, CURRENT_TIMESTAMP, 
    CURRENT_TIMESTAMP
  ), 
  (
    143125942, 140015664, 0, CURRENT_TIMESTAMP, 
    CURRENT_TIMESTAMP
  ), 
  (
    143126085, 142212804,-3591, CURRENT_TIMESTAMP, 
    CURRENT_TIMESTAMP
  ), 
  (
    143126085, 143448486,-3078, CURRENT_TIMESTAMP, 
    CURRENT_TIMESTAMP
  ), 
  (
    143129275, 139540423, 0, CURRENT_TIMESTAMP, 
    CURRENT_TIMESTAMP
  ), 
  (
    143129275, 143129336,-513, CURRENT_TIMESTAMP, 
    CURRENT_TIMESTAMP
  ), 
  (
    143129275, 143129359,-1026, CURRENT_TIMESTAMP, 
    CURRENT_TIMESTAMP
  ), 
  (
    143129275, 143129365,-1539, CURRENT_TIMESTAMP, 
    CURRENT_TIMESTAMP
  ), 
  (
    143129275, 143476341,-2052, CURRENT_TIMESTAMP, 
    CURRENT_TIMESTAMP
  ), 
  (
    143130160, 140732875,-1539, CURRENT_TIMESTAMP, 
    CURRENT_TIMESTAMP
  ), 
  (
    143130160, 141064991,-1026, CURRENT_TIMESTAMP, 
    CURRENT_TIMESTAMP
  ), 
  (
    143130160, 142065397,-513, CURRENT_TIMESTAMP, 
    CURRENT_TIMESTAMP
  ), 
  (
    143130160, 142665696, 0, CURRENT_TIMESTAMP, 
    CURRENT_TIMESTAMP
  ), 
  (
    143130743, 140618914,-4104, CURRENT_TIMESTAMP, 
    CURRENT_TIMESTAMP
  ), 
  (
    143130743, 140968372,-3591, CURRENT_TIMESTAMP, 
    CURRENT_TIMESTAMP
  ), 
  (
    143130743, 140969073,-3078, CURRENT_TIMESTAMP, 
    CURRENT_TIMESTAMP
  ), 
  (
    143130743, 141077709,-2565, CURRENT_TIMESTAMP, 
    CURRENT_TIMESTAMP
  ), 
  (
    143130743, 141256534,-2052, CURRENT_TIMESTAMP, 
    CURRENT_TIMESTAMP
  ), 
  (
    143130743, 141601558,-1539, CURRENT_TIMESTAMP, 
    CURRENT_TIMESTAMP
  ), 
  (
    143130743, 142178229,-1026, CURRENT_TIMESTAMP, 
    CURRENT_TIMESTAMP
  ), 
  (
    143130743, 142227267,-513, CURRENT_TIMESTAMP, 
    CURRENT_TIMESTAMP
  ), 
  (
    143130743, 142386974, 0, CURRENT_TIMESTAMP, 
    CURRENT_TIMESTAMP
  ), 
  (
    143130888, 138309516,-2052, CURRENT_TIMESTAMP, 
    CURRENT_TIMESTAMP
  ), 
  (
    143130888, 140461924,-1539, CURRENT_TIMESTAMP, 
    CURRENT_TIMESTAMP
  ), 
  (
    143130888, 141306924,-1026, CURRENT_TIMESTAMP, 
    CURRENT_TIMESTAMP
  ), 
  (
    143130888, 142361141, 0, CURRENT_TIMESTAMP, 
    CURRENT_TIMESTAMP
  ), 
  (
    143130888, 142494858,-513, CURRENT_TIMESTAMP, 
    CURRENT_TIMESTAMP
  ), 
  (
    143130939, 136938986,-513, CURRENT_TIMESTAMP, 
    CURRENT_TIMESTAMP
  ), 
  (
    143130939, 136979070,-1026, CURRENT_TIMESTAMP, 
    CURRENT_TIMESTAMP
  ), 
  (
    143130939, 140564326, 0, CURRENT_TIMESTAMP, 
    CURRENT_TIMESTAMP
  ), 
  (
    143132970, 143133023, 0, CURRENT_TIMESTAMP, 
    CURRENT_TIMESTAMP
  ), 
  (
    143132970, 143133027,-513, CURRENT_TIMESTAMP, 
    CURRENT_TIMESTAMP
  ) ON CONFLICT ("work_item_id") DO 
UPDATE 
SET 
  updated_at =(
    CASE WHEN (
      "work_item_parent_links"."work_item_parent_id" IS NOT DISTINCT 
      FROM 
        excluded."work_item_parent_id" 
        AND "work_item_parent_links"."relative_position" IS NOT DISTINCT 
      FROM 
        excluded."relative_position"
    ) THEN "work_item_parent_links".updated_at ELSE CURRENT_TIMESTAMP END
  ), 
  "work_item_parent_id" = excluded."work_item_parent_id", 
  "relative_position" = excluded."relative_position" RETURNING "id"

When `group_id` is specified (records for single group, not scheduled in this MR)

https://console.postgres.ai/gitlab/gitlab-production-main/sessions/27212/commands/84604

SELECT 
  "epics"."iid" 
FROM 
  "epics" 
WHERE 
  "epics"."iid" >= 1 
  AND "epics"."group_id" = 10510295
ORDER BY 
  "epics"."iid" ASC 
LIMIT 
  1

https://console.postgres.ai/gitlab/gitlab-production-main/sessions/27212/commands/84606

SELECT 
  "epics"."iid" 
FROM 
  "epics" 
WHERE 
  "epics"."iid" >= 1 
  AND "epics"."group_id" = 10510295 
  AND "epics"."iid" >= 1 
ORDER BY 
  "epics"."iid" ASC 
LIMIT 
  1 OFFSET 100

https://console.postgres.ai/gitlab/gitlab-production-main/sessions/27212/commands/84607

SELECT 
  "epics"."iid" 
FROM 
  "epics" 
WHERE 
  "epics"."iid" BETWEEN 1 
  AND 131 
  AND "epics"."group_id" = 10510295 
ORDER BY 
  "epics"."iid" ASC 
LIMIT 
  1

https://console.postgres.ai/gitlab/gitlab-production-main/sessions/27212/commands/84609

SELECT 
  "epics".* 
FROM 
  "epics" 
WHERE 
  "epics"."iid" BETWEEN 1 
  AND 131 
  AND "epics"."group_id" = 10510295 
  AND "epics"."iid" >= 1;

https://console.postgres.ai/gitlab/gitlab-production-main/sessions/27212/commands/84611

SELECT 
  "epic_issues"."id" 
FROM 
  "epic_issues" 
WHERE 
  "epic_issues"."epic_id" IN (
    SELECT 
      "epics".id 
    FROM 
      "epics" 
    WHERE 
      "epics"."iid" BETWEEN 1 
      AND 131 
      AND "epics"."group_id" = 10510295 
      AND "epics"."iid" >= 1
  ) 
ORDER BY 
  "epic_issues"."id" ASC 
LIMIT 
  1

https://postgres.ai/console/gitlab/gitlab-production-main/sessions/27261/commands/84769

SELECT 
  "epic_issues"."id" 
FROM 
  "epic_issues" 
WHERE 
  "epic_issues"."epic_id" IN (
    SELECT 
      "epics".id 
    FROM 
      "epics" 
    WHERE 
      "epics"."iid" BETWEEN 1 
      AND 131 
      AND "epics"."group_id" = 10510295 
      AND "epics"."iid" >= 1
  ) 
  AND "epic_issues"."id" >= 579982 
ORDER BY 
  "epic_issues"."id" ASC 
LIMIT 
  1 OFFSET 100

https://console.postgres.ai/gitlab/gitlab-production-main/sessions/27212/commands/84613

SELECT 
  "epic_issues"."id", 
  "epic_issues"."epic_id", 
  "epic_issues"."issue_id", 
  "epic_issues"."relative_position", 
  epics.issue_id AS parent_issue_id 
FROM 
  "epic_issues" 
  INNER JOIN epics ON epics.id = epic_issues.epic_id 
WHERE 
  "epic_issues"."epic_id" IN (
    SELECT 
      "epics"."id" 
    FROM 
      "epics" 
    WHERE 
      "epics"."iid" BETWEEN 1 
      AND 131 
      AND "epics"."group_id" = 10510295 
      AND "epics"."iid" >= 1
  ) 
  AND "epic_issues"."id" >= 579982 FOR 
UPDATE

MR acceptance checklist

Please evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.

Related to #451062

Edited Mar 27, 2024 by Mario Celi

Draft: [DO NOT MERGE] Backfill epic_issues into work item parent links