Skip to content

Swap id columns on sent_notifications

Jon Jenkins requested to merge 389344-column-swap-2 into master

What does this MR do and why?

Swaps sent_notifications.id and sent_notifications.id_convert_to_bigint, rebuilds the primary key constraint, and correctly reassigns the sequence.

Database migrations

Up 
main: == 20230328184031 SwapSentNotificationsIdColumns: migrating ===================
main: -- transaction_open?()
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0859s
main: -- index_exists?(:sent_notifications, :id_convert_to_bigint, {:unique=>true, :name=>:index_sent_notifications_on_id_convert_to_bigint, :algorithm=>:concurrently})
main:    -> 0.0025s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0003s
main: -- add_index(:sent_notifications, :id_convert_to_bigint, {:unique=>true, :name=>:index_sent_notifications_on_id_convert_to_bigint, :algorithm=>:concurrently})
main:    -> 0.0037s
main: -- execute("RESET statement_timeout")
main:    -> 0.0003s
main: -- transaction_open?()
main:    -> 0.0000s
main: -- execute("LOCK TABLE sent_notifications IN ACCESS EXCLUSIVE MODE")
main:    -> 0.0005s
main: -- quote_column_name(:id_tmp)
main:    -> 0.0000s
main: -- quote_column_name(:id)
main:    -> 0.0000s
main: -- quote_column_name(:id_convert_to_bigint)
main:    -> 0.0000s
main: -- execute("ALTER TABLE sent_notifications RENAME COLUMN \"id\" TO \"id_tmp\"")
main:    -> 0.0008s
main: -- execute("ALTER TABLE sent_notifications RENAME COLUMN \"id_convert_to_bigint\" TO \"id\"")
main:    -> 0.0006s
main: -- execute("ALTER TABLE sent_notifications RENAME COLUMN \"id_tmp\" TO \"id_convert_to_bigint\"")
main:    -> 0.0002s
main: -- quote_table_name("trigger_7f4fcd5aa322")
main:    -> 0.0000s
main: -- execute("ALTER FUNCTION \"trigger_7f4fcd5aa322\" RESET ALL")
main:    -> 0.0027s
main: -- execute("ALTER SEQUENCE sent_notifications_id_seq OWNED BY sent_notifications.id")
main:    -> 0.0010s
main: -- change_column_default(:sent_notifications, :id, #<Proc:0x000000011f7c5388 /Users/leonardodarosa/gdk/gitlab/db/post_migrate/20230328184031_swap_sent_notifications_id_columns.rb:47 (lambda)>)
main:    -> 0.0028s
main: -- change_column_default(:sent_notifications, :id_convert_to_bigint, 0)
main:    -> 0.0016s
main: -- execute("ALTER TABLE sent_notifications DROP CONSTRAINT sent_notifications_pkey CASCADE")
main:    -> 0.0008s
main: -- rename_index(:sent_notifications, :index_sent_notifications_on_id_convert_to_bigint, "sent_notifications_pkey")
main:    -> 0.0003s
main: -- execute("ALTER TABLE sent_notifications ADD CONSTRAINT sent_notifications_pkey PRIMARY KEY USING INDEX sent_notifications_pkey")
main:    -> 0.0006s
main: == 20230328184031 SwapSentNotificationsIdColumns: migrated (0.1355s) ==========

ci: == 20230328184031 SwapSentNotificationsIdColumns: migrating ===================
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- view_exists?(:postgres_partitions)
ci:    -> 0.0006s
ci: -- index_exists?(:sent_notifications, :id_convert_to_bigint, {:unique=>true, :name=>:index_sent_notifications_on_id_convert_to_bigint, :algorithm=>:concurrently})
ci:    -> 0.0035s
ci: -- execute("SET statement_timeout TO 0")
ci:    -> 0.0002s
ci: -- add_index(:sent_notifications, :id_convert_to_bigint, {:unique=>true, :name=>:index_sent_notifications_on_id_convert_to_bigint, :algorithm=>:concurrently})
ci:    -> 0.0069s
ci: -- execute("RESET statement_timeout")
ci:    -> 0.0003s
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- execute("LOCK TABLE sent_notifications IN ACCESS EXCLUSIVE MODE")
ci:    -> 0.0002s
ci: -- quote_column_name(:id_tmp)
ci:    -> 0.0000s
ci: -- quote_column_name(:id)
ci:    -> 0.0000s
ci: -- quote_column_name(:id_convert_to_bigint)
ci:    -> 0.0000s
ci: -- execute("ALTER TABLE sent_notifications RENAME COLUMN \"id\" TO \"id_tmp\"")
ci:    -> 0.0003s
ci: -- execute("ALTER TABLE sent_notifications RENAME COLUMN \"id_convert_to_bigint\" TO \"id\"")
ci:    -> 0.0002s
ci: -- execute("ALTER TABLE sent_notifications RENAME COLUMN \"id_tmp\" TO \"id_convert_to_bigint\"")
ci:    -> 0.0002s
ci: -- quote_table_name("trigger_7f4fcd5aa322")
ci:    -> 0.0000s
ci: -- execute("ALTER FUNCTION \"trigger_7f4fcd5aa322\" RESET ALL")
ci:    -> 0.0006s
ci: -- execute("ALTER SEQUENCE sent_notifications_id_seq OWNED BY sent_notifications.id")
ci:    -> 0.0008s
ci: -- change_column_default(:sent_notifications, :id, #<Proc:0x000000012c9bf398 /Users/leonardodarosa/gdk/gitlab/db/post_migrate/20230328184031_swap_sent_notifications_id_columns.rb:47 (lambda)>)
ci:    -> 0.0031s
ci: -- change_column_default(:sent_notifications, :id_convert_to_bigint, 0)
ci:    -> 0.0013s
ci: -- execute("ALTER TABLE sent_notifications DROP CONSTRAINT sent_notifications_pkey CASCADE")
ci:    -> 0.0011s
ci: -- rename_index(:sent_notifications, :index_sent_notifications_on_id_convert_to_bigint, "sent_notifications_pkey")
ci:    -> 0.0003s
ci: -- execute("ALTER TABLE sent_notifications ADD CONSTRAINT sent_notifications_pkey PRIMARY KEY USING INDEX sent_notifications_pkey")
ci:    -> 0.0005s
ci: == 20230328184031 SwapSentNotificationsIdColumns: migrated (0.0374s) ==========
Down 
main: == 20230328184031 SwapSentNotificationsIdColumns: reverting ===================
main: -- transaction_open?()
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0028s
main: -- index_exists?(:sent_notifications, :id_convert_to_bigint, {:unique=>true, :name=>:index_sent_notifications_on_id_convert_to_bigint, :algorithm=>:concurrently})
main:    -> 0.0023s
main: -- add_index(:sent_notifications, :id_convert_to_bigint, {:unique=>true, :name=>:index_sent_notifications_on_id_convert_to_bigint, :algorithm=>:concurrently})
main:    -> 0.0012s
main: -- transaction_open?()
main:    -> 0.0000s
main: -- execute("LOCK TABLE sent_notifications IN ACCESS EXCLUSIVE MODE")
main:    -> 0.0003s
main: -- quote_column_name(:id_tmp)
main:    -> 0.0000s
main: -- quote_column_name(:id)
main:    -> 0.0000s
main: -- quote_column_name(:id_convert_to_bigint)
main:    -> 0.0000s
main: -- execute("ALTER TABLE sent_notifications RENAME COLUMN \"id\" TO \"id_tmp\"")
main:    -> 0.0004s
main: -- execute("ALTER TABLE sent_notifications RENAME COLUMN \"id_convert_to_bigint\" TO \"id\"")
main:    -> 0.0004s
main: -- execute("ALTER TABLE sent_notifications RENAME COLUMN \"id_tmp\" TO \"id_convert_to_bigint\"")
main:    -> 0.0003s
main: -- quote_table_name("trigger_7f4fcd5aa322")
main:    -> 0.0000s
main: -- execute("ALTER FUNCTION \"trigger_7f4fcd5aa322\" RESET ALL")
main:    -> 0.0003s
main: -- execute("ALTER SEQUENCE sent_notifications_id_seq OWNED BY sent_notifications.id")
main:    -> 0.0003s
main: -- change_column_default(:sent_notifications, :id, #<Proc:0x0000000111c74518 /Users/leonardodarosa/gdk/gitlab/db/post_migrate/20230328184031_swap_sent_notifications_id_columns.rb:47 (lambda)>)
main:    -> 0.0018s
main: -- change_column_default(:sent_notifications, :id_convert_to_bigint, 0)
main:    -> 0.0013s
main: -- execute("ALTER TABLE sent_notifications DROP CONSTRAINT sent_notifications_pkey CASCADE")
main:    -> 0.0004s
main: -- rename_index(:sent_notifications, :index_sent_notifications_on_id_convert_to_bigint, "sent_notifications_pkey")
main:    -> 0.0003s
main: -- execute("ALTER TABLE sent_notifications ADD CONSTRAINT sent_notifications_pkey PRIMARY KEY USING INDEX sent_notifications_pkey")
main:    -> 0.0005s
main: == 20230328184031 SwapSentNotificationsIdColumns: reverted (0.0382s) ==========

ci: == 20230328184031 SwapSentNotificationsIdColumns: reverting ===================
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- view_exists?(:postgres_partitions)
ci:    -> 0.0006s
ci: -- index_exists?(:sent_notifications, :id_convert_to_bigint, {:unique=>true, :name=>:index_sent_notifications_on_id_convert_to_bigint, :algorithm=>:concurrently})
ci:    -> 0.0021s
ci: -- add_index(:sent_notifications, :id_convert_to_bigint, {:unique=>true, :name=>:index_sent_notifications_on_id_convert_to_bigint, :algorithm=>:concurrently})
ci:    -> 0.0019s
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- execute("LOCK TABLE sent_notifications IN ACCESS EXCLUSIVE MODE")
ci:    -> 0.0003s
ci: -- quote_column_name(:id_tmp)
ci:    -> 0.0000s
ci: -- quote_column_name(:id)
ci:    -> 0.0000s
ci: -- quote_column_name(:id_convert_to_bigint)
ci:    -> 0.0000s
ci: -- execute("ALTER TABLE sent_notifications RENAME COLUMN \"id\" TO \"id_tmp\"")
ci:    -> 0.0003s
ci: -- execute("ALTER TABLE sent_notifications RENAME COLUMN \"id_convert_to_bigint\" TO \"id\"")
ci:    -> 0.0003s
ci: -- execute("ALTER TABLE sent_notifications RENAME COLUMN \"id_tmp\" TO \"id_convert_to_bigint\"")
ci:    -> 0.0004s
ci: -- quote_table_name("trigger_7f4fcd5aa322")
ci:    -> 0.0001s
ci: -- execute("ALTER FUNCTION \"trigger_7f4fcd5aa322\" RESET ALL")
ci:    -> 0.0003s
ci: -- execute("ALTER SEQUENCE sent_notifications_id_seq OWNED BY sent_notifications.id")
ci:    -> 0.0003s
ci: -- change_column_default(:sent_notifications, :id, #<Proc:0x000000012af6cd88 /Users/leonardodarosa/gdk/gitlab/db/post_migrate/20230328184031_swap_sent_notifications_id_columns.rb:47 (lambda)>)
ci:    -> 0.0018s
ci: -- change_column_default(:sent_notifications, :id_convert_to_bigint, 0)
ci:    -> 0.0014s
ci: -- execute("ALTER TABLE sent_notifications DROP CONSTRAINT sent_notifications_pkey CASCADE")
ci:    -> 0.0003s
ci: -- rename_index(:sent_notifications, :index_sent_notifications_on_id_convert_to_bigint, "sent_notifications_pkey")
ci:    -> 0.0003s
ci: -- execute("ALTER TABLE sent_notifications ADD CONSTRAINT sent_notifications_pkey PRIMARY KEY USING INDEX sent_notifications_pkey")
ci:    -> 0.0005s
ci: == 20230328184031 SwapSentNotificationsIdColumns: reverted (0.0399s) ==========

MR acceptance checklist

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

Related to #389344 (closed)

Edited by Krasimir Angelov

Merge request reports