Backfill Migrations for ML::Candidate.internal_id

What does this MR do and why?

Follow up from !115401 (merged), this backfills the added columns. The first commit on this branch has the changes of the previous MR, and will be removed once that is merged.

Database

20230321163947 BackfillMlCandidatesProjectId

UP

main: == 20230321163947 BackfillMlCandidatesProjectId: migrating ====================
main: -- execute("      UPDATE ml_candidates\n      SET project_id = temp.project_id\n      FROM (\n          SELECT ml_candidates.id AS id, ml_experiments.project_id AS project_id\n          FROM ml_candidates INNER JOIN ml_experiments ON ml_candidates.experiment_id = ml_experiments.id\n      ) AS temp\n      WHERE ml_candidates.id = temp.id\n")
main:    -> 0.0057s
main: == 20230321163947 BackfillMlCandidatesProjectId: migrated (0.0188s) ===========

DOWN

main: == 20230321163947 BackfillMlCandidatesProjectId: reverting ====================
main: == 20230321163947 BackfillMlCandidatesProjectId: reverted (0.0033s) ===========

20230321170823 BackfillMlCandidatesInternalId

UP

main: == 20230321170823 BackfillMlCandidatesInternalId: migrating ===================
main: -- execute("      UPDATE ml_candidates\n      SET internal_id = temp.internal_id_num\n      FROM (\n        SELECT id, ROW_NUMBER() OVER (PARTITION BY project_id ORDER BY internal_id, id ASC) AS internal_id_num\n        FROM ml_candidates\n      ) AS temp\n      WHERE ml_candidates.id = temp.id\n")
main:    -> 0.0019s
main: == 20230321170823 BackfillMlCandidatesInternalId: migrated (0.0099s) ==========

DOWN

main: == 20230321170823 BackfillMlCandidatesInternalId: reverting ===================
main: == 20230321170823 BackfillMlCandidatesInternalId: reverted (0.0025s) ==========

How to set up and validate locally

The below process replicates the tests:

1. On master, create an experiment and a few candidates:

   exp = Ml::Experiment.create!(name: "Gitlab Experiment", user_id: user_id, project_id: project_id)
   c1 = exp.candidates.create!(user_id: user_id, start_time: 0) }
   c2 = exp.candidates.create!(user_id: user_id, start_time: 1) }

2. Perform the migrations on !115401 (merged)

3. Verify that values are indeed nil:

   [c1.internal_id, c2.internal_id, c1.project_id, c2.project_id].compact.empty? # should be true

4. Perform the migrations on this MR

5. verify that values were filled correctly

   [c1.internal_id, c2.internal_id, c1.project_id, c2.project_id].compact.empty? # should be false

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