I'm using a postgres database to maintain a list of rooms and connected users to each room.
Users can enter a room whenever they want, but they should leave the room when they close the browser.
A good flow of events should be
User enters room (user's room var is set) -> ... -> User disconnects and server notices (user's room var is unset)
But what if this happens?
User enters room (user's room var is set) -> ... -> Server crashes or shuts down for updates -> User disconnects and server doesn't notice (user's room var is still set) -> Server is back on
In this last case, the database state is already broken. What's the best way to deal with something like this? Thanks
Let's divide the answer into 2 aspects:
User Aspect:
Regardless of the language at hand, you should be made aware of disconnection events using a Socket event/exception handling.
If the server crashes, your user will experience an abrupt socket disconnection/connection closing/session termination, depending on which framework your are using. TCP Sockets also have keepalive (SO_KEEPALIVE) exactly for that (you can usually control these (or similar) settings from the high-level protocol.
So, all you need to to do in that case is run maintenance code on the user's end (unset a variable in you describe case)
Server Aspect:
It's a bit trickier here. What you are basically looking for is ephemeral state management, meaning, the ability to react to abprut service/server termination (server crashes that result in an corrupted/unclean state), and clean-up after them
For that, Technologies like Zookeeper or Consul exist. I personally recommend Zookeeper, as I have built similar solutions on top of it in the past, several times.
With zookeeper, when your server startup, it can, for instance, creates an EPHEMERAL node. That node will be created once the server goes up, and will remain there for as long as the server is alive and connected to the Zookeeper cluster. If the server crashes unexpectedly, This node is removed.
You can then have a separate application/script that listens to events on that zk node/path. If it's suddenly remove, you can run a cleanup routine on the database.
This approach supports multiple app instances of course - you can listen on an events under a path and have all server instance register using different nodes under it. The removed node can contain instance specific identifiers, and you can use those to clean up specific instance state from the database.
It can also be a wise choice to remove clean-up/maintenance duty to a separate component
(Note that ZooKeeper requires careful attention when dealing connection/state events)
Some additional Zookeeper reading material
Final Thoughts:
Of course the answer can be fine-tuned based on specific needs that were not presented in the question.
When building complex, stateful solution, I personally aim to deal with crashes on all ends of the solutions, playing 'safe' where possible