[Gllug] postgres question

[Richard Huxton]

On 22/08/11 09:56, Oliver Howe wrote:
>
> Hi,
>
> I am seeing some waiting postgres processes when users create large
> select queries via the web browser.
> Often their browser times out, or they leave the page, but the query
> persists.
>
> When I do a pg_top and then Q to show what the query is doing it shows
> me the select is still happening.
>
> I have tried cancelling this select with
>
> SELECT pg_cancel_backend(8354) FROM pg_stat_activity;
>
> where 8354 was the PID of the select process
>
> but it persisted.

My guess is it's blocking on a socket trying to send results back. It'll 
time out eventually whenever your TCP timeouts occur (can be a very long 
time).

This assumes that the client is talking to PG over TCP/IP. Is that the case?

If you want the queries to cancel when they close the browser, have a 
small javascript timer talk to the webserver (or a holding page with a 
refresh). If you've not heard from a client for 60 seconds then the 
web-app can cancel its query.

-- 
   Richard Huxton
   Archonet Ltd
--
Gllug mailing list  -  Gllug at gllug.org.uk
http://lists.gllug.org.uk/mailman/listinfo/gllug