proposal: add new dblink function, dblink_wait_for_query()

From: Merlin Moncure <mmoncure(at)gmail(dot)com>
To: PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: proposal: add new dblink function, dblink_wait_for_query()
Date: 2026-09-15 20:26:22
Message-ID: CAHyXU0w92SrpQtKca9dq=YEod44XbKWCoQuANN15C0svPLYhzw@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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.

Here's an example of the kind of code I'm trying to optimize
<https://github.com/merlinm/pgasync/blob/1fb08afcc3f6c66fb447e044775c8fda29a297b1/async_server.sql#L1410>

merlin

Browse pgsql-hackers by date

  From Date Subject
Next Message Bharath Rupireddy 2026-09-15 20:27:00 Re: pg_xmin_horizon: a system view of everything pinning the xmin horizon
Previous Message Ilia Evdokimov 2026-09-15 18:58:22 Re: Apply extended statistics to join clause during parameterized path costing