Temporarily increase 'statement_timeout' for Postgres queries in Rails?
To expand on the accepted answer, here's how one could implement a module DatabaseTimeout
, that also makes sure to reset the statement_timeout
setting back to its original value.
# Ruby's `Timeout` doesn't prevent queries from running for a long time.
#
# To prove this, run the following in a console (yes, twice):
# Timeout.timeout(1.second) { ActiveRecord::Base.connection.execute('SELECT pg_sleep(100);') }
# Timeout.timeout(1.second) { ActiveRecord::Base.connection.execute('SELECT pg_sleep(100);') }
# => The 2nd call should run for a long time.
#
# DatabaseTimeout's purpose is to enforce that each query doesn't run for more than the given timeout:
# DatabaseTimeout.timeout(1.second) { ActiveRecord::Base.connection.execute('SELECT pg_sleep(100);') }
# DatabaseTimeout.timeout(1.second) { ActiveRecord::Base.connection.execute('SELECT pg_sleep(100);') }
# => Both queries are interrupted after 1 second
module DatabaseTimeout
# Usage: DatabaseTimeout.timeout(10) { run_some_query }
def self.timeout(nb_seconds)
original_timeout = ActiveRecord::Base.connection.execute('SHOW statement_timeout').first['statement_timeout']
ActiveRecord::Base.connection.execute("SET statement_timeout = '#{nb_seconds.to_i}s'")
yield
ensure
if original_timeout
ActiveRecord::Base.connection.execute("SET statement_timeout = #{original_timeout}")
end
end
end
I think you can achieve that only by changing the statement_timeout
for whole connection then revert it back:
def execute_expensive_query
ActiveRecord::Base.connection.execute 'SET statement_timeout = 600000' # 10 minutes
# DB query with long execution time
ensure
ActiveRecord::Base.connection.execute 'SET statement_timeout = 5000' # 5 seconds
end
On DB level, you can set statement_timeout
for the current transaction only as per this guide:
BEGIN;
SET LOCAL statement_timeout = 250;
...
COMMIT;