Unfortunately setTimeout is not implemented for JDBC/postgres. Is there some way I can simulate or workaround this? Functionally I want to execute the query and the then break if it takes longer than N seconds
1
-
I found this question and answer helpful also for a python/psycopg2 issue I encountered. Psycopg2 seems to have allow timeout setting at connection time, but this interface was abstracted away in my case. Adding this comment to benefit others searching on SO.Setjmp– Setjmp2013-04-27 17:52:37 +00:00Commented Apr 27, 2013 at 17:52
Add a comment
|
3 Answers
The "statement_timeout" looks like what you want.
SET statement_timeout TO 1000; -- for a second
<your_query_here>;
RESET statement_timeout; -- reset
4 Comments
Magnus Hagander
You're better off using RESET statement_timeout; after the query has completed - in case there is a default value...
Babar
Tested this on pgAdmin3. Only worked when the SET statement_timeout is executed separately from the actual query. When executed together, pgAdmin3 uses whatever the timeout value was already set and dosen't use the one provided with the query.
David Leppik
Alternatively, use SET LOCAL statement_timeout TO 1000, which limits the scope to the current transaction. That way you don't have to worry about making it to the RESET, you just have to limit the scope of the transaction.
David Leppik
To make it clearer, I just wrote my own answer; feel free to update yours.
Using the LOCAL keyword limits the scope of the statement_timeout to the current transaction. That way, if anything goes wrong (e.g. it times out) the timeout gets reset.
BEGIN TRANSACTION;
SET LOCAL statement_timeout TO 1000; -- one-second timeout
SELECT COUNT(*) FROM really_huge_table; -- your slow query
ROLLBACK; -- reset
Comments
One way might be to try running the query in a Timer class. Throw an exception if the Timer ends without a value returned.
Hibernate and JDO supply such a construct. Maybe they'd be good alternatives for you.
1 Comment
David Leppik
You have to be careful how this is implemented; if done wrong, you end up with many blocked threads holding SQL connections that are just being ignored. Part of the issue here is that you can't force a thread to throw an exception if it's in the middle of doing something.