Rails 4.2 Postgres 9.4.4 statement_timeout doesn't work

user_4164651 0
Viewed 904

I am trying to set a statement_timeout. I tried both setting in database.yml file like this

variables:
  statement_timeout: 1000

And this

ActiveRecord::Base.connection.execute("SET statement_timeout = 1000")

Tested with

ActiveRecord::Base.connection.execute("select pg_sleep(4)")

And they both don't have any effect.

I am running postgres 10 in my local and the statement_timeouts works just expected. But on my server that is running postgres 9.4.4, it simply doesn't do anything.

I've check Postgres' doc for 9.4 and statement_timeout is available. Anyone can shed some light?

postgresql ruby-on-rails
1 Answers

I wasn't able to replicate this issue locally using: Postgresql 9.4.26. But it might be useful to share what I've tried and some thoughts around the server issue. Here is what I've tried (a useful bit might be a query to verify the PG version from rails):

# Confirming I am executing against 9.4.x PG:

irb(main):002:0> ActiveRecord::Base.connection.execute("select version()")
   (10.8ms)  select version()
=> #<PG::Result:0x00007ff74782e060 status=PGRES_TUPLES_OK ntuples=1 nfields=1 cmd_tuples=1>
irb(main):003:0> _.first
=> {"version"=>"PostgreSQL 9.4.26 on x86_64-apple-darwin18.7.0, compiled by Apple clang version 11.0.0 (clang-1100.0.33.17), 64-bit"}


# Set timeout:

irb(main):004:0> ActiveRecord::Base.connection.execute("SET statement_timeout = 1000")
   (0.4ms)  SET statement_timeout = 1000
=> #<PG::Result:0x00007ff7720a3d88 status=PGRES_COMMAND_OK ntuples=0 nfields=0 cmd_tuples=0>


# Confirm it works - it is ~1s and also stacktrace is pretty explicit about it:

irb(main):005:0> ActiveRecord::Base.connection.execute("select pg_sleep(4)")
   (1071.2ms)  select pg_sleep(4)
.... (stacktrace hidden)
ActiveRecord::StatementInvalid (PG::QueryCanceled: ERROR:  canceling statement due to statement timeout)
: select pg_sleep(4)

Here is what to try

Since the issue occurs on server only and since statement_timeout works on other minor version and locally, one thing that comes to mind is the lack of privileges to update statement_timeout from where it is attempted. Perhaps rails pg login used to make db connection is not allowed to update that setting. The best would be to verify that either via rails console on a server:

irb(main):004:0> ActiveRecord::Base.connection.execute("SET statement_timeout = 1000")
irb(main):004:0> irb(main):003:0> ActiveRecord::Base.connection.execute("show statement_timeout").first
   (0.2ms)  show statement_timeout
=> {"statement_timeout"=>"1s"}

Or, it can be checked directly via psql console (some deployments allow this too):

psql myserveruser # if this was heroku's pg: heroku pg:psql

postgres=# set statement_timeout = 1000;
SET
postgres=# select pg_sleep(4);
ERROR:  canceling statement due to statement timeout
Time: 1068.067 ms (00:01.068)

Other thing to keep in mind (taken from https://dba.stackexchange.com/a/83035/90903):

The way statement_timeout works, the time starts counting when the server receives a new command from the client...

And if a function does SET statement_timeout = 100; it will have an effect only starting at the next command from the client.

user_1771540
Related