Skip to content
Snippets Groups Projects

Resolve "Address the Primary Key Overflow risk for the taggings table - Step 2: Finalize background migrations and swap columns"

What does this MR do?

This MR finalize the conversion to bigint for taggings table.

At a high level, the operation takes the following steps:

  1. Ensure the migration of id to bigint is completed.
  2. Ensure the migration of taggable_id to bigint is completed.
  3. Copy index
  4. Swap columns, drop existing PK, define new PK, swap sequences

Cleanup (removing the old int columns and the triggers) will be done in later MR.

Migration Output

Up
== 20210806131706 FinalizeTagginsBigintConversion: migrating ==================
-- transaction_open?()
   -> 0.0000s
-- index_exists?("taggings", :id_convert_to_bigint, {:unique=>true, :name=>"index_taggings_on_id_convert_to_bigint", :algorithm=>:concurrently})
   -> 0.0041s
-- transaction_open?()
   -> 0.0000s
-- index_exists?("taggings", [:taggable_id_convert_to_bigint, :taggable_type], {:name=>"i_taggings_on_taggable_id_convert_to_bigint_and_taggable_type", :algorithm=>:concurrently})
   -> 0.0036s
-- transaction_open?()
   -> 0.0000s
-- index_exists?("taggings", [:taggable_id_convert_to_bigint, :taggable_type, :context], {:name=>"i_taggings_on_taggable_bigint_and_taggable_type_and_context", :algorithm=>:concurrently})
   -> 0.0030s
-- transaction_open?()
   -> 0.0000s
-- index_exists?("taggings", [:tag_id, :taggable_id_convert_to_bigint, :taggable_type, :context, :tagger_id, :tagger_type], {:unique=>true, :name=>"taggings_idx_tmp", :algorithm=>:concurrently})
   -> 0.0027s
-- transaction_open?()
   -> 0.0000s
-- index_exists?("taggings", :id_convert_to_bigint, {:where=>"taggable_type = 'Project'", :name=>"tmp_index_taggings_on_id_bigint_where_taggable_type_project", :algorithm=>:concurrently})
   -> 0.0027s
-- execute("SET statement_timeout TO 0")
   -> 0.0005s
-- add_index("taggings", :id_convert_to_bigint, {:where=>"taggable_type = 'Project'", :name=>"tmp_index_taggings_on_id_bigint_where_taggable_type_project", :algorithm=>:concurrently})
   -> 0.0029s
-- execute("RESET statement_timeout")
   -> 0.0006s
-- execute("LOCK TABLE taggings IN ACCESS EXCLUSIVE MODE")
   -> 0.0006s
-- quote_table_name("taggings")
   -> 0.0000s
-- quote_column_name(:taggable_id)
   -> 0.0000s
-- quote_column_name("taggable_id_tmp")
   -> 0.0000s
-- execute("ALTER TABLE \"taggings\" RENAME COLUMN \"taggable_id\" TO \"taggable_id_tmp\"")
   -> 0.0007s
-- quote_table_name("taggings")
   -> 0.0000s
-- quote_column_name(:taggable_id_convert_to_bigint)
   -> 0.0000s
-- quote_column_name(:taggable_id)
   -> 0.0000s
-- execute("ALTER TABLE \"taggings\" RENAME COLUMN \"taggable_id_convert_to_bigint\" TO \"taggable_id\"")
   -> 0.0006s
-- quote_table_name("taggings")
   -> 0.0000s
-- quote_column_name("taggable_id_tmp")
   -> 0.0000s
-- quote_column_name(:taggable_id_convert_to_bigint)
   -> 0.0000s
-- execute("ALTER TABLE \"taggings\" RENAME COLUMN \"taggable_id_tmp\" TO \"taggable_id_convert_to_bigint\"")
   -> 0.0006s
-- quote_table_name("taggings")
   -> 0.0000s
-- quote_column_name(:id)
   -> 0.0000s
-- quote_column_name("id_tmp")
   -> 0.0000s
-- execute("ALTER TABLE \"taggings\" RENAME COLUMN \"id\" TO \"id_tmp\"")
   -> 0.0006s
-- quote_table_name("taggings")
   -> 0.0000s
-- quote_column_name(:id_convert_to_bigint)
   -> 0.0000s
-- quote_column_name(:id)
   -> 0.0000s
-- execute("ALTER TABLE \"taggings\" RENAME COLUMN \"id_convert_to_bigint\" TO \"id\"")
   -> 0.0006s
-- quote_table_name("taggings")
   -> 0.0000s
-- quote_column_name("id_tmp")
   -> 0.0000s
-- quote_column_name(:id_convert_to_bigint)
   -> 0.0000s
-- execute("ALTER TABLE \"taggings\" RENAME COLUMN \"id_tmp\" TO \"id_convert_to_bigint\"")
   -> 0.0006s
-- quote_table_name("trigger_aebe8b822ad3")
   -> 0.0000s
-- execute("ALTER FUNCTION \"trigger_aebe8b822ad3\" RESET ALL")
   -> 0.0007s
-- execute("ALTER SEQUENCE taggings_id_seq OWNED BY taggings.id")
   -> 0.0015s
-- change_column_default("taggings", :id, #)
   -> 0.0029s
-- change_column_default("taggings", :id_convert_to_bigint, 0)
   -> 0.0017s
-- execute("ALTER TABLE taggings DROP CONSTRAINT taggings_pkey CASCADE")
   -> 0.0012s
-- rename_index("taggings", "index_taggings_on_id_convert_to_bigint", "taggings_pkey")
   -> 0.0007s
-- execute("ALTER TABLE taggings ADD CONSTRAINT taggings_pkey PRIMARY KEY USING INDEX taggings_pkey")
   -> 0.0014s
-- execute("DROP INDEX index_taggings_on_taggable_id_and_taggable_type")
   -> 0.0006s
-- rename_index("taggings", "i_taggings_on_taggable_id_convert_to_bigint_and_taggable_type", "index_taggings_on_taggable_id_and_taggable_type")
   -> 0.0006s
-- execute("DROP INDEX index_taggings_on_taggable_id_and_taggable_type_and_context")
   -> 0.0006s
-- rename_index("taggings", "i_taggings_on_taggable_bigint_and_taggable_type_and_context", "index_taggings_on_taggable_id_and_taggable_type_and_context")
   -> 0.0006s
-- execute("DROP INDEX taggings_idx")
   -> 0.0006s
-- rename_index("taggings", "taggings_idx_tmp", "taggings_idx")
   -> 0.0006s
-- execute("DROP INDEX tmp_index_taggings_on_id_where_taggable_type_project")
   -> 0.0006s
-- rename_index("taggings", "tmp_index_taggings_on_id_bigint_where_taggable_type_project", "tmp_index_taggings_on_id_where_taggable_type_project")
   -> 0.0006s
== 20210806131706 FinalizeTagginsBigintConversion: migrated (0.0940s) =========
Down
== 20210806131706 FinalizeTagginsBigintConversion: reverting ==================
-- transaction_open?()
   -> 0.0000s
-- index_exists?("taggings", :id_convert_to_bigint, {:unique=>true, :name=>"index_taggings_on_id_convert_to_bigint", :algorithm=>:concurrently})
   -> 0.0034s
-- execute("SET statement_timeout TO 0")
   -> 0.0005s
-- add_index("taggings", :id_convert_to_bigint, {:unique=>true, :name=>"index_taggings_on_id_convert_to_bigint", :algorithm=>:concurrently})
   -> 0.0029s
-- execute("RESET statement_timeout")
   -> 0.0005s
-- transaction_open?()
   -> 0.0000s
-- index_exists?("taggings", [:taggable_id_convert_to_bigint, :taggable_type], {:name=>"i_taggings_on_taggable_id_convert_to_bigint_and_taggable_type", :algorithm=>:concurrently})
   -> 0.0024s
-- add_index("taggings", [:taggable_id_convert_to_bigint, :taggable_type], {:name=>"i_taggings_on_taggable_id_convert_to_bigint_and_taggable_type", :algorithm=>:concurrently})
   -> 0.0019s
-- transaction_open?()
   -> 0.0000s
-- index_exists?("taggings", [:taggable_id_convert_to_bigint, :taggable_type, :context], {:name=>"i_taggings_on_taggable_bigint_and_taggable_type_and_context", :algorithm=>:concurrently})
   -> 0.0024s
-- add_index("taggings", [:taggable_id_convert_to_bigint, :taggable_type, :context], {:name=>"i_taggings_on_taggable_bigint_and_taggable_type_and_context", :algorithm=>:concurrently})
   -> 0.0018s
-- transaction_open?()
   -> 0.0000s
-- index_exists?("taggings", [:tag_id, :taggable_id_convert_to_bigint, :taggable_type, :context, :tagger_id, :tagger_type], {:unique=>true, :name=>"taggings_idx_tmp", :algorithm=>:concurrently})
   -> 0.0029s
-- add_index("taggings", [:tag_id, :taggable_id_convert_to_bigint, :taggable_type, :context, :tagger_id, :tagger_type], {:unique=>true, :name=>"taggings_idx_tmp", :algorithm=>:concurrently})
   -> 0.0019s
-- transaction_open?()
   -> 0.0000s
-- index_exists?("taggings", :id_convert_to_bigint, {:where=>"taggable_type = 'Project'", :name=>"tmp_index_taggings_on_id_bigint_where_taggable_type_project", :algorithm=>:concurrently})
   -> 0.0030s
-- add_index("taggings", :id_convert_to_bigint, {:where=>"taggable_type = 'Project'", :name=>"tmp_index_taggings_on_id_bigint_where_taggable_type_project", :algorithm=>:concurrently})
   -> 0.0020s
-- execute("LOCK TABLE taggings IN ACCESS EXCLUSIVE MODE")
   -> 0.0006s
-- quote_table_name("taggings")
   -> 0.0000s
-- quote_column_name(:taggable_id)
   -> 0.0000s
-- quote_column_name("taggable_id_tmp")
   -> 0.0000s
-- execute("ALTER TABLE \"taggings\" RENAME COLUMN \"taggable_id\" TO \"taggable_id_tmp\"")
   -> 0.0006s
-- quote_table_name("taggings")
   -> 0.0000s
-- quote_column_name(:taggable_id_convert_to_bigint)
   -> 0.0000s
-- quote_column_name(:taggable_id)
   -> 0.0000s
-- execute("ALTER TABLE \"taggings\" RENAME COLUMN \"taggable_id_convert_to_bigint\" TO \"taggable_id\"")
   -> 0.0006s
-- quote_table_name("taggings")
   -> 0.0000s
-- quote_column_name("taggable_id_tmp")
   -> 0.0000s
-- quote_column_name(:taggable_id_convert_to_bigint)
   -> 0.0000s
-- execute("ALTER TABLE \"taggings\" RENAME COLUMN \"taggable_id_tmp\" TO \"taggable_id_convert_to_bigint\"")
   -> 0.0005s
-- quote_table_name("taggings")
   -> 0.0000s
-- quote_column_name(:id)
   -> 0.0000s
-- quote_column_name("id_tmp")
   -> 0.0000s
-- execute("ALTER TABLE \"taggings\" RENAME COLUMN \"id\" TO \"id_tmp\"")
   -> 0.0006s
-- quote_table_name("taggings")
   -> 0.0000s
-- quote_column_name(:id_convert_to_bigint)
   -> 0.0000s
-- quote_column_name(:id)
   -> 0.0000s
-- execute("ALTER TABLE \"taggings\" RENAME COLUMN \"id_convert_to_bigint\" TO \"id\"")
   -> 0.0006s
-- quote_table_name("taggings")
   -> 0.0000s
-- quote_column_name("id_tmp")
   -> 0.0000s
-- quote_column_name(:id_convert_to_bigint)
   -> 0.0000s
-- execute("ALTER TABLE \"taggings\" RENAME COLUMN \"id_tmp\" TO \"id_convert_to_bigint\"")
   -> 0.0006s
-- quote_table_name("trigger_aebe8b822ad3")
   -> 0.0000s
-- execute("ALTER FUNCTION \"trigger_aebe8b822ad3\" RESET ALL")
   -> 0.0006s
-- execute("ALTER SEQUENCE taggings_id_seq OWNED BY taggings.id")
   -> 0.0007s
-- change_column_default("taggings", :id, #)
   -> 0.0021s
-- change_column_default("taggings", :id_convert_to_bigint, 0)
   -> 0.0016s
-- execute("ALTER TABLE taggings DROP CONSTRAINT taggings_pkey CASCADE")
   -> 0.0008s
-- rename_index("taggings", "index_taggings_on_id_convert_to_bigint", "taggings_pkey")
   -> 0.0006s
-- execute("ALTER TABLE taggings ADD CONSTRAINT taggings_pkey PRIMARY KEY USING INDEX taggings_pkey")
   -> 0.0009s
-- execute("DROP INDEX index_taggings_on_taggable_id_and_taggable_type")
   -> 0.0006s
-- rename_index("taggings", "i_taggings_on_taggable_id_convert_to_bigint_and_taggable_type", "index_taggings_on_taggable_id_and_taggable_type")
   -> 0.0006s
-- execute("DROP INDEX index_taggings_on_taggable_id_and_taggable_type_and_context")
   -> 0.0006s
-- rename_index("taggings", "i_taggings_on_taggable_bigint_and_taggable_type_and_context", "index_taggings_on_taggable_id_and_taggable_type_and_context")
   -> 0.0006s
-- execute("DROP INDEX taggings_idx")
   -> 0.0006s
-- rename_index("taggings", "taggings_idx_tmp", "taggings_idx")
   -> 0.0006s
-- execute("DROP INDEX tmp_index_taggings_on_id_where_taggable_type_project")
   -> 0.0006s
-- rename_index("taggings", "tmp_index_taggings_on_id_bigint_where_taggable_type_project", "tmp_index_taggings_on_id_where_taggable_type_project")
   -> 0.0005s
== 20210806131706 FinalizeTagginsBigintConversion: reverted (0.0780s) =========

Database migrations

Timings

From DB Lab:

  1. CREATE INDEX CONCURRENTLY "i_taggings_on_taggable_bigint_and_taggable_type_and_context" ON "taggings" ("taggable_id_convert_to_bigint", "taggable_type", "context");

    Duration: 94.860 min - https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/5353/commands/18774

  2. CREATE INDEX CONCURRENTLY "i_taggings_on_taggable_id_convert_to_bigint_and_taggable_type" ON "taggings" ("taggable_id_convert_to_bigint", "taggable_type");

    Duration: 58.935 min - https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/5353/commands/18767

  3. CREATE UNIQUE INDEX CONCURRENTLY "taggings_idx_tmp2" ON "taggings" ("tag_id", "taggable_id_convert_to_bigint", "taggable_type", "context", "tagger_id", "tagger_type");

    Duration: 109.485 min - https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/5353/commands/18803

  4. CREATE UNIQUE INDEX CONCURRENTLY "index_taggings_on_id_convert_to_bigint" ON "taggings" ("id_convert_to_bigint");

    Duration: 56.231 min - https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/5353/commands/18843

  5. CREATE INDEX CONCURRENTLY "tmp_index_taggings_on_id_convert_to_bigint_where_taggable_type_project" ON "taggings" ("id_convert_to_bigint") WHERE taggable_type = 'Project'

    Duration: 30.368 min - https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/6267/commands/21182

Screenshots or Screencasts (strongly suggested)

Does this MR meet the acceptance criteria?

Conformity

Availability and Testing

Security

Does this MR contain changes to processing or storing of credentials or tokens, authorization and authentication methods or other items described in the security review guidelines? If not, then delete this Security section.

  • Label as security and @ mention @gitlab-com/gl-security/appsec
  • The MR includes necessary changes to maintain consistency between UI, API, email, or other methods
  • Security reports checked/validated by a reviewer from the AppSec team

Related to #334823 (closed)

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
  • @dfrazao-gitlab Thanks! This looks good, I have just couple of comments.

  • Diogo Frazão added 1 commit

    added 1 commit

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

    Compare with previous version

  • Diogo Frazão added 1 commit

    added 1 commit

    • 8022f10b - Finalize conversion to bigint for taggings

    Compare with previous version

  • Diogo Frazão changed the description

    changed the description

  • Diogo Frazão resolved all threads

    resolved all threads

  • mentioned in epic &4785 (closed)

  • Krasimir Angelov approved this merge request

    approved this merge request

  • @dfrazao-gitlab Thanks, looks good! We'll have to pre-create indexes for this one.

    Passing to @pbair for maintainer review / merge.

  • added databasereviewed label and removed databaseactive label

  • Krasimir Angelov requested review from @pbair and removed review request for @krasio

    requested review from @pbair and removed review request for @krasio

    • Resolved by Patrick Bair

      :wave: @krasio, thanks for approving this merge request.

      Please consider starting a new pipeline if:

      • This is the first time the merge request is approved, or
      • The merge request is ready to be merged, and there has not been a merge request pipeline in the last 2 hours.

      For more info, refer to the guideline.

  • Thanks for the review, @krasio!

  • Patrick Bair mentioned in merge request !67532 (merged)

    mentioned in merge request !67532 (merged)

  • Diogo Frazão added 1 commit

    added 1 commit

    • aa11f330 - Finalize conversion to bigint for taggings

    Compare with previous version

  • 🤖 GitLab Bot 🤖 changed milestone to %14.3

    changed milestone to %14.3

    • Resolved by Patrick Bair

      @dfrazao-gitlab Another MR !67574 (merged) was merged today that added a temporary index on the id column of this table: tmp_index_taggings_on_id_where_taggable_type_project. The temporary index will be kept through until %14.4, so we would need to handle it in this migration.

      Can you update this migration to also properly convert the new index to cover the correct column, as we have done with the others?

      The other migration is db/post_migrate/20210730104800_schedule_extract_project_topics_into_separate_table.rb, so we should already have the correct ordering, since the migration in this MR will come after that.

      /cc @krasio

  • Patrick Bair removed review request for @pbair

    removed review request for @pbair

  • mentioned in issue #339280 (closed)

  • Krasimir Angelov added 4670 commits

    added 4670 commits

    Compare with previous version

  • Krasimir Angelov changed the description

    changed the description

  • Krasimir Angelov marked this merge request as draft

    marked this merge request as draft

  • Krasimir Angelov changed the description

    changed the description

  • Krasimir Angelov mentioned in merge request !69185 (closed)

    mentioned in merge request !69185 (closed)

  • Patrick Bair requested review from @pbair

    requested review from @pbair

  • Patrick Bair resolved all threads

    resolved all threads

  • Patrick Bair marked this merge request as ready

    marked this merge request as ready

  • Patrick Bair approved this merge request

    approved this merge request

  • added databaseapproved label and removed databasereviewed label

  • @gitlab-org/release/managers Giving a heads up for a post-deployment migration which has to create a big index, taking about 20 minutes in database-lab.

    This migration should not be at risk of deadlocks as some other similar migrations as happened here: gitlab-com/gl-infra/production#5339 (closed), but in the even it fails to get a lock for some reason, it is safe to retry.

  • Patrick Bair enabled an automatic merge when the pipeline for 5bcb9ca9 succeeds

    enabled an automatic merge when the pipeline for 5bcb9ca9 succeeds

  • Patrick Bair resolved all threads

    resolved all threads

  • Patrick Bair aborted the automatic merge because source branch was updated

    aborted the automatic merge because source branch was updated

  • Patrick Bair added 925 commits

    added 925 commits

    Compare with previous version

  • Patrick Bair resolved all threads

    resolved all threads

  • Patrick Bair enabled an automatic merge when the pipeline for 0fa3897a succeeds

    enabled an automatic merge when the pipeline for 0fa3897a succeeds

  • Diogo Frazão aborted the automatic merge because source branch was updated

    aborted the automatic merge because source branch was updated

  • Diogo Frazão added 146 commits

    added 146 commits

    Compare with previous version

  • Diogo Frazão added 1 commit

    added 1 commit

    • 9899635e - Finalize conversion to bigint for taggings

    Compare with previous version

  • Diogo Frazão added 316 commits

    added 316 commits

    Compare with previous version

  • Patrick Bair enabled an automatic merge when the pipeline for d0fad448 succeeds

    enabled an automatic merge when the pipeline for d0fad448 succeeds

  • merged

  • Patrick Bair mentioned in commit d3d80a33

    mentioned in commit d3d80a33

  • added workflowcanary label and removed workflowstaging label

  • added workflowproduction label and removed workflowcanary label

  • Diogo Frazão mentioned in merge request !70050 (merged)

    mentioned in merge request !70050 (merged)

  • mentioned in issue #341650 (closed)

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