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

[raoxiaoyan]

@dustinkredmond
This is part of the design, it was intentionally designed this way.
We don't cascade delete the related route. The main reason is to mitigate the risk of accidentally deleting the route.