Skip to content

Add cascade delete foreign key web_hooks, services

Andy Schoenen requested to merge 283938-add-web_hooks-service-foreign-key into master

What does this MR do?

It adds a foreign key for services on the web_hooks table

Why

So far there is no UI for deleting a project integration (service record). The idea is that an integration can be deactivated by setting active = false so deletion is not necessary. However we recently introduced instance and group level integrations. An integration added to a group or instance, will create a new services record on each project in the group/instance. For those integrations we want to be able to actually delete services records and the associated webhooks.

There are 12980 ServiceHooks that belong to non existing services on gitlab.com (https://explain.depesz.com/s/83Tw)

Related issues

Database

up

> bundle exec rails db:migrate       
== 20201119125730 AddWebHooksServiceForeignKey: migrating =====================
-- transaction_open?()
   -> 0.0000s
-- index_exists?(:web_hooks, :service_id, {:name=>"index_web_hooks_on_service_id", :algorithm=>:concurrently})
   -> 0.0065s
-- execute("SET statement_timeout TO 0")
   -> 0.0006s
-- add_index(:web_hooks, :service_id, {:name=>"index_web_hooks_on_service_id", :algorithm=>:concurrently})
   -> 0.0071s
-- execute("RESET ALL")
   -> 0.0009s
-- transaction_open?()
   -> 0.0000s
-- foreign_keys(:web_hooks)
   -> 0.0060s
-- execute("ALTER TABLE web_hooks\nADD CONSTRAINT fk_d47999a98a\nFOREIGN KEY (service_id)\nREFERENCES services (id)\nON DELETE CASCADE\nNOT VALID;\n")
   -> 0.0032s
== 20201119125730 AddWebHooksServiceForeignKey: migrated (0.0353s) ============

down

> bundle exec rails db:rollback STEP=1
== 20201119125730 AddWebHooksServiceForeignKey: reverting =====================
-- foreign_keys(:web_hooks)
   -> 0.0031s
-- remove_foreign_key(:web_hooks, {:column=>:service_id})
   -> 0.0040s
-- transaction_open?()
   -> 0.0000s
-- indexes(:web_hooks)
   -> 0.0038s
-- execute("SET statement_timeout TO 0")
   -> 0.0003s
-- remove_index(:web_hooks, {:algorithm=>:concurrently, :name=>"index_web_hooks_on_service_id"})
   -> 0.0021s
-- execute("RESET ALL")
   -> 0.0002s
== 20201119125730 AddWebHooksServiceForeignKey: reverted (0.0177s) ============

Before the constrained can be validated, we need to clean up invalid data. This will be done in !48263 (merged) after this one is merged.

Does this MR meet the acceptance criteria?

Conformity

Availability and Testing

Edited by Andy Schoenen

Merge request reports