CODEWITHSUNDEEP
ruby-on-railspostgresqlheroku
Clark
Clark

Reputation: 3013

how to set postgresql command timeout in rails

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?

Upvotes: 19

Views: 17203

Answers (4)

user6105366
user6105366

Reputation:

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.

Upvotes: 6

Bill Lipa
Bill Lipa

Reputation: 2079

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

Upvotes: 23

Clark
Clark

Reputation: 3013

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?

Upvotes: 6

user80168
user80168

Reputation:

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';

Upvotes: 5

Related Questions