Finalize ci_job_artifacts conversion to bigint

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

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

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) ==

db/post_migrate/20210706212710_finalize_ci_job_artifacts_bigint_conversion.rb

@@ -28,6 +28,7 @@ def down
   private
 
   def swap
+    add_concurrent_index TABLE_NAME, :job_id_convert_to_bigint, unique: true, name: 'index_ci_job_artifact_on_job_id_convert_to_bigint'
     # This is to replace the existing "ci_job_artifacts_pkey" PRIMARY KEY, btree (id)
     add_concurrent_index TABLE_NAME, :id_convert_to_bigint, unique: true, name: 'index_ci_job_artifact_on_id_convert_to_bigint'
     # This is to replace the existing "index_ci_job_artifacts_for_terraform_reports" btree (project_id, id) where (file_type = 18)
@@ -48,14 +49,26 @@ def swap
       name: fk_artifacts_archive_id_tmp,
       on_delete: :nullify
 
-    # Add a FK on `job_id_convert_to_bigint` to `ci_builds(id)`, the old FK (fk_rails_c5137cb2c1)
-    # is removed below since it won't be dropped automatically.
-    fk_ci_builds_job_id = concurrent_foreign_key_name(TABLE_NAME, :job_id, prefix: 'fk_rails_')
-    fk_ci_builds_job_id_tmp = "#{fk_ci_builds_job_id}_tmp"
-    add_concurrent_foreign_key TABLE_NAME, :ci_builds,
-      column: :job_id_convert_to_bigint,
-      name: fk_ci_builds_job_id_tmp,
-      on_delete: :cascade
+    # # Add a FK on `job_id_convert_to_bigint` to `ci_builds(id)`, the old FK (fk_rails_c5137cb2c1)
+    # # is removed below since it won't be dropped automatically.
+    # fk_ci_builds_job_id = concurrent_foreign_key_name(TABLE_NAME, :job_id, prefix: 'fk_rails_')
+    # fk_ci_builds_job_id_tmp = "#{fk_ci_builds_job_id}_tmp"
+
+    # add_concurrent_foreign_key TABLE_NAME, :ci_builds,
+    #   column: :job_id_convert_to_bigint,
+    #   name: fk_ci_builds_job_id_tmp,
+    #   on_delete: :cascade
+
+    # Add a foreign key on `job_id_convert_to_bigint` before we swap the columns and drop the old FK ()
+    add_concurrent_foreign_key TABLE_NAME, :ci_builds, column: :job_id_convert_to_bigint, on_delete: :cascade, name: 'fk_rails_c5137cb2c1_tmp'
+
+
+
+
+
+
+
+
 
     with_lock_retries(raise_on_exhaustion: true) do
       # We'll need  ACCESS EXCLUSIVE lock on the related tables,
@@ -88,7 +101,7 @@ def swap
       change_column_default TABLE_NAME, :job_id_convert_to_bigint, 0
 
       # Swap PK constraint
-      execute "ALTER TABLE #{TABLE_NAME} DROP CONSTRAINT ci_job_artifacts_pkey CASCADE" # this will drop fk_36c74129da
+      execute "ALTER TABLE #{TABLE_NAME} DROP CONSTRAINT ci_job_artifacts_pkey CASCADE" # this will drop ci_job_artifacts_pkey primary key
       rename_index TABLE_NAME, 'index_ci_job_artifact_on_id_convert_to_bigint', 'ci_job_artifacts_pkey'
       execute "ALTER TABLE #{TABLE_NAME} ADD CONSTRAINT ci_job_artifacts_pkey PRIMARY KEY USING INDEX ci_job_artifacts_pkey"
 
@@ -102,13 +115,17 @@ def swap
       execute 'DROP INDEX index_ci_job_artifacts_on_job_id_and_file_type'
       rename_index TABLE_NAME, 'index_ci_job_artifacts_on_job_id_and_file_type_bigint', 'index_ci_job_artifacts_on_job_id_and_file_type'
 
+      rename_index TABLE_NAME, 'index_ci_job_artifact_on_job_id_convert_to_bigint', 'index_ci_job_artifact_on_job_id'
+
       # Change the name of the temporary FK for project_pages_metadata(artifacts_archive_id) -> id
       rename_constraint(:project_pages_metadata, fk_artifacts_archive_id_tmp, fk_artifacts_archive_id)
 
-      # Change the name of the old FK (will be dropped when old index is updated)
-      rename_constraint(TABLE_NAME, fk_ci_builds_job_id, "#{fk_ci_builds_job_id}_old_id")
-      # Change the name of the temporary FK for job_id -> ci_builds(id)
-      rename_constraint(TABLE_NAME, fk_ci_builds_job_id_tmp, fk_ci_builds_job_id)
+      # Drop original FK on the old int4 `job_id` (fk_rails_c5137cb2c1)
+      remove_foreign_key TABLE_NAME, name: 'fk_rails_c5137cb2c1'
+
+      # We swapped the columns but the FK for job_id is still using the temporary name for the job_id_convert_to_bigint column
+      # So we have to also swap the FK name now that we dropped the other one with the same
+      rename_constraint(TABLE_NAME, 'fk_rails_c5137cb2c1_tmp', 'fk_rails_c5137cb2c1')
     end
   end
 end