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

.gitlab/issue_templates/Deprecations.md

+<!-- Use this template as a starting point for deprecations. -->
+
+### Deprecation Summary
+
+<!--
+This should contain a brief description of the feature or functionality that is deprecated. The description should clearly state the potential impact of the deprecation to end users.
+
+It is recommended that you link to the documentation.
+
+The description of the deprecation should state what actions the user should take to rectify the behavior. If the deprecation is scheduled for an upcoming release, the content should remain in the deprecations documentation page until it has been completed. For example, if a deprecation is announced in 14.9 and scheduled to be completed in 15.0, the same content would be included in the documentation for 14.9, 14.10, and 15.0.
+-->
+
+### Breaking Change
+
+<!-- Is this a breaking change or not? If so, please add instructions for how users can update their workflow. -->
+### Affected Topology
+
+<!--
+Who is affected by this deprecation, Self-managed users, SaaS users, or both? This is especially important when nearing the annual major release where breaking changes and removals are typically introduced. These changes might be seen on GitLab.com before the official release date.
+-->
+
+### Affected Tier
+
+<!--
+Which tier is this feature available in?
+
+* Free
+* Premium
+* Ultimate
+-->
+
+### Checklist
+
+- [ ] @mention your stage's stable counterparts on this issue. For example, Customer Support, Customer Success (Technical Account Manager), Product Marketing Manager.
+  - To see who the stable counterparts are for a product team visit [product categories](https://about.gitlab.com/handbook/product/categories/)
+       - If there is no stable counterpart listed for Sales/CS please mention `@timtams`
+       - If there is no stable counterpart listed for Support please @mention `@gitlab-com/support/managers`
+       - If there is no stable counterpart listed for Marketing please mention `@williamchia`
+
+- [ ] @mention your GPM so that they are aware of planned deprecations. The goal is to have reviews happen at least two releases before the final removal of the feature or introduction of a breaking change.
+
+### Deprecation Milestone
+
+<!-- In which milestone will this deprecation happen? -->
+
+### Links
+
+<!--
+Add links to any relevant documentation or code that will provide additional details or clarity regarding the planned change. Also, include a link to the removal issue if relevant.
+-->