❓ Rails + Psql: Timeout.timeout vs statement timeout vs local statement timeout? [question]

[fragkakis]

Hello,

Thank you for this great resource 🙏 !

not sure if this type of issue is acceptable for the repo, but here it goes:

I have a potentially expensive query that could take more time than I would want. I can think of the following ways to limit it:

1. Wrap it in Timeout.timeout (I know this guide is clearly against it, please bear with me):

begin
  Timeout::timeout(TIMEOUT_SECONDS, Timeout::Error) do
    @count = calculate_expensive_count
  end
rescue Timeout::Error => ex
  # handle
end

What happens to the query in case it exceeds TIMEOUT_SECONDS? Is it killed, or will it go on until it finishes?

2. Temporarily set global timeout

begin
  ActiveRecord::Base.connection.execute("SET statement_timeout TO #{TIMEOUT_SECONDS*1000}")
  @count = calculate_expensive_count
rescue ActiveRecord::StatementInvalid
  # handle
ensure
  ActiveRecord::Base.connection.execute("SET statement_timeout TO #{ORIGINAL_PSQL_TIMEOUT_SECONDS*1000")
end

3. Start a transaction and set a local timeout

begin
  ActiveRecord::Base.transaction do
    ActiveRecord::Base.connection.execute("SET LOCAL statement_timeout TO #{TIMEOUT_SECONDS*1000}")
    @count = calculate_expensive_count
  end  
rescue ActiveRecord::StatementInvalid
  # handle
end

Between 2 and 3 I prefer 3 because it does not mess with the connection's statement timeout, which is cleaner. I know the repo clearly instructs against Timeout::Timeout, I have just added it because the example given in the linked article is about Redis, and not the DB. Stylewise, I would prefer 1 if it's not dangerous (for the particular use case), because I don't need multiple statements in Postgres.

Which is preferable and why?

[ankane]

Hey @fragkakis, 3 is preferred. 1 can leave the query running on the database server and has the same concerns as Redis in the linked article. 3 is better than 2 for the reason you mentioned.

[ankane]

Simple test case for 1:

require "active_record"
require "pg"
require "timeout"

ActiveRecord::Base.establish_connection adapter: "postgresql", database: "timeout_test"

begin
  Timeout::timeout(1) do
    ActiveRecord::Base.connection.execute("SELECT pg_sleep(10)")
  end
rescue Timeout::Error
  puts "Timed out"
end

started_at = Time.now
ActiveRecord::Base.connection.select_all("SELECT 1").to_a
p (Time.now - started_at).round

The connection remains busy until the first statement completes, even after the timeout.

[fragkakis]

Thank you very much. I am closing the issue.