bug: key client_id is not present in table clients

[geekbrother]

We have rare database error messages containing the following error:

Store(Database(Database(PgDatabaseError { severity: Error, code: "23503", message: "insert or update on table \"notifications\" violates foreign key constraint \"fk_notifications_client_id\"", detail: Some("Key (client_id)=(...) is not present in table \"clients\"."), hint: None, position: None, where: None, schema: Some("public"), table: Some("notifications"), column: None, data_type: None, constraint: Some("fk_notifications_client_id"), file: Some("ri_triggers.c"), line: Some(2509), routine: Some("ri_ReportViolation") })))

We have a client_id related constraint in the database schema that prevents inserting the record to the notifications table without the related client id.

At this error, we are trying to insert the notification without the corresponding client id in the clients table.

When investigating the issue by connecting to the database and inspecting clients and notifications tables, the inspection reveals that the client with the corresponding id is present in the table for the id that caused the error.

We have two different endpoints for the client creation and the notification publishing. When the client creation calls there is INSERT and DELETE into the clients table.

The constraint error occurs as a race condition when two endpoints call in parallel and in the first we are deleting the client id for that we are inserting the new notification from the other notification endpoint before the new row is inserted.