Thanks Jeremy, I will try the idea and see how it goes. On Tue, Mar 23, 2021 at 6:56 PM Jeremy Evans <[email protected]> wrote:
> On Tue, Mar 23, 2021 at 2:11 PM Dwayne <[email protected]> wrote: > >> >> Hi, >> >> Our reporting DB can generate queries that take a long time to execute. >> When filters on our web app changes a new DB query is run, each one is >> expensive. >> >> For e.g.: >> >> - Change filters >> - HTTP request, A, to reporting endpoint causes expensive query >> (takes 10 secs) >> - 1 second later >> - Change filters >> - HTTP request, B, to reporting endpoint causes expensive query >> (takes 10 secs) >> - 1 second later >> - Change filters >> - HTTP request, C, to reporting endpoint causes expensive query >> (takes 10 secs) >> >> Now, I have three expensive queries running but I don't care about the >> results returned from A and B. *How can I find and cancel the queries >> due to A and B?* >> >> One approach being considered: >> >> Tag the DB queries we make with a UUID coming with the HTTP request. Have >> the front-end hold that latest query UUID requested and send a cancellation >> request along with a new one when the report changes. >> >> I can tag the dataset with `ds.comment("uuid: #{uuid}")` by using the >> sql_comments extension. *How can I find the connection that's executing >> the query for that tagged dataset in order to cancel the query?* >> > > Sequel does not offer direct support for canceling queries. Any way to do > this is going to be database dependent. For PostgreSQL, you could possibly > use a shared hash with UUID (or other session-specific identifier) keys and > values that are arrays of pids (you can get the pid for the connection > using pg_backend_pid). Then when a new request comes in, for each entry in > the array of pids, use pg_cancel_backend. It's also possible to store the > information in a database table instead of using a shared hash. > > Thanks, > Jeremy > > -- > You received this message because you are subscribed to a topic in the > Google Groups "sequel-talk" group. > To unsubscribe from this topic, visit > https://groups.google.com/d/topic/sequel-talk/BEUNlaPaKR8/unsubscribe. > To unsubscribe from this group and all its topics, send an email to > [email protected]. > To view this discussion on the web visit > https://groups.google.com/d/msgid/sequel-talk/CADGZSSfcY%3DGTjMN_rOaamBVOexyx09j0yCkEemtBxiKzL4t%3DYw%40mail.gmail.com > <https://groups.google.com/d/msgid/sequel-talk/CADGZSSfcY%3DGTjMN_rOaamBVOexyx09j0yCkEemtBxiKzL4t%3DYw%40mail.gmail.com?utm_medium=email&utm_source=footer> > . > -- You received this message because you are subscribed to the Google Groups "sequel-talk" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/sequel-talk/CADr58vMvJv67Hsc5edFw%3Dr9BVd5C3SdbXdaSQMBjj7cB0QtB7A%40mail.gmail.com.
