Enable more flexible killing of jobs

[hammerhead]

Problem Statement

Killing all running jobs of a certain user or all jobs that match a certain pattern (like all SELECT statements) is currently not easily possible. KILL only supports ALL or a single job ID. One needs to manually gather all job ids and send a sequence of KILL statements.

Possible Solutions

Support a function (like PostgreSQL's pg_cancel_backend) so you can do something like:

SELECT kill(id)
FROM sys.jobs
WHERE stmt LIKE '%my_table%'
  AND username = 'u';

Considered Alternatives

No response

[hlcianfagna]

Another alternative solution could be to support a generic function to run arbitrary commands against a foreign server, we could then configure a foreign server pointing to the same CrateDB instance and use it to run the dynamically generated KILL statements.

[mfussenegger]

Could you elaborate a bit more on why there's a need to kill jobs like that?

[hammerhead]

One example is the abortion of a benchmark run consisting of many queries, and you want to kill those before starting another run. The benchmark tool itself didn't manage to kill them itself. KILL ALL cannot be used, as there is also parallel ingestion which should remain running. As the benchmark is using a particular user, it is handy to kill all queries of that user.

[matriv]

I'm only guessing here, but I could see it being handy for a CrateDB user that connects to the DB with different users, one doing insertion, one doing dashboard queries, one doing heavy analytic queries, etc. and you want to kill for example all heavy analytic queries that are saturating the cluster under some heavy ingest load peak.

Could also be that you want to do something similar based on sql statements filtering, if for example someone is experimenting (on a prod cluster) with new analytic queries that are long-running/resource-consuming, and you want to kill them. (if you have hundreds or thousands of jobs in sys.jobs table won't be easy to filter out by scrolling)