Implement request cancellation for postgres & MySQL

[glittershark]

The postgres wire protocol supports cancelling requests - we should implement that in readyset-psql.

This might be tricky in the case of eg migrations, since those do run in a background thread and we probably do want cancelling something like a CREATE CACHE statement to cancel the migration if possible. More investigation will be necessary to determine the implications of cancelling running migrations.

[davisjc]

We should implement this.

[jasobrown-rs]

I got a bit nerd sniped into looking at this, at least for PG (I did not research mysql). Here's my notes:

There are two ways to cancel a query: over the wire protocol, or a SQL command.

wire protocol

When a connection is established, the server can send along, in addition to the rest of the normal message payload, a BackendKeyData (link) message. The only real interesting field is the "secretKey". The client is expected to remember the secret key … When that client issues a query that takes a loooong time/gets stuck, a second client can connect to postgres, and send a CancelRequest message (link). However, that second client needs the secretKey from the first session. Unless the client logged that secretKey at some point before issuing the query, there's no way to get it (to give it to the second client). I think this entire design is highly impractical, and frankly a security hole. Further, while it might be possible for driver developers to implement this, it seems complicated and impractical as it's really only the original client (in the same process) this might have access to the secretKey. Hence, we should probably forgo this possibility.

SQL command

There is a function that can be invoked, pg_cancel_backend(). (link) The user must be logged in with the proper privs, so basically an admin. To actually cancel a query, you need to execute select pg_cancel_backend(pid), where pid is the identifier of the client session where the query-to-be-cancelled is executing. I'm not sure if we track pid/session information anywhere. This option seems more tractable, and frankly seems like the far more accepted industry practice for cancelling a runaway query. In addition to tracking pid/session info, we need a special branch for handling the SELECT (rather than naively proxying upstream). We'll need to handle the case of queries proxied to the upstream (and cancelling those, as well). And we need to determine if we care about admin privs; perhaps not as far as readyset is concerned, but we if need to cancel on the upstream.