From the title I was hoping for this being hacky on the server application side, like how it aborts and clears the memory for a running query.<p>Still an interesting read. Just wondering, why can't the TCP connection of the query not be used to send a cancellation request? Why does it have the be out of band?
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.<p>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.<p>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.<p>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?
> 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.<p><a href="https://datatracker.ietf.org/doc/html/rfc6093" rel="nofollow">https://datatracker.ietf.org/doc/html/rfc6093</a>:<p><i>“it is strongly recommended that applications do not employ urgent indications. Nevertheless, urgent indications are still retained as a mandatory part of the TCP protocol to support the few legacy applications that employ them. However, it is expected that even these applications will have difficulties in environments with middleboxes.”</i>
It isn't really easy to do. A client may send tons of data over the connection, probably data which is calculated by the client as the client's buffer empties. If the server clears the buffers all the time to check for a cancellation it may have quite bad consequences.
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.<p>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:<p><a href="https://learn.microsoft.com/en-us/openspecs/windows_protocols/ms-tds/dc28579f-49b1-4a78-9c5f-63fbda002d2e" rel="nofollow">https://learn.microsoft.com/en-us/openspecs/windows_protocol...</a>
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.
As others have said, TCP allows sending urgent packets, precisely for solving this problem.<p>At the receiver, a signal handler must be used, which will be invoked when an urgent packet is received, with SIGURG.
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.