Allow interactive queries finish in the background

[alexey-milovidov]

Use case

A user can run an interactive session with long-running INSERT SELECT.

If the session is interrupted, the query is typically canceled (after an exception about connection loss in an attempt to send progress or read a network packet from a user).

But it is sad if the query was running for a long time.

Describe the solution you'd like

If a query is not expected to return any result to the user (such as SELECT) and does not require any data from the user (such as INSERT without SELECT), we can catch exceptions related to a network connection with the user, stop sending the progress packets and waiting for the incoming packets (such as cancellation), and continue normally.

The user can reconnect and then observe the progress in SHOW PROCESSLIST.

Additional context

The current behavior (query termination on connection loss) is also good. The behavior should be controllable by a setting.

[slvrtrn]

This is extremely useful for apps that do automated data copy between tables. For example, when such an app gets a request to change the PK of the table or something similar, and this is not a small table.

Current workaround: use HTTP instead of Native, send the request with the statement and pre-generated query_id, wait until it appears in system.query_log, and then cancel the outgoing HTTP request, periodically polling system.query_log afterward to get the status.

This HTTP trick sounds like a very hacky approach. Still, the only alternative is to wait on the wire for, potentially, hours, where a single transient network issue can fail the entire execution, as, if I understand correctly, a closed Native connection will result in query cancellation (unlike HTTP).

[lance-cognitiv]

@slvrtrn let me know if you came up with a good solution for this

[matbour]

When I was importing Google Cloud Ethereum dataset, I had to use @slvrtrn's solution, which is working great atm. However, as mentioned previously, allowing "send and forget" a command via the CLI could be a great addtion.