Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

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

neon.com

Post reply on HN
Print view View original

11–20 of 50 posts

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

#11

Post by hlinnaka »

jtwaleson wrote: post #2

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. 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 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.

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

#13

Post by ZiiS »

rlpb wrote: post #6

TCP 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.

Unfortunately the can be many buffers between you and the server which "urgent data" doesn't skip by design. (the were also lots of implementation problems)

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

#14

Post by ZiiS »

A good write up explaining how assumptions of network and security design have changed so much over the years. Also you have to give credit nowadays for not overly sensationalizing 'heebie-jeebies level 6'. I certainly continue reusing a connection I assumed was TLS after a cancel so was vulnerable to a DoS; but equally if the next statement was canceled I would switch to a new connection no harm no foul.

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

#15

Post by johannes1234321 »

mike_hearn wrote: post #10
jtwaleson wrote: post #2

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. 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 w…

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.

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

#16

Post by adrian_b »

hlinnaka wrote: post #11
jtwaleson wrote: post #2

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. 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 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.

At the receiver, a signal handler must be used, which will be invoked when an urgent packet is received, with SIGURG.

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

#18

Post by dmurray »

i18nagentai wrote: post #17

[flagged]

Doesn't it also make sense in the context of modern networking assumptions?

I've never had to connect to PostGres in an adversarial environment. I've been at work or at home and I connected to PostGres instances owned by me or my employer. If I tried to connect to my work instance from a coffee shop, the first thing I'd do would be to log in to a VPN. That's your multiplexed protocol layer right there: the security happens at the network layer and your cancel happens at the application layer.

This is a different situation from websites. I connect to websites owned by third parties all the time, and I want my communication there to be encrypted at the application layer.

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

#19

Post by Someone »

mike_hearn wrote: post #10
jtwaleson wrote: post #2

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. 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 w…

> 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.

https://datatracker.ietf.org/doc/html/rfc6093:

“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.”

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

#20

Post by gpderetta »

TLS is not async signal safe. But having a dedicated thread whose responsibility is to only send cancel tokens via a TLS connection and is woken up by a posix semaphore seems a small, self contained change that doesn't require any major refactoring.
Post reply on HN