Обсуждение: Apache - DBI - Postgresql: Cancelling queries
I am having trouble with users firing off a query from the web interface, and then getting bored of waiting and firing off a different query. As http is stateless apache, (and so the perl cgi script) only realises that the user has gone when it ties to send data back to the user and gets a "broken pipe" type response. This is usually too late as by this time the query has completed, using up valuable resources. Is there a tried and tested solution to this problem? If so please let me know! If not... I am working on a work around at the moment but have run into trouble! I have read the DBI man pages but there doesn't seem to be a cancel query function implemented, can anyone confirm or deny this? Can i send some control sequence using DBI to cancel the query? I had taken the approach of having two perl threads, one to run the query and one to poll the browser every second to see if the user is still there. Plan X was then to cancel the query if the user had ended the connection. The first problem was the lack of cancel query, second problem seems to be that the DBI database handles cannot be shared between thread - so i will have to pass a signal to the thread waiting for query to return to cancel it? anyone else tried this? any more gaping pitfalls that i should be aware of?! Thanks!
> The first problem was the lack of cancel query, second problem seems to
> be that the DBI database handles cannot be shared between thread - so i
> will have to pass a signal to the thread waiting for query to return to
> cancel it? anyone else tried this? any more gaping pitfalls that i
> should be aware of?!
Never fork() a process using DBI; When either the child or the parent
dies, the connection on both of them will close, even if it wasn't
actively using it.
As for #1, here's the answer from perldoc DBI:
To assist in implementing these operations, the DBI pro
vides a "cancel" method for statement handles. The "can
cel" method should abort the current operation and is
designed to be called from a signal handler.
However, it must be stressed that: a) few drivers imple
ment this at the moment (the DBI provides a default method
that just returns "undef"); and b) even if implemented,
there is still a possibility that the statement handle,
and possibly the parent database handle, will not be
usable afterwards.
If "cancel" returns true, then it has successfully invoked
the database engines own cancel function. If it returns
false, then "cancel" failed. If it returns "undef", then
the database engine does not have cancel implemented.
Jon
The "solution" I finally implemented seems to be pretty good, graying out the button after it's pushed with javascript. That means no more doubleclick problem, and no more hammering away at the same button. It does not preclude the reloading of the page (reactivating the button) or just going somewhere else and issuing another query. The "real" solutions involving cute little client side applets that phone home to the server to see if the query is still running and show a phony status bar seem like too much work and still don't prevent malicious wankers from issuing multiple queries. Good luck! Mat wrote: >I am having trouble with users firing off a query from the web >interface, and then getting bored of waiting and firing off a different >query. > >As http is stateless apache, (and so the perl cgi script) only realises >that the user has gone when it ties to send data back to the user and >gets a "broken pipe" type response. This is usually too late as by this >time the query has completed, using up valuable resources. > >Is there a tried and tested solution to this problem? >If so please let me know! > >If not... > >I am working on a work around at the moment but have run into trouble! > >I have read the DBI man pages but there doesn't seem to be a cancel >query function implemented, can anyone confirm or deny this? > >Can i send some control sequence using DBI to cancel the query? > >I had taken the approach of having two perl threads, one to run the >query and one to poll the browser every second to see if the user is >still there. Plan X was then to cancel the query if the user had ended >the connection. > >The first problem was the lack of cancel query, second problem seems to >be that the DBI database handles cannot be shared between thread - so i >will have to pass a signal to the thread waiting for query to return to >cancel it? anyone else tried this? any more gaping pitfalls that i >should be aware of?! > >Thanks! > > > > >---------------------------(end of broadcast)--------------------------- >TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > >
If it's double-click stuff why don't you just not submit duplicate queries? There are very many methods - e.g. must have valid token to submit query, token only valid for one query. That sort of thing. e.g. store hash of certain values (hidden params, sessionid) in the form (including a random salt, and row num) in a table, as a token. Then send the form with token to the user. When user submits form, do a select for update on the table for that row, if particular row does not have matching token, flag an error. If no row - flag error. Update row to invalidate the token. You could even limit the number of concurrent queries that way, with a bit more overhead. You'll have a bottleneck, but since it's only for expensive (slow) queries it might not be a big problem. Javascript is annoying, plus if it's off it doesn't work. Regards, Link. At 01:19 PM 8/1/2003 +0000, Ian Harding wrote: >The "solution" I finally implemented seems to be pretty good, graying out >the button after it's pushed with javascript. That means no more >doubleclick problem, and no more hammering away at the same button. It >does not preclude the reloading of the page (reactivating the button) or >just going somewhere else and issuing another query. > >The "real" solutions involving cute little client side applets that phone >home to the server to see if the query is still running and show a phony >status bar seem like too much work and still don't prevent malicious >wankers from issuing multiple queries. > >Good luck! > >Mat wrote: > >>I am having trouble with users firing off a query from the web >>interface, and then getting bored of waiting and firing off a different >>query. >> >>As http is stateless apache, (and so the perl cgi script) only realises >>that the user has gone when it ties to send data back to the user and >>gets a "broken pipe" type response. This is usually too late as by this >>time the query has completed, using up valuable resources. >> >>Is there a tried and tested solution to this problem? >>If so please let me know! >> >>If not... >> >>I am working on a work around at the moment but have run into trouble! >> >>I have read the DBI man pages but there doesn't seem to be a cancel >>query function implemented, can anyone confirm or deny this? >> >>Can i send some control sequence using DBI to cancel the query? >> >>I had taken the approach of having two perl threads, one to run the >>query and one to poll the browser every second to see if the user is >>still there. Plan X was then to cancel the query if the user had ended >>the connection. >> >>The first problem was the lack of cancel query, second problem seems to >>be that the DBI database handles cannot be shared between thread - so i >>will have to pass a signal to the thread waiting for query to return to >>cancel it? anyone else tried this? any more gaping pitfalls that i >>should be aware of?! >> >>Thanks! >> >> >> >> >>---------------------------(end of broadcast)--------------------------- >>TIP 2: you can get off all lists at once with the unregister command >> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) >> > > > >---------------------------(end of broadcast)--------------------------- >TIP 9: the planner will ignore your desire to choose an index scan if your > joining column's datatypes do not match >
Javascript does suck, but I have a captive audience. They have to have it turned on or nothing happens when you click thesubmit button. If nothing happens when you click the submit button, you don't get paid =8^O >>> Lincoln Yeoh <lyeoh@pop.jaring.my> 08/02/03 02:32AM >>> If it's double-click stuff why don't you just not submit duplicate queries? There are very many methods - e.g. must have valid token to submit query, token only valid for one query. That sort of thing. e.g. store hash of certain values (hidden params, sessionid) in the form (including a random salt, and row num) in a table, as a token. Then send the form with token to the user. When user submits form, do a select for update on the table for that row, if particular row does not have matching token, flag an error. If no row - flag error. Update row to invalidate the token. You could even limit the number of concurrent queries that way, with a bit more overhead. You'll have a bottleneck, but since it's only for expensive (slow) queries it might not be a big problem. Javascript is annoying, plus if it's off it doesn't work. Regards, Link. At 01:19 PM 8/1/2003 +0000, Ian Harding wrote: >The "solution" I finally implemented seems to be pretty good, graying out >the button after it's pushed with javascript. That means no more >doubleclick problem, and no more hammering away at the same button. It >does not preclude the reloading of the page (reactivating the button) or >just going somewhere else and issuing another query. > >The "real" solutions involving cute little client side applets that phone >home to the server to see if the query is still running and show a phony >status bar seem like too much work and still don't prevent malicious >wankers from issuing multiple queries. > >Good luck! > >Mat wrote: > >>I am having trouble with users firing off a query from the web >>interface, and then getting bored of waiting and firing off a different >>query. >> >>As http is stateless apache, (and so the perl cgi script) only realises >>that the user has gone when it ties to send data back to the user and >>gets a "broken pipe" type response. This is usually too late as by this >>time the query has completed, using up valuable resources. >> >>Is there a tried and tested solution to this problem? >>If so please let me know! >> >>If not... >> >>I am working on a work around at the moment but have run into trouble! >> >>I have read the DBI man pages but there doesn't seem to be a cancel >>query function implemented, can anyone confirm or deny this? >> >>Can i send some control sequence using DBI to cancel the query? >> >>I had taken the approach of having two perl threads, one to run the >>query and one to poll the browser every second to see if the user is >>still there. Plan X was then to cancel the query if the user had ended >>the connection. >> >>The first problem was the lack of cancel query, second problem seems to >>be that the DBI database handles cannot be shared between thread - so i >>will have to pass a signal to the thread waiting for query to return to >>cancel it? anyone else tried this? any more gaping pitfalls that i >>should be aware of?! >> >>Thanks! >> >> >> >> >>---------------------------(end of broadcast)--------------------------- >>TIP 2: you can get off all lists at once with the unregister command >> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) >> > > > >---------------------------(end of broadcast)--------------------------- >TIP 9: the planner will ignore your desire to choose an index scan if your > joining column's datatypes do not match >