Finalize job_id conversion to bigint for ci_job_artifacts

Merged Diogo Frazão requested to merge 325615/finalize_ci_job_artifacts_bigint-part-ii into master

What does this MR do?

  • Migrate job_id foreign key to a new column
  • Migrate indexes to use the job_id_convert_to_bigint column
  • Rename job_id_convert_to_bigint column to their original name
  • Regenerate trigger

Database Review

Migration Output

Up
== 20210809143931 FinalizeJobIdConversionToBigintForCiJobArtifacts: migrating =
-- transaction_open?()
   -> 0.0000s
-- index_exists?("ci_job_artifacts", [:expire_at, :job_id_convert_to_bigint], {:name=>"index_ci_job_artifacts_on_expire_at_and_job_id_bigint", :algorithm=>:concurrently})
   -> 0.0039s
-- execute("SET statement_timeout TO 0")
   -> 0.0005s
-- add_index("ci_job_artifacts", [:expire_at, :job_id_convert_to_bigint], {:name=>"index_ci_job_artifacts_on_expire_at_and_job_id_bigint", :algorithm=>:concurrently})
   -> 0.0031s
-- execute("RESET ALL")
   -> 0.0005s
-- transaction_open?()
   -> 0.0000s
-- index_exists?("ci_job_artifacts", [:job_id_convert_to_bigint, :file_type], {:name=>"index_ci_job_artifacts_on_job_id_and_file_type_bigint", :unique=>true, :algorithm=>:concurrently})
   -> 0.0030s
-- add_index("ci_job_artifacts", [:job_id_convert_to_bigint, :file_type], {:name=>"index_ci_job_artifacts_on_job_id_and_file_type_bigint", :unique=>true, :algorithm=>:concurrently})
   -> 0.0022s
-- transaction_open?()
   -> 0.0000s
-- foreign_keys("ci_job_artifacts")
   -> 0.0029s
-- execute("ALTER TABLE ci_job_artifacts\nADD CONSTRAINT fk_rails_c5137cb2c1_tmp\nFOREIGN KEY (job_id_convert_to_bigint)\nREFERENCES ci_builds (id)\nON DELETE CASCADE\nNOT VALID;\n")
   -> 0.0020s
-- execute("ALTER TABLE ci_job_artifacts VALIDATE CONSTRAINT fk_rails_c5137cb2c1_tmp;")
   -> 0.0054s
-- execute("LOCK TABLE ci_job_artifacts, ci_builds IN ACCESS EXCLUSIVE MODE")
   -> 0.0005s
-- quote_table_name("ci_job_artifacts")
   -> 0.0000s
-- quote_column_name(:job_id)
   -> 0.0000s
-- quote_column_name("job_id_tmp")
   -> 0.0000s
-- execute("ALTER TABLE \"ci_job_artifacts\" RENAME COLUMN \"job_id\" TO \"job_id_tmp\"")
   -> 0.0006s
-- quote_table_name("ci_job_artifacts")
   -> 0.0000s
-- quote_column_name(:job_id_convert_to_bigint)
   -> 0.0000s
-- quote_column_name(:job_id)
   -> 0.0000s
-- execute("ALTER TABLE \"ci_job_artifacts\" RENAME COLUMN \"job_id_convert_to_bigint\" TO \"job_id\"")
   -> 0.0006s
-- quote_table_name("ci_job_artifacts")
   -> 0.0000s
-- quote_column_name("job_id_tmp")
   -> 0.0000s
-- quote_column_name(:job_id_convert_to_bigint)
   -> 0.0000s
-- execute("ALTER TABLE \"ci_job_artifacts\" RENAME COLUMN \"job_id_tmp\" TO \"job_id_convert_to_bigint\"")
   -> 0.0005s
-- quote_table_name("trigger_be1804f21693")
   -> 0.0000s
-- execute("ALTER FUNCTION \"trigger_be1804f21693\" RESET ALL")
   -> 0.0006s
-- change_column_default("ci_job_artifacts", :job_id, nil)
   -> 0.0020s
-- change_column_default("ci_job_artifacts", :job_id_convert_to_bigint, 0)
   -> 0.0019s
-- execute("DROP INDEX index_ci_job_artifacts_on_expire_at_and_job_id")
   -> 0.0007s
-- rename_index("ci_job_artifacts", "index_ci_job_artifacts_on_expire_at_and_job_id_bigint", "index_ci_job_artifacts_on_expire_at_and_job_id")
   -> 0.0006s
-- execute("DROP INDEX index_ci_job_artifacts_on_job_id_and_file_type")
   -> 0.0006s
-- rename_index("ci_job_artifacts", "index_ci_job_artifacts_on_job_id_and_file_type_bigint", "index_ci_job_artifacts_on_job_id_and_file_type")
   -> 0.0006s
-- remove_foreign_key("ci_job_artifacts", {:name=>"fk_rails_c5137cb2c1"})
   -> 0.0027s
-- quote_table_name("ci_job_artifacts")
   -> 0.0000s
-- quote_column_name("fk_rails_c5137cb2c1_tmp")
   -> 0.0000s
-- quote_column_name("fk_rails_c5137cb2c1")
   -> 0.0000s
-- execute("ALTER TABLE \"ci_job_artifacts\"\nRENAME CONSTRAINT \"fk_rails_c5137cb2c1_tmp\" TO \"fk_rails_c5137cb2c1\"\n")
   -> 0.0005s
== 20210809143931 FinalizeJobIdConversionToBigintForCiJobArtifacts: migrated (0.0764s)
Down
== 20210809143931 FinalizeJobIdConversionToBigintForCiJobArtifacts: reverting =
-- transaction_open?()
   -> 0.0000s
-- index_exists?("ci_job_artifacts", [:expire_at, :job_id_convert_to_bigint], {:name=>"index_ci_job_artifacts_on_expire_at_and_job_id_bigint", :algorithm=>:concurrently})
   -> 0.0038s
-- execute("SET statement_timeout TO 0")
   -> 0.0005s
-- add_index("ci_job_artifacts", [:expire_at, :job_id_convert_to_bigint], {:name=>"index_ci_job_artifacts_on_expire_at_and_job_id_bigint", :algorithm=>:concurrently})
   -> 0.0029s
-- execute("RESET ALL")
   -> 0.0005s
-- transaction_open?()
   -> 0.0000s
-- index_exists?("ci_job_artifacts", [:job_id_convert_to_bigint, :file_type], {:name=>"index_ci_job_artifacts_on_job_id_and_file_type_bigint", :unique=>true, :algorithm=>:concurrently})
   -> 0.0028s
-- add_index("ci_job_artifacts", [:job_id_convert_to_bigint, :file_type], {:name=>"index_ci_job_artifacts_on_job_id_and_file_type_bigint", :unique=>true, :algorithm=>:concurrently})
   -> 0.0019s
-- transaction_open?()
   -> 0.0000s
-- foreign_keys("ci_job_artifacts")
   -> 0.0029s
-- execute("ALTER TABLE ci_job_artifacts\nADD CONSTRAINT fk_rails_c5137cb2c1_tmp\nFOREIGN KEY (job_id_convert_to_bigint)\nREFERENCES ci_builds (id)\nON DELETE CASCADE\nNOT VALID;\n")
   -> 0.0015s
-- execute("ALTER TABLE ci_job_artifacts VALIDATE CONSTRAINT fk_rails_c5137cb2c1_tmp;")
   -> 0.0050s
-- execute("LOCK TABLE ci_job_artifacts, ci_builds IN ACCESS EXCLUSIVE MODE")
   -> 0.0005s
-- quote_table_name("ci_job_artifacts")
   -> 0.0000s
-- quote_column_name(:job_id)
   -> 0.0000s
-- quote_column_name("job_id_tmp")
   -> 0.0000s
-- execute("ALTER TABLE \"ci_job_artifacts\" RENAME COLUMN \"job_id\" TO \"job_id_tmp\"")
   -> 0.0006s
-- quote_table_name("ci_job_artifacts")
   -> 0.0000s
-- quote_column_name(:job_id_convert_to_bigint)
   -> 0.0000s
-- quote_column_name(:job_id)
   -> 0.0000s
-- execute("ALTER TABLE \"ci_job_artifacts\" RENAME COLUMN \"job_id_convert_to_bigint\" TO \"job_id\"")
   -> 0.0005s
-- quote_table_name("ci_job_artifacts")
   -> 0.0000s
-- quote_column_name("job_id_tmp")
   -> 0.0000s
-- quote_column_name(:job_id_convert_to_bigint)
   -> 0.0000s
-- execute("ALTER TABLE \"ci_job_artifacts\" RENAME COLUMN \"job_id_tmp\" TO \"job_id_convert_to_bigint\"")
   -> 0.0005s
-- quote_table_name("trigger_be1804f21693")
   -> 0.0000s
-- execute("ALTER FUNCTION \"trigger_be1804f21693\" RESET ALL")
   -> 0.0005s
-- change_column_default("ci_job_artifacts", :job_id, nil)
   -> 0.0021s
-- change_column_default("ci_job_artifacts", :job_id_convert_to_bigint, 0)
   -> 0.0019s
-- execute("DROP INDEX index_ci_job_artifacts_on_expire_at_and_job_id")
   -> 0.0006s
-- rename_index("ci_job_artifacts", "index_ci_job_artifacts_on_expire_at_and_job_id_bigint", "index_ci_job_artifacts_on_expire_at_and_job_id")
   -> 0.0006s
-- execute("DROP INDEX index_ci_job_artifacts_on_job_id_and_file_type")
   -> 0.0006s
-- rename_index("ci_job_artifacts", "index_ci_job_artifacts_on_job_id_and_file_type_bigint", "index_ci_job_artifacts_on_job_id_and_file_type")
   -> 0.0005s
-- remove_foreign_key("ci_job_artifacts", {:name=>"fk_rails_c5137cb2c1"})
   -> 0.0031s
-- quote_table_name("ci_job_artifacts")
   -> 0.0000s
-- quote_column_name("fk_rails_c5137cb2c1_tmp")
   -> 0.0000s
-- quote_column_name("fk_rails_c5137cb2c1")
   -> 0.0000s
-- execute("ALTER TABLE \"ci_job_artifacts\"\nRENAME CONSTRAINT \"fk_rails_c5137cb2c1_tmp\" TO \"fk_rails_c5137cb2c1\"\n")
   -> 0.0006s
== 20210809143931 FinalizeJobIdConversionToBigintForCiJobArtifacts: reverted (0.0499s)

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

Relates to #325615 (closed)

Edited by Diogo Frazão