Replace FKs for p_ci_stages
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.
Merge request reports
Activity
changed milestone to %16.10
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 +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 VALID1 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 VALID2 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" ASC2 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 $82 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 $46 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 $31 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 $81 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 $42 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 $42 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 +0.00 B 20240222000003 - RemoveForeignKeyProjectRepositoryStates Post deploy 2.5 s +0.00 B 20240222000004 - DropProjectRepositoryStatesTable Post deploy 2.0 s -1.59 GiB 20240226081837 - EnsureIdUniquenessForPCiStages Post deploy 2.7 s +0.00 B 20240226102153 - RemoveClusterAgentTokensProjectIdNotNull Post deploy 1.7 s +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 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 +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 VALID2 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" ASC1 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 VALID2 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 $46 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 $31 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 $81 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 $82 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 $42 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 $42 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 +0.00 B 20240222000003 - RemoveForeignKeyProjectRepositoryStates Post deploy 3.4 s +0.00 B 20240222000004 - DropProjectRepositoryStatesTable Post deploy 2.2 s -72.00 KiB 20240226081837 - EnsureIdUniquenessForPCiStages Post deploy 3.1 s +0.00 B 20240226102153 - RemoveClusterAgentTokensProjectIdNotNull Post deploy 2.3 s +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
Brought to you by gitlab-org/database-team/gitlab-com-database-testing. Epic
- A deleted user
added databasereview pending label
1 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 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
(UTC+8, 7 hours ahead of author)
@stomlinson
(UTC-6, 7 hours behind author)
Please check reviewer's status!
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
danger-review
job that generated this comment.Generated by
Dangeradded workflowin review label and removed workflowready for development label
mentioned in issue #439065 (closed)
- Resolved by Marius Bobin
- A deleted user
added database-testing-automation label
assigned to @morefice
mentioned in epic &7522
- Resolved by Marius Bobin
@panoskanell can you review this MR please?
requested review from @panoskanell
- Resolved by Marius Bobin
@panoskanell
, thanks for approving this merge request.This is the first time the merge request has been approved. To ensure we don't only run predictive pipelines, and we don't break
master
, a new pipeline will be started shortly.Please wait for the pipeline to start before resolving this discussion and set auto-merge for the new pipeline. See merging a merge request for more details.
added pipeline:mr-approved label
requested review from @mbobin and removed review request for @panoskanell
E2E Test Result Summary
allure-report-publisher
generated test report!e2e-test-on-gdk:
test report for 50e234a6expand 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:
test report for e75f105aexpand 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 | ✅ | +-------------+--------+--------+---------+-------+-------+--------+
added databaseapproved label and removed databasereview pending label
enabled an automatic merge when the pipeline for 7bfec7cc succeeds
- Resolved by Max Orefice
Master is currently broken
added 268 commits
-
deb2602b...1dcbd93a - 267 commits from branch
master
- e75f105a - Replace FKs for p_ci_stages
-
deb2602b...1dcbd93a - 267 commits from branch
enabled an automatic merge when the pipeline for e1816cd9 succeeds
added 157 commits
-
e75f105a...d8e0d977 - 156 commits from branch
master
- 50e234a6 - Replace FKs for p_ci_stages
-
e75f105a...d8e0d977 - 156 commits from branch
Hello @morefice
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!
This message was generated automatically. You're welcome to improve it.
mentioned in commit 25b4b109
added workflowstaging-canary label and removed workflowin review label
added workflowcanary label and removed workflowstaging-canary label
added workflowstaging label and removed workflowcanary label
added workflowproduction label and removed workflowstaging label
added workflowpost-deploy-db-staging label and removed workflowproduction label
mentioned in incident gitlab-com/gl-infra/production#17682 (closed)
added workflowpost-deploy-db-production label and removed workflowpost-deploy-db-staging label
added releasedcandidate label
mentioned in merge request !146459 (merged)
mentioned in merge request kubitus-project/kubitus-installer!2869 (merged)
added releasedpublished label and removed releasedcandidate label
added pipelinetier-3 label