CODEWITHSUNDEEP
ruby-on-railsdatabasepostgresqltimeout
Fabian Jäger
Fabian Jäger

Reputation: 497

Temporarily increase 'statement_timeout' for Postgres queries in Rails?

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?

Upvotes: 23

Views: 18808

Answers (3)

ENewmeration
ENewmeration

Reputation: 111

As @berkes pointed out, you run the risk of polluting concurrent queries on the same AR connection with the accepted answer (e.g. we ran into issues when using the above with PgBouncer). So it should be run in a transaction. This is what I wrote for use at my company:

module DatabaseTimeout
  module_function

  # Usage: DatabaseTimeout.timeout(10) { run_some_query }
  def timeout(nb_seconds)
    ActiveRecord::Base.transaction do
      ActiveRecord::Base.connection.execute("SET LOCAL statement_timeout = '#{nb_seconds.to_i}s'")
      yield
    end
  end
end

Upvotes: 11

Matthieu Libeer
Matthieu Libeer

Reputation: 2365

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

Upvotes: 12

Moamen Naanou
Moamen Naanou

Reputation: 1713

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;

Upvotes: 29

Related Questions