The basic idea here is to implement a new SQL API routine:
dblink_wait_for_query(_timeout INTERVAL, BOOL exit_immediately) -> TEXT[]
The API seems limited in that it can only return an array of ready connections from ALL the connections available to the main connection, which feels limiting. I'd almost want the API to accept an array of connections.
That's the point; it pushes the 'sleep loop' into WaitEventSetWait() (basically, epoll) so that the SQL layer activates when there's something to do; you get ready to consume connections back out of the interface (with 'exit_immediately' = true, that's exactly one, but if passed false you can wait for more). This is exactly analogous to poll vs epoll, with perhaps less spectacular gains, since with large connection_counts (> 100) other factors sneak in.
The basic problem solved is that there is no real optimal value for the pg_sleep(); In my experience simple query rates in the 10s of thousands are possible in ideal conditions, and it's difficult to prevent stalls on the sleep with irregular traffic. Low sleep values can help with that, but then you burn CPU. Having said all that, I do kinda agree with you, maybe not for exactly the stated reasons.
Here's a more complete example showing the difference:
https://pastebin.com/kCfVMbF1 I'm getting around 40% less time spent using the new interface, but this would be under absolutely ideal conditions. In the real world the surrounding management might make those gains insubstantial. The loop to is_busy is unrolled, but that's close to reality since you have to keep track of what the connection is actually doing since is_busy returns 0 for idle connections. Lowering connection counts brings results to par, but that's not practical forother reasons.
The output yieled is:
NOTICE: extension "dblink" already exists, skipping
NOTICE: classic
NOTICE: 19000 queries to go!
<snip>
NOTICE: 1000 queries to go!
NOTICE: 0 queries to go!
NOTICE: 00:00:01.001021
NOTICE: modified
NOTICE: 19000 queries to go!
<snip>
NOTICE: 1000 queries to go!
NOTICE: 0 queries to go!
NOTICE: 00:00:00.589864
...note the 30k+ query rate.
Another point: this could be a foundation for asynchronous connection support, since SSL connections can stall for quite some time. This is obviously a much more complicated change. But I'm not sure this is worth moving forward with -- I was expecting more honestly. Also of note, this really drains file descriptors, at least on mac. Thanks for the feedback!
merlin
