Terminate query on page exit

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.

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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.

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=600 to kill anything that's still alive
after 10 minutes.

Yours,
Laurenz Albe

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general