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 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/CADGZSSfcY%3DGTjMN_rOaamBVOexyx09j0yCkEemtBxiKzL4t%3DYw%40mail.gmail.com.
