Finalize conversion of ci_stages.id to bigint

What does this MR do?

This MR finalizes the conversion to bigint for the ci_stages table, following the pattern in !64779 (merged)

It takes the following steps:

• Ensure the migration from id to id_convert_to_bigint is complete
• Create the new indexes and foreign keys for the swap to the bigint column
• Swap the columns

See #327623 (closed)

Database Migration Timing

Timings:

The two required indexes for this have been pre-created on GitLab.com, to speed up execution of the migration. So these two statements should be no-ops when the migration runs on our environments:

• CREATE UNIQUE INDEX CONCURRENTLY "index_ci_stages_on_id_convert_to_bigint" ON "ci_stages" ("id_convert_to_bigint")
  • 46 minutes, https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/5147/commands/18196
• CREATE INDEX CONCURRENTLY "index_ci_stages_on_pipeline_id_and_id_convert_to_bigint" ON "ci_stages" ("pipeline_id", "id_convert_to_bigint") WHERE status in (0, 1, 2, 8, 9, 10)
  • 24 minutes, https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/5147/commands/18200

This statement will still need to be executed:

• ALTER TABLE ci_builds VALIDATE CONSTRAINT fk_3a9eaa254d_tmp
  • 71 minutes, https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/5147/commands/18203

Migration Output

Up

== 20210707210916 FinalizeCiStagesBigintConversion: migrating =================
-- transaction_open?()
   -> 0.0000s
-- index_exists?("ci_stages", :id_convert_to_bigint, {:unique=>true, :name=>"index_ci_stages_on_id_convert_to_bigint", :algorithm=>:concurrently})
   -> 0.0028s
-- execute("SET statement_timeout TO 0")
   -> 0.0005s
-- add_index("ci_stages", :id_convert_to_bigint, {:unique=>true, :name=>"index_ci_stages_on_id_convert_to_bigint", :algorithm=>:concurrently})
   -> 0.0029s
-- execute("RESET ALL")
   -> 0.0005s
-- transaction_open?()
   -> 0.0000s
-- index_exists?("ci_stages", [:pipeline_id, :id_convert_to_bigint], {:name=>"index_ci_stages_on_pipeline_id_and_id_convert_to_bigint", :where=>"status in (0, 1, 2, 8, 9, 10)", :algorithm=>:concurrently})
   -> 0.0022s
-- add_index("ci_stages", [:pipeline_id, :id_convert_to_bigint], {:name=>"index_ci_stages_on_pipeline_id_and_id_convert_to_bigint", :where=>"status in (0, 1, 2, 8, 9, 10)", :algorithm=>:concurrently})
   -> 0.0018s
-- transaction_open?()
   -> 0.0000s
-- foreign_keys(:ci_builds)
   -> 0.0031s
-- execute("LOCK TABLE ci_stages, ci_builds IN SHARE ROW EXCLUSIVE MODE")
   -> 0.0006s
-- execute("ALTER TABLE ci_builds\nADD CONSTRAINT fk_3a9eaa254d_tmp\nFOREIGN KEY (stage_id)\nREFERENCES ci_stages (id_convert_to_bigint)\nON DELETE CASCADE\nNOT VALID;\n")
   -> 0.0016s
-- execute("ALTER TABLE ci_builds VALIDATE CONSTRAINT fk_3a9eaa254d_tmp;")
   -> 0.0051s
-- execute("LOCK TABLE ci_stages, ci_builds IN ACCESS EXCLUSIVE MODE")
   -> 0.0005s
-- quote_column_name("id_tmp")
   -> 0.0000s
-- quote_column_name(:id)
   -> 0.0000s
-- quote_column_name(:id_convert_to_bigint)
   -> 0.0000s
-- execute("ALTER TABLE ci_stages RENAME COLUMN \"id\" TO \"id_tmp\"")
   -> 0.0006s
-- execute("ALTER TABLE ci_stages RENAME COLUMN \"id_convert_to_bigint\" TO \"id\"")
   -> 0.0006s
-- execute("ALTER TABLE ci_stages RENAME COLUMN \"id_tmp\" TO \"id_convert_to_bigint\"")
   -> 0.0007s
-- quote_table_name("trigger_490d204c00b3")
   -> 0.0000s
-- execute("ALTER FUNCTION \"trigger_490d204c00b3\" RESET ALL")
   -> 0.0009s
-- execute("ALTER SEQUENCE ci_stages_id_seq OWNED BY ci_stages.id")
   -> 0.0008s
-- change_column_default("ci_stages", :id, #)
   -> 0.0028s
-- change_column_default("ci_stages", :id_convert_to_bigint, 0)
   -> 0.0034s
-- execute("ALTER TABLE ci_stages DROP CONSTRAINT ci_stages_pkey CASCADE")
   -> 0.0013s
-- rename_index("ci_stages", "index_ci_stages_on_id_convert_to_bigint", "ci_stages_pkey")
   -> 0.0009s
-- execute("ALTER TABLE ci_stages ADD CONSTRAINT ci_stages_pkey PRIMARY KEY USING INDEX ci_stages_pkey")
   -> 0.0010s
-- execute("DROP INDEX index_ci_stages_on_pipeline_id_and_id")
   -> 0.0008s
-- rename_index("ci_stages", "index_ci_stages_on_pipeline_id_and_id_convert_to_bigint", "index_ci_stages_on_pipeline_id_and_id")
   -> 0.0007s
-- quote_table_name(:ci_builds)
   -> 0.0000s
-- quote_column_name("fk_3a9eaa254d_tmp")
   -> 0.0000s
-- quote_column_name("fk_3a9eaa254d")
   -> 0.0000s
-- execute("ALTER TABLE \"ci_builds\"\nRENAME CONSTRAINT \"fk_3a9eaa254d_tmp\" TO \"fk_3a9eaa254d\"\n")
   -> 0.0007s
== 20210707210916 FinalizeCiStagesBigintConversion: migrated (0.0636s) ========

Down

== 20210707210916 FinalizeCiStagesBigintConversion: reverting =================
-- transaction_open?()
   -> 0.0000s
-- index_exists?("ci_stages", :id_convert_to_bigint, {:unique=>true, :name=>"index_ci_stages_on_id_convert_to_bigint", :algorithm=>:concurrently})
   -> 0.0033s
-- execute("SET statement_timeout TO 0")
   -> 0.0005s
-- add_index("ci_stages", :id_convert_to_bigint, {:unique=>true, :name=>"index_ci_stages_on_id_convert_to_bigint", :algorithm=>:concurrently})
   -> 0.0033s
-- execute("RESET ALL")
   -> 0.0006s
-- transaction_open?()
   -> 0.0000s
-- index_exists?("ci_stages", [:pipeline_id, :id_convert_to_bigint], {:name=>"index_ci_stages_on_pipeline_id_and_id_convert_to_bigint", :where=>"status in (0, 1, 2, 8, 9, 10)", :algorithm=>:concurrently})
   -> 0.0022s
-- add_index("ci_stages", [:pipeline_id, :id_convert_to_bigint], {:name=>"index_ci_stages_on_pipeline_id_and_id_convert_to_bigint", :where=>"status in (0, 1, 2, 8, 9, 10)", :algorithm=>:concurrently})
   -> 0.0021s
-- transaction_open?()
   -> 0.0000s
-- foreign_keys(:ci_builds)
   -> 0.0031s
-- execute("LOCK TABLE ci_stages, ci_builds IN SHARE ROW EXCLUSIVE MODE")
   -> 0.0006s
-- execute("ALTER TABLE ci_builds\nADD CONSTRAINT fk_3a9eaa254d_tmp\nFOREIGN KEY (stage_id)\nREFERENCES ci_stages (id_convert_to_bigint)\nON DELETE CASCADE\nNOT VALID;\n")
   -> 0.0014s
-- execute("ALTER TABLE ci_builds VALIDATE CONSTRAINT fk_3a9eaa254d_tmp;")
   -> 0.0049s
-- execute("LOCK TABLE ci_stages, ci_builds IN ACCESS EXCLUSIVE MODE")
   -> 0.0005s
-- quote_column_name("id_tmp")
   -> 0.0000s
-- quote_column_name(:id)
   -> 0.0000s
-- quote_column_name(:id_convert_to_bigint)
   -> 0.0000s
-- execute("ALTER TABLE ci_stages RENAME COLUMN \"id\" TO \"id_tmp\"")
   -> 0.0005s
-- execute("ALTER TABLE ci_stages RENAME COLUMN \"id_convert_to_bigint\" TO \"id\"")
   -> 0.0005s
-- execute("ALTER TABLE ci_stages RENAME COLUMN \"id_tmp\" TO \"id_convert_to_bigint\"")
   -> 0.0005s
-- quote_table_name("trigger_490d204c00b3")
   -> 0.0000s
-- execute("ALTER FUNCTION \"trigger_490d204c00b3\" RESET ALL")
   -> 0.0007s
-- execute("ALTER SEQUENCE ci_stages_id_seq OWNED BY ci_stages.id")
   -> 0.0007s
-- change_column_default("ci_stages", :id, #)
   -> 0.0024s
-- change_column_default("ci_stages", :id_convert_to_bigint, 0)
   -> 0.0017s
-- execute("ALTER TABLE ci_stages DROP CONSTRAINT ci_stages_pkey CASCADE")
   -> 0.0011s
-- rename_index("ci_stages", "index_ci_stages_on_id_convert_to_bigint", "ci_stages_pkey")
   -> 0.0006s
-- execute("ALTER TABLE ci_stages ADD CONSTRAINT ci_stages_pkey PRIMARY KEY USING INDEX ci_stages_pkey")
   -> 0.0006s
-- execute("DROP INDEX index_ci_stages_on_pipeline_id_and_id")
   -> 0.0006s
-- rename_index("ci_stages", "index_ci_stages_on_pipeline_id_and_id_convert_to_bigint", "index_ci_stages_on_pipeline_id_and_id")
   -> 0.0005s
-- quote_table_name(:ci_builds)
   -> 0.0000s
-- quote_column_name("fk_3a9eaa254d_tmp")
   -> 0.0000s
-- quote_column_name("fk_3a9eaa254d")
   -> 0.0000s
-- execute("ALTER TABLE \"ci_builds\"\nRENAME CONSTRAINT \"fk_3a9eaa254d_tmp\" TO \"fk_3a9eaa254d\"\n")
   -> 0.0006s
== 20210707210916 FinalizeCiStagesBigintConversion: reverted (0.0514s) ========

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