Postgresql server-side support for session-based statement timeouts

[Cobus Carstens]

Hi all

Something that may be worth noting is that Postgresql has server-side support for session-based statement timeouts. Search for "statement_timeout" here: http://www.postgresql.org/docs/9.3/static/runtime-config-client.html

If one needs to cap the amount of time that may be spent running a statement and also want to leave the database in a known/consistent state, this is an option.
I am not advocating that this be used at a driver level -- just bringing it to everyone's attention. It may be useful.

psql example:

<snip>

=> set statement_timeout=1500; -- Timeout in ms
=> select pg_sleep(1); -- Sleep 1 second
 pg_sleep
----------
 
(1 row)

=> select pg_sleep(2); -- Sleep 2 seconds
ERROR:  canceling statement due to statement timeout

</snip>

Erlang example:

<snip>
> pgsql:squery(H, "SET statement_timeout=1500").
{ok,[],[]}

> pgsql:squery(H, "SELECT pg_sleep(1)").       
{ok,[{column,<<"pg_sleep">>,void,4,-1,0}],[{<<>>}]}

> pgsql:squery(H, "SELECT pg_sleep(2)").
{error,{error,error,<<"57014">>, <<"canceling statement due to statement timeout">>,[]}}

</snip>

Regards,
  Cobus

[David Welton]

After finally digging into the timeout stuff some more, and chatting a
bit with the kind and helpful people on #postgres, I think this is the
best option. The CanceRequest stuff is a mess.

It can be set via the SET statement at any time during a session, so
you can change it as needs be.

Another thing that would make sense to me, if you have a need for
something closer to per-query control over this, is to have N
connections to Postgres, where some of those are dedicated to queries
with no timeouts or slow queries, and other connections where there is
a timeout set for whatever low value you want.

> --
> You received this message because you are subscribed to the Google Groups
> "Erlang epgsql Postgres driver use and development" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to epgsql+un...@googlegroups.com.
> For more options, visit https://groups.google.com/d/optout.



--
David N. Welton

http://www.welton.it/davidw/

http://www.dedasys.com/

[Joe DeVivo]

I've been looking for a way to do this, to migrate our sqerl library from wg -> epgsql.

I was able to use the set statement timeout command to do this effectively.

https://github.com/opscode/sqerl/pull/88

Right now, it just mimics the expected behavior from wg's branch, but at least it shows that it can do that. This works for us because we're checking these connections out of a pool, and we'll be able to add specific per query timeouts in a future feature set.

There's probably a more elegant way to do this at the epgsql level, but this solution works for now.

Thanks!

--joe