How to cancel expensive Postgresql query from Rails?

Question

In my Rails application I have the controller that is responsible for generating reports. Some of those reports take a lot of time to generate, which exceed 30 seconds limit on Heroku. In such case I want to display notification to the user after 25 seconds and also cancel the database query. My initial idea was to use Timeout.

class ReportsController < ApplicationController
  def expensive_report
    Timeout.timeout(25) do
      @results = ExpensiveQuery.new(params).results
    end
  rescue Timeout::Error
    render action: "timeout"
  end
end

Timing out works fine, but the respective query is not canceled. It is easy to reproduce in Rails console

begin
  Timeout.timeout(1) do
    ActiveRecord::Base.connection.execute("SELECT pg_sleep(10)")
  end
rescue Timeout::Error
  puts "Timeout"
end

result = ActiveRecord::Base.connection.execute("SELECT 1 AS value")
puts result[0]["value"]

This code will output "Timeout" and then block on line result = ActiveRecord::Base.connection.execute("SELECT 1 AS value") until pg_sleep query finishes.

How can I cancel such query from within Rails? I am hosting my app on Heroku, so priviliges are limited to run commands such as pg_cancel_backend or pg_terminate_backend.

Answer 1

you can set statement_timeout on session level (without transaction and skipping local).

or transaction:

t=# begin; set local statement_timeout to 1; select pg_sleep(3);end;
BEGIN
Time: 0.096 ms
SET
Time: 0.098 ms
ERROR:  canceling statement due to statement timeout
Time: 1.768 ms
ROLLBACK
Time: 0.066 ms

or as default for the user:

alter user no_long_qries set statement_timeout to 1;