Finalize ci_job_artifacts conversion to bigint

Merged Alex Ives requested to merge 325615/finalize_ci_job_artifacts_bigint into master

What does this MR do?

  • Migrate all foreign keys to new column
  • Migrate indexes to use new columns
  • Rename columns to their original names

Relates to #325615 (closed)

Migration information:

This migration has the required indexes already created as part of !67532 (merged), so the add_concurrent_index statements should be a no-op on GitLab.com environments.

Verifying the indexes already exist in production:

gitlabhq_production=> \d ci_job_artifacts
                                              Table "public.ci_job_artifacts"
          Column          |           Type           | Collation | Nullable |                   Default
--------------------------+--------------------------+-----------+----------+----------------------------------------------
 id                       | integer                  |           | not null | nextval('ci_job_artifacts_id_seq'::regclass)
 project_id               | integer                  |           | not null |
 job_id_convert_to_bigint | integer                  |           | not null | 0
 file_type                | integer                  |           | not null |
 size                     | bigint                   |           |          |
 created_at               | timestamp with time zone |           | not null |
 updated_at               | timestamp with time zone |           | not null |
 expire_at                | timestamp with time zone |           |          |
 file                     | character varying        |           |          |
 file_store               | integer                  |           |          | 1
 file_sha256              | bytea                    |           |          |
 file_format              | smallint                 |           |          |
 file_location            | smallint                 |           |          |
 id_convert_to_bigint     | bigint                   |           | not null | 0
 job_id                   | bigint                   |           | not null |
Indexes:
    "ci_job_artifacts_pkey" PRIMARY KEY, btree (id)
    "index_ci_job_artifact_on_id_convert_to_bigint" UNIQUE, btree (id_convert_to_bigint)
    "index_ci_job_artifacts_on_job_id_and_file_type" UNIQUE, btree (job_id, file_type)
    "index_ci_job_artifacts_for_terraform_reports" btree (project_id, id) WHERE file_type = 18
    "index_ci_job_artifacts_for_terraform_reports_bigint" btree (project_id, id_convert_to_bigint) WHERE file_type = 18
    "index_ci_job_artifacts_id_for_terraform_reports" btree (id) WHERE file_type = 18
    "index_ci_job_artifacts_id_for_terraform_reports_bigint" btree (id_convert_to_bigint) WHERE file_type = 18
    "index_ci_job_artifacts_on_expire_at_and_job_id" btree (expire_at, job_id)
    "index_ci_job_artifacts_on_file_store" btree (file_store)
    "index_ci_job_artifacts_on_file_type_for_devops_adoption" btree (file_type, project_id, created_at) WHERE file_type = ANY (ARRAY[5, 6, 8, 23])
    "index_ci_job_artifacts_on_project_id" btree (project_id)
    "index_ci_job_artifacts_on_project_id_for_security_reports" btree (project_id) WHERE file_type = ANY (ARRAY[5, 6, 7, 8])
Check constraints:
    "check_27f0f6dbab" CHECK (file_store IS NOT NULL)
Foreign-key constraints:
    "fk_rails_9862d392f9" FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
    "fk_rails_c5137cb2c1" FOREIGN KEY (job_id) REFERENCES ci_builds(id) ON DELETE CASCADE
Referenced by:
    TABLE "project_pages_metadata" CONSTRAINT "fk_69366a119e" FOREIGN KEY (artifacts_archive_id) REFERENCES ci_job_artifacts(id) ON DELETE SET NULL
Triggers:
    trigger_be1804f21693 BEFORE INSERT OR UPDATE ON ci_job_artifacts FOR EACH ROW EXECUTE FUNCTION trigger_be1804f21693()

Timing for adding and validating the foreign key:

pbair@localhost:6022/gitlabhq_dblab# ALTER TABLE project_pages_metadata ADD CONSTRAINT fk_69366a119e_tmp FOREIGN KEY (artifacts_archive_id) REFERENCES ci_job_artifacts (id_convert_to_bigint) ON DELETE SET NULL NOT VALID;
ALTER TABLE
Time: 139.639 ms

pbair@localhost:6022/gitlabhq_dblab# ALTER TABLE project_pages_metadata VALIDATE CONSTRAINT fk_69366a119e_tmp;
ALTER TABLE
Time: 194779.757 ms (03:14.780)

The remaining operations in the with_lock_retries block are metadata-only operations, and should execute quickly. The addition of the new primary key will use the existing unique index, so should also be a metadata-only operation:

pbair@localhost:6022/gitlabhq_dblab*# ALTER TABLE ci_job_artifacts ADD CONSTRAINT ci_job_artifacts_pkey PRIMARY KEY USING INDEX ci_job_artifacts_pkey;
ALTER TABLE
Time: 76.077 ms

Does this MR meet the acceptance criteria?

Conformity

Availability and Testing

Database Review

Migration Output

Up
== 20210706212710 FinalizeCiJobArtifactsBigintConversion: migrating ===========
-- transaction_open?()
   -> 0.0000s
-- index_exists?("ci_job_artifacts", :id_convert_to_bigint, {:unique=>true, :name=>"index_ci_job_artifact_on_id_convert_to_bigint", :algorithm=>:concurrently})
   -> 0.0036s
-- execute("SET statement_timeout TO 0")
   -> 0.0004s
-- add_index("ci_job_artifacts", :id_convert_to_bigint, {:unique=>true, :name=>"index_ci_job_artifact_on_id_convert_to_bigint", :algorithm=>:concurrently})
   -> 0.0030s
-- execute("RESET ALL")
   -> 0.0005s
-- transaction_open?()
   -> 0.0000s
-- index_exists?("ci_job_artifacts", [:project_id, :id_convert_to_bigint], {:name=>"index_ci_job_artifacts_for_terraform_reports_bigint", :where=>"file_type = 18", :algorithm=>:concurrently})
   -> 0.0028s
-- add_index("ci_job_artifacts", [:project_id, :id_convert_to_bigint], {:name=>"index_ci_job_artifacts_for_terraform_reports_bigint", :where=>"file_type = 18", :algorithm=>:concurrently})
   -> 0.0019s
-- transaction_open?()
   -> 0.0000s
-- index_exists?("ci_job_artifacts", [:id_convert_to_bigint], {:name=>"index_ci_job_artifacts_id_for_terraform_reports_bigint", :where=>"file_type = 18", :algorithm=>:concurrently})
   -> 0.0034s
-- add_index("ci_job_artifacts", [:id_convert_to_bigint], {:name=>"index_ci_job_artifacts_id_for_terraform_reports_bigint", :where=>"file_type = 18", :algorithm=>:concurrently})
   -> 0.0022s
-- transaction_open?()
   -> 0.0000s
-- foreign_keys(:project_pages_metadata)
   -> 0.0027s
-- execute("ALTER TABLE project_pages_metadata\nADD CONSTRAINT fk_69366a119e_tmp\nFOREIGN KEY (artifacts_archive_id)\nREFERENCES ci_job_artifacts (id_convert_to_bigint)\nON DELETE SET NULL\nNOT VALID;\n")
   -> 0.0018s
-- execute("ALTER TABLE project_pages_metadata VALIDATE CONSTRAINT fk_69366a119e_tmp;")
   -> 0.0027s
-- execute("LOCK TABLE ci_job_artifacts, project_pages_metadata IN ACCESS EXCLUSIVE MODE")
   -> 0.0005s
-- quote_table_name("ci_job_artifacts")
   -> 0.0000s
-- quote_column_name(:id)
   -> 0.0000s
-- quote_column_name("id_tmp")
   -> 0.0000s
-- execute("ALTER TABLE \"ci_job_artifacts\" RENAME COLUMN \"id\" TO \"id_tmp\"")
   -> 0.0006s
-- quote_table_name("ci_job_artifacts")
   -> 0.0000s
-- quote_column_name(:id_convert_to_bigint)
   -> 0.0000s
-- quote_column_name(:id)
   -> 0.0000s
-- execute("ALTER TABLE \"ci_job_artifacts\" RENAME COLUMN \"id_convert_to_bigint\" TO \"id\"")
   -> 0.0005s
-- quote_table_name("ci_job_artifacts")
   -> 0.0000s
-- quote_column_name("id_tmp")
   -> 0.0000s
-- quote_column_name(:id_convert_to_bigint)
   -> 0.0000s
-- execute("ALTER TABLE \"ci_job_artifacts\" RENAME COLUMN \"id_tmp\" TO \"id_convert_to_bigint\"")
   -> 0.0005s
-- quote_table_name("trigger_be1804f21693")
   -> 0.0000s
-- execute("ALTER FUNCTION \"trigger_be1804f21693\" RESET ALL")
   -> 0.0006s
-- execute("ALTER SEQUENCE ci_job_artifacts_id_seq OWNED BY ci_job_artifacts.id")
   -> 0.0006s
-- change_column_default("ci_job_artifacts", :id, #)
   -> 0.0024s
-- change_column_default("ci_job_artifacts", :id_convert_to_bigint, 0)
   -> 0.0019s
-- execute("ALTER TABLE ci_job_artifacts DROP CONSTRAINT ci_job_artifacts_pkey CASCADE")
NOTICE:  drop cascades to constraint fk_69366a119e on table project_pages_metadata
   -> 0.0009s
-- rename_index("ci_job_artifacts", "index_ci_job_artifact_on_id_convert_to_bigint", "ci_job_artifacts_pkey")
   -> 0.0006s
-- execute("ALTER TABLE ci_job_artifacts ADD CONSTRAINT ci_job_artifacts_pkey PRIMARY KEY USING INDEX ci_job_artifacts_pkey")
   -> 0.0006s
-- execute("DROP INDEX index_ci_job_artifacts_for_terraform_reports")
   -> 0.0007s
-- rename_index("ci_job_artifacts", "index_ci_job_artifacts_for_terraform_reports_bigint", "index_ci_job_artifacts_for_terraform_reports")
   -> 0.0006s
-- execute("DROP INDEX index_ci_job_artifacts_id_for_terraform_reports")
   -> 0.0006s
-- rename_index("ci_job_artifacts", "index_ci_job_artifacts_id_for_terraform_reports_bigint", "index_ci_job_artifacts_id_for_terraform_reports")
   -> 0.0006s
-- quote_table_name(:project_pages_metadata)
   -> 0.0000s
-- quote_column_name("fk_69366a119e_tmp")
   -> 0.0000s
-- quote_column_name("fk_69366a119e")
   -> 0.0000s
-- execute("ALTER TABLE \"project_pages_metadata\"\nRENAME CONSTRAINT \"fk_69366a119e_tmp\" TO \"fk_69366a119e\"\n")
   -> 0.0005s
== 20210706212710 FinalizeCiJobArtifactsBigintConversion: migrated (0.0789s) ==
Down
== 20210706212710 FinalizeCiJobArtifactsBigintConversion: reverting ===========
-- transaction_open?()
   -> 0.0000s
-- index_exists?("ci_job_artifacts", :id_convert_to_bigint, {:unique=>true, :name=>"index_ci_job_artifact_on_id_convert_to_bigint", :algorithm=>:concurrently})
   -> 0.0063s
-- execute("SET statement_timeout TO 0")
   -> 0.0008s
-- add_index("ci_job_artifacts", :id_convert_to_bigint, {:unique=>true, :name=>"index_ci_job_artifact_on_id_convert_to_bigint", :algorithm=>:concurrently})
   -> 0.0048s
-- execute("RESET ALL")
   -> 0.0008s
-- transaction_open?()
   -> 0.0000s
-- index_exists?("ci_job_artifacts", [:project_id, :id_convert_to_bigint], {:name=>"index_ci_job_artifacts_for_terraform_reports_bigint", :where=>"file_type = 18", :algorithm=>:concurrently})
   -> 0.0046s
-- add_index("ci_job_artifacts", [:project_id, :id_convert_to_bigint], {:name=>"index_ci_job_artifacts_for_terraform_reports_bigint", :where=>"file_type = 18", :algorithm=>:concurrently})
   -> 0.0032s
-- transaction_open?()
   -> 0.0000s
-- index_exists?("ci_job_artifacts", [:id_convert_to_bigint], {:name=>"index_ci_job_artifacts_id_for_terraform_reports_bigint", :where=>"file_type = 18", :algorithm=>:concurrently})
   -> 0.0040s
-- add_index("ci_job_artifacts", [:id_convert_to_bigint], {:name=>"index_ci_job_artifacts_id_for_terraform_reports_bigint", :where=>"file_type = 18", :algorithm=>:concurrently})
   -> 0.0023s
-- transaction_open?()
   -> 0.0000s
-- foreign_keys(:project_pages_metadata)
   -> 0.0039s
-- execute("ALTER TABLE project_pages_metadata\nADD CONSTRAINT fk_69366a119e_tmp\nFOREIGN KEY (artifacts_archive_id)\nREFERENCES ci_job_artifacts (id_convert_to_bigint)\nON DELETE SET NULL\nNOT VALID;\n")
   -> 0.0036s
-- execute("ALTER TABLE project_pages_metadata VALIDATE CONSTRAINT fk_69366a119e_tmp;")
   -> 0.0045s
-- execute("LOCK TABLE ci_job_artifacts, project_pages_metadata IN ACCESS EXCLUSIVE MODE")
   -> 0.0007s
-- quote_table_name("ci_job_artifacts")
   -> 0.0000s
-- quote_column_name(:id)
   -> 0.0000s
-- quote_column_name("id_tmp")
   -> 0.0000s
-- execute("ALTER TABLE \"ci_job_artifacts\" RENAME COLUMN \"id\" TO \"id_tmp\"")
   -> 0.0008s
-- quote_table_name("ci_job_artifacts")
   -> 0.0001s
-- quote_column_name(:id_convert_to_bigint)
   -> 0.0000s
-- quote_column_name(:id)
   -> 0.0000s
-- execute("ALTER TABLE \"ci_job_artifacts\" RENAME COLUMN \"id_convert_to_bigint\" TO \"id\"")
   -> 0.0008s
-- quote_table_name("ci_job_artifacts")
   -> 0.0000s
-- quote_column_name("id_tmp")
   -> 0.0000s
-- quote_column_name(:id_convert_to_bigint)
   -> 0.0000s
-- execute("ALTER TABLE \"ci_job_artifacts\" RENAME COLUMN \"id_tmp\" TO \"id_convert_to_bigint\"")
   -> 0.0008s
-- quote_table_name("trigger_be1804f21693")
   -> 0.0000s
-- execute("ALTER FUNCTION \"trigger_be1804f21693\" RESET ALL")
   -> 0.0008s
-- execute("ALTER SEQUENCE ci_job_artifacts_id_seq OWNED BY ci_job_artifacts.id")
   -> 0.0015s
-- change_column_default("ci_job_artifacts", :id, #)
   -> 0.0036s
-- change_column_default("ci_job_artifacts", :id_convert_to_bigint, 0)
   -> 0.0043s
-- execute("ALTER TABLE ci_job_artifacts DROP CONSTRAINT ci_job_artifacts_pkey CASCADE")
NOTICE:  drop cascades to constraint fk_69366a119e on table project_pages_metadata
   -> 0.0019s
-- rename_index("ci_job_artifacts", "index_ci_job_artifact_on_id_convert_to_bigint", "ci_job_artifacts_pkey")
   -> 0.0019s
-- execute("ALTER TABLE ci_job_artifacts ADD CONSTRAINT ci_job_artifacts_pkey PRIMARY KEY USING INDEX ci_job_artifacts_pkey")
   -> 0.0021s
-- execute("DROP INDEX index_ci_job_artifacts_for_terraform_reports")
   -> 0.0012s
-- rename_index("ci_job_artifacts", "index_ci_job_artifacts_for_terraform_reports_bigint", "index_ci_job_artifacts_for_terraform_reports")
   -> 0.0024s
-- execute("DROP INDEX index_ci_job_artifacts_id_for_terraform_reports")
   -> 0.0011s
-- rename_index("ci_job_artifacts", "index_ci_job_artifacts_id_for_terraform_reports_bigint", "index_ci_job_artifacts_id_for_terraform_reports")
   -> 0.0013s
-- quote_table_name(:project_pages_metadata)
   -> 0.0000s
-- quote_column_name("fk_69366a119e_tmp")
   -> 0.0000s
-- quote_column_name("fk_69366a119e")
   -> 0.0000s
-- execute("ALTER TABLE \"project_pages_metadata\"\nRENAME CONSTRAINT \"fk_69366a119e_tmp\" TO \"fk_69366a119e\"\n")
   -> 0.0008s
== 20210706212710 FinalizeCiJobArtifactsBigintConversion: reverted (0.0888s) ==
Edited by Patrick Bair