Updates to database that should be handled by application logic

[dustinkredmond]

Is there an existing issue for this?

• I have searched the existing issues

Kong version ($ kong version)

3.6

Current Behavior

Currently, when a user tries to delete a gateway service, a delete is passed from the application to the database even though a route object still exists that references the gateway service. This results in a foreign key violation. This logic should be handled at the application layer instead of relying on a referential integrity constraint at the database layer. This can be seen from the PostgreSQL server log if standard log levels are used.

postgresql.log

2024-05-03 15:55:57.714 EDT [2174177] ERROR:  update or delete on table "services" violates foreign key constraint "routes_service_id_fkey" on table "routes"
2024-05-03 15:55:57.714 EDT [2174177] DETAIL:  Key (id, ws_id)=(6860e4ea-dfc3-4fd7-957a-95e74006c14d, ed690604-acd9-4032-ad4b-926d11cbea17) is still referenced from table "routes".
2024-05-03 15:55:57.714 EDT [2174177] STATEMENT:  DELETE
          FROM "services"
         WHERE ("id") = ('6860e4ea-dfc3-4fd7-957a-95e74006c14d')
           AND ("ws_id" = 'ed690604-acd9-4032-ad4b-926d11cbea17')

Expected Behavior

Kong should check if any routes are configured for a service before issuing an SQL delete from the services table.

Steps To Reproduce

Configure a gateway service with at least one route. Try to delete the service from the Kong manager UI.

Anything else?

No response