Skip to content
Snippets Groups Projects

Resolve "Convert ci_builds_runner_session.build_id to bigint - Step 2: Finalize background migrations and swap columns"

All threads resolved!

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:

  1. Ensure the migration of build_id to bigint is completed.
  2. Copy index and FK
  3. 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:

  1. 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

  2. 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

Availability and Testing

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)

Edited by Diogo Frazão

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
  • Krasimir Angelov removed review request for @krasio and @alinamihaila

    removed review request for @krasio and @alinamihaila

  • Diogo Frazão added 1 commit

    added 1 commit

    • 67736e0b - Finalize conversion to bigint for ci_builds_runner_session

    Compare with previous version

  • Diogo Frazão changed the description

    changed the description

  • Krasimir Angelov approved this merge request

    approved this merge request

  • added databasereviewed label and removed databaseactive label

    • Resolved by Patrick Bair

      :wave: @krasio, thanks for approving this merge request.

      Please consider starting a new pipeline if:

      • This is the first time the merge request is approved, or
      • The merge request is ready to be merged, and there has not been a merge request pipeline in the last 2 hours.

      For more info, refer to the guideline.

  • added workflowin review label and removed workflowin dev label

  • Patrick Bair requested review from @pbair

    requested review from @pbair

  • Diogo Frazão added 2014 commits

    added 2014 commits

    Compare with previous version

  • Patrick Bair
  • Patrick Bair
  • Diogo Frazão added 1 commit

    added 1 commit

    • 4c40073f - Finalize conversion to bigint for ci_builds_runner_session

    Compare with previous version

  • Diogo Frazão added 1 commit

    added 1 commit

    • 5d13cf3d - Finalize conversion to bigint for ci_builds_runner_session

    Compare with previous version

  • Patrick Bair approved this merge request

    approved this merge request

  • added databaseapproved label and removed databasereviewed label

  • Patrick Bair resolved all threads

    resolved all threads

    • Resolved by Patrick Bair

      :wave: @pbair, thanks for approving this merge request.

      Please consider starting a new pipeline if:

      • This is the first time the merge request is approved, or
      • The merge request is ready to be merged, and there has not been a merge request pipeline in the last 2 hours.

      For more info, refer to the guideline.

  • Patrick Bair resolved all threads

    resolved all threads

  • Patrick Bair enabled an automatic merge when the pipeline for 06f5cbc7 succeeds

    enabled an automatic merge when the pipeline for 06f5cbc7 succeeds

  • merged

  • Patrick Bair mentioned in commit 94120bc6

    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 devopsdata stores label and removed devopssystems label

  • Please register or sign in to reply
    Loading