2

A client makes a request to a Postgres database for data. The database is too slow to respond. Is it possible for the client to send a CANCEL request or something to tell Postgres to stop working on the query?

  • It is possible to send a ctrl+c or similar from the client that runs the query. Otherwise, it is only possible to kill it with pg_terminate_backend(), that needs superuser rights. Or it can be sent a SIGTERM from the OS, if you have access there. – dezso Apr 23 '15 at 16:33
  • 2
    pg_terminate_backend and pg_cancel_backend do not require superuser rights on 9.2 or above if you're terminating a connection owned by the same user. So if you're on a recent version, use that. – Craig Ringer Apr 24 '15 at 0:37
1

Possible workaround: we created a stored procedure for users that would accept a PID to be killed (eg: sp_kill 4221) and would write the requesting user name and PID into a table. That table was checked every 5 minutes by a script. If the PID requested belonged to the user who requested it and it was still active (ie, select pid, usename from pg_stat_activity;) then the superuser would do the kill on the user's behalf.

Note that we only implemented this in development.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.