New function `emacsql-async`

[cireu]

Currently all emacsql function are synchorously, but it's waste of time to wait for sql backend write a lot of data into database. So I suggest a function

(emacsql-async DB CALLBACK ERRORBACK &rest ARGS)

Which allow user to execute sql statement asynchorously.

If you think it's resonable and I' ll try to implement this.

[skeeto]

Hmmm, I think this makes sense. I'd expect that a particular connection could only handle one asynchronous operation at a time, so a connection pool might also make a good addition. The Python library asyncpg would, for the most part, be a good model to follow when deciding semantics. Unfortunately the pg.el backend wouldn't be supported since it doesn't have an async interface.

Transactions would need some re-invention when used asynchronously. The emacsql-with-transaction macro uses unwind-protect to track transaction level. This won't work in the context of emacsql-async. The callback caller in EmacSQL would need to handle that detail — though what happens if the callback needs to do another asynchronous operation before "returning"? This is where Emacs' weak/lacking support for async really shows.

In case you hadn't already seen it, I explored this a bit earlier this year:

An Async / Await Library for Emacs Lisp https://nullprogram.com/blog/2019/03/10/

Since it's built on generators it inherits the limitations of generators, like no support for unwind-protect forms. And like Python's asyncio, it also doesn't really work unless every system involved buys into it:

https://drewdevault.com/2019/11/26/Avoid-traumatic-changes.html

[nbfalcon]

I was also thinking about adding an async interface, since I don't like forge hanging when invoking forge-pull. I propose the following (and am willing to implement that if you agree with my proposal @skeeto):

• Requests would be processed in-order: each request has to finish before the next is even processed. This avoids edge cases like reads completing before writes (i.e. implied r/w barriers after every op) and greatly simplifies the implementation, since we don't have to specify sequence numbers with each request
• emacs-sql-connection would be extended with a callbacks fifo-queue field. emacsql-async would push a handler to the end of it (e.g. using nconc) and then send the request to the process. emacsql would do the same, but then spin around waiting until its callback is invoked. This is necessary as its result is available only once all callbacks before it have been invoked. Each callback would take a boolean indicating whether its data argument is an error or not and a data argument, the response (inspired by nodejs' `readFileAsync).
• emacsql-waiting-p can no longer assume that the process buffer can have only one result (so checking the last two chars of it for "#\n" is no longer sufficient), but must instead check if there is one result available, ignoring everything afterwards (which might be a partial result or even a full one)
• emacsql-read also has to handle multiple results
• a process filter would be added that calls emacsql-waiting-p after each input, and dispatches the first callback if necessary. For the sqlite backend, this means checking for a newline in the process buffer
• (as an optimization) there could be per-backend process filters that can e.g. check for newlines in only the new output, and increase a count of available results. This means that less data has to be looked at after each output write (compared to searching for a newline from the beginning of the buffer each time).

There might be more complexity involved in making this work for pgsql, though.

Any thoughts?