Query cancellation in ClickHouse

[ruslanen]

Hi, I use last version of ClickHouse.Client (6.7.1) and I want to get some advices about correct query cancellation.

My .NET application generates different complex and long-running SELECT queries to the ClickHouse.
I know that under the hood ClickHouse.Client uses HttpClient which works with connection pool (https://github.com/DarkWanderer/ClickHouse.Client/wiki/Connection-lifetime-&-pooling).
It means if I cancel query in my app it will not be cancelled in ClickHouse because of connection pool implementation in .NET.
I also used ConnectionString parameter set_cancel_http_readonly_queries_on_client_close=1; but now I realized that it was useless.
My question is how to cancel queries correctly?
For example, should I use KILL QUERY WHERE query_id=<id> or I have to make my own HttpClient which works different way with connection pool?

[DarkWanderer]

Hi @ruslanen

Currently, there is no support for cancelling query on the server if cancelled on the client. This is an interesting idea to implement, but at the moment it is not there

However, if your app is 'interactive' (i.e: it is only expected to run one query at a time), a way to solve this would be to use a single instance of ClickHouseConnection with UseSession flag alongside replace_running_query setting and without custom HttpClient/HttpClientHandler. This way, the library will spawn a single HTTP connection with same lifetime as the ClickHouseConnection instance, and executing next query will cause previous one to be cancelled

[ruslanen]

Hi @ruslanen

Currently, there is no support for cancelling query on the server if cancelled on the client. This is an interesting idea to implement, but at the moment it is not there

However, if your app is 'interactive' (i.e: it is only expected to run one query at a time), a way to solve this would be to use a single instance of ClickHouseConnection with UseSession flag alongside replace_running_query setting and without custom HttpClient/HttpClientHandler. This way, the library will spawn a single HTTP connection with same lifetime as the ClickHouseConnection instance, and executing next query will cause previous one to be cancelled

Unfortunately, my application may have many parallel requests, so this option will not work for me.
Any way thanks a lot for your reply!
Most likely I will implement KILL QUERY.