Finalize ci_job_artifacts conversion to bigint
-
Review changes -
-
Download -
Patches
-
Plain diff
1 unresolved thread
1 unresolved thread
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 msDoes 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
Merge request reports
Loading