Re: best way to kill long running query?

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')

I don't think you can set GUC parameters from the ODBC driver. Your
options are:

* postgresql.conf. Will apply to all sessions to the databse.

* database. Use ALTER DATABLASE foo SET statement_timeout=<something>.
Will then affect all connections to that database.

* user. Use ALTER ROLE foo SET statement_timeout=<something>. Will then
affect all connections from that user.

* change your application to issue a "SET statement_timeout=<something>"
query before anything else it sends.

Note that statement timeout will cancel the whole command. It won't
return "as many rows as it has reached by the timeout", it will return
nothing at all.

//Magnus