Resolve "Convert ci_builds_runner_session.build_id to bigint - Step 2: Finalize background migrations and swap columns"
What does this MR do?
This MR finalize the conversion to bigint
for ci_builds_runner_session
table.
At a high level, the operation takes the following steps:
- Ensure the migration of
build_id
tobigint
is completed. - Copy index and FK
- Swap columns
Cleanup (removing the old int
columns and the triggers) will be done in later MR.
Migration Output
Up
== 20210720091902 FinalizeCiBuildsRunnerSessionBigintConversion: migrating ==== -- transaction_open?() -> 0.0000s -- index_exists?("ci_builds_runner_session", :build_id_convert_to_bigint, {:unique=>true, :name=>"index_ci_builds_runner_session_on_build_id_convert_to_bigint", :algorithm=>:concurrently}) -> 0.0019s -- execute("SET statement_timeout TO 0") -> 0.0005s -- add_index("ci_builds_runner_session", :build_id_convert_to_bigint, {:unique=>true, :name=>"index_ci_builds_runner_session_on_build_id_convert_to_bigint", :algorithm=>:concurrently}) -> 0.0028s -- execute("RESET ALL") -> 0.0005s -- transaction_open?() -> 0.0000s -- foreign_keys("ci_builds_runner_session") -> 0.0030s -- execute("ALTER TABLE ci_builds_runner_session\nADD CONSTRAINT fk_rails_70707857d3_tmp\nFOREIGN KEY (build_id_convert_to_bigint)\nREFERENCES ci_builds (id)\nON DELETE CASCADE\nNOT VALID;\n") -> 0.0016s -- execute("ALTER TABLE ci_builds_runner_session VALIDATE CONSTRAINT fk_rails_70707857d3_tmp;") -> 0.0042s -- execute("LOCK TABLE ci_builds_runner_session, ci_builds IN ACCESS EXCLUSIVE MODE") -> 0.0006s -- quote_table_name("ci_builds_runner_session") -> 0.0000s -- quote_column_name(:build_id) -> 0.0000s -- quote_column_name("build_id_tmp") -> 0.0000s -- execute("ALTER TABLE \"ci_builds_runner_session\" RENAME COLUMN \"build_id\" TO \"build_id_tmp\"") -> 0.0006s -- quote_table_name("ci_builds_runner_session") -> 0.0000s -- quote_column_name(:build_id_convert_to_bigint) -> 0.0000s -- quote_column_name(:build_id) -> 0.0000s -- execute("ALTER TABLE \"ci_builds_runner_session\" RENAME COLUMN \"build_id_convert_to_bigint\" TO \"build_id\"") -> 0.0006s -- quote_table_name("ci_builds_runner_session") -> 0.0000s -- quote_column_name("build_id_tmp") -> 0.0000s -- quote_column_name(:build_id_convert_to_bigint) -> 0.0000s -- execute("ALTER TABLE \"ci_builds_runner_session\" RENAME COLUMN \"build_id_tmp\" TO \"build_id_convert_to_bigint\"") -> 0.0005s -- quote_table_name("trigger_51ab7cef8934") -> 0.0000s -- execute("ALTER FUNCTION \"trigger_51ab7cef8934\" RESET ALL") -> 0.0006s -- change_column_default("ci_builds_runner_session", :build_id, nil) -> 0.0020s -- change_column_default("ci_builds_runner_session", :build_id_convert_to_bigint, 0) -> 0.0017s -- execute("DROP INDEX index_ci_builds_runner_session_on_build_id") -> 0.0006s -- rename_index("ci_builds_runner_session", "index_ci_builds_runner_session_on_build_id_convert_to_bigint", "index_ci_builds_runner_session_on_build_id") -> 0.0005s -- remove_foreign_key("ci_builds_runner_session", {:name=>"fk_rails_70707857d3"}) -> 0.0029s -- quote_table_name("ci_builds_runner_session") -> 0.0000s -- quote_column_name("fk_rails_70707857d3_tmp") -> 0.0000s -- quote_column_name("fk_rails_70707857d3") -> 0.0000s -- execute("ALTER TABLE \"ci_builds_runner_session\"\nRENAME CONSTRAINT \"fk_rails_70707857d3_tmp\" TO \"fk_rails_70707857d3\"\n") -> 0.0005s == 20210720091902 FinalizeCiBuildsRunnerSessionBigintConversion: migrated (0.0647s)
Down
== 20210720091902 FinalizeCiBuildsRunnerSessionBigintConversion: reverting ==== -- transaction_open?() -> 0.0000s -- index_exists?("ci_builds_runner_session", :build_id_convert_to_bigint, {:unique=>true, :name=>"index_ci_builds_runner_session_on_build_id_convert_to_bigint", :algorithm=>:concurrently}) -> 0.0026s -- execute("SET statement_timeout TO 0") -> 0.0005s -- add_index("ci_builds_runner_session", :build_id_convert_to_bigint, {:unique=>true, :name=>"index_ci_builds_runner_session_on_build_id_convert_to_bigint", :algorithm=>:concurrently}) -> 0.0030s -- execute("RESET ALL") -> 0.0005s -- transaction_open?() -> 0.0000s -- foreign_keys("ci_builds_runner_session") -> 0.0028s -- execute("ALTER TABLE ci_builds_runner_session\nADD CONSTRAINT fk_rails_70707857d3_tmp\nFOREIGN KEY (build_id_convert_to_bigint)\nREFERENCES ci_builds (id)\nON DELETE CASCADE\nNOT VALID;\n") -> 0.0016s -- execute("ALTER TABLE ci_builds_runner_session VALIDATE CONSTRAINT fk_rails_70707857d3_tmp;") -> 0.0043s -- execute("LOCK TABLE ci_builds_runner_session, ci_builds IN ACCESS EXCLUSIVE MODE") -> 0.0005s -- quote_table_name("ci_builds_runner_session") -> 0.0000s -- quote_column_name(:build_id) -> 0.0000s -- quote_column_name("build_id_tmp") -> 0.0000s -- execute("ALTER TABLE \"ci_builds_runner_session\" RENAME COLUMN \"build_id\" TO \"build_id_tmp\"") -> 0.0006s -- quote_table_name("ci_builds_runner_session") -> 0.0000s -- quote_column_name(:build_id_convert_to_bigint) -> 0.0000s -- quote_column_name(:build_id) -> 0.0000s -- execute("ALTER TABLE \"ci_builds_runner_session\" RENAME COLUMN \"build_id_convert_to_bigint\" TO \"build_id\"") -> 0.0005s -- quote_table_name("ci_builds_runner_session") -> 0.0000s -- quote_column_name("build_id_tmp") -> 0.0000s -- quote_column_name(:build_id_convert_to_bigint) -> 0.0000s -- execute("ALTER TABLE \"ci_builds_runner_session\" RENAME COLUMN \"build_id_tmp\" TO \"build_id_convert_to_bigint\"") -> 0.0005s -- quote_table_name("trigger_51ab7cef8934") -> 0.0000s -- execute("ALTER FUNCTION \"trigger_51ab7cef8934\" RESET ALL") -> 0.0006s -- change_column_default("ci_builds_runner_session", :build_id, nil) -> 0.0017s -- change_column_default("ci_builds_runner_session", :build_id_convert_to_bigint, 0) -> 0.0016s -- execute("DROP INDEX index_ci_builds_runner_session_on_build_id") -> 0.0006s -- rename_index("ci_builds_runner_session", "index_ci_builds_runner_session_on_build_id_convert_to_bigint", "index_ci_builds_runner_session_on_build_id") -> 0.0006s -- remove_foreign_key("ci_builds_runner_session", {:name=>"fk_rails_70707857d3"}) -> 0.0029s -- quote_table_name("ci_builds_runner_session") -> 0.0000s -- quote_column_name("fk_rails_70707857d3_tmp") -> 0.0000s -- quote_column_name("fk_rails_70707857d3") -> 0.0000s -- execute("ALTER TABLE \"ci_builds_runner_session\"\nRENAME CONSTRAINT \"fk_rails_70707857d3_tmp\" TO \"fk_rails_70707857d3\"\n") -> 0.0005s == 20210720091902 FinalizeCiBuildsRunnerSessionBigintConversion: reverted (0.0377s)
Database migrations
Timings
From DB Lab:
-
CREATE UNIQUE INDEX CONCURRENTLY "index_ci_builds_runner_session_on_build_id_convert_to_bigint" ON "ci_builds_runner_session" ("build_id_convert_to_bigint");
Duration: 284.506 ms - https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/5268/commands/18590
-
ALTER TABLE ci_builds_runner_session VALIDATE CONSTRAINT fk_rails_70707857d3_tmp;
Duration: 3.273 s - https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/5268/commands/18592
Screenshots or Screencasts (strongly suggested)
Does this MR meet the acceptance criteria?
Conformity
-
I have included changelog trailers, or none are needed. (Does this MR need a changelog?) -
I have added/updated documentation, or it's not needed. (Is documentation required?) -
I have properly separated EE content from FOSS, or this MR is FOSS only. (Where should EE code go?) -
I have added information for database reviewers in the MR description, or it's not needed. (Does this MR have database related changes?) -
I have self-reviewed this MR per code review guidelines. -
This MR does not harm performance, or I have asked a reviewer to help assess the performance impact. (Merge request performance guidelines) -
I have followed the style guides. -
This change is backwards compatible across updates, or this does not apply.
Availability and Testing
-
I have added/updated tests following the Testing Guide, or it's not needed. (Consider all test levels. See the Test Planning Process.) -
I have tested this MR in all supported browsers, or it's not needed. -
I have informed the Infrastructure department of a default or new setting change per definition of done, or it's not needed.
Security
Does this MR contain changes to processing or storing of credentials or tokens, authorization and authentication methods or other items described in the security review guidelines? If not, then delete this Security section.
-
Label as security and @ mention @gitlab-com/gl-security/appsec
-
The MR includes necessary changes to maintain consistency between UI, API, email, or other methods -
Security reports checked/validated by a reviewer from the AppSec team
Related to #328445 (closed)
Merge request reports
Activity
changed milestone to %14.2
assigned to @dfrazao-gitlab
added 1 commit
- 0946be3e - Finalize conversion to bigint for ci_builds_runner_session
- A deleted user
added database 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 tooling, ~"tooling::pipelines", ~"tooling::workflow", 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 Michał Zając ( @Quintasan
) (UTC+2, same timezone as@dfrazao-gitlab
)Tiger Watson ( @tigerwnz
) (UTC+10, 8 hours ahead of@dfrazao-gitlab
)~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
danger-review
job that generated this comment.Generated by
Dangeradded 1 commit
- bf301971 - Finalize conversion to bigint for ci_builds_runner_session
assigned to @krasio
added database-testing-automation label
added typefeature typemaintenance labels
added 1 commit
- bf5f84b0 - Finalize conversion to bigint for ci_builds_runner_session
added 1 commit
- a2a6bf40 - Finalize conversion to bigint for ci_builds_runner_session
added 1 commit
- 6d41b86f - Finalize conversion to bigint for ci_builds_runner_session
marked the checklist item I have included changelog trailers, or none are needed. (Does this MR need a changelog?) as completed
marked the checklist item I have added/updated documentation, or it's not needed. (Is documentation required?) as completed
marked the checklist item I have properly separated EE content from FOSS, or this MR is FOSS only. (Where should EE code go?) as completed
marked the checklist item I have added information for database reviewers in the MR description, or it's not needed. (Does this MR have database related changes?) as completed
marked the checklist item This change is backwards compatible across updates, or this does not apply. as completed
marked the checklist item I have followed the style guides. as completed
marked the checklist item This MR does not harm performance, or I have asked a reviewer to help assess the performance impact. (Merge request performance guidelines) as completed
marked the checklist item I have self-reviewed this MR per code review guidelines. as completed
Database migrations
1 Warnings 20210805131510 - FinalizeCiBuildsRunnerSessionBigintConversion had a query that exceeded
timing guidelines. Run time should not exceed 100ms, but was it was 3132.98ms. Please consider
possible options to improve the query performance.ALTER TABLE ci_builds_runner_session
VALIDATE CONSTRAINT fk_rails_70707857d3_tmpMigrations 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 20210805131510 - FinalizeCiBuildsRunnerSessionBigintConversion Post deploy 6.3 s -1.16 MiB Runtime Histogram for all migrations
Query Runtime Count 0 seconds - 0.01 seconds 0 0.01 seconds - 0.1 seconds 57 0.1 seconds - 1 second 2 1 second - 5 minutes 1 5 minutes + 0 Migration: 20210805131510 - FinalizeCiBuildsRunnerSessionBigintConversion- Type: Post deploy
- Duration: 6.3 s
- Database size change: -1.16 MiB
Query Calls Total Time Max Time Mean Time Rows ALTER TABLE ci_builds_runner_session VALIDATE CONSTRAINT fk_rails_70707857d3_tmp
1 3133.0 ms 3133.0 ms 3133.0 ms 0 CREATE UNIQUE INDEX CONCURRENTLY "index_ci_builds_runner_session_on_build_id_convert_to_bigint" ON "ci_builds_runner_session" ("build_id_convert_to_bigint") /*application:test*/
1 77.0 ms 77.0 ms 77.0 ms 0 ALTER TABLE ci_builds_runner_session ADD CONSTRAINT fk_rails_70707857d3_tmp FOREIGN KEY (build_id_convert_to_bigint) REFERENCES ci_builds (id) ON DELETE CASCADE NOT VALID
1 38.9 ms 38.9 ms 38.9 ms 0 DROP INDEX index_ci_builds_runner_session_on_build_id /*application:test*/
1 10.1 ms 10.1 ms 10.1 ms 0 ALTER TABLE "ci_builds_runner_session" DROP CONSTRAINT "fk_rails_70707857d3" /*application:test*/
1 8.1 ms 8.1 ms 8.1 ms 0 ALTER TABLE "ci_builds_runner_session" ALTER COLUMN "build_id" DROP DEFAULT /*application:test*/
1 7.7 ms 7.7 ms 7.7 ms 0 ALTER TABLE "ci_builds_runner_session" ALTER COLUMN "build_id_convert_to_bigint" SET DEFAULT 0 /*application:test*/
1 7.6 ms 7.6 ms 7.6 ms 0 ALTER FUNCTION "trigger_51ab7cef8934" RESET ALL /*application:test*/
1 3.3 ms 3.3 ms 3.3 ms 0 SELECT "batched_background_migrations".*
FROM "batched_background_migrations"
WHERE "batched_background_migrations"."job_class_name" = $1 AND "batched_background_migrations"."table_name" = $2 AND "batched_background_migrations"."column_name" = $3 AND (job_arguments = $4)
ORDER BY "batched_background_migrations"."id" ASC
LIMIT $5 /*application:test*/1 0.9 ms 0.9 ms 0.9 ms 1 SELECT "feature_gates".*
FROM "feature_gates"
WHERE "feature_gates"."feature_key" = $1 /*application:test*/1 0.8 ms 0.8 ms 0.8 ms 0 ALTER TABLE "ci_builds_runner_session" RENAME COLUMN "build_id" TO "build_id_tmp" /*application:test*/
1 0.3 ms 0.3 ms 0.3 ms 0 ALTER INDEX "index_ci_builds_runner_session_on_build_id_convert_to_bigint" RENAME TO "index_ci_builds_runner_session_on_build_id" /*application:test*/
1 0.2 ms 0.2 ms 0.2 ms 0 ALTER TABLE "ci_builds_runner_session" RENAME COLUMN "build_id_tmp" TO "build_id_convert_to_bigint" /*application:test*/
1 0.2 ms 0.2 ms 0.2 ms 0 ALTER TABLE "ci_builds_runner_session" RENAME COLUMN "build_id_convert_to_bigint" TO "build_id" /*application:test*/
1 0.2 ms 0.2 ms 0.2 ms 0 LOCK TABLE ci_builds, ci_builds_runner_session IN SHARE ROW EXCLUSIVE MODE /*application:test*/
1 0.2 ms 0.2 ms 0.2 ms 0 ALTER TABLE "ci_builds_runner_session" RENAME CONSTRAINT "fk_rails_70707857d3_tmp" TO "fk_rails_70707857d3"/*application:test*/
1 0.2 ms 0.2 ms 0.2 ms 0 LOCK TABLE ci_builds, ci_builds_runner_session IN ACCESS EXCLUSIVE MODE /*application:test*/
1 0.1 ms 0.1 ms 0.1 ms 0 Histogram for FinalizeCiBuildsRunnerSessionBigintConversion
Query Runtime Count 0 seconds - 0.01 seconds 0 0.01 seconds - 0.1 seconds 57 0.1 seconds - 1 second 2 1 second - 5 minutes 1 5 minutes + 0
Other migrations pending on GitLab.com
Migration Type Total runtime Result DB size change Clone Details
Clone ID Clone Created At Clone Data Timestamp Expected Removal Time database-testing-727977
2021-08-05 13:38:57 UTC 2021-08-05 11:59:47 UTC 2021-08-06 01:40:16 +0000 Artifacts
Brought to you by gitlab-org/database-team/gitlab-com-database-testing. Epic
requested review from @alinamihaila
mentioned in epic &4785 (closed)
requested review from @krasio
- Resolved by Diogo Frazão
- Resolved by Patrick Bair
@dfrazao-gitlab Thanks! This looks good. Let's keep it as draft, we may have to do some updates based on the decision we make in https://gitlab.slack.com/archives/CNZ8E900G/p1626885953486800.
@alinamihaila I'll take the database review on this one. Of course feel free to take a look if you want to.
removed review request for @krasio and @alinamihaila
added 1 commit
- 67736e0b - Finalize conversion to bigint for ci_builds_runner_session
added databasereviewed label and removed databaseactive label
unassigned @krasio
- Resolved by Patrick Bair
added workflowin review label and removed workflowin dev label
requested review from @pbair
added 2014 commits
-
67736e0b...e7ea1609 - 2013 commits from branch
master
- 2163f8a6 - Finalize conversion to bigint for ci_builds_runner_session
-
67736e0b...e7ea1609 - 2013 commits from branch
- Resolved by Diogo Frazão
- Resolved by Diogo Frazão
added 1 commit
- 4c40073f - Finalize conversion to bigint for ci_builds_runner_session
added 1 commit
- 5d13cf3d - Finalize conversion to bigint for ci_builds_runner_session
added databaseapproved label and removed databasereviewed label
- Resolved by Patrick Bair
enabled an automatic merge when the pipeline for 06f5cbc7 succeeds
mentioned in commit 94120bc6
added workflowstaging label and removed workflowin review label
added workflowcanary label and removed workflowstaging label
added workflowproduction label and removed workflowcanary label
added releasedcandidate label
added releasedpublished label and removed releasedcandidate label
mentioned in merge request kubitus-project/kubitus-installer!123 (merged)
removed typefeature label
added devopsdata stores label and removed devopssystems label
added groupdatabase frameworks label and removed groupdatabase [DEPRECATED] label
added pipeline:mr-approved label