CODEWITHSUNDEEP
ruby-on-railsrubypostgresqlactiverecord
dwmcc
dwmcc

Reputation: 1074

Cancel current/active query in Ruby on Rails

In my project I am running a massive query returning lots of data. Due to the size of this query, the runtime sometimes exceeds 1 minute. Ignoring other issues this may create, is there any way to cancel a currently running query?

For example, the user starts the query and is redirected to a standard 'Loading Results...' page. If they then decide that they no longer want the results, they can click a 'Cancel' button which will somehow terminate the running query.

Is this possible and how can it be done?

Thanks!

Upvotes: 2

Views: 2825

Answers (3)

bma
bma

Reputation: 9756

I did something like this recently (adding a "cancel" button to our application). The plpgsql function I hacked together is below, which is basically a wrapper around the pg_cancel_backend() function.

Caveats:

1). Your application must create an unique id (or UUID) and pass that in to the function as the "p_uid" parameter.

2). I hardcoded the application user (that doesn't change).

3). Postgresql 9.0+ (this was created for 9.2, untested in any earlier versions)

4). Security could be tightened up a bit in the function.

CREATE OR REPLACE FUNCTION public.cancel_user_query(p_uid TEXT, OUT retval BOOLEAN) RETURNS boolean
AS $function$
DECLARE
BEGIN
    /*  The pg_sleep() call at the start is because if a user issues a query
        then promptly tries to kill it, it will likely not have shown up in the
        pg_stat_activity view yet (there is a delay before it appears).
        XXX: The GUC setting "track_activities" *MUST* be enabled for this to work.
    */

    retval := FALSE;
    IF ( current_setting('track_activities')::BOOLEAN IS NOT TRUE ) THEN
        RAISE WARNING '[PUBLIC.CANCEL_USER_QUERY] - "track_activities" *MUST* be enabled for this to work';
        RETURN;
    END IF;

    /* In a system under regular high load, this might need to be bumped higher than 2 seconds */
    PERFORM pg_sleep(2); 

    WITH q AS ( SELECT pid FROM pg_stat_activity WHERE LOWER(usename) = 'YOUR_APP_USER' AND application_name = p_uid AND state <> 'idle' )
    SELECT pg_cancel_backend(pid) AS retval
    INTO retval
    FROM q;

    if ( retval IS NOT TRUE ) then
        retval := FALSE;
    end if;

    RETURN;

EXCEPTION
    WHEN others THEN
        RAISE WARNING '[PUBLIC.CANCEL_USER_QUERY] - ERROR: %',sqlerrm;
        retval := FALSE;
        RETURN;
END;
$function$ LANGUAGE plpgsql SECURITY DEFINER;

On click of the "Cancel query" button, the application executes the function and checks the returned status. Eg. SELECT retval FROM public.cancel_user_query('asifdaqiwaviafasdf') retval

Upvotes: 1

pdoherty926
pdoherty926

Reputation: 10349

Try using pg_cancel_backend.

From the docs:

Cancel a backend's current query. You can execute this against another backend that has exactly the same role as the user calling the function. In all other cases, you must be a superuser.

Upvotes: 3

Matt Gibson
Matt Gibson

Reputation: 14949

The only way I know of is to restart the database, although sometimes you can send a cancel signal, depending on the DB setup. You could look at the rack-timeout gem perhaps?

The real issue is that your query should not take that long, and I would strongly advise not approaching the problem this way. A far better way would be to examine the query and attempt to optimise it. There are few, if any queries that should legitimately be taking >1 minute to complete. Get a dump of the data onto your local machine and run EXPLAIN on the query to find the bottlenecks. I have gotten large 142s queries down to <3s by adding the right indexes, so I would be amazed if you cannot get the same effect.

Upvotes: 1

Related Questions