I believe the suggestion is to have a TLS endpoint in the server, which demultiplexes the incoming CancelRequest and signals to the corresponding worker process via shared memory
The problem isn't on the server; the server already knows how to cancel things, and already supports cancellation over TLS. It's just that psql doesn't use it, due to the need for a refactor to make that work. Other psql-like frontends do already use it, as the article points out.