Skip to content

Step2b - Creates migration that swaps columns for CiBuildNeeds

Laura Montemayor requested to merge step2b-swap-columns-ci-build-needs into master

What does this MR do and why?

This is Step2b for migrating ci_build_needs.id to BigInt: #389343 (closed)

Previously, this was being done in one step, but given that a portion of the migration is a ddl migration and needs to run on both databases - it is incompatible with restrict_gitlab_migration gitlab_schema: :gitlab_ci, which we need for the first step. The documentation will be updated soon - more context here.

Step 2a - Verify that the background migration is finished only on the ci database: ensure_batched_background_migration_is_finished: !111968 (merged)
Step 2b - Swap out the column on both databases. <- You are here

UP

ci: == 20230220102212 SwapColumnsCiBuildNeedsBigIntConversion: migrating ==========
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- view_exists?(:postgres_partitions)
ci:    -> 0.0691s
ci: -- index_exists?("ci_build_needs", :id_convert_to_bigint, {:unique=>true, :name=>"index_ci_build_needs_on_id_convert_to_bigint", :algorithm=>:concurrently})
ci:    -> 0.0093s
ci: -- execute("SET statement_timeout TO 0")
ci:    -> 0.0004s
ci: -- add_index("ci_build_needs", :id_convert_to_bigint, {:unique=>true, :name=>"index_ci_build_needs_on_id_convert_to_bigint", :algorithm=>:concurrently})
ci:    -> 0.0062s
ci: -- execute("RESET statement_timeout")
ci:    -> 0.0003s
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- execute("LOCK TABLE ci_build_needs IN ACCESS EXCLUSIVE MODE")
ci:    -> 0.0003s
ci: -- quote_column_name(:id)
ci:    -> 0.0000s
ci: -- quote_column_name("id_tmp")
ci:    -> 0.0000s
ci: -- quote_column_name(:id_convert_to_bigint)
ci:    -> 0.0000s
ci: -- execute("ALTER TABLE ci_build_needs RENAME COLUMN \"id\" TO \"id_tmp\"")
ci:    -> 0.0003s
ci: -- execute("ALTER TABLE ci_build_needs RENAME COLUMN \"id_convert_to_bigint\" TO \"id\"")
ci:    -> 0.0005s
ci: -- execute("ALTER TABLE ci_build_needs RENAME COLUMN \"id_tmp\" TO \"id_convert_to_bigint\"")
ci:    -> 0.0004s
ci: -- quote_table_name("trigger_3207b8d0d6f3")
ci:    -> 0.0000s
ci: -- execute("ALTER FUNCTION \"trigger_3207b8d0d6f3\" RESET ALL")
ci:    -> 0.0003s
ci: -- execute("ALTER SEQUENCE ci_build_needs_id_seq OWNED BY ci_build_needs.id")
ci:    -> 0.0014s
ci: -- change_column_default("ci_build_needs", :id, #<Proc:0x000000012b03ed10 /Users/avielle/dev/gitlab/gitlab-development-kit/gitlab/db/post_migrate/20230220102212_swap_columns_ci_build_needs_big_int_conversion.rb:49 (lambda)>)
ci:    -> 0.0028s
ci: -- change_column_default("ci_build_needs", :id_convert_to_bigint, 0)
ci:    -> 0.0018s
ci: -- execute("ALTER TABLE ci_build_needs DROP CONSTRAINT ci_build_needs_pkey CASCADE")
ci:    -> 0.0006s
ci: -- rename_index("ci_build_needs", "index_ci_build_needs_on_id_convert_to_bigint", "ci_build_needs_pkey")
ci:    -> 0.0003s
ci: -- execute("ALTER TABLE ci_build_needs ADD CONSTRAINT ci_build_needs_pkey PRIMARY KEY USING INDEX ci_build_needs_pkey")
ci:    -> 0.0007s
ci: == 20230220102212 SwapColumnsCiBuildNeedsBigIntConversion: migrated (0.2713s) =

DOWN

ci: == 20230220102212 SwapColumnsCiBuildNeedsBigIntConversion: reverting ==========
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- view_exists?(:postgres_partitions)
ci:    -> 0.0750s
ci: -- index_exists?("ci_build_needs", :id_convert_to_bigint, {:unique=>true, :name=>"index_ci_build_needs_on_id_convert_to_bigint", :algorithm=>:concurrently})
ci:    -> 0.0108s
ci: -- execute("SET statement_timeout TO 0")
ci:    -> 0.0003s
ci: -- add_index("ci_build_needs", :id_convert_to_bigint, {:unique=>true, :name=>"index_ci_build_needs_on_id_convert_to_bigint", :algorithm=>:concurrently})
ci:    -> 0.0066s
ci: -- execute("RESET statement_timeout")
ci:    -> 0.0023s
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- execute("LOCK TABLE ci_build_needs IN ACCESS EXCLUSIVE MODE")
ci:    -> 0.0003s
ci: -- quote_column_name(:id)
ci:    -> 0.0000s
ci: -- quote_column_name("id_tmp")
ci:    -> 0.0000s
ci: -- quote_column_name(:id_convert_to_bigint)
ci:    -> 0.0000s
ci: -- execute("ALTER TABLE ci_build_needs RENAME COLUMN \"id\" TO \"id_tmp\"")
ci:    -> 0.0003s
ci: -- execute("ALTER TABLE ci_build_needs RENAME COLUMN \"id_convert_to_bigint\" TO \"id\"")
ci:    -> 0.0004s
ci: -- execute("ALTER TABLE ci_build_needs RENAME COLUMN \"id_tmp\" TO \"id_convert_to_bigint\"")
ci:    -> 0.0004s
ci: -- quote_table_name("trigger_3207b8d0d6f3")
ci:    -> 0.0000s
ci: -- execute("ALTER FUNCTION \"trigger_3207b8d0d6f3\" RESET ALL")
ci:    -> 0.0003s
ci: -- execute("ALTER SEQUENCE ci_build_needs_id_seq OWNED BY ci_build_needs.id")
ci:    -> 0.0003s
ci: -- change_column_default("ci_build_needs", :id, #<Proc:0x000000012dca45d0 /Users/avielle/dev/gitlab/gitlab-development-kit/gitlab/db/post_migrate/20230220102212_swap_columns_ci_build_needs_big_int_conversion.rb:49 (lambda)>)
ci:    -> 0.0018s
ci: -- change_column_default("ci_build_needs", :id_convert_to_bigint, 0)
ci:    -> 0.0013s
ci: -- execute("ALTER TABLE ci_build_needs DROP CONSTRAINT ci_build_needs_pkey CASCADE")
ci:    -> 0.0004s
ci: -- rename_index("ci_build_needs", "index_ci_build_needs_on_id_convert_to_bigint", "ci_build_needs_pkey")
ci:    -> 0.0004s
ci: -- execute("ALTER TABLE ci_build_needs ADD CONSTRAINT ci_build_needs_pkey PRIMARY KEY USING INDEX ci_build_needs_pkey")
ci:    -> 0.0004s
ci: == 20230220102212 SwapColumnsCiBuildNeedsBigIntConversion: reverted (0.2704s) =

                                   Table "public.ci_build_needs"
        Column        |  Type   | Collation | Nullable |                  Default
----------------------+---------+-----------+----------+--------------------------------------------
 id_convert_to_bigint | integer |           | not null | 0
 name                 | text    |           | not null |
 artifacts            | boolean |           | not null | true
 optional             | boolean |           | not null | false
 build_id             | bigint  |           | not null |
 partition_id         | bigint  |           | not null | 100
 id                   | bigint  |           | not null | nextval('ci_build_needs_id_seq'::regclass)
Indexes:
    "ci_build_needs_pkey" PRIMARY KEY, btree (id)
    "index_ci_build_needs_on_build_id_and_name" UNIQUE, btree (build_id, name)
    "index_ci_build_needs_on_partition_id_build_id" btree (partition_id, build_id)
Foreign-key constraints:
    "fk_rails_3cf221d4ed_p" FOREIGN KEY (partition_id, build_id) REFERENCES ci_builds(partition_id, id) ON UPDATE CASCADE ON DELETE CASCADE
Triggers:
    trigger_3207b8d0d6f3 BEFORE INSERT OR UPDATE ON ci_build_needs FOR EACH ROW EXECUTE FUNCTION trigger_3207b8d0d6f3()

MR acceptance checklist

This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.

Edited by Laura Montemayor

Merge request reports