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?
Add a comment
|
3 Answers
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;
Comments
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
4 Comments
chris finne
Need single quotes around the original_timeout in the ensure
berkes
How does this deal with transactions? It appears that the SHOW statement_timeout then a connection.execute and then the actual transacted-query would cause issues. Or am I misunderstanding how Rails handles this in transactions?
Matthieu Libeer
@berkes When
DatabaseTimeout.timeout is called inside a transaction, the SHOW statement and the 2 SET statements are part of the transaction, which I think is not an issue.berkes
@MatthieuLibeer thanks. But that means you have to wrap all
DatabaseTimeout.timeout in a database transaction, or run the risk of polluting concurrent queries (on the same AR connection) with the altered timeout, not?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
3 Comments
steve
This is super handy, thanks for this. One note:
statement_timeout does not accept units, as far as I can tell. Units are ms. Not sure if this is config-related or postgres-version related.
mochatiger
@steve using pg 14.8 at least, either with seconds and single quotes:
'10s' or no quotes: 1000 - both worksteve
Thanks @mochatiger, that's good to know! I'm on postgres version 12.18