Timeout slow queries ?

[riston]

What to do you think about adding check if the query time exceed certain limit then it stops the execution ?

[benighted]

This is already a postgres feature, and can be set per session at runtime:

statement_timeout (integer)

Abort any statement that takes more than the specified number of milliseconds, starting from the time the command arrives at the server from the client. If log_min_error_statement is set to ERROR or lower, the statement that timed out will also be logged. A value of zero (the default) turns this off.

From http://www.postgresql.org/docs/9.3/static/runtime-config-client.html#GUC-STATEMENT-TIMEOUT

[riston]

Yes it is, but like in many cloud postgtres providers like Heroku do not allow to change these settings. Its only possible if you have full access to change db config.

[hoegaarden]

Just tried on heroku:

# SET statement_timeout TO "1ms";
SET
Time: 48.992 ms
# SHOW statement_timeout;
 statement_timeout 
-------------------
 1ms
(1 row)

Time: 44.789 ms
# select pg_sleep(1);
ERROR:  canceling statement due to statement timeout
Time: 91.434 ms
# SET statement_timeout TO DEFAULT;
SET
Time: 45.139 ms
# SHOW statement_timeout;
 statement_timeout 
-------------------
 0
(1 row)

Time: 46.045 ms
# 

HTH

[strk]

There are still cases in which this would be useful, in particular when used with pg_bouncer which on lost connection tries to reconnect keeping the query on hold as if it was a query taking a long time to execute. In reality postgresql is never hit with that waiting query, so cannot timeout on its side

[brianc]

I think this would be best served in a separate module. node-postgres supports the 'cancel' command...but having timeouts built in is outside the scope of this already overscoped module I think. 😄

I'm happy to discuss or give some tips via email or IRC if you're interested in implementing a timeout module. brian.m.carlson@gmail.com or brianc on freenode

[davisford]

FWIW @brianc I disagree. I think this library should expose this as a configurable option. We're hitting some nasty issues with this now on our data warehouse. We're lucky enough that we have complete control over the database, so we can configure this in postgres itself, but I feel pretty strongly that the right thing to do is expose the ability to set this in the library itself.

[juliusza]

statement_timeout (integer) would work only while client has connection to server, what if your TCP connection is stuck instead? Timeout should be implemented in PG driver as well.

[edevil]

So, just to be clear, there is currently no way to specify a query timeout via configuration that would guard against a non-responsive server (after establishing the connection)?

[abenhamdine]

for thread readers : see also brianc/node-pg-pool#85 for timeout issue when the pool is full
unfortunately, the fix is not released yet