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) ==
Edited by Patrick Bair