Drop schedule and status columns from alert escalations (!72235) · Merge requests · GitLab.org / GitLab

Sarah Yasonik requested to merge sy-remove-deprecated-alert-escalation-columns into master Oct 12, 2021

What does this MR do and why?

This MR drops the scheduled_id and status columns from the incident_management_pending_alert_escalations table, after they were ignored in %14.2 !65635 (merged).

Database

State of table before

gitlabhq_development=# \d incident_management_pending_alert_escalations
                                 Partitioned table "public.incident_management_pending_alert_escalations"
   Column    |           Type           | Collation | Nullable |                                  Default                                  
-------------+--------------------------+-----------+----------+---------------------------------------------------------------------------
 id          | bigint                   |           | not null | nextval('incident_management_pending_alert_escalations_id_seq'::regclass)
 rule_id     | bigint                   |           | not null | 
 alert_id    | bigint                   |           | not null | 
 schedule_id | bigint                   |           |          | 
 process_at  | timestamp with time zone |           | not null | 
 created_at  | timestamp with time zone |           | not null | 
 updated_at  | timestamp with time zone |           | not null | 
 status      | smallint                 |           |          | 
Partition key: RANGE (process_at)
Indexes:
    "incident_management_pending_alert_escalations_pkey" PRIMARY KEY, btree (id, process_at)
    "index_incident_management_pending_alert_escalations_on_alert_id" btree (alert_id)
    "index_incident_management_pending_alert_escalations_on_rule_id" btree (rule_id)
    "index_incident_management_pending_alert_escalations_on_schedule" btree (schedule_id)
Foreign-key constraints:
    "fk_rails_8d8de95da9" FOREIGN KEY (alert_id) REFERENCES alert_management_alerts(id) ON DELETE CASCADE
    "fk_rails_f3d17bc8af" FOREIGN KEY (rule_id) REFERENCES incident_management_escalation_rules(id) ON DELETE CASCADE
    "fk_rails_fcbfd9338b" FOREIGN KEY (schedule_id) REFERENCES incident_management_oncall_schedules(id) ON DELETE CASCADE
Number of partitions: 11 (Use \d+ to list them.)

Up

Migration output

% bin/rails db:migrate VERSION=20211012155931     
== 20211012155931 RemoveScheduleAndStatusFromPendingAlertEscalations: migrating 
-- remove_column(:incident_management_pending_alert_escalations, :schedule_id)
   -> 0.0053s
-- remove_column(:incident_management_pending_alert_escalations, :status)
   -> 0.0016s
== 20211012155931 RemoveScheduleAndStatusFromPendingAlertEscalations: migrated (0.0356s)

State of table after migration

gitlabhq_development=# \d incident_management_pending_alert_escalations
                                 Partitioned table "public.incident_management_pending_alert_escalations"
   Column   |           Type           | Collation | Nullable |                                  Default                                  
------------+--------------------------+-----------+----------+---------------------------------------------------------------------------
 id         | bigint                   |           | not null | nextval('incident_management_pending_alert_escalations_id_seq'::regclass)
 rule_id    | bigint                   |           | not null | 
 alert_id   | bigint                   |           | not null | 
 process_at | timestamp with time zone |           | not null | 
 created_at | timestamp with time zone |           | not null | 
 updated_at | timestamp with time zone |           | not null | 
Partition key: RANGE (process_at)
Indexes:
    "incident_management_pending_alert_escalations_pkey" PRIMARY KEY, btree (id, process_at)
    "index_incident_management_pending_alert_escalations_on_alert_id" btree (alert_id)
    "index_incident_management_pending_alert_escalations_on_rule_id" btree (rule_id)
Foreign-key constraints:
    "fk_rails_8d8de95da9" FOREIGN KEY (alert_id) REFERENCES alert_management_alerts(id) ON DELETE CASCADE
    "fk_rails_f3d17bc8af" FOREIGN KEY (rule_id) REFERENCES incident_management_escalation_rules(id) ON DELETE CASCADE
Number of partitions: 11 (Use \d+ to list them.)

Down

Migration output

 % bin/rails db:migrate:down VERSION=20211012155931
== 20211012155931 RemoveScheduleAndStatusFromPendingAlertEscalations: reverting 
-- column_exists?(:incident_management_pending_alert_escalations, :schedule_id)
   -> 0.0053s
-- add_column(:incident_management_pending_alert_escalations, :schedule_id, :bigint)
   -> 0.0120s
-- column_exists?(:incident_management_pending_alert_escalations, :status)
   -> 0.0016s
-- add_column(:incident_management_pending_alert_escalations, :status, :smallint)
   -> 0.0059s
-- transaction_open?()
   -> 0.0000s
-- index_name_exists?(:incident_management_pending_alert_escalations, "index_incident_management_pending_alert_escalations_on_schedule")
   -> 0.0061s
-- transaction_open?()
   -> 0.0000s
-- index_exists?("gitlab_partitions_dynamic.incident_management_pending_alert_escalations_000000", :schedule_id, {:name=>"index_2c8485b3c9", :algorithm=>:concurrently})
   -> 0.0080s
-- execute("SET statement_timeout TO 0")
   -> 0.0015s
-- add_index("gitlab_partitions_dynamic.incident_management_pending_alert_escalations_000000", :schedule_id, {:name=>"index_2c8485b3c9", :algorithm=>:concurrently})
   -> 0.0194s
-- execute("RESET statement_timeout")
   -> 0.0009s
-- transaction_open?()
   -> 0.0000s
-- index_exists?("gitlab_partitions_dynamic.incident_management_pending_alert_escalations_202107", :schedule_id, {:name=>"index_7dd814eba4", :algorithm=>:concurrently})
   -> 0.0026s
-- add_index("gitlab_partitions_dynamic.incident_management_pending_alert_escalations_202107", :schedule_id, {:name=>"index_7dd814eba4", :algorithm=>:concurrently})
   -> 0.0054s
-- transaction_open?()
   -> 0.0000s
-- index_exists?("gitlab_partitions_dynamic.incident_management_pending_alert_escalations_202108", :schedule_id, {:name=>"index_df69712358", :algorithm=>:concurrently})
   -> 0.0026s
-- add_index("gitlab_partitions_dynamic.incident_management_pending_alert_escalations_202108", :schedule_id, {:name=>"index_df69712358", :algorithm=>:concurrently})
   -> 0.0039s
-- transaction_open?()
   -> 0.0000s
-- index_exists?("gitlab_partitions_dynamic.incident_management_pending_alert_escalations_202109", :schedule_id, {:name=>"index_234ada60d5", :algorithm=>:concurrently})
   -> 0.0022s
-- add_index("gitlab_partitions_dynamic.incident_management_pending_alert_escalations_202109", :schedule_id, {:name=>"index_234ada60d5", :algorithm=>:concurrently})
   -> 0.0058s
-- transaction_open?()
   -> 0.0000s
-- index_exists?("gitlab_partitions_dynamic.incident_management_pending_alert_escalations_202110", :schedule_id, {:name=>"index_234916c25e", :algorithm=>:concurrently})
   -> 0.0066s
-- add_index("gitlab_partitions_dynamic.incident_management_pending_alert_escalations_202110", :schedule_id, {:name=>"index_234916c25e", :algorithm=>:concurrently})
   -> 0.0038s
-- transaction_open?()
   -> 0.0000s
-- index_exists?("gitlab_partitions_dynamic.incident_management_pending_alert_escalations_202111", :schedule_id, {:name=>"index_b4fecd25e3", :algorithm=>:concurrently})
   -> 0.0019s
-- add_index("gitlab_partitions_dynamic.incident_management_pending_alert_escalations_202111", :schedule_id, {:name=>"index_b4fecd25e3", :algorithm=>:concurrently})
   -> 0.0046s
-- transaction_open?()
   -> 0.0000s
-- index_exists?("gitlab_partitions_dynamic.incident_management_pending_alert_escalations_202112", :schedule_id, {:name=>"index_7858802a6f", :algorithm=>:concurrently})
   -> 0.0018s
-- add_index("gitlab_partitions_dynamic.incident_management_pending_alert_escalations_202112", :schedule_id, {:name=>"index_7858802a6f", :algorithm=>:concurrently})
   -> 0.0062s
-- transaction_open?()
   -> 0.0000s
-- index_exists?("gitlab_partitions_dynamic.incident_management_pending_alert_escalations_202201", :schedule_id, {:name=>"index_8e78de1e1e", :algorithm=>:concurrently})
   -> 0.0018s
-- add_index("gitlab_partitions_dynamic.incident_management_pending_alert_escalations_202201", :schedule_id, {:name=>"index_8e78de1e1e", :algorithm=>:concurrently})
   -> 0.0050s
-- transaction_open?()
   -> 0.0000s
-- index_exists?("gitlab_partitions_dynamic.incident_management_pending_alert_escalations_202202", :schedule_id, {:name=>"index_2e9f854469", :algorithm=>:concurrently})
   -> 0.0035s
-- add_index("gitlab_partitions_dynamic.incident_management_pending_alert_escalations_202202", :schedule_id, {:name=>"index_2e9f854469", :algorithm=>:concurrently})
   -> 0.0058s
-- transaction_open?()
   -> 0.0000s
-- index_exists?("gitlab_partitions_dynamic.incident_management_pending_alert_escalations_202203", :schedule_id, {:name=>"index_cd37479ef6", :algorithm=>:concurrently})
   -> 0.0025s
-- add_index("gitlab_partitions_dynamic.incident_management_pending_alert_escalations_202203", :schedule_id, {:name=>"index_cd37479ef6", :algorithm=>:concurrently})
   -> 0.0053s
-- transaction_open?()
   -> 0.0000s
-- index_exists?("gitlab_partitions_dynamic.incident_management_pending_alert_escalations_202204", :schedule_id, {:name=>"index_fb6cd86e25", :algorithm=>:concurrently})
   -> 0.0021s
-- add_index("gitlab_partitions_dynamic.incident_management_pending_alert_escalations_202204", :schedule_id, {:name=>"index_fb6cd86e25", :algorithm=>:concurrently})
   -> 0.0054s
-- add_index(:incident_management_pending_alert_escalations, :schedule_id, {:name=>"index_incident_management_pending_alert_escalations_on_schedule"})
   -> 0.0076s
-- transaction_open?()
   -> 0.0000s
-- foreign_keys(:incident_management_pending_alert_escalations)
   -> 0.0102s
-- transaction_open?()
   -> 0.0000s
-- foreign_keys("gitlab_partitions_dynamic.incident_management_pending_alert_escalations_000000")
   -> 0.0041s
-- execute("ALTER TABLE gitlab_partitions_dynamic.incident_management_pending_alert_escalations_000000\nADD CONSTRAINT fk_rails_fcbfd9338b\nFOREIGN KEY (schedule_id)\nREFERENCES incident_management_oncall_schedules (id)\nON DELETE CASCADE\nNOT VALID;\n")
   -> 0.0212s
-- execute("ALTER TABLE gitlab_partitions_dynamic.incident_management_pending_alert_escalations_000000 VALIDATE CONSTRAINT fk_rails_fcbfd9338b;")
   -> 0.0110s
-- transaction_open?()
   -> 0.0000s
-- foreign_keys("gitlab_partitions_dynamic.incident_management_pending_alert_escalations_202107")
   -> 0.0027s
-- execute("ALTER TABLE gitlab_partitions_dynamic.incident_management_pending_alert_escalations_202107\nADD CONSTRAINT fk_rails_fcbfd9338b\nFOREIGN KEY (schedule_id)\nREFERENCES incident_management_oncall_schedules (id)\nON DELETE CASCADE\nNOT VALID;\n")
   -> 0.0024s
-- execute("ALTER TABLE gitlab_partitions_dynamic.incident_management_pending_alert_escalations_202107 VALIDATE CONSTRAINT fk_rails_fcbfd9338b;")
   -> 0.0133s
-- transaction_open?()
   -> 0.0000s
-- foreign_keys("gitlab_partitions_dynamic.incident_management_pending_alert_escalations_202108")
   -> 0.0025s
-- execute("ALTER TABLE gitlab_partitions_dynamic.incident_management_pending_alert_escalations_202108\nADD CONSTRAINT fk_rails_fcbfd9338b\nFOREIGN KEY (schedule_id)\nREFERENCES incident_management_oncall_schedules (id)\nON DELETE CASCADE\nNOT VALID;\n")
   -> 0.0013s
-- execute("ALTER TABLE gitlab_partitions_dynamic.incident_management_pending_alert_escalations_202108 VALIDATE CONSTRAINT fk_rails_fcbfd9338b;")
   -> 0.0074s
-- transaction_open?()
   -> 0.0000s
-- foreign_keys("gitlab_partitions_dynamic.incident_management_pending_alert_escalations_202109")
   -> 0.0037s
-- execute("ALTER TABLE gitlab_partitions_dynamic.incident_management_pending_alert_escalations_202109\nADD CONSTRAINT fk_rails_fcbfd9338b\nFOREIGN KEY (schedule_id)\nREFERENCES incident_management_oncall_schedules (id)\nON DELETE CASCADE\nNOT VALID;\n")
   -> 0.0013s
-- execute("ALTER TABLE gitlab_partitions_dynamic.incident_management_pending_alert_escalations_202109 VALIDATE CONSTRAINT fk_rails_fcbfd9338b;")
   -> 0.0040s
-- transaction_open?()
   -> 0.0000s
-- foreign_keys("gitlab_partitions_dynamic.incident_management_pending_alert_escalations_202110")
   -> 0.0024s
-- execute("ALTER TABLE gitlab_partitions_dynamic.incident_management_pending_alert_escalations_202110\nADD CONSTRAINT fk_rails_fcbfd9338b\nFOREIGN KEY (schedule_id)\nREFERENCES incident_management_oncall_schedules (id)\nON DELETE CASCADE\nNOT VALID;\n")
   -> 0.0022s
-- execute("ALTER TABLE gitlab_partitions_dynamic.incident_management_pending_alert_escalations_202110 VALIDATE CONSTRAINT fk_rails_fcbfd9338b;")
   -> 0.0079s
-- transaction_open?()
   -> 0.0000s
-- foreign_keys("gitlab_partitions_dynamic.incident_management_pending_alert_escalations_202111")
   -> 0.0027s
-- execute("ALTER TABLE gitlab_partitions_dynamic.incident_management_pending_alert_escalations_202111\nADD CONSTRAINT fk_rails_fcbfd9338b\nFOREIGN KEY (schedule_id)\nREFERENCES incident_management_oncall_schedules (id)\nON DELETE CASCADE\nNOT VALID;\n")
   -> 0.0016s
-- execute("ALTER TABLE gitlab_partitions_dynamic.incident_management_pending_alert_escalations_202111 VALIDATE CONSTRAINT fk_rails_fcbfd9338b;")
   -> 0.0077s
-- transaction_open?()
   -> 0.0000s
-- foreign_keys("gitlab_partitions_dynamic.incident_management_pending_alert_escalations_202112")
   -> 0.0049s
-- execute("ALTER TABLE gitlab_partitions_dynamic.incident_management_pending_alert_escalations_202112\nADD CONSTRAINT fk_rails_fcbfd9338b\nFOREIGN KEY (schedule_id)\nREFERENCES incident_management_oncall_schedules (id)\nON DELETE CASCADE\nNOT VALID;\n")
   -> 0.0015s
-- execute("ALTER TABLE gitlab_partitions_dynamic.incident_management_pending_alert_escalations_202112 VALIDATE CONSTRAINT fk_rails_fcbfd9338b;")
   -> 0.0047s
-- transaction_open?()
   -> 0.0000s
-- foreign_keys("gitlab_partitions_dynamic.incident_management_pending_alert_escalations_202201")
   -> 0.0031s
-- execute("ALTER TABLE gitlab_partitions_dynamic.incident_management_pending_alert_escalations_202201\nADD CONSTRAINT fk_rails_fcbfd9338b\nFOREIGN KEY (schedule_id)\nREFERENCES incident_management_oncall_schedules (id)\nON DELETE CASCADE\nNOT VALID;\n")
   -> 0.0014s
-- execute("ALTER TABLE gitlab_partitions_dynamic.incident_management_pending_alert_escalations_202201 VALIDATE CONSTRAINT fk_rails_fcbfd9338b;")
   -> 0.0032s
-- transaction_open?()
   -> 0.0000s
-- foreign_keys("gitlab_partitions_dynamic.incident_management_pending_alert_escalations_202202")
   -> 0.0024s
-- execute("ALTER TABLE gitlab_partitions_dynamic.incident_management_pending_alert_escalations_202202\nADD CONSTRAINT fk_rails_fcbfd9338b\nFOREIGN KEY (schedule_id)\nREFERENCES incident_management_oncall_schedules (id)\nON DELETE CASCADE\nNOT VALID;\n")
   -> 0.0012s
-- execute("ALTER TABLE gitlab_partitions_dynamic.incident_management_pending_alert_escalations_202202 VALIDATE CONSTRAINT fk_rails_fcbfd9338b;")
   -> 0.0044s
-- transaction_open?()
   -> 0.0000s
-- foreign_keys("gitlab_partitions_dynamic.incident_management_pending_alert_escalations_202203")
   -> 0.0030s
-- execute("ALTER TABLE gitlab_partitions_dynamic.incident_management_pending_alert_escalations_202203\nADD CONSTRAINT fk_rails_fcbfd9338b\nFOREIGN KEY (schedule_id)\nREFERENCES incident_management_oncall_schedules (id)\nON DELETE CASCADE\nNOT VALID;\n")
   -> 0.0018s
-- execute("ALTER TABLE gitlab_partitions_dynamic.incident_management_pending_alert_escalations_202203 VALIDATE CONSTRAINT fk_rails_fcbfd9338b;")
   -> 0.0045s
-- transaction_open?()
   -> 0.0000s
-- foreign_keys("gitlab_partitions_dynamic.incident_management_pending_alert_escalations_202204")
   -> 0.0025s
-- execute("ALTER TABLE gitlab_partitions_dynamic.incident_management_pending_alert_escalations_202204\nADD CONSTRAINT fk_rails_fcbfd9338b\nFOREIGN KEY (schedule_id)\nREFERENCES incident_management_oncall_schedules (id)\nON DELETE CASCADE\nNOT VALID;\n")
   -> 0.0014s
-- execute("ALTER TABLE gitlab_partitions_dynamic.incident_management_pending_alert_escalations_202204 VALIDATE CONSTRAINT fk_rails_fcbfd9338b;")
   -> 0.0041s
-- add_foreign_key(:incident_management_pending_alert_escalations, :incident_management_oncall_schedules, {:column=>:schedule_id, :on_delete=>:cascade, :name=>"fk_rails_fcbfd9338b", :validate=>true})
   -> 0.0097s
== 20211012155931 RemoveScheduleAndStatusFromPendingAlertEscalations: reverted (0.6315s)

State of table after migration

gitlabhq_development=# \d incident_management_pending_alert_escalations
                                 Partitioned table "public.incident_management_pending_alert_escalations"
   Column    |           Type           | Collation | Nullable |                                  Default                                  
-------------+--------------------------+-----------+----------+---------------------------------------------------------------------------
 id          | bigint                   |           | not null | nextval('incident_management_pending_alert_escalations_id_seq'::regclass)
 rule_id     | bigint                   |           | not null | 
 alert_id    | bigint                   |           | not null | 
 process_at  | timestamp with time zone |           | not null | 
 created_at  | timestamp with time zone |           | not null | 
 updated_at  | timestamp with time zone |           | not null | 
 schedule_id | bigint                   |           |          | 
 status      | smallint                 |           |          | 
Partition key: RANGE (process_at)
Indexes:
    "incident_management_pending_alert_escalations_pkey" PRIMARY KEY, btree (id, process_at)
    "index_incident_management_pending_alert_escalations_on_alert_id" btree (alert_id)
    "index_incident_management_pending_alert_escalations_on_rule_id" btree (rule_id)
    "index_incident_management_pending_alert_escalations_on_schedule" btree (schedule_id)
Foreign-key constraints:
    "fk_rails_8d8de95da9" FOREIGN KEY (alert_id) REFERENCES alert_management_alerts(id) ON DELETE CASCADE
    "fk_rails_f3d17bc8af" FOREIGN KEY (rule_id) REFERENCES incident_management_escalation_rules(id) ON DELETE CASCADE
    "fk_rails_fcbfd9338b" FOREIGN KEY (schedule_id) REFERENCES incident_management_oncall_schedules(id) ON DELETE CASCADE
Number of partitions: 11 (Use \d+ to list them.)

MR acceptance checklist

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

I have evaluated the MR acceptance checklist for this MR.

Edited Oct 13, 2021 by Sarah Yasonik

Drop schedule and status columns from alert escalations

What does this MR do and why?

Database

Up

Down

MR acceptance checklist

Merge request reports