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.
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;
You can make that timeout apply to all database requests by adding this to the config/database.yml:
default: &default
adapter: postgresql
...
variables:
statement_timeout: 25000
Or this to the end of config/environment.rb:
ActiveRecord::Base.connection.execute('set statement_timeout to 25000')
I found cool solution that does not require modifications to current queries.
class ReportsController < ApplicationController
def expensive_report
Timeout.timeout(25) do
@results = ExpensiveQuery.new(params).results
end
rescue Timeout::Error
ActiveRecord::Base.connection.raw_connection.cancel
render action: "timeout"
end
end
When running in console I need to add check if connection is active.
begin
Timeout.timeout(1) do
ActiveRecord::Base.connection.execute("SELECT pg_sleep(10)")
end
rescue Timeout::Error
ActiveRecord::Base.connection.raw_connection.cancel
ActiveRecord::Base.connection.active?
puts "Timeout"
end
result = ActiveRecord::Base.connection.execute("SELECT 1 AS value")
puts result[0]["value"]
Without calling ActiveRecord::Base.connection.active? it will raise ActiveRecord::StatementInvalid: PG::QueryCanceled.
This works pretty nice, but I am yet sure if there are any hidden problems with that.