Feature request: execute function on client connection state changed

[W1M0R]

It would be useful to be able to specify an SQL function for postgres-websockets, that will be executed when a WebSocket client connects or disconnects. The function will receive the client identity, perhaps the connecting JWT or some other connection-related properties such as IP, etc. The function will also receive the state of the connection (perhaps based on https://developer.mozilla.org/en-US/docs/Web/API/WebSocket), e.g. CONNECTING, OPEN, CLOSING, CLOSED.

Users of the function could use it to schedule or stop cron jobs, or do database cleanup or setup tasks, or to run pg_notify tasks etc.

[diogob]

@W1M0R it seems to me that a safe design would be to have the funciton explicitly specified in the config to avoid information flowing without consent by who setup the service. This would also prevent any cost associated with the function execution for users that are not interested in this feature.

Any thoughts?

[W1M0R]

That sounds like a good approach to me.

[W1M0R]

This feature would also allow for use cases such as #41, where the user can implement the function to track the number of active connections.

[diogob]

@W1M0R I have changed the approach here and decided to try a metadata channel that can be used from another websocket or from a database listener. To execute a function one would need an additional software listening to same channel used by postgres-websockets. I understand that it's not as convenient as the original proposed feature. But if you could give it a try or review the PR #67 that would be much appreciated.

[W1M0R]

Hi @diogob, thanks for all your efforts, and sorry for the late reply. I'm currently swamped at work, and won't be able to try this feature out anytime soon. The functionality described in your pull request would allow me to achieve most of my goals. The last hurdle is to be able to get notified of websocket connection closed events. That is probably the hardest problem to solve.