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

On Tue, Sep 22, 2026 at 4:02 PM Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:

> 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
>
>
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.

But I'm not sure it's even necessary, as I suspect a lot of the gains
you're getting is just reduced context switches. Could you get the same
savings from something like this?

-- create sub-connections, add each conn string to conn_array, add each to
variable conn_array of type text[]
WHILE true
LOOP
EXIT WHEN length(conn_array) < 1;

SELECT array_agg(c.conn)
INTO finished_arr
FROM unnest(conn_array) as c(conn)
WHERE dblink_is_busy(c.conn) = 0

IF finished_arr IS NULL
THEN
pg_sleep(0.1);
CONTINUE;
END IF;

FOREACH finconn IN ARRAY finished_arr
LOOP
PERFORM ...
PERFORM ...
END LOOP;
conn_array := ARRAY (SELECT unnest(conn_array) EXCEPT SELECT
unnest(finished_arr));
END LOOP;