dblink has some nice capabilities for executing background queries, but it is missing some functionality; there is no ability to open connections asynchronously, and any polling of outstanding queries has do be done in SQL with a dblink_is_busy() loop. The polling issue is more severe especially if large amounts of result data are pulled over the connection since only one PQconsumeInput can execute at any one time. This idea attempts to mitigate that issue. dblink may be somewhat baroque, but it remains to often be the best way to do db to db querying and the only way to reliably way to issue background work in cloud SQL environments.
The basic idea here is to implement a new SQL API routine:
dblink_wait_for_query(_timeout INTERVAL) -> TEXT[]
The idea is that the function would wait untl an asynchronous query (sent via dblink_send_query) becomes 'unbusy', or ready to return a result without waiting unless timeout happens first. The returned list is an array of names that are ready for dblink_get_result without blocking.
The implementation would be to:
1. create a WaitEventSet in dblink_init,
2. Add the libpq socket to the set inside of dblink_send_query()
3. Remove the libpqsocket (if present) during dblink_get_result()
4. in dblink_wait_for_query, issue WaitEventSetWait() converting and passing the SQL supplied timeout. Open receiving evented traffic, it would resove the connection via hash table lookup from the socket and run an is_busy check on it. All connections returning as not busy would be added to the result set and passed back to the SQL layer. If none did, the timeout would be locally adjusted, and WaitEventSetWait would be re-issued
I'm looking for feedback on the approach, especially regarding the appropriateness of using WaitEventSetWait in this context. This idea aims to significantly optimize any scenario where dblink issues a large number of queries or queries that return large amounts of data.
merlin
