Finalize conversion to bigint for ci_build_needs

Merged Krasimir Angelov requested to merge 328046-finalize-ci-builds-needs-bigint-conversion into master

What does this MR do?

This MR finalize the conversion to bigint for ci_build_needs table.

At a high level, the operation takes the following steps:

  1. Ensure the migration of build_id to build_id_convert_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.

Database migrations

Timings

From DB Lab:

  1. CREATE UNIQUE INDEX CONCURRENTLY "index_ci_build_needs_on_build_id_convert_to_bigint_and_name" ON "ci_build_needs" ("build_id_convert_to_bigint", "name")

    Duration: 14.934 min - https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/4944/commands/17310

  2. ALTER TABLE ci_build_needs VALIDATE CONSTRAINT fk_rails_3cf221d4ed_tmp

    Duration: 120.246 min - https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/4944/commands/17470

Output

Up
$ bundle exec rails db:migrate:up VERSION=20210701033622
== 20210701033622 FinalizeCiBuildsNeedsBigintConversion: migrating ============
-- transaction_open?()
   -> 0.0000s
-- index_exists?("ci_build_needs", [:build_id_convert_to_bigint, :name], {:unique=>true, :name=>"index_ci_build_needs_on_build_id_convert_to_bigint_and_name", :algorithm=>:concurrently})
   -> 0.0025s
-- execute("SET statement_timeout TO 0")
   -> 0.0008s
-- add_index("ci_build_needs", [:build_id_convert_to_bigint, :name], {:unique=>true, :name=>"index_ci_build_needs_on_build_id_convert_to_bigint_and_name", :algorithm=>:concurrently})
   -> 0.0101s
-- execute("RESET ALL")
   -> 0.0007s
-- transaction_open?()
   -> 0.0000s
-- foreign_keys("ci_build_needs")
   -> 0.0043s
-- execute("LOCK TABLE ci_builds, ci_build_needs IN SHARE ROW EXCLUSIVE MODE")
   -> 0.0008s
-- execute("ALTER TABLE ci_build_needs\nADD CONSTRAINT fk_rails_3cf221d4ed_tmp\nFOREIGN KEY (build_id_convert_to_bigint)\nREFERENCES ci_builds (id)\nON DELETE CASCADE\nNOT VALID;\n")
   -> 0.0043s
-- execute("ALTER TABLE ci_build_needs VALIDATE CONSTRAINT fk_rails_3cf221d4ed_tmp;")
   -> 0.0179s
-- execute("LOCK TABLE ci_builds, ci_build_needs IN ACCESS EXCLUSIVE MODE")
   -> 0.0008s
-- quote_table_name("ci_build_needs")
   -> 0.0000s
-- quote_column_name(:build_id)
   -> 0.0000s
-- quote_column_name("build_id_tmp")
   -> 0.0000s
-- execute("ALTER TABLE \"ci_build_needs\" RENAME COLUMN \"build_id\" TO \"build_id_tmp\"")
   -> 0.0008s
-- quote_table_name("ci_build_needs")
   -> 0.0000s
-- quote_column_name(:build_id_convert_to_bigint)
   -> 0.0000s
-- quote_column_name(:build_id)
   -> 0.0000s
-- execute("ALTER TABLE \"ci_build_needs\" RENAME COLUMN \"build_id_convert_to_bigint\" TO \"build_id\"")
   -> 0.0008s
-- quote_table_name("ci_build_needs")
   -> 0.0000s
-- quote_column_name("build_id_tmp")
   -> 0.0000s
-- quote_column_name(:build_id_convert_to_bigint)
   -> 0.0000s
-- execute("ALTER TABLE \"ci_build_needs\" RENAME COLUMN \"build_id_tmp\" TO \"build_id_convert_to_bigint\"")
   -> 0.0008s
-- quote_table_name("trigger_21e7a2602957")
   -> 0.0000s
-- execute("ALTER FUNCTION \"trigger_21e7a2602957\" RESET ALL")
   -> 0.0008s
-- change_column_default("ci_build_needs", :build_id, nil)
   -> 0.0022s
-- change_column_default("ci_build_needs", :build_id_convert_to_bigint, 0)
   -> 0.0033s
-- execute("DROP INDEX index_ci_build_needs_on_build_id_and_name")
   -> 0.0008s
-- rename_index("ci_build_needs", "index_ci_build_needs_on_build_id_convert_to_bigint_and_name", "index_ci_build_needs_on_build_id_and_name")
   -> 0.0008s
-- remove_foreign_key("ci_build_needs", {:name=>"fk_rails_3cf221d4ed"})
   -> 0.0042s
-- quote_table_name("ci_build_needs")
   -> 0.0000s
-- quote_column_name("fk_rails_3cf221d4ed_tmp")
   -> 0.0000s
-- quote_column_name("fk_rails_3cf221d4ed")
   -> 0.0000s
-- execute("ALTER TABLE \"ci_build_needs\"\nRENAME CONSTRAINT \"fk_rails_3cf221d4ed_tmp\" TO \"fk_rails_3cf221d4ed\"\n")
   -> 0.0007s
== 20210701033622 FinalizeCiBuildsNeedsBigintConversion: migrated (0.0961s) ===
Down
$ bundle exec rails db:migrate:down VERSION=20210701033622
== 20210701033622 FinalizeCiBuildsNeedsBigintConversion: reverting ============
-- transaction_open?()
   -> 0.0000s
-- index_exists?("ci_build_needs", [:build_id_convert_to_bigint, :name], {:unique=>true, :name=>"index_ci_build_needs_on_build_id_convert_to_bigint_and_name", :algorithm=>:concurrently})
   -> 0.0032s
-- execute("SET statement_timeout TO 0")
   -> 0.0006s
-- add_index("ci_build_needs", [:build_id_convert_to_bigint, :name], {:unique=>true, :name=>"index_ci_build_needs_on_build_id_convert_to_bigint_and_name", :algorithm=>:concurrently})
   -> 0.0041s
-- execute("RESET ALL")
   -> 0.0007s
-- transaction_open?()
   -> 0.0000s
-- foreign_keys("ci_build_needs")
   -> 0.0036s
-- execute("LOCK TABLE ci_builds, ci_build_needs IN SHARE ROW EXCLUSIVE MODE")
   -> 0.0008s
-- execute("ALTER TABLE ci_build_needs\nADD CONSTRAINT fk_rails_3cf221d4ed_tmp\nFOREIGN KEY (build_id_convert_to_bigint)\nREFERENCES ci_builds (id)\nON DELETE CASCADE\nNOT VALID;\n")
   -> 0.0020s
-- execute("ALTER TABLE ci_build_needs VALIDATE CONSTRAINT fk_rails_3cf221d4ed_tmp;")
   -> 0.0057s
-- execute("LOCK TABLE ci_builds, ci_build_needs IN ACCESS EXCLUSIVE MODE")
   -> 0.0008s
-- quote_table_name("ci_build_needs")
   -> 0.0000s
-- quote_column_name(:build_id)
   -> 0.0000s
-- quote_column_name("build_id_tmp")
   -> 0.0000s
-- execute("ALTER TABLE \"ci_build_needs\" RENAME COLUMN \"build_id\" TO \"build_id_tmp\"")
   -> 0.0009s
-- quote_table_name("ci_build_needs")
   -> 0.0000s
-- quote_column_name(:build_id_convert_to_bigint)
   -> 0.0000s
-- quote_column_name(:build_id)
   -> 0.0000s
-- execute("ALTER TABLE \"ci_build_needs\" RENAME COLUMN \"build_id_convert_to_bigint\" TO \"build_id\"")
   -> 0.0010s
-- quote_table_name("ci_build_needs")
   -> 0.0000s
-- quote_column_name("build_id_tmp")
   -> 0.0000s
-- quote_column_name(:build_id_convert_to_bigint)
   -> 0.0000s
-- execute("ALTER TABLE \"ci_build_needs\" RENAME COLUMN \"build_id_tmp\" TO \"build_id_convert_to_bigint\"")
   -> 0.0010s
-- quote_table_name("trigger_21e7a2602957")
   -> 0.0001s
-- execute("ALTER FUNCTION \"trigger_21e7a2602957\" RESET ALL")
   -> 0.0009s
-- change_column_default("ci_build_needs", :build_id, nil)
   -> 0.0027s
-- change_column_default("ci_build_needs", :build_id_convert_to_bigint, 0)
   -> 0.0022s
-- execute("DROP INDEX index_ci_build_needs_on_build_id_and_name")
   -> 0.0008s
-- rename_index("ci_build_needs", "index_ci_build_needs_on_build_id_convert_to_bigint_and_name", "index_ci_build_needs_on_build_id_and_name")
   -> 0.0010s
-- remove_foreign_key("ci_build_needs", {:name=>"fk_rails_3cf221d4ed"})
   -> 0.0040s
-- quote_table_name("ci_build_needs")
   -> 0.0000s
-- quote_column_name("fk_rails_3cf221d4ed_tmp")
   -> 0.0000s
-- quote_column_name("fk_rails_3cf221d4ed")
   -> 0.0000s
-- execute("ALTER TABLE \"ci_build_needs\"\nRENAME CONSTRAINT \"fk_rails_3cf221d4ed_tmp\" TO \"fk_rails_3cf221d4ed\"\n")
   -> 0.0008s
== 20210701033622 FinalizeCiBuildsNeedsBigintConversion: reverted (0.0555s) ===

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

Edited by Krasimir Angelov