GoKawiilThere 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?
How CancelRequest works
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 cancel it (this isn’t great, but the heebie-jeebies are pretty mild so far: maybe a 2 or 3 out of 10).
But here’s a bigger surprise: psql always sends this CancelRequest unencrypted. Even if the connection carrying the query to be cancelled has the strictest possible TLS settings ( sslmode=verify-full , channel_binding=require , and so on), psql always goes right ahead and cancels in plaintext.
The Postgres server on the other end of the exchange has accepted CancelRequest messages over TLS ever since it got TLS support. But until Postgres 17 — that is, less than 18 months ago — there was simply no support for encrypting a CancelRequest in libpq, the client-side Postgres C library on which psql is built.
... continue reading