If I understand the bug report correctly you have two layers of
pgbouncer and it's only the second layer that is introducing these
latencies? I wonder if that should help point out why these latencies
are happening but I don't have any immediate ideas.
I suppose there are two avenues for addressing this, but they're both
probably large projects, not simple bug fixes....
1) PGBouncer is the one breaking the session <-> tcp stream
correspondence. Perhaps it should learn about cancellation messages
and learn about how to determine whether that backend session is still
allocated to the same incoming session? I'm not sure how that would
happen though since the cancellation request doesn't have any
information about which session is the intended target.
2) The postgres client could include some meta information about what
request the client is intending to cancel. But that has a few gotchas
as well. Clients stream many requests and process the responses as
they arrive. Just because the client has processed up to a certain
point doesn't mean the server hasn't streamed out responses past that.
It's quite common to want to cancel precisely because a stream of
responses are coming.