Skip to content
Snippets Groups Projects

Finalize conversion for FKs targeting p_ci_builds - 3

Merged Marius Bobin requested to merge 414396-finalize-fk-conversion-5 into master
All threads resolved!

What does this MR do and why?

Completes the FK swap for p_ci_builds_metadata and p_ci_runner_machine_builds on .com

Screenshots or screen recordings

Screenshots are required for UI changes, and strongly recommended for all other merge requests.

Before After

How to set up and validate locally

Numbered steps to set up and validate the change are strongly suggested.

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

Edited by Marius Bobin

Merge request reports

Loading
Loading

Activity

Filter activity
  • Approvals
  • Assignees & reviewers
  • Comments (from bots)
  • Comments (from users)
  • Commits & branches
  • Edits
  • Labels
  • Lock status
  • Mentions
  • Merge request status
  • Tracking
  • Marius Bobin changed milestone to %16.2

    changed milestone to %16.2

  • assigned to @mbobin

  • Contributor
    1 Warning
    :warning: You've made some app changes, but didn't add any tests.
    That's OK as long as you're refactoring existing code,
    but please consider adding any of the maintenancepipelines, maintenancerefactor, maintenanceworkflow, documentation, QA labels.

    Reviewer roulette

    Changes that require review have been detected!

    Please refer to the table below for assigning reviewers and maintainers suggested by Danger in the specified category:

    Category Reviewer Maintainer
    database João Pereira current availability (@jdrpereira) (UTC+1, 2 hours behind @mbobin) Alper Akgun current availability (@a_akgun) (UTC+3, same timezone as @mbobin)
    ~"migration" No reviewer available No maintainer available

    To spread load more evenly across eligible reviewers, Danger has picked a candidate for each review slot, based on their timezone. Feel free to override these selections if you think someone else would be better-suited or use the GitLab Review Workload Dashboard to find other available reviewers.

    To read more on how to use the reviewer roulette, please take a look at the Engineering workflow and code review guidelines. Please consider assigning a reviewer or maintainer who is a domain expert in the area of the merge request.

    Once you've decided who will review this merge request, assign them as a reviewer! Danger does not automatically notify them for you.

    If needed, you can retry the :repeat: danger-review job that generated this comment.

    Generated by :no_entry_sign: Danger

  • A deleted user added Data WarehouseImpact Check label
  • Marius Bobin changed title from Replace foreign keys to use the partitioned table - V4 to Finalize conversion for FKs targeting p_ci_builds - 3 self-managed

    changed title from Replace foreign keys to use the partitioned table - V4 to Finalize conversion for FKs targeting p_ci_builds - 3 self-managed

  • Marius Bobin changed target branch from master to 414396-partitioned-fks-v4

    changed target branch from master to 414396-partitioned-fks-v4

  • Marius Bobin changed title from Finalize conversion for FKs targeting p_ci_builds - 3 self-managed to Finalize conversion for FKs targeting p_ci_builds - 3

    changed title from Finalize conversion for FKs targeting p_ci_builds - 3 self-managed to Finalize conversion for FKs targeting p_ci_builds - 3

  • mentioned in issue #414396 (closed)

  • Marius Bobin changed the description

    changed the description

  • Marius Bobin mentioned in merge request !124311 (merged)

    mentioned in merge request !124311 (merged)

  • Contributor

    Allure report

    allure-report-publisher generated test report!

    e2e-test-on-gdk: :heavy_minus_sign: test report for b1d74d4d

    expand test summary
    +------------------------------------------------------------+
    |                       suites summary                       |
    +-------+--------+--------+---------+-------+-------+--------+
    |       | passed | failed | skipped | flaky | total | result |
    +-------+--------+--------+---------+-------+-------+--------+
    +-------+--------+--------+---------+-------+-------+--------+
    | Total | 0      | 0      | 0       | 0     | 0     | ➖     |
    +-------+--------+--------+---------+-------+-------+--------+
  • Marius Bobin deleted the 414396-partitioned-fks-v4 branch. This merge request now targets the master branch

    deleted the 414396-partitioned-fks-v4 branch. This merge request now targets the master branch

  • Marius Bobin mentioned in epic &7522

    mentioned in epic &7522

  • Marius Bobin added 200 commits

    added 200 commits

    Compare with previous version

  • Marius Bobin requested review from @morefice

    requested review from @morefice

  • Contributor

    Database migrations (on the main database)

    Migrations included in this change have been executed on gitlab.com data for testing purposes. For details, please see the migration testing pipeline (limited access).

    Migration Type Total runtime Result DB size change
    20230704074120 - ReplaceOldFkPCiBuildsMetadataToBuilds Post deploy 2.7 s :white_check_mark: +0.00 B
    20230704074124 - ReplaceOldFkPCiRunnerMachineBuildsToBuilds Post deploy 2.4 s :white_check_mark: +0.00 B
    Runtime Histogram for all migrations
    Query Runtime Count
    0 seconds - 0.01 seconds 0
    0.01 seconds - 0.1 seconds 22
    0.1 seconds - 1 second 1
    1 second - 5 seconds 0
    5 seconds - 15 seconds 0
    15 seconds - 5 minutes 0
    5 minutes + 0

    Migration: 20230704074120 - ReplaceOldFkPCiBuildsMetadataToBuilds

    • Type: Post deploy
    • Duration: 2.7 s
    • Database size change: +0.00 B
    Calls Total Time Max Time Mean Time Rows Query
    1 70.0 ms 70.0 ms 70.0 ms 0
    ALTER TABLE "p_ci_builds_metadata" DROP CONSTRAINT "fk_e20479742e_p"
    1 10.9 ms 10.9 ms 10.9 ms 0
    ALTER TABLE "p_ci_builds_metadata" RENAME CONSTRAINT "temp_fk_e20479742e_p" TO "fk_e20479742e_p"
    1 9.1 ms 9.1 ms 9.1 ms 0
    SELECT "postgres_autovacuum_activity".*
    FROM "postgres_autovacuum_activity" WHERE "postgres_autovacuum_activity"."wraparound_prevention" = $1 AND (schema = current_schema()) AND "postgres_autovacuum_activity"."table" IN ($2, $3)
    1 7.4 ms 7.4 ms 7.4 ms 2
    SELECT "postgres_partitions".*
    FROM "postgres_partitions" WHERE "postgres_partitions"."parent_identifier" = $1
    ORDER BY "postgres_partitions"."name" ASC
    2 7.0 ms 4.8 ms 3.5 ms 1
    SELECT $1 AS one
    FROM "postgres_foreign_keys" WHERE "postgres_foreign_keys"."constrained_table_name" = $2 AND "postgres_foreign_keys"."referenced_table_name" = $3 AND "postgres_foreign_keys"."name" = $4
    LIMIT $5
    1 3.0 ms 3.0 ms 3.0 ms 0
    ALTER TABLE "public"."ci_builds_metadata" RENAME CONSTRAINT "temp_fk_e20479742e_p" TO "fk_e20479742e_p"
    1 0.5 ms 0.5 ms 0.5 ms 0
    LOCK TABLE ci_builds, p_ci_builds_metadata IN ACCESS EXCLUSIVE MODE
    1 0.3 ms 0.3 ms 0.3 ms 1
    SELECT "postgres_partitioned_tables".*
    FROM "postgres_partitioned_tables" WHERE (identifier = concat(current_schema(), $1, $2))
    LIMIT $3
    1 0.2 ms 0.2 ms 0.2 ms 0
    ALTER TABLE "gitlab_partitions_dynamic"."ci_builds_metadata_101" RENAME CONSTRAINT "temp_fk_e20479742e_p" TO "fk_e20479742e_p"
    2 0.0 ms 0.0 ms 0.0 ms 2
    SELECT pg_backend_pid()
    Histogram for ReplaceOldFkPCiBuildsMetadataToBuilds
    Query Runtime Count
    0 seconds - 0.01 seconds 0
    0.01 seconds - 0.1 seconds 11
    0.1 seconds - 1 second 1
    1 second - 5 seconds 0
    5 seconds - 15 seconds 0
    15 seconds - 5 minutes 0
    5 minutes + 0

    Migration: 20230704074124 - ReplaceOldFkPCiRunnerMachineBuildsToBuilds

    • Type: Post deploy
    • Duration: 2.4 s
    • Database size change: +0.00 B
    Calls Total Time Max Time Mean Time Rows Query
    1 7.2 ms 7.2 ms 7.2 ms 1
    SELECT "postgres_partitions".*
    FROM "postgres_partitions" WHERE "postgres_partitions"."parent_identifier" = $1
    ORDER BY "postgres_partitions"."name" ASC
    1 5.0 ms 5.0 ms 5.0 ms 0
    ALTER TABLE "p_ci_runner_machine_builds" DROP CONSTRAINT "fk_bb490f12fe_p"
    2 3.0 ms 2.9 ms 1.5 ms 1
    SELECT $1 AS one
    FROM "postgres_foreign_keys" WHERE "postgres_foreign_keys"."constrained_table_name" = $2 AND "postgres_foreign_keys"."referenced_table_name" = $3 AND "postgres_foreign_keys"."name" = $4
    LIMIT $5
    1 0.8 ms 0.8 ms 0.8 ms 0
    SELECT "postgres_autovacuum_activity".*
    FROM "postgres_autovacuum_activity" WHERE "postgres_autovacuum_activity"."wraparound_prevention" = $1 AND (schema = current_schema()) AND "postgres_autovacuum_activity"."table" = $2
    1 0.2 ms 0.2 ms 0.2 ms 0
    LOCK TABLE ci_builds, p_ci_runner_machine_builds IN ACCESS EXCLUSIVE MODE
    1 0.2 ms 0.2 ms 0.2 ms 0
    ALTER TABLE "gitlab_partitions_dynamic"."ci_runner_machine_builds_100" RENAME CONSTRAINT "temp_fk_bb490f12fe_p" TO "fk_bb490f12fe_p"
    1 0.2 ms 0.2 ms 0.2 ms 1
    SELECT "postgres_partitioned_tables".*
    FROM "postgres_partitioned_tables" WHERE (identifier = concat(current_schema(), $1, $2))
    LIMIT $3
    1 0.2 ms 0.2 ms 0.2 ms 0
    ALTER TABLE "p_ci_runner_machine_builds" RENAME CONSTRAINT "temp_fk_bb490f12fe_p" TO "fk_bb490f12fe_p"
    2 0.0 ms 0.0 ms 0.0 ms 2
    SELECT pg_backend_pid()
    Histogram for ReplaceOldFkPCiRunnerMachineBuildsToBuilds
    Query Runtime Count
    0 seconds - 0.01 seconds 0
    0.01 seconds - 0.1 seconds 11
    0.1 seconds - 1 second 0
    1 second - 5 seconds 0
    5 seconds - 15 seconds 0
    15 seconds - 5 minutes 0
    5 minutes + 0

    Other information

    Other migrations pending on GitLab.com
    Migration Type Total runtime Result DB size change
    20230704062132 - ReplacePCiBuildsMetadataForeignKeyV5 Post deploy 8.4 s :warning: :grey_question:
    20230704062136 - ReplacePCiRunnerMachineBuildsForeignKeyV4 Post deploy 1.8 s :white_check_mark: +0.00 B
    Clone details
    Clone ID Clone Created At Clone Data Timestamp Expected Removal Time
    database-testing-2088855-10530553-main 2023-07-06T13:22:54Z 2023-07-06T12:04:03Z 2023-07-07 01:30:56 +0000
    database-testing-2088855-10530553-ci 2023-07-06T13:22:54Z 2023-07-06T08:46:44Z 2023-07-07 01:30:56 +0000

    Job artifacts

    Database migrations (on the ci database)

    Migrations included in this change have been executed on gitlab.com data for testing purposes. For details, please see the migration testing pipeline (limited access).

    Migration Type Total runtime Result DB size change
    20230704074120 - ReplaceOldFkPCiBuildsMetadataToBuilds Post deploy 3.4 s :white_check_mark: +8.00 KiB
    20230704074124 - ReplaceOldFkPCiRunnerMachineBuildsToBuilds Post deploy 3.4 s :white_check_mark: +0.00 B
    Runtime Histogram for all migrations
    Query Runtime Count
    0 seconds - 0.01 seconds 0
    0.01 seconds - 0.1 seconds 23
    0.1 seconds - 1 second 0
    1 second - 5 seconds 0
    5 seconds - 15 seconds 0
    15 seconds - 5 minutes 0
    5 minutes + 0

    Migration: 20230704074120 - ReplaceOldFkPCiBuildsMetadataToBuilds

    • Type: Post deploy
    • Duration: 3.4 s
    • Database size change: +8.00 KiB
    Calls Total Time Max Time Mean Time Rows Query
    1 25.5 ms 25.5 ms 25.5 ms 0
    ALTER TABLE "p_ci_builds_metadata" DROP CONSTRAINT "fk_e20479742e_p"
    1 6.4 ms 6.4 ms 6.4 ms 0
    SELECT "postgres_autovacuum_activity".*
    FROM "postgres_autovacuum_activity" WHERE "postgres_autovacuum_activity"."wraparound_prevention" = $1 AND (schema = current_schema()) AND "postgres_autovacuum_activity"."table" IN ($2, $3)
    1 5.6 ms 5.6 ms 5.6 ms 2
    SELECT "postgres_partitions".*
    FROM "postgres_partitions" WHERE "postgres_partitions"."parent_identifier" = $1
    ORDER BY "postgres_partitions"."name" ASC
    2 6.9 ms 5.5 ms 3.5 ms 1
    SELECT $1 AS one
    FROM "postgres_foreign_keys" WHERE "postgres_foreign_keys"."constrained_table_name" = $2 AND "postgres_foreign_keys"."referenced_table_name" = $3 AND "postgres_foreign_keys"."name" = $4
    LIMIT $5
    1 1.7 ms 1.7 ms 1.7 ms 0
    ALTER TABLE "public"."ci_builds_metadata" RENAME CONSTRAINT "temp_fk_e20479742e_p" TO "fk_e20479742e_p"
    1 1.5 ms 1.5 ms 1.5 ms 0
    ALTER TABLE "gitlab_partitions_dynamic"."ci_builds_metadata_101" RENAME CONSTRAINT "temp_fk_e20479742e_p" TO "fk_e20479742e_p"
    1 0.4 ms 0.4 ms 0.4 ms 0
    LOCK TABLE ci_builds, p_ci_builds_metadata IN ACCESS EXCLUSIVE MODE
    1 0.3 ms 0.3 ms 0.3 ms 1
    SELECT "postgres_partitioned_tables".*
    FROM "postgres_partitioned_tables" WHERE (identifier = concat(current_schema(), $1, $2))
    LIMIT $3
    1 0.2 ms 0.2 ms 0.2 ms 0
    ALTER TABLE "p_ci_builds_metadata" RENAME CONSTRAINT "temp_fk_e20479742e_p" TO "fk_e20479742e_p"
    2 0.0 ms 0.0 ms 0.0 ms 2
    SELECT pg_backend_pid()
    Histogram for ReplaceOldFkPCiBuildsMetadataToBuilds
    Query Runtime Count
    0 seconds - 0.01 seconds 0
    0.01 seconds - 0.1 seconds 12
    0.1 seconds - 1 second 0
    1 second - 5 seconds 0
    5 seconds - 15 seconds 0
    15 seconds - 5 minutes 0
    5 minutes + 0

    Migration: 20230704074124 - ReplaceOldFkPCiRunnerMachineBuildsToBuilds

    • Type: Post deploy
    • Duration: 3.4 s
    • Database size change: +0.00 B
    Calls Total Time Max Time Mean Time Rows Query
    1 4.8 ms 4.8 ms 4.8 ms 1
    SELECT "postgres_partitions".*
    FROM "postgres_partitions" WHERE "postgres_partitions"."parent_identifier" = $1
    ORDER BY "postgres_partitions"."name" ASC
    2 2.7 ms 2.6 ms 1.3 ms 1
    SELECT $1 AS one
    FROM "postgres_foreign_keys" WHERE "postgres_foreign_keys"."constrained_table_name" = $2 AND "postgres_foreign_keys"."referenced_table_name" = $3 AND "postgres_foreign_keys"."name" = $4
    LIMIT $5
    1 1.6 ms 1.6 ms 1.6 ms 0
    ALTER TABLE "p_ci_runner_machine_builds" DROP CONSTRAINT "fk_bb490f12fe_p"
    1 0.8 ms 0.8 ms 0.8 ms 0
    SELECT "postgres_autovacuum_activity".*
    FROM "postgres_autovacuum_activity" WHERE "postgres_autovacuum_activity"."wraparound_prevention" = $1 AND (schema = current_schema()) AND "postgres_autovacuum_activity"."table" = $2
    1 0.3 ms 0.3 ms 0.3 ms 0
    LOCK TABLE ci_builds, p_ci_runner_machine_builds IN ACCESS EXCLUSIVE MODE
    1 0.3 ms 0.3 ms 0.3 ms 1
    SELECT "postgres_partitioned_tables".*
    FROM "postgres_partitioned_tables" WHERE (identifier = concat(current_schema(), $1, $2))
    LIMIT $3
    1 0.2 ms 0.2 ms 0.2 ms 0
    ALTER TABLE "gitlab_partitions_dynamic"."ci_runner_machine_builds_100" RENAME CONSTRAINT "temp_fk_bb490f12fe_p" TO "fk_bb490f12fe_p"
    1 0.2 ms 0.2 ms 0.2 ms 0
    ALTER TABLE "p_ci_runner_machine_builds" RENAME CONSTRAINT "temp_fk_bb490f12fe_p" TO "fk_bb490f12fe_p"
    2 0.0 ms 0.0 ms 0.0 ms 2
    SELECT pg_backend_pid()
    Histogram for ReplaceOldFkPCiRunnerMachineBuildsToBuilds
    Query Runtime Count
    0 seconds - 0.01 seconds 0
    0.01 seconds - 0.1 seconds 11
    0.1 seconds - 1 second 0
    1 second - 5 seconds 0
    5 seconds - 15 seconds 0
    15 seconds - 5 minutes 0
    5 minutes + 0

    Other information

    Other migrations pending on GitLab.com
    Migration Type Total runtime Result DB size change
    20230621072848 - AddTextLimitToCiVariableDescription Post deploy 20.2 s :warning: +0.00 B
    20230621083052 - AddTextLimitToCiGroupVariableDescription Post deploy 5.6 s :warning: +0.00 B
    20230621102941 - ReplaceOldFkCiJobArtifactsToBuildsV2 Post deploy 3.1 s :warning: +0.00 B
    20230621103000 - ReplaceOldFkCiRunningBuildsToBuildsV2 Post deploy 2.7 s :white_check_mark: +0.00 B
    20230621103043 - ReplaceOldFkCiJobVariablesToBuildsV2 Post deploy 3.4 s :white_check_mark: +0.00 B
    20230626072436 - DropTmpIndexJobArtifactsIdAndExpireAt Post deploy 4.0 s :white_check_mark: +0.00 B
    20230627174139 - AddIndexToPoolRepositories Post deploy 3.9 s :warning: +0.00 B
    20230629011859 - CleanupBigintConversionForNotesForGitlabCom Post deploy 2.8 s :white_check_mark: +0.00 B
    20230629024032 - EnsureEventsBigintBackfillIsFinishedForGitlabDotCom Post deploy 2.5 s :white_check_mark: +0.00 B
    20230629024403 - EventsBigintCreateIndexeAsyncForGitlabDotCom Post deploy 3.6 s :white_check_mark: +0.00 B
    20230703122803 - CreateVulnerabilityUuidTypeMigrationIndex Post deploy 2.6 s :white_check_mark: +0.00 B
    20230704042302 - PrepareRemovalIndexDeploymentsOnProjectIdSha Post deploy 3.9 s :white_check_mark: +0.00 B
    20230704062132 - ReplacePCiBuildsMetadataForeignKeyV5 Post deploy 2.8 s :white_check_mark: +0.00 B
    20230704062136 - ReplacePCiRunnerMachineBuildsForeignKeyV4 Post deploy 2.6 s :white_check_mark: +0.00 B
    20230705115215 - RemoveUserDetailsProvisionedByGroupAtColumn Post deploy 2.8 s :white_check_mark: +0.00 B
    Clone details
    Clone ID Clone Created At Clone Data Timestamp Expected Removal Time
    database-testing-2088855-10530553-main 2023-07-06T13:22:54Z 2023-07-06T12:04:03Z 2023-07-07 01:30:56 +0000
    database-testing-2088855-10530553-ci 2023-07-06T13:22:54Z 2023-07-06T08:46:44Z 2023-07-07 01:30:56 +0000

    Job artifacts


    Brought to you by gitlab-org/database-team/gitlab-com-database-testing. Epic

  • Max Orefice approved this merge request

    approved this merge request

  • :wave: @morefice, thanks for approving this merge request.

    This is the first time the merge request is approved. To ensure full test coverage, a new pipeline will be started shortly.

    For more info, please refer to the following links:

  • Max Orefice removed review request for @morefice

    removed review request for @morefice

  • Max Orefice requested review from @stomlinson

    requested review from @stomlinson

  • added databasereviewed label and removed databasereview pending label

  • Marius Bobin removed review request for @stomlinson

    removed review request for @stomlinson

  • Marius Bobin added 1465 commits

    added 1465 commits

    Compare with previous version

  • Marius Bobin
  • Marius Bobin requested review from @morefice

    requested review from @morefice

  • Max Orefice requested review from @stomlinson and removed review request for @morefice

    requested review from @stomlinson and removed review request for @morefice

  • added databasereviewed label and removed databasereview pending label

  • Marius Bobin marked the checklist item I have evaluated the MR acceptance checklist for this MR. as completed

    marked the checklist item I have evaluated the MR acceptance checklist for this MR. as completed

  • Marius Bobin added 577 commits

    added 577 commits

    Compare with previous version

  • Simon Tomlinson approved this merge request

    approved this merge request

  • added databaseapproved label and removed databasereviewed label

  • Simon Tomlinson resolved all threads

    resolved all threads

  • Simon Tomlinson enabled an automatic merge when the pipeline for 13a4bda1 succeeds

    enabled an automatic merge when the pipeline for 13a4bda1 succeeds

  • Hello @mbobin :wave:

    The database team is looking for ways to improve the database review process and we would love your help!

    If you'd be open to someone on the database team reaching out to you for a chat, or if you'd like to leave some feedback asynchronously, just post a reply to this comment mentioning:

    @gitlab-org/database-team

    And someone will be by shortly!

    Thanks for your help! :heart:

    This message was generated automatically. You're welcome to improve it.

  • added workflowstaging label and removed workflowcanary label

  • Marius Bobin mentioned in commit e2197cc1

    mentioned in commit e2197cc1

  • Marius Bobin mentioned in merge request !126122 (merged)

    mentioned in merge request !126122 (merged)

  • Marius Bobin mentioned in commit dc3f2f4d

    mentioned in commit dc3f2f4d

  • Ravi Kumar mentioned in merge request !126304 (merged)

    mentioned in merge request !126304 (merged)

  • mentioned in merge request mbobin/headway!1 (closed)

  • Please register or sign in to reply
    Loading