Skip to content
Snippets Groups Projects

Finalize ci_job_artifacts conversion to bigint

Merged Alex Ives requested to merge 325615/finalize_ci_job_artifacts_bigint into master

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

Availability and Testing

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
Loading

Activity

Filter activity
  • Approvals
  • Assignees & reviewers
  • Comments (from bots)
  • Comments (from users)
  • Commits & branches
  • Edits
  • Labels
  • Lock status
  • Mentions
  • Merge request status
  • Tracking
  • 2 Warnings
    :warning: Please add a merge request type to this merge request.
    :warning: 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 :no_entry_sign: Danger

  • mentioned in epic &4785 (closed)

  • Alex Ives resolved all threads

    resolved all threads

  • Alex Ives marked this merge request as ready

    marked this merge request as ready

  • Alex Ives requested review from @stomlinson

    requested review from @stomlinson

  • Alex Ives added 1 commit

    added 1 commit

    • 575d3478 - Finalize ci_job_artifacts conversion to bigint

    Compare with previous version

  • Alex Ives changed the description

    changed the description

  • Alex Ives removed review request for @stomlinson

    removed review request for @stomlinson

  • Alex Ives changed target branch from 288005-finalize-events-bigint-conversion to master

    changed target branch from 288005-finalize-events-bigint-conversion to master

  • A deleted user added backend label

    added backend label

  • Alex Ives added 2351 commits

    added 2351 commits

    Compare with previous version

  • Alex Ives changed the description

    changed the description

  • 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

  • Alex Ives added 1 commit

    added 1 commit

    • d4ad2ebb - Finalize ci_job_artifacts conversion to bigint

    Compare with previous version

  • Alex Ives
  • Alex Ives changed the description

    changed the description

  • Alex Ives added 1 commit

    added 1 commit

    • 6c905920 - Finalize ci_job_artifacts conversion to bigint

    Compare with previous version

  • Alex Ives added 1 commit

    added 1 commit

    • 503896d4 - Finalize ci_job_artifacts conversion to bigint

    Compare with previous version

  • Alex Ives
  • Alex Ives requested review from @stomlinson

    requested review from @stomlinson

  • removed workflowin dev label

  • Alex Ives added 155 commits

    added 155 commits

    Compare with previous version

  • Krasimir Angelov
  • assigned to @stomlinson and @dfrazao-gitlab and unassigned @alexives

  • Diogo Frazão added 1 commit

    added 1 commit

    • fb2b8b82 - Change column default for job_id

    Compare with previous version

  • Diogo Frazão added 1 commit

    added 1 commit

    • a6eccc6e - Apply 1 suggestion(s) to 1 file(s)

    Compare with previous version

  • Diogo Frazão added 2 commits

    added 2 commits

    • 85614b63 - Change comment for ci_job_artifacts_pkey drop
    • a959c11c - Merge branch '325615/finalize_ci_job_artifacts_bigint' of...

    Compare with previous version

  • Diogo Frazão added 1 commit

    added 1 commit

    Compare with previous version

  • Diogo Frazão changed the description

    changed the description

  • Diogo Frazão added 1 commit

    added 1 commit

    • 9ff12a21 - add unique index for job_id_convert_to_bigint

    Compare with previous version

  • Diogo Frazão added 1 commit

    added 1 commit

    Compare with previous version

  • Diogo Frazão added 1 commit

    added 1 commit

    • fd812033 - find another way to create a FK

    Compare with previous version

  • Diogo Frazão added 1 commit

    added 1 commit

    • 951b4ba6 - Finalize ci_job_artifacts conversion to bigint

    Compare with previous version

  • Diogo Frazão added 1 commit

    added 1 commit

    Compare with previous version

  • Diogo Frazão added 1 commit

    added 1 commit

    • e931b204 - Finalize ci_job_artifacts conversion to bigint

    Compare with previous version

  • Diogo Frazão requested review from @pbair

    requested review from @pbair

  • Loading
  • Loading
  • Loading
  • Loading
  • Loading
  • Loading
  • Loading
  • Loading
  • Loading
  • Loading
  • Please register or sign in to reply
    Loading