I'm using heroku and heroku postgresql. How do I set db command timeout so that I get an exception when a sql command takes longer than 10 seconds?
Add a comment
|
4 Answers
Configure your database.yml like this, the key bit being the variables hash:
defaults: &default
adapter: postgresql
encoding: unicode
pool: 5
min_messages: warning
variables:
statement_timeout: 5000
5 Comments
theANDYM
You can
SET other parameters per session as well: github.com/rails/rails/blob/4-2-stable/activerecord/lib/…
Schneems
If I run
rails dbconsole and then SHOW statement_timeout; it says that i've not got any statement timeout. If this works, is there a way to verify that it works?
Bill Lipa
I see the same behavior with
rails db. The easiest way to validate it is something like User.select('pg_sleep(10)') from the rails console and verifying that it times out after 5 seconds or whatever you configured it to.
collimarco
Is there any way to add this only to the frontend and not to background workers (Sidekiq)?
Bill Lipa
Sure. The database.yml can contain ERB (ruby logic) so you could do something like pass a command line flag or set an environment variable for that process type, then do an if on the timeout config.
I found a solution here: Ruby on Rails: How to set a database timeout in application configuration?
Add
ActiveRecord::Base.connection.execute('set statement_timeout to 10000')
in the end of the environment.rb file.
Does anyone see a better way?
2 Comments
GregB
This command runs the SQL command from the other answer through the ActiveRecord connection so that is remains true for the duration of the connection. It should be effective.
Ace Dimasuhid
would this work in heroku? i.e heroku cutting your worker connection after 30 secs?
Adding
ActiveRecord::Base.connection.execute('set statement_timeout to 10000')
to the end of the environment.rb file did not work for me.
What worked was adding
ActiveRecord::Base.connection.execute("SET statement_timeout = '10s'")
to an initialize file.
Comments
I don't know ruby, but in PostgreSQL you can simply run this:
SET statement_timeout = '10s'
and afterwards, all queries in this connection (session) will have strict limit on runtime.
You can also change it in postgresql.conf as global default, or even make it default in given database or for given user like:
ALTER DATABASE web SET statement_timeout = '10s';
ALTER USER bad_user SET statement_timeout = '10s';