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
