Stop (long) running SQL query in PostgreSQL when session or requests no longer exist?

I'm not sure where to start on addressing this issue but if I have a AJAX web application that sends requests to the server and runs long queries on the database (postgresql in my case), is there a way to stop or kill the queries if while still running, the user refreshes the page or closes the session...etc?

Solution 1:

To stop the query:

SELECT pg_cancel_backend(procpid);

To kill the database connection:

SELECT pg_terminate_backend(procpid);

To get an overview of the current transactions, to get the proced id's:

SELECT * FROM pg_stat_activity;

http://www.postgresql.org/docs/current/interactive/functions-admin.html

Solution 2:

Considering your psql configured to run and connect to current dev database this one liner comes handy in development when testing complicated queries which can hung, just kills whatever runs:

If not configured properly - add flags for user/password/database

psql -c "SELECT procpid FROM pg_stat_activity;" -t | xargs -n1 -I {} psql -c "SELECT pg_cancel_backend({})"