The way CTRL-C in Postgres CLI cancels queries is incredibly hack-y

Summary

There are a few different reasons to hit the brakes on a Postgres query. Maybe it’s taking too long to finish. Maybe you realised you forgot to create an index that will make it orders of magnitude quicker. Maybe there’s some reason the results are no longer needed. Or maybe you, or your LLM buddy, made a mistake in the SQL, and you only noticed it while you were waiting for it to return. Maybe it’s even a mistake [scary chords play] that could have valuable production data as collateral damage. But let’s hope it isn’t (or, if it is, that you’re using a system that does time-travel, like Neon). Whatever the reason, if you’re a psql command-line user, Ctrl-C is in your muscle memory. So now you’re looking at the words Cancel request sent, followed shortly after by the not-really-an-error message ERROR: cancelling statement due to user request. But what’s going on behind the scenes? To cancel a Postgres query, the Postgres client makes a new and additional connection to the server, in the form of a CancelRequest. The server distinguishes this from an ordinary client connection via a magic protocol version number at the beginning of the startup message: the latest Postgres protocol is v3.2 (or 0x00030002), but a CancelRequest claims to be v1234.5678 (or 0x04d2162e). A CancelRequest targets a connection rather than a specific query. The target connection is identified to the server by two numbers that the server originally provided to the client at the end of their connection handshake (via the BackendKeyData message). The numbers are a 4-byte process ID and a secret random key value, traditionally also 4 bytes long. Aside from an initial length-of-message value, that’s everything the client sends. No credentials are required except that 4-byte secret key. It’s perhaps slightly surprising that Postgres cancels by connection rather than by query. It leads to a race condition: we risk cancelling a different query to the one that was running at the moment we asked to cance...