Re: RFC: Async query processing

On Tue, Apr 22, 2014 at 3:45 PM, Florian Weimer <fweimer@redhat.com> wrote:
> On 01/03/2014 06:06 PM, Claudio Freire wrote:
>
>> Per-query expectations could be such a thing. And it can even work with
>> PQexec:
>>
>> PQexec(con, "SELECT nextval('a_id_seq') FROM generate_series(1,10);");
>> --read--
>> PQexec(con, "SELECT nextval('b_id_seq') FROM generate_series(1,10);");
>> --read--
>> PQexec(con, "INSERT INTO a (...);", PQEXPECT_NO_RESULT | PQASYNC_CORK);
>> PQexec(con, "INSERT INTO b (...);", PQEXPECT_NO_RESULT | PQASYNC_CORK);
>> PQexec(con, "INSERT INTO a (...);", PQEXPECT_NO_RESULT | PQASYNC_CORK);
>> PQexec(con, "INSERT INTO b (...);", PQEXPECT_NO_RESULT | PQASYNC_CORK);
>> PQexec(con, "INSERT INTO a (...);", PQEXPECT_NO_RESULT | PQASYNC_CORK);
>> PQexec(con, "INSERT INTO b (...);", PQEXPECT_NO_RESULT | PQASYNC_CORK);
>> ... 9 times...
>> PQexec(con, "INSERT INTO a (...);", PQEXPECT_NO_RESULT | PQASYNC_CORK);
>> PQexec(con, "INSERT INTO b (...);", PQEXPECT_NO_RESULT | PQASYNC);
>> do {
>>     // do something useful
>> } while (PQflush());
>>
>> Here, the PQASYNC flag would temporarily switch to non-blocking I/O,
>> and buffer what cannot be sent. PQASNC_CORK, would only buffer (only
>> send if the buffer is full). After any ASYNC call, PQflush would be
>> necessary (to flush the send queue and to consume the expected
>> responses), but I can imagine any synchronous call (PQexec,
>> PQsendQuery or whatever) could detect a non-empty buffer and just
>> blockingly flush right there.
>
>
> How would you consume results once they arrive?  I think this only covers
> the no-result case,

You could do PQEXPECT_ROWCOUNT for storing rowcounts (and supply a
pointer to a result buffer), or PQ_BUFFER_RESULTS and do the same. The
user would have to know beforehand the size of the result set (or an
upper bound of it), and readiness notification would also need to be
solved. There could also be PQEXPECT_DISCARD_RESULTS.

Alternatively, you could use a callback, node-style, and it would
solve everything (including readiness and error notification), but I'm
not sure about the portability of such a thing. Callbacks certainly
would be tricky when ABI compatibility has to be maintained. It would
however be a much better interface.

The pattern here, is the API needs to perform all the magic and
complex buffering and flushing, it should not be on the application
side.

> and it has since come to my attention that the Node.js
> folks are looking for general async response processing.

Node support would take a little more work. Specifically, for node to
work with this API, the non-blocking case has to be handled properly,
allowing node to wait on the FDs instead of requiring it to flush and
block on the event loop thread.

That means a buffer at least as large as the query parameters, which
should be no problem (but might be tricky to implement), and handling
the equivalent of EWOULDBLOCK at the PQexec(.., PQASYNC) calls.

In any case, on any specific connection, query processing is linear.
So you really need a "result callback queue" (however you implement
it, be the aplication or the API). What I propose is moving as much as
possible to the API, since it will be common to all users of the async
functionality, and it will make it possible to fix bugs in that code
centrally too.

My earlier examples where all about discarding results, because that's
what enables the most thoughput, and it covers lots of cases. But, as
has been mentioned in previous posts, rowcounts at the very least have
to be handled as well, so there's that. I guess we can throw in
generic result callbacks (and errbacks) if the ABI allows it, and it
will be a net win in clarity and simplicity.


On Tue, Apr 22, 2014 at 3:49 PM, Florian Weimer <fweimer@redhat.com> wrote:
> On 04/22/2014 07:03 PM, Claudio Freire wrote:
>>
>> On Tue, Apr 22, 2014 at 8:19 AM, Florian Weimer <fweimer@redhat.com>
>> wrote:
>>>
>>> Feedback in this thread was, "we want something like this in libpq, but
>>> not
>>> the thing you proposed".  But there have been no concrete
>>> counter-proposals,
>>> and some of the responses did not take into account the inherent
>>> complexities of round-trip avoidance.  So I'm not sure how to move this
>>> topic forward.
>>
>>
>> What exactly do you mean by not taking into account?
>
>
> A couple of them were along the lines "let's just send batches of queries
> and sync between batches".  This does not seem very helpful to me because
> sizing the batches is difficult, and the sizes could be quite large.

Not at all.

The proposal certainly has that in their examples, but the API can be
used with no explicit synchronization. That's what I was going for
when I suggested that other API calls could flush implicitly when
needed. If you never call flush, flush happens on its own when
necessary. If you never call synchronous API, you never wait for
replies. Though when you need the results (as in right now), you need
to flush explicitly, there's no way around that.

Also, feel free to consider all this mere opinion. I'm certainly not
the one implementing it ;-)