The way CTRL-C in Postgres CLI cancels queries is incredibly hack-y
1–10 of 50 posts
Re: The way CTRL-C in Postgres CLI cancels queries is incredibly hack-y
#2From 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.
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?
Re: The way CTRL-C in Postgres CLI cancels queries is incredibly hack-y
#3From 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. 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?
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.
Re: The way CTRL-C in Postgres CLI cancels queries is incredibly hack-y
#4From 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. 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?
MSSQL uses a special message over an existing connection:
https://learn.microsoft.com/en-us/openspecs/windows_protocol...
Re: The way CTRL-C in Postgres CLI cancels queries is incredibly hack-y
#5I think I can understand why this wasn’t addressed for so long: in the vast majority of cases if your db is exposed on a network level to untrusted sources, then you probably have far bigger problems?
Re: The way CTRL-C in Postgres CLI cancels queries is incredibly hack-y
#6TCP has an "urgent data" feature that might have been used for this kind of thing, used for Ctrl-C in telnet, etc. It can be used to bypass any pending send buffer and received by the server ahead of any unread data.
Re: The way CTRL-C in Postgres CLI cancels queries is incredibly hack-y
#7From 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. 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?
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.
Re: The way CTRL-C in Postgres CLI cancels queries is incredibly hack-y
#8TCP has an "urgent data" feature that might have been used for this kind of thing, used for Ctrl-C in telnet, etc. It can be used to bypass any pending send buffer and received by the server ahead of any unread data.
Fun fact: Oracle implements cancellation this way.
The downside is that sometimes connections are proxied in ways that lose these unusual packets. Looking at you, Docker...
Re: The way CTRL-C in Postgres CLI cancels queries is incredibly hack-y
#9I think I can understand why this wasn’t addressed for so long: in the vast majority of cases if your db is exposed on a network level to untrusted sources, then you probably have far bigger problems?
it's also very tricky to do given the current architecture on the server side where one single-threaded process handles the connection and uses (for all intents and purposes) sync io.
In such a scenario, listening (and acting) on cancellation requests on the same connection becomes very hard, so fixing this goes way beyond "just".
Re: The way CTRL-C in Postgres CLI cancels queries is incredibly hack-y
#10From 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. 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.
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?