暂时增加 Rails 中 Postgres 查询的"statement_timeout"？

Question

我已经在database.yml到几秒钟内配置了我的statement_timeout，但是我的应用程序中有一些昂贵的查询，需要更长的查询执行时间。在每个查询级别实现此目的的推荐方法是什么？ 我需要暂时将statement_timeout设置为更大的值，执行查询并将其重置为默认值？还是甚至不需要重置？

Answer 1

我认为您只能通过更改整个连接的statement_timeout然后将其还原来实现这一目标：

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

在数据库级别，您只能按照本指南为当前事务设置statement_timeout：

BEGIN;
SET LOCAL statement_timeout = 250;
...
COMMIT;

Answer 2

为了扩展接受的答案，以下是实现模块DatabaseTimeout的方法，这也确保将statement_timeout设置重置回其原始值。

# 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

Answer 3

正如@berkes指出的那样，您冒着污染同一AR连接上并发查询的风险(例如，我们在将上述方法与PgBouncer一起使用时遇到了问题(。所以它应该在事务中运行。这是我写的供我公司使用的：

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