add ON DELETE CASCADE to routes during delete of service

[darrenjennings]

Summary

Suggestion to cascade delete routes when deleting a service. If a service has a lot of routes, it is painful to get /services/:service/routes and then DELETE /routes/:id. Was not able to see any historical significance of not having cascade delete of routes when deleting a service, and it seems that sometimes we cascade delete other entities. For example, if you create a plugin on a service, then deleting the service also deletes the plugin. @thibaultcha mentioned that we might want to allow for dangling routes while deleting the service so that the router never produces invalid responses. Could this be circumvented by disassociating the service from the route before performing the service delete?

Added migration locally and tested to success:

ALTER TABLE routes DROP CONSTRAINT routes_service_id_fkey,
        ADD CONSTRAINT routes_service_id_fkey FOREIGN KEY (service_id, ws_id)
        REFERENCES services(id, ws_id) ON DELETE CASCADE;

{ service = { type = "foreign", reference = "services", on_delete = "cascade" }, },

It is nice to have safety guards during DELETE of services since you could be performing a very destructive action, so curious if it is better to "opt-in" by not creating the DB association but handling at the ORM level e.g. DELETE /services/:service/routes?cascade=true. This is setup in other ORM's like e.g. doctrine's cascade="remove" and orphanRemoval so that you would opt-in to the relationship deletion.

[bungle]

We have on_delete=cascade in some entities:

kong/kong/db/schema/entities/plugins.lua

Lines 18 to 20 in 28e4baf

	{ route = { type = "foreign", reference = "routes", default = null, on_delete = "cascade", }, },
	{ service = { type = "foreign", reference = "services", default = null, on_delete = "cascade", }, },
	{ consumer = { type = "foreign", reference = "consumers", default = null, on_delete = "cascade", }, },

[bungle]

The one thing about cascades that worries me is the cache. I think we might have some problems with it already. This same problem is probably also with caching entities that have ttl for longer than ttl. As deletions happen on DB level, there is no worker_events or cluster_events to be processed, and that can lead to issues, like one node having the entity still somewhere in cache while new node does not have it, which leads to inconsistent behavior between nodes.