Re: best way to kill long running query?

On Mar 21, 2007, at 3:09 PM, Bill Eaton wrote:

>>> I want to allow some queries for my users to run for a prescribed
>>> period
> of
>>> time and kill them if they go over time. Is there a good way to
>>> do this?
>
>> set statement_timeout perhaps?
>
> Ooh. I like that. It would be absolutely brilliant if I could
> figure out how
> to get it to work with ADO and the Windoze ODBC driver. I've tried
> appending
> statement_timeout to my connection string i.e.
>   ConnString = "DRIVER={PostgreSQL
> Unicode};SERVER=MYSERVER;DATABASE=MYDB;UID=client;set_timeout=1"
> but it has no effect on a SELECT statement that takes at least 3 or 4
> seconds to execute and only returns 184 (out of 600,000) records.
>
> I've also tried different syntaxes to pass the parameter
>   set_timeout=1
>   set_timeout='1'
>   set_timeout=(1)
>   set_timeout=('1')

that doesn't look like "statement_timeout" to me, but then my glasses
might be out of date.

try this as postgres superuser for your user:

alter user foobar set statement_timeout=1;

where foobar is the user you connect as.

then this user's default statement_timeout is set.... he can override
it at will, though.