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;