Skip to content
Snippets Groups Projects

Replace FKs for p_ci_stages

Merged Max Orefice requested to merge morefice/p-ci-stages-fks into master
All threads resolved!

Ref: #439065 (closed)

What does this MR do and why?

This MR creates a FK pointing to p_ci_stages now that we partitioned Ci::Stage as it doesn't exist for the moment.

It creates the constraint and marks it as invalid as it will be validated over the weekend on gitlab.com.

Why are we doing this?

Now the we successfully partitioned ci_stages we need to make sure the referenced FK is targeting our new routing table.

https://docs.gitlab.com/ee/development/database/partitioning/list.html#step-7---re-point-foreign-keys-to-parent-table

Edited by Max Orefice

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
  • Max Orefice changed milestone to %16.10

    changed milestone to %16.10

  • 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
    20240226141402 - ReplaceCiBuildsCiStagesForeignKey Post deploy 3.6 s :white_check_mark: +8.00 KiB [note]
    Runtime Histogram for all migrations
    Query Runtime Count
    0 seconds - 0.01 seconds 0
    0.01 seconds - 0.1 seconds 25
    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: 20240226141402 - ReplaceCiBuildsCiStagesForeignKey

    • Type: Post deploy
    • Duration: 3.6 s
    • Database size change: +8.00 KiB [note]
    Calls Total Time Max Time Mean Time Rows Query
    1 33.8 ms 33.8 ms 33.8 ms 0
    ALTER TABLE public.ci_builds ADD CONSTRAINT tmp_fk_3a9eaa254d_p FOREIGN KEY (partition_id, stage_id) REFERENCES p_ci_stages (partition_id, id) ON
    UPDATE CASCADE ON DELETE CASCADE NOT VALID
    1 4.6 ms 4.6 ms 4.6 ms 0
    ALTER TABLE gitlab_partitions_dynamic.ci_builds_101 ADD CONSTRAINT tmp_fk_3a9eaa254d_p FOREIGN KEY (partition_id, stage_id) REFERENCES p_ci_stages (partition_id, id) ON
    UPDATE CASCADE ON DELETE CASCADE NOT VALID
    2 6.6 ms 3.4 ms 3.3 ms 4
    SELECT "postgres_partitions".*
    FROM "postgres_partitions" WHERE "postgres_partitions"."parent_identifier" = $1
    ORDER BY "postgres_partitions"."name" ASC
    2 2.2 ms 2.2 ms 1.1 ms 2
    INSERT INTO "postgres_async_foreign_key_validations" ("created_at", "updated_at", "name", "table_name") VALUES ($1, $2, $3, $4) RETURNING "id"
    1 2.0 ms 2.0 ms 2.0 ms 0
    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 AND "postgres_foreign_keys"."constrained_columns" = $5 AND "postgres_foreign_keys"."referenced_columns" = $6 AND "postgres_foreign_keys"."on_delete_action" = $7
    LIMIT $8
    2 3.2 ms 1.7 ms 1.6 ms 2
    SELECT $1 AS one
    FROM "postgres_foreign_keys" WHERE "postgres_foreign_keys"."constrained_table_identifier" = $2 AND "postgres_foreign_keys"."name" = $3
    LIMIT $4
    6 1.7 ms 0.4 ms 0.3 ms 2
    SELECT "postgres_partitioned_tables".*
    FROM "postgres_partitioned_tables" WHERE (identifier = concat(current_schema(), $1, $2))
    LIMIT $3
    1 0.3 ms 0.3 ms 0.3 ms 0
    LOCK TABLE p_ci_stages, public.ci_builds IN SHARE ROW EXCLUSIVE MODE
    2 0.3 ms 0.3 ms 0.2 ms 0
    SELECT $1 AS one
    FROM "postgres_foreign_keys" WHERE "postgres_foreign_keys"."constrained_table_identifier" = $2 AND "postgres_foreign_keys"."referenced_table_name" = $3 AND "postgres_foreign_keys"."name" = $4 AND "postgres_foreign_keys"."constrained_columns" = $5 AND "postgres_foreign_keys"."referenced_columns" = $6 AND "postgres_foreign_keys"."on_delete_action" = $7
    LIMIT $8
    1 0.3 ms 0.3 ms 0.3 ms 0
    LOCK TABLE p_ci_stages, gitlab_partitions_dynamic.ci_builds_101 IN SHARE ROW EXCLUSIVE MODE
    2 0.0 ms 0.0 ms 0.0 ms 0
    SELECT $1 AS one
    FROM "postgres_async_foreign_key_validations" WHERE "postgres_async_foreign_key_validations"."name" = $2 AND "postgres_async_foreign_key_validations"."table_name" = $3
    LIMIT $4
    2 0.0 ms 0.0 ms 0.0 ms 0
    SELECT "postgres_async_foreign_key_validations".*
    FROM "postgres_async_foreign_key_validations" WHERE "postgres_async_foreign_key_validations"."constraint_type" = $1 AND "postgres_async_foreign_key_validations"."name" = $2 AND "postgres_async_foreign_key_validations"."table_name" = $3
    LIMIT $4
    2 0.0 ms 0.0 ms 0.0 ms 2
    SELECT pg_backend_pid()
    Histogram for ReplaceCiBuildsCiStagesForeignKey
    Query Runtime Count
    0 seconds - 0.01 seconds 0
    0.01 seconds - 0.1 seconds 25
    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
    20240206095832 - AddIndexToNotesWhereNoteableTypeIsNull Post deploy 2.9 s :white_check_mark: +0.00 B
    20240222000003 - RemoveForeignKeyProjectRepositoryStates Post deploy 2.5 s :warning: +0.00 B
    20240222000004 - DropProjectRepositoryStatesTable Post deploy 2.0 s :warning: -1.59 GiB
    20240226081837 - EnsureIdUniquenessForPCiStages Post deploy 2.7 s :warning: +0.00 B
    20240226102153 - RemoveClusterAgentTokensProjectIdNotNull Post deploy 1.7 s :white_check_mark: +0.00 B
    Clone details
    Clone ID Clone Created At Clone Data Timestamp Expected Removal Time
    database-testing-2913271-12974186-main 2024-02-27T07:26:58Z 2024-02-26T19:37:00Z 2024-02-27 19:31:56 +0000
    database-testing-2913271-12974186-ci 2024-02-27T07:26:59Z 2024-02-27T04:46:37Z 2024-02-27 19:31: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
    20240226141402 - ReplaceCiBuildsCiStagesForeignKey Post deploy 4.1 s :white_check_mark: +8.00 KiB [note]
    Runtime Histogram for all migrations
    Query Runtime Count
    0 seconds - 0.01 seconds 0
    0.01 seconds - 0.1 seconds 25
    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: 20240226141402 - ReplaceCiBuildsCiStagesForeignKey

    • Type: Post deploy
    • Duration: 4.1 s
    • Database size change: +8.00 KiB [note]
    Calls Total Time Max Time Mean Time Rows Query
    1 24.1 ms 24.1 ms 24.1 ms 0
    ALTER TABLE public.ci_builds ADD CONSTRAINT tmp_fk_3a9eaa254d_p FOREIGN KEY (partition_id, stage_id) REFERENCES p_ci_stages (partition_id, id) ON
    UPDATE CASCADE ON DELETE CASCADE NOT VALID
    2 6.4 ms 3.2 ms 3.2 ms 4
    SELECT "postgres_partitions".*
    FROM "postgres_partitions" WHERE "postgres_partitions"."parent_identifier" = $1
    ORDER BY "postgres_partitions"."name" ASC
    1 2.7 ms 2.7 ms 2.7 ms 0
    ALTER TABLE gitlab_partitions_dynamic.ci_builds_101 ADD CONSTRAINT tmp_fk_3a9eaa254d_p FOREIGN KEY (partition_id, stage_id) REFERENCES p_ci_stages (partition_id, id) ON
    UPDATE CASCADE ON DELETE CASCADE NOT VALID
    2 2.6 ms 2.4 ms 1.3 ms 2
    INSERT INTO "postgres_async_foreign_key_validations" ("created_at", "updated_at", "name", "table_name") VALUES ($1, $2, $3, $4) RETURNING "id"
    2 4.3 ms 2.4 ms 2.2 ms 2
    SELECT $1 AS one
    FROM "postgres_foreign_keys" WHERE "postgres_foreign_keys"."constrained_table_identifier" = $2 AND "postgres_foreign_keys"."name" = $3
    LIMIT $4
    6 1.5 ms 0.4 ms 0.2 ms 2
    SELECT "postgres_partitioned_tables".*
    FROM "postgres_partitioned_tables" WHERE (identifier = concat(current_schema(), $1, $2))
    LIMIT $3
    1 0.3 ms 0.3 ms 0.3 ms 0
    LOCK TABLE p_ci_stages, public.ci_builds IN SHARE ROW EXCLUSIVE MODE
    1 0.2 ms 0.2 ms 0.2 ms 0
    LOCK TABLE p_ci_stages, gitlab_partitions_dynamic.ci_builds_101 IN SHARE ROW EXCLUSIVE MODE
    2 0.2 ms 0.2 ms 0.1 ms 0
    SELECT $1 AS one
    FROM "postgres_foreign_keys" WHERE "postgres_foreign_keys"."constrained_table_identifier" = $2 AND "postgres_foreign_keys"."referenced_table_name" = $3 AND "postgres_foreign_keys"."name" = $4 AND "postgres_foreign_keys"."constrained_columns" = $5 AND "postgres_foreign_keys"."referenced_columns" = $6 AND "postgres_foreign_keys"."on_delete_action" = $7
    LIMIT $8
    1 0.1 ms 0.1 ms 0.1 ms 0
    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 AND "postgres_foreign_keys"."constrained_columns" = $5 AND "postgres_foreign_keys"."referenced_columns" = $6 AND "postgres_foreign_keys"."on_delete_action" = $7
    LIMIT $8
    2 0.0 ms 0.0 ms 0.0 ms 0
    SELECT $1 AS one
    FROM "postgres_async_foreign_key_validations" WHERE "postgres_async_foreign_key_validations"."name" = $2 AND "postgres_async_foreign_key_validations"."table_name" = $3
    LIMIT $4
    2 0.0 ms 0.0 ms 0.0 ms 0
    SELECT "postgres_async_foreign_key_validations".*
    FROM "postgres_async_foreign_key_validations" WHERE "postgres_async_foreign_key_validations"."constraint_type" = $1 AND "postgres_async_foreign_key_validations"."name" = $2 AND "postgres_async_foreign_key_validations"."table_name" = $3
    LIMIT $4
    2 0.0 ms 0.0 ms 0.0 ms 2
    SELECT pg_backend_pid()
    Histogram for ReplaceCiBuildsCiStagesForeignKey
    Query Runtime Count
    0 seconds - 0.01 seconds 0
    0.01 seconds - 0.1 seconds 25
    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
    20240206095832 - AddIndexToNotesWhereNoteableTypeIsNull Post deploy 3.9 s :white_check_mark: +0.00 B
    20240222000003 - RemoveForeignKeyProjectRepositoryStates Post deploy 3.4 s :warning: +0.00 B
    20240222000004 - DropProjectRepositoryStatesTable Post deploy 2.2 s :white_check_mark: -72.00 KiB
    20240226081837 - EnsureIdUniquenessForPCiStages Post deploy 3.1 s :warning: +0.00 B
    20240226102153 - RemoveClusterAgentTokensProjectIdNotNull Post deploy 2.3 s :white_check_mark: +0.00 B
    Clone details
    Clone ID Clone Created At Clone Data Timestamp Expected Removal Time
    database-testing-2913271-12974186-main 2024-02-27T07:26:58Z 2024-02-26T19:37:00Z 2024-02-27 19:31:56 +0000
    database-testing-2913271-12974186-ci 2024-02-27T07:26:59Z 2024-02-27T04:46:37Z 2024-02-27 19:31:56 +0000

    Job artifacts


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

  • A deleted user added databasereview pending label
  • 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.
    1 Message
    :book: CHANGELOG missing:

    If this merge request needs a changelog entry, add the Changelog trailer to the commit message you want to add to the changelog.

    If this merge request doesn't need a CHANGELOG entry, feel free to ignore this message.

    Reviewer roulette

    Category Reviewer Maintainer
    database @carlad-gl profile link current availability (UTC+8, 7 hours ahead of author) @stomlinson profile link current availability (UTC-6, 7 hours behind author)

    Please check reviewer's status!

    • available Reviewer is available!
    • unavailable Reviewer is unavailable!

    Please refer to documentation page for guidance on how you can benefit from the Reviewer Roulette, or use the GitLab Review Workload Dashboard to find other available reviewers.

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

    Generated by :no_entry_sign: Danger

  • mentioned in issue #439065 (closed)

  • Max Orefice changed the description

    changed the description

  • Max Orefice
  • assigned to @morefice

  • Marius Bobin mentioned in epic &7522

    mentioned in epic &7522

  • Max Orefice requested review from @panoskanell

    requested review from @panoskanell

  • Panos Kanellidis approved this merge request

    approved this merge request

  • Panos Kanellidis requested review from @mbobin and removed review request for @panoskanell

    requested review from @mbobin and removed review request for @panoskanell

  • Max Orefice changed the description

    changed the description

  • Contributor

    E2E Test Result Summary

    allure-report-publisher generated test report!

    e2e-test-on-gdk: :white_check_mark: test report for 50e234a6

    expand test summary
    +------------------------------------------------------------------+
    |                          suites summary                          |
    +-------------+--------+--------+---------+-------+-------+--------+
    |             | passed | failed | skipped | flaky | total | result |
    +-------------+--------+--------+---------+-------+-------+--------+
    | Verify      | 35     | 0      | 1       | 0     | 36    | ✅     |
    | Create      | 8      | 0      | 3       | 0     | 11    | ✅     |
    | Govern      | 3      | 0      | 0       | 0     | 3     | ✅     |
    | Package     | 0      | 0      | 1       | 0     | 1     | ➖     |
    | Plan        | 4      | 0      | 0       | 0     | 4     | ✅     |
    | Monitor     | 4      | 0      | 0       | 0     | 4     | ✅     |
    | Data Stores | 2      | 0      | 0       | 0     | 2     | ✅     |
    +-------------+--------+--------+---------+-------+-------+--------+
    | Total       | 56     | 0      | 5       | 0     | 61    | ✅     |
    +-------------+--------+--------+---------+-------+-------+--------+

    e2e-package-and-test: :white_check_mark: test report for e75f105a

    expand test summary
    +------------------------------------------------------------------+
    |                          suites summary                          |
    +-------------+--------+--------+---------+-------+-------+--------+
    |             | passed | failed | skipped | flaky | total | result |
    +-------------+--------+--------+---------+-------+-------+--------+
    | Verify      | 147    | 0      | 30      | 3     | 177   | ✅     |
    | Create      | 153    | 0      | 19      | 4     | 172   | ✅     |
    | Monitor     | 8      | 0      | 0       | 0     | 8     | ✅     |
    | Package     | 0      | 0      | 2       | 0     | 2     | ➖     |
    | Plan        | 8      | 0      | 0       | 0     | 8     | ✅     |
    | Data Stores | 4      | 0      | 0       | 0     | 4     | ✅     |
    | Govern      | 6      | 0      | 0       | 0     | 6     | ✅     |
    +-------------+--------+--------+---------+-------+-------+--------+
    | Total       | 326    | 0      | 51      | 7     | 377   | ✅     |
    +-------------+--------+--------+---------+-------+-------+--------+
  • Marius Bobin approved this merge request

    approved this merge request

  • added databaseapproved label and removed databasereview pending label

  • Marius Bobin resolved all threads

    resolved all threads

  • Marius Bobin enabled an automatic merge when the pipeline for 7bfec7cc succeeds

    enabled an automatic merge when the pipeline for 7bfec7cc succeeds

  • Max Orefice aborted the automatic merge because source branch was updated

    aborted the automatic merge because source branch was updated

  • Max Orefice added 268 commits

    added 268 commits

    Compare with previous version

  • Marius Bobin resolved all threads

    resolved all threads

  • Marius Bobin enabled an automatic merge when the pipeline for e1816cd9 succeeds

    enabled an automatic merge when the pipeline for e1816cd9 succeeds

  • Max Orefice aborted the automatic merge because source branch was updated

    aborted the automatic merge because source branch was updated

  • Max Orefice added 157 commits

    added 157 commits

    Compare with previous version

  • merged

  • Hello @morefice :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.

  • Marius Bobin mentioned in commit 25b4b109

    mentioned in commit 25b4b109

  • added workflowstaging label and removed workflowcanary label

  • Max Orefice mentioned in merge request !146459 (merged)

    mentioned in merge request !146459 (merged)

  • Please register or sign in to reply
    Loading