Cleanup foreign key web_hooks, services

🤓 What does this MR do?

In !47821 (merged) we added a foreign key to web_hooks on service_id but we could not validate the key because there are many records that are referencing a service which doesn't exist anymore. This migration should delete those records.

🔗 Related issued

• #283938 (closed)

🐘 Database

Background migration RemoveOrphanServiceHooks

up

> bundle exec rails db:migrate 
== 20201203123201 RemoveOrphanServiceHooks: migrating =========================
== 20201203123201 RemoveOrphanServiceHooks: migrated (0.2537s) ================

down

> bundle exec rails db:rollback STEP=1
== 20201203123201 RemoveOrphanServiceHooks: reverting =========================
== 20201203123201 RemoveOrphanServiceHooks: reverted (0.0000s) ================

The migration runs this query in batches of 1000 (https://explain.depesz.com/s/mFFW):

SELECT
  web_hooks.id
FROM
  web_hooks
WHERE
  web_hooks.type = 'ServiceHook'
  AND web_hooks.service_id NOT IN (
    SELECT
      services.id
    FROM
      services
    )
  AND web_hooks.service_id IS NOT NULL
ORDER BY
  web_hooks.id ASC
LIMIT 1

And deletes each batch with (https://explain.depesz.com/s/7jeI):

DELETE FROM web_hooks
WHERE web_hooks.type = 'ServiceHook'
  AND web_hooks.service_id NOT IN (
    SELECT
      services.id
    FROM
      services
    )
  AND web_hooks.service_id IS NOT NULL
  AND web_hooks.id >= 1007
  AND web_hooks.id < 2007

I run a similar query in #database-lab to figure out how many records would be affected. The result was 11892 rows https://explain.depesz.com/s/LDP7

I also tried to delete all records in one go to get an idea how fast the batch delete would be (https://explain.depesz.com/s/Im87):

Time: 13.659 s
  - planning: 0.173 ms
  - execution: 13.659 s
    - I/O read: 3.579 s
    - I/O write: 0.000 ms

Does this MR meet the acceptance criteria?

Conformity

• Changelog entry
• [-] Documentation (if required)
• [-] Code review guidelines
• Merge request performance guidelines
• Style guides
• Database guides
• [-] Separation of EE specific content

Availability and Testing

• Review and add/update tests for this feature/bug. Consider all test levels. See the Test Planning Process.
• [-] Tested in all supported browsers
• [-] Informed Infrastructure department of a default or new setting change, if applicable per definition of done