Temporarily increase 'statement_timeout' for Postgres queries in Rails?

Question

I already configured my statement_timeout in database.yml to some seconds, but there are some expensive queries in my application, which require longer query execution times. What would be the recommended way to achieve this on a per-query level? I would need to temporarily set the statement_timeout to a larger value, execute the query and reset it to the default value? Or is the resetting not even required?

Answer 1

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;

Answer 2

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