First time asking here, if you think, that i should add something or can clarify in any other way, please tell me.
I am trying to write two files in Python: the first file is the server, the second is a client that tests the speed of the server. Server The server must is implemented using an fastapi. On startup, the server initialize the database, creating a table for messages if it does not exist at startup. The server is providing one HTTP POST method that:
• receives a JSON message containing the sender's name and text and saves it to the database (the choice of a specific database is not critical)
• returns JSON with the last 10 (or fewer if the history has not accumulated) messages, including the current one
• each message in the response must contain the name of the sender, the text of the message, the date of sending, the serial number of the message and the number of messages from the current user
• the date, serial number and counter of user messages must be calculated at the time of saving and written to the database The following points are of fundamental importance:
• the response to a request should not contain messages received after the current request
• messages should not be omitted in the response to a request
• message serial numbers must not match
• messages from the same user must not have the same message counters Such situations arises when the server processes two requests asynchronously (or two parallel server replicas process requests simultaneously). It is necessary to anticipate and prevent such situations using database tools.
I am using PostgreSQL as a DB, and i cant find a propper tool to manage the inputs of messages, as their serial numbers match eachother from time to time.
First, I tried making the sequence numbers parameter unique. This led to the loss of some messages.
I also tried using PostgreSQL Advisory Locks. Which did not lead to any visible result.
I understand that the main problem is that if there are two replicas of the server, messages “collide” at the entrance to the database, and it is necessary to form some kind of homogeneous stream that could simultaneously receive messages from both replicas of the servers and alternately write them to database. But I just can’t find the right tool using the capabilities of the database.
I am already using FASTapi as my server, and SQLAchemy as ORM.