Thread: Terminate query on page exit

Terminate query on page exit

From
"david harel"
Date:
Greetings,



I use postgresql on a Linux server on a virtual machine (despite my protest
to IT personal).

The client is typically a web server implementing PHP sites.

Customers many times close a page typically when a heavy report runs for too
long.

Using the command "top", I got the impression that the query still runs even
though the page was closed.

I want to make sure the query is dropped when the customer closes the page.

Any method to kill such query would it still hang out there?



Thanks
























David Harel




QIS LTD

Re: Terminate query on page exit

From
Albe Laurenz
Date:
david harel wrote:
> I use postgresql on a Linux server on a virtual machine (despite my prote=
st to IT personal).
> The client is typically a web server implementing PHP sites.
>
> Customers many times close a page typically when a heavy report runs for =
too long.
> Using the command "top", I got the impression that the query still runs e=
ven though the page was
> closed.

That is true.

> I want to make sure the query is dropped when the customer closes the pag=
e.
> Any method to kill such query would it still hang out there?

One crude method would be to set statement_timeout to a nonzero
value - then queries that take longer than that many seconds
will be canceled.

Another option would be to use asynchronous query processing.
The challenge is of course to regularly poll the query status,
but if you can detect that the page is closed you could send
a cancel request (pg_cancel_query in PHP).

Yours,
Laurenz Albe

Re: Terminate query on page exit

From
patrick keshishian
Date:
On Tuesday, February 19, 2013, Albe Laurenz wrote:

> david harel wrote:
> > I use postgresql on a Linux server on a virtual machine (despite my
> protest to IT personal).
> > The client is typically a web server implementing PHP sites.
> >
> > Customers many times close a page typically when a heavy report runs for
> too long.
> > Using the command "top", I got the impression that the query still runs
> even though the page was
> > closed.
>
> That is true.
>
> > I want to make sure the query is dropped when the customer closes the
> page.
> > Any method to kill such query would it still hang out there?
>
> One crude method would be to set statement_timeout to a nonzero
> value - then queries that take longer than that many seconds
> will be canceled.


you don't truly mean to advise that, do you? :)
--patrick

Re: Terminate query on page exit

From
Pavel Stehule
Date:
2013/2/19 patrick keshishian <pkeshish@gmail.com>:
> On Tuesday, February 19, 2013, Albe Laurenz wrote:
>>
>> david harel wrote:
>> > I use postgresql on a Linux server on a virtual machine (despite my
>> > protest to IT personal).
>> > The client is typically a web server implementing PHP sites.
>> >
>> > Customers many times close a page typically when a heavy report runs for
>> > too long.
>> > Using the command "top", I got the impression that the query still runs
>> > even though the page was
>> > closed.
>>
>> That is true.
>>
>> > I want to make sure the query is dropped when the customer closes the
>> > page.
>> > Any method to kill such query would it still hang out there?
>>
>> One crude method would be to set statement_timeout to a nonzero
>> value - then queries that take longer than that many seconds
>> will be canceled.
>
>
> you don't truly mean to advise that, do you? :)

it is not bad advice - usually all long queries should be cancelled by
timeout - and timeout is the most simple and sometimes good enough
solution. You can set timeout just for account used for login from web
application

But there are not simple solution - complete solution needs AJAX, back
calls, ...

Regards

Pavel Stehule

> --patrick

Re: Terminate query on page exit

From
Albe Laurenz
Date:
patrick keshishian wrote:
>>> Any method to kill such query would it still hang out there?
>>
>> One crude method would be to set statement_timeout to a nonzero
>> value - then queries that take longer than that many seconds
>> will be canceled.
>=20
> you don't truly mean to advise that, do you? :)

Well, it might be useful as a kind of safeguard against
runaway queries that hog the server for hours.

If you expect that no reasonable query will take
more than a couple of minutes, you could set
statement_timeout=3D600 to kill anything that's still alive
after 10 minutes.

Yours,
Laurenz Albe

Re: Terminate query on page exit

From
Jeff Janes
Date:
On Tue, Feb 19, 2013 at 4:38 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
> 2013/2/19 patrick keshishian <pkeshish@gmail.com>:
>> On Tuesday, February 19, 2013, Albe Laurenz wrote:
>>>
>>>
>>> One crude method would be to set statement_timeout to a nonzero
>>> value - then queries that take longer than that many seconds
>>> will be canceled.
>>
>>
>> you don't truly mean to advise that, do you? :)
>
> it is not bad advice - usually all long queries should be cancelled by
> timeout - and timeout is the most simple and sometimes good enough
> solution. You can set timeout just for account used for login from web
> application

It would be nice if a long running query could occasionally check to
see if it still has somewhere to send the results it is computing.
Rather than running for hours only to give a "could not send data to
client: Broken pipe" as soon as the first row becomes available.
client_alive_timeout?

Cheers,

Jeff

Re: Terminate query on page exit

From
Pavel Stehule
Date:
2013/2/19 Jeff Janes <jeff.janes@gmail.com>:
> On Tue, Feb 19, 2013 at 4:38 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
>> 2013/2/19 patrick keshishian <pkeshish@gmail.com>:
>>> On Tuesday, February 19, 2013, Albe Laurenz wrote:
>>>>
>>>>
>>>> One crude method would be to set statement_timeout to a nonzero
>>>> value - then queries that take longer than that many seconds
>>>> will be canceled.
>>>
>>>
>>> you don't truly mean to advise that, do you? :)
>>
>> it is not bad advice - usually all long queries should be cancelled by
>> timeout - and timeout is the most simple and sometimes good enough
>> solution. You can set timeout just for account used for login from web
>> application
>
> It would be nice if a long running query could occasionally check to
> see if it still has somewhere to send the results it is computing.
> Rather than running for hours only to give a "could not send data to
> client: Broken pipe" as soon as the first row becomes available.
> client_alive_timeout?

it is not bad idea

Regards

Pavel

>
> Cheers,
>
> Jeff