I was at a meeting today to discuss implementing an e-commerce site using amongst other things OpenACS and PostgreSQL and I was asked a question which I couldn't directly answer and I've been pondering ever since.
Basically, is there any way to correlate a request from a user to a query running in Postgres and can that query be killed if the user goes away?
This comes from an experience the customer had with a web app that fired off expensive queries, the user would get fed up with waiting and fire the query again, and again, and again - eventually bringing the db server to its knees (I should point out that the app server was Tomcat not AOLserver based).
I know I can kill a running Postgres process and can discover the query etc using tools like pgMonitor but obvously this doesn't indicate where the query orginated and so you risk killing the wrong SELECT if two users are running the same query.
The other obvious answer is don't have expensive queries which could cripple the server or use hardware which is capable of supporting the worst case scenario but this isn't always possible. For instance if I fire several searches at the OpenACS site without waiting for the result, would this upset things?
The question of automatically killing queries where the user has gone away is interesting given the stateless nature of web interfaces. Has anyone ever coded anything which does this?
Am I worrying unnecessarily? Your thoughts or rants would be appreciated.