Re: proposal: add new dblink function, dblink_wait_for_query()

On Tue, Sep 15, 2026 at 2:26 PM Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:

> 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, BOOL exit_immediately) -> TEXT[]
>

I have a WIP implementation of this. It was suprisingly easy. So far,
performance gains are nothing special, only 5-10% vs classic is_busy() loop
in most cases. It shines in scenarios where it waits on a mix of short
and long queries, as the client process doesn't need to loop, saving cpu
time and some tricky coding, which can be important in certain contexts.

The WaitEventSet must be rebuilt completely upon every new/existing query,
but FWICT that does not impact performance or unnecessarily complicate the
code, since a hash from socket to connection must be maintained anyways.
Other than that, there's not much to say about the implementation, the
WaitEventSet interface is really nice.

Code like this:
FOR c IN 1..conn_count
LOOP
IF finished[c]
THEN
CONTINUE;
END IF;

conn := 'test' || c;

IF dblink_is_busy('test' || c) = 0
THEN
_found := true;

PERFORM * FROM dblink_get_result(conn) AS R(V TEXT);
PERFORM * FROM dblink_get_result(conn) AS R(V TEXT);

finished[c] := true;
remaining := remaining - 1;

END IF;
END LOOP;

IF NOT _found
THEN
PERFORM pg_sleep(0.01);
END IF;

can be turned into this:
returned := dblink_wait_for_query(500.0, false);

FOREACH conn IN ARRAY returned
LOOP
PERFORM count(*) FROM dblink_get_result(conn) AS R(V TEXT);
PERFORM * FROM dblink_get_result(conn) AS R(V TEXT);
END LOOP;

Since performance gains were not quite as nice as I was hoping, I thought
I'd pause here and solicit feedback and/or other comments, or at least see
if there's any interest. m

merlin