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) ==
Merge request reports
Activity
changed milestone to %14.2
assigned to @alexives
- Resolved by Alex Ives
2 Warnings Please add a merge request type to this merge request. You've made some app changes, but didn't add any tests.
That's OK as long as you're refactoring existing code,
but please consider adding any of the tooling, ~"tooling::pipelines", ~"tooling::workflow", documentation, QA labels.Reviewer roulette
Changes that require review have been detected!
Please refer to the table below for assigning reviewers and maintainers suggested by Danger in the specified category:
Category Reviewer Maintainer database Diogo Frazão ( @dfrazao-gitlab
) (UTC+2, 7 hours ahead of@alexives
)Patrick Bair ( @pbair
) (UTC-4, 1 hour ahead of@alexives
)~migration No reviewer available No maintainer available To spread load more evenly across eligible reviewers, Danger has picked a candidate for each review slot, based on their timezone. Feel free to override these selections if you think someone else would be better-suited or use the GitLab Review Workload Dashboard to find other available reviewers.
To read more on how to use the reviewer roulette, please take a look at the Engineering workflow and code review guidelines. Please consider assigning a reviewer or maintainer who is a domain expert in the area of the merge request.
Once you've decided who will review this merge request, assign them as a reviewer! Danger does not automatically notify them for you.
If needed, you can retry the
danger-review
job that generated this comment.Generated by
Dangermentioned in epic &4785 (closed)
- Resolved by Patrick Bair
@stomlinson would you mind taking a look at this? I think it's ready for review!
requested review from @stomlinson
added 1 commit
- 575d3478 - Finalize ci_job_artifacts conversion to bigint
removed review request for @stomlinson
- A deleted user
added backend label
added 2351 commits
-
575d3478...4cd1fc0a - 2350 commits from branch
master
- 1ed604e4 - Finalize ci_job_artifacts conversion to bigint
-
575d3478...4cd1fc0a - 2350 commits from branch
Database migrations
Migrations included in this change have been executed on gitlab.com data for testing purposes. For details, please see the migration testing pipeline (limited access).
Migration Type Total runtime Result DB size change
Other migrations pending on GitLab.com
Migration Type Total runtime Result DB size change Clone Details
Clone ID Clone Created At Clone Data Timestamp Expected Removal Time database-testing-735846
2021-08-10 14:07:16 UTC 2021-08-10 12:01:31 UTC 2021-08-11 02:11:29 +0000 Artifacts
Brought to you by gitlab-org/database-team/gitlab-com-database-testing. Epic
added 1 commit
- d4ad2ebb - Finalize ci_job_artifacts conversion to bigint
- Resolved by Patrick Bair
added 1 commit
- 6c905920 - Finalize ci_job_artifacts conversion to bigint
added 1 commit
- 503896d4 - Finalize ci_job_artifacts conversion to bigint
- Resolved by Patrick Bair
- Resolved by Patrick Bair
Okay, @stomlinson - ready for that review magic now
requested review from @stomlinson
removed workflowin dev label
- A deleted user
added database-testing-automation label
added 155 commits
-
503896d4...113aac25 - 154 commits from branch
master
- 1f7034e0 - Finalize ci_job_artifacts conversion to bigint
-
503896d4...113aac25 - 154 commits from branch
- Resolved by Patrick Bair
- Resolved by Patrick Bair
- Resolved by Patrick Bair
- Resolved by Diogo Frazão
assigned to @stomlinson and @dfrazao-gitlab and unassigned @alexives
added 1 commit
- 9ff12a21 - add unique index for job_id_convert_to_bigint
added 1 commit
- 951b4ba6 - Finalize ci_job_artifacts conversion to bigint
added 1 commit
- e931b204 - Finalize ci_job_artifacts conversion to bigint
requested review from @pbair