Because Postgres is a very old codebase and was written in a style that assumes there are no threads, and thus there's nothing to listen for a cancellation packet whilst work is getting done. A lot of UNIXes had very poor support for threads for a long time and so this kind of multi-process architecture is common in old codebases.

The TCP URG bit came out of this kind of problem. It triggers a SIGURG signal on UNIX which interrupts the process. Oracle works this way.

These days you'd implement cancellation by having one thread handle inbound messages and another thread do the actual work with shared memory to implement a cooperative cancellation mechanic.

But we should in general have sympathy here. Very little software and very few protocols properly implements any form of cancellation. HTTP hardly does for normal requests, and even if it did, how many web servers abort request processing if the connection drops?

I don't know much about postgres, but as I understand it, it's a pretty standard server application. Read a request from the client, work on the request, send the result, read the next request.

Changing that to poll for a cancellation while working is a big change. Also, the server would need to buffer any pipelined requests while looking for a cancellation request. A second connection is not without wrinkles, but it avoids a lot of network complexity.

MSSQL uses a special message over an existing connection:

https://learn.microsoft.com/en-us/openspecs/windows_protocol...

Because then the cancellation request would get queued behind any other data that's in flight from the client to the server. In the worst case the TCP buffers are full, and the client cannot even send the request until the server processes some of the existing data that's in-flight.

It's basically got a thread per connection, while it's working on a query that thread isn't listening to incoming traffic on the network socket any more.