Async results support? about sqlpp11 HOT 41 CLOSED

Hi,

I am anything but an expert on async, so please bear with me, too :-)

I assume you want to do something like

async_db(foo_query, foo_callback);
async_db(bar_query, bar_callback);

That should be easy to accomplish, I think. The only real issue I see is that you probably need to have one connection per parallel query, so async_db should probably hold a pool of connections.

Also, your callback would need to know which result type to expect. This might be a bit annoying with C++11, but should be not be an issue with C++14, afaict.

Inside of async_db, you would take a connection from the pool and perform something with std::async/std::future, I guess (I just have a foggy idea of how that would look like).

I haven't planned a feature like that, but since you are experienced with async, maybe you can give it a try? A pull request for this feature would be quite welcome! And I can try to help when you run into obstacle.

Best,

Roland

from sqlpp11.

When doing async thing you tend to use either libevent (used in node.js)
or boost.asio. I personally prefer the boost variant but it depends on what
you want to integrate with - they don't play well together.

The pooling sounds normal. What you want to stay away from is to use one
thread per connection. To do that you need nonblocking io everywhere. Would
that be possible with the existing codebase? or would it require a big
rewrite?

If you would like to go the boost-asio route I could possibly be of some
help since I've been there many times.

As with the previous msvc++ question - I normally prefer visual studio as
development tool even if we (always) deploy on linux so first I would need
to get the codebase running on windows.... (I don't care
about performance on that platform just being able to debug is a huge win)

best
svante

2015-04-27 7:29 GMT+02:00 Roland Bock [email protected]:

Hi,

I am anything but an expert on async, so please bear with me, too :-)

I assume you want to do something like

async_db(foo_query, foo_callback);async_db(bar_query, bar_callback);

That should be easy to accomplish, I think. The only real issue I see is
that you probably need to have one connection per parallel query, so
async_db should probably hold a pool of connections.

Also, your callback would need to know which result type to expect. This
might be a bit annoying with C++11, but should be not be an issue with
C++14, afaict.

Inside of async_db, you would take a connection from the pool and perform
something with std::async/std::future, I guess (I just have a foggy idea of
how that would look like).

I haven't planned a feature like that, but since you are experienced with
async, maybe you can give it a try? A pull request for this feature would
be quite welcome! And I can try to help when you run into obstacle.

Best,

Roland

—
Reply to this email directly or view it on GitHub
#35 (comment).

from sqlpp11.

It would be awsome if async db were supported like socket IO in boost::asio. Especially if yield was supported. That would open up for writing async db calls as coroutines - making the source code simple and easy to write and understand.

You can have a look here for an example of what I mean: https://github.com/jgaa/modern_async_cpp_example

from sqlpp11.

On 2015-04-27 08:03, svante karlsson wrote:

When doing async thing you tend to use either libevent (used in node.js)
or boost.asio. I personally prefer the boost variant but it depends on
what
you want to integrate with - they don't play well together.
In that case, maybe we should have two async_db types, one for libevent
and one for boost.asio?

The pooling sounds normal.
Pooling is not yet implemented in the library. Users do that themselves
where required.

What you want to stay away from is to use one
thread per connection.
You mean /more than /one thread per connection?

To do that you need nonblocking io everywhere. Would
that be possible with the existing codebase? or would it require a big
rewrite?
As of now, sqlpp11 is thread-agnostic. The connector libraries might
need to know about threads, e.g. the one for mysql does.

If you would like to go the boost-asio route I could possibly be of some
help since I've been there many times.
I am fine with boost.asio, especially since this seems to be the way the
standard is going. I won't get started with this myself very soon, but
I'd be happy to assist.

As with the previous msvc++ question - I normally prefer visual studio as
development tool even if we (always) deploy on linux so first I would need
to get the codebase running on windows.... (I don't care
about performance on that platform just being able to debug is a huge win)
Maybe you can team up with Jarle trying to make the library compile with
MSVC? Maybe their support for C++11 is good enough by now...

Best,

Roland

from sqlpp11.

On 2015-04-27 09:17, Jarle Aase wrote:

It would be awsome if async db were supported like socket IO in
boost::asio. Especially if yield was supportad. That would open up for
writing async db calls as coroutines - making the source code simple
and easy to write and understand.

You guys should really team up :-)

You can have a look here for an example of what I mean:
https://github.com/jgaa/modern_async_cpp_example

Thanks for the link. I hope to find the time to look at it in more
detail soon.

from sqlpp11.

I'm still waiting for MSVC support for C++11 for but I'd thought that I should share (my) ideas of a simple async lib.

I'd just had the need for this in a postgres based project and I made up a fairly small library that wraps the c-api of postgres and adds platform independent boost based async calls. Not at all what I hope to get from sqlpp11 but it works really well with a async http library. Maybe you can "steal" some ideas.

https://github.com/bitbouncer/postgres-asio

/svante

from sqlpp11.

Thanks! I'll take a closer look as soon as I find the time to do so.

from sqlpp11.

Would love to see some async support added. Possibly even via "detaching" the generated SQL string and sending it over the conn asyncronously, and then using it to "construct" the SQL table objects to loop over when the query is ready.

As much as I love the look of this library, I just cannot use it for my use case as long as a query blocks the thread I generate it from.

from sqlpp11.

Tempus fugit... I have that on my to do list, but still haven't found the time to actually tackle async.

Of course, any pseudo code explaining how you would use it would be helpful, once I get going.

from sqlpp11.

@rbock, there were some trivial samples in the async postgres-asio library that I wrote up as example on how to do async on top on something synchronous.

https://github.com/bitbouncer/postgres-asio/blob/master/samples/query_sample/query_sample.cpp

That said I'm using that tiny library in production with the addition of automatic generation of avro (with schemas). https://avro.apache.org/

That would be a really cool feature on top of a modern generic sql library (hint, hint)

from sqlpp11.

Thanks for the link (and the hint) :-)

I need to clean up a few other things first, but async is high on my list, and your and Jarle's links certainly will make it easier to start...

from sqlpp11.

I'm a bit late to the party, but I just want to make a small suggestion to keep the ongoing coroutine proposal (eg, co_await) in mind if you're considering adding async. I think it would be great for users of sqlpp11 to be able to simply co_await a response from the database through sqlpp11. Choosing an async design that doesn't fit with co_await would be a shame.

from sqlpp11.

Bret,

You're not late for the party. I still haven't gotten to start here, I am afraid. Thus, your input is very welcome and in time :-)

Actually, I am still hoping for a pull request to get started as I am not exactly an expert on async.

Best,

Roland

from sqlpp11.

Hi all,

I am by no means an expert in this area, but I am really interested in starting an initial attempt on implementing async support through boost asio.

From what I understand, we would like to have 2 ways to perform async query-- one with the traditional callback approach and the other the coroutine yield approach, is that correct? I came up with pseudo code of 2 hopefully typical use cases with my very limited understanding of async:

sql::connection async_db(config);

// Callback approach
auto foo::foo_callback(error_code ec, sqlpp::future future)
{
    if (ec)
    {
        // Catch error
    }
    for (const auto& row : future.get())
    {
        // Process result
    }
}
async_db(foo_query, bind(foo_callback, foo_ptr, async_db.future()));
async_db(bar_query, bind(bar_callback, bar_ptr, async_db.future()));


// Coroutine yield approach
auto future = async_db(foo_query, yield_context[ec]);
if (ec)
{
    // Catch error
}
for (const auto& row : future.get())
{
    // Process result
}

Am I at least going in the right direction? Any suggestions?

Cheers,
Frank

from sqlpp11.

Hi Frank,

I am also no async expert. Based on the little that I believe to understand, this seems to be going in the right direction indeed.

It might be beneficial to go a bit further: Instead of obtaining the whole result set from the future, you could read/process each row in that fashion (obtaining the next row might take considerable time).

Cheers,

Roland

from sqlpp11.

Instead of obtaining the whole result set from the future, you could read/process each row in that fashion (obtaining the next row might take considerable time).

I didn't quite get what you meant by that. Do you mean instead of:

for (const auto& row : future.get())
{
    // Process result
}

we do:

while (future.get_row())
{
    // Process row
}

I think I might be completely misunderstanding but assuming that is what you meant, is it even possible for us to process a row while the rest of the resultset is being received? If not could you elaborate on what you meant by "read/process each row in that fashion." ? Thanks.

Back to implementing callbacks, I think it is more achievable than coroutines with what we currently have, because it can be done by just adding a library like asio or libevent. There's an example online that breaks down the query process so that we can send multiple queries in parallel and process the result with callbacks: http://jan.kneschke.de/projects/mysql/async-mysql-queries-with-c-api/

However the coroutine approach seems more challenging because we probably need some sort of coroutine library. I looked at a few of them, namely co2(requires a subset of boost), Stackless-coroutine, boost::coroutine, and a few other trivial ones. I still don't know which one would be most suitable in our case.

The boost coroutine library is the only one among the three that supports both stackful and stackless coroutines. The other two are both stackless. I don't foresee a need for stackful coroutines, because for our purposes I don't see a case where we will be running coroutines inside another coroutine.

While the boost coroutine library is the most powerful and most well integrated with the boost::asio library, unlike asio which has a standalone version, the boost coroutine library depends on many other boost libraries and after all is considerably larger than the other 2.

I also looked at the sample project Jarle posted, it uses boost asio and coroutine, which would together expand the user's source code size by 20MB, I don't know if it would be the most elegant solution. For those who want to use sqlpp11 with asio, they still have the option to use the standalone version of asio(less than 3MB), but the boost coroutine is much heavier and does not have a standalone version to my knowledge.

As of right now I am thinking maybe we can integrate a lightweight coroutine library, something like Stackless-coroutine into sqlpp11 to add support for coroutines based query, and use asio or libevent for callbacks.

Cheers,
Frank

from sqlpp11.

In ODBC it looks like the way to run several statements at once is to change the statement handle to async and then keep checking the return code from SQLExecute or SQLFetch to see if it is still executing. AFAIK you can't do callbacks in unixodbc but can in Windows. In order to do callback-style async with the ODBC connector then you'd either have to keep checking on another thread or be running on Windows. Doing in another thread would probably require mutexes in ODBC to ensure thread safety. I think you might be able to have something like

dp.async(query, [=](error_code ec, auto&& results){
    if(ec) {
        //handle error
        return;
    }
    results.async_each([=](error_code ec, auto&& row) {
        if(ec) {
            //handle error
            return;
        }
        //do stuff
    }, [=](size_t row_count){
        //Do something now that results are all done
    });
});

As far as incorporating another library, I'd personally rather wait until the stl implements it or use some kind of header-only library like HH's date.h. Doesn't c++17 have some parallel implementations?

from sqlpp11.

Thank you for your insights, Erroneous1. It looks like the std coroutine library is still in its preliminary draft TS stage as of March 2017. While we don't have stl coroutine, there indeed seems to be a header-only solution that we might be able to use:

stackless_coroutine:

"Stackless_coroutine provides a portable, self-contained, library only, C++14 emulation of stackless coroutines without macros.

Back to implementing callbacks, I have little experience with ODBC, but I did briefly looked at the ODBC connector you developed for sqlpp11. After looking at the Async Mysql Query with C-API post again it looks like by breaking up mysql_real_query the only benefit we get is that we no longer have to block after sending a query until the result begins to be read, so reading the result remains a blocking procedure. I think maybe we can combine it with this post to implement callbacks for the Mysql connector. I think with the information above we have a relatively clear path to implementing callbacks with asio.

After implementing callbacks I assume sqlpp11 will also have to manage a connection pool since we can't use the same connection for parallel queries. I am still quite fuzzy on how to manage sql connections. What do we do when all available connections are in use? Should we grow the connection pool or block until one becomes available? What else should I be aware of when designing the connection pool?

There's also the issue of deciding how many worker threads to give to the asio io_service used by sqlpp11. Do we follow the hardware concurrency by default and let the users override the number of worker threads?

Again thank you guys very much for the information. I really hope I can make some real progress and contribute to the project soon.

Cheers,
Frank

from sqlpp11.

Hi Frank,

I am excited to see your approach!

Regarding the connection pool: I believe that a pool could be implemented independent of sqlpp11 or the connector libraries. Personally, I would

• make the desired size configurable
• allow growth beyond that if additional connections are requested
• automatically shrink it back

Of course, the latter two items could be configurable, too.

Best,

Roland

from sqlpp11.

Hi Roland,

Thanks again for the info. I will keep you posted.

Best,
Frank

from sqlpp11.

Note that in ODBC if you turn on async it will allow you to do another query while the first one is running, so you could run multiple queries at once on the same connection. As long as everything is running on the same thread or mutexes are used, you should be fine to use a single connection. Please take with a grain of salt since I have never done this or any async in ODBC so I'm just going off of what I find in documentation.

If you do connection pools in sqlpp11 then I think it would make it so you could do async without connector support other than copy constructors or a clone function to make a new connection. The default behavior is to block until it is done of course so if you run queries on another thread with it's own connection then it should be good to go.

from sqlpp11.

From https://docs.microsoft.com/en-us/sql/odbc/reference/develop-app/statement-handles:

Each statement is identified by a statement handle. A statement is associated with a single connection, and there can be multiple statements on that connection. Some drivers limit the number of active statements they support; the SQL_MAX_CONCURRENT_ACTIVITIES option in SQLGetInfo specifies how many active statements a driver supports on a single connection.

Note that, in my experience, it is common for SQL_MAX_CONCURRENT_ACTIVITIES to be 1 (for example using the SQL Server Native Client).

from sqlpp11.

Good to know Toby. Sounds like multiple connections would be the way to go about it for ODBC and undoubtedly others.

MariaDB reports 0 on my setup for SQL_MAX_CONCURRENT_ACTIVITIES (unlimited/unknown).

from sqlpp11.

I just put together an outline for a generic connection pool: #155 Please let me know what needs improvement, thanks!

Frank

from sqlpp11.

BTW: Did you see this?
https://herbsutter.com/2017/03/24/trip-report-winter-iso-c-standards-meeting-kona-c17-is-complete/

A coroutine TS will be shipped with C++17.

from sqlpp11.

That's awesome! I can't wait to see it! It would be even more amazing if we upgrade to c++17 so that we can ship coroutine query without a 3rd party library.

Frank

from sqlpp11.

Actually, I am already working on sqlpp17. It will take a few months until it will be in any usable state, but it is taking shape. And C++17 is great!

from sqlpp11.

I did some more reading and it occurred to me that coroutine seems to be only useful to us if we want to implement a result generator. Other than that, I don't see how callbacks can be either replaced or improved with resumable functions.

@bkuhns Could you elaborate on why you think co_await for a response from the database would be helpful? After much thought I don't see how it would be different from a synchronous query. It would be amazing if you could provide some pseudo code to explain some most common use cases. Thanks.

from sqlpp11.

@Erroneous1 implemented queries that return std::future<result>. I think what he did would be a great way to defer result processing within the same scope, and it also opened another way to implement callbacks. @rbock I would be interested in your opinion on which way you would prefer:

Future based apporach: (pseudo code)

auto callback = [](sqlpp::query_future<Pool_connection, Query> result)
{
    try
    {
        auto connection_result = result.get();
        auto& connection = connection_result.first;
        for(auto row : connection_result.second)
        {
            std::cout << (row.alpha.is_null() ? "NULL" : row.alpha) << '\n';
        }
    }
    catch(const std::exception& e)
    {
        // All exceptions are routed here, so users can use if-else to diagnose the problem
        std::cout << "Caught exception " << e.what() << '\n';
    }
};

Boost-like callback with variadic parameters:

auto callback = [](auto error, auto result, auto conn)
{
    if (!!error)
    {
        // connection exception or query exception
        std::cerr << error.what() << std::endl;
    }
    try
    {
        for (const auto& row : result)
        {
            std::cout << (row.alpha.is_null() ? "NULL" : row.alpha) << '\n';
        }
    }
    catch (sqlpp::exception e)
    {
        // result processing exception
    }
};

While the boost-like approach has already been implemented in the bind.h in the latest version of the PR, the future based appoarch can cut down the complexity of our existing implementation. I think they both have their pros and cons, so it would be great if you could give us your opinion/preference.

from sqlpp11.

I did some testing with msvc 2017 with compiler flag /await, and here's my test code:

std::future<void> work()
{
    co_await std::async(std::launch::async, [&]
    {
        std::this_thread::sleep_for(std::chrono::milliseconds(1000));
        done = true;
    });
    std::cerr << "Done working, thread # " << thread_id() << std::endl;
    std::this_thread::sleep_for(std::chrono::milliseconds(2000));
}

The result of running work repetitively:

Done working, thread # 26452
Done working, thread # 23316
Done working, thread # 26540
Done working, thread # 28432
Done working, thread # 26352
Done working, thread # 21624
Done working, thread # 26168
Done working, thread # 27288
Done working, thread # 4724
Done working, thread # 16524
Done working, thread # 24696
Done working, thread # 17644

What I noticed is that every time a coroutine is resumed, it is resumed on a new thread. I am looking for a way to reuse threads for this, but if it is not possible with the current coroutine library, if we were to use async coroutines to do sql query, we would not only have to use a thread for query, but also a new thread everytime we process the result. @buhns Wouldn't this render the use of coroutine for sql query obsolete? I think the result shows that at least using coroutines this way is slower than callbacks for sure.

Edit: I found out that it is possible to manually resume a coroutine on a user provided thread. I guess it means we will also have to keep a thread_pool that resumes coroutines from time to time. All things considered, query_futures are definitey a must if we want to implement coroutines, but still when it comes to callbacks, I think we don't necessarily want to pass futures as parameters, because the moment a callback is called, the result should already be present, and doing an extra result.get() seems convoluted to me.

from sqlpp11.

It's possible the culprit is std::async. In GCC's version it starts a new thread each time unless you specify std::launch::deferred. It's possible co_await is taking over the thread of the future from std::async but I really don't know enough about how that all works.

Can you refactor it to use a function that doesn't use std::async to see if that has the same behavior? Maybe post to a 2 thread thread pool? I'd bet if you do that it might alternate between threads. Also you could print out the thread id before co_await and inside of the lambda to see what it's doing.

from sqlpp11.

I tested co_await with std::async in mind again. Here's the result:

std::future<void> work()
{
    std::cerr << "Start working, thread # " << thread_id() << std::endl;
    co_await std::async(std::launch::async, [&]
    {
        std::cerr << "Working, thread # " << thread_id() << std::endl;
        std::this_thread::sleep_for(std::chrono::milliseconds(1000));
        done = true;
    });

    std::cerr << "Done working, thread # " << thread_id() << std::endl;
    std::this_thread::sleep_for(std::chrono::milliseconds(2000));
}

Start working, thread # 11528
Working, thread # 27892
Done working, thread # 20248

Start working, thread # 11528
Working, thread # 27892
Done working, thread # 1276

Start working, thread # 11528
Working, thread # 27892
Done working, thread # 29008

Start working, thread # 11528
Working, thread # 27892
Done working, thread # 28404

Start working, thread # 11528
Working, thread # 27892
Done working, thread # 25196

Start working, thread # 11528
Working, thread # 27892
Done working, thread # 24492

Start working, thread # 11528
Working, thread # 27892
Done working, thread # 24184

Start working, thread # 11528
Working, thread # 27892
Done working, thread # 17460

According to http://stackoverflow.com/questions/41413489/c1z-coroutine-threading-context-and-coroutine-scheduling, a win32 thread_pool is used to implement std::async for msvc, so the thread re-usage is the expected behavior. However since std::future doesn't have a .then() function yet, everytime we call the default co_await, this happens:

template<class _Ty>
	void await_suspend(future<_Ty>& _Fut,
		experimental::coroutine_handle<> _ResumeCb)
	{	// change to .then when future gets .then
	thread _WaitingThread([&_Fut, _ResumeCb]{
		_Fut.wait();
		_ResumeCb();
	});
	_WaitingThread.detach();
	}

Without task continuation from the future.then(), essentially some new/external thread must be present to observe the status of the future and resume the callback. Of course we can override the co_await operator, but that means implementing coroutines for sqlpp11 in any meaningful way would require our own task scheduler. I remember we talked about using a small thread_pool to multiplex our sql query tasks, so if we are adamant about having our own thread_pool in sqlpp, we can definitely make coroutine happen. Though I imagine it would be a bit tricky to create an elegant interface to allow sqlpp to work with an external thread_pool.

from sqlpp11.

Interesting results. I didn't know MSVC used a threadpool for std::async. Well it sounds like what we need is our own future descendant class that implements a .then function if __cplusplus is <= 201402l or is using future otherwise. Would that work with co_yield so that it doesn't spawn off new threads? I know Facebook's folly futures have .then().

from sqlpp11.

SO gives an answer to implementing future.then(): implementing-futurethen-equivalent-for-asynchronous-execution-in-c11, which I think we can use to implement callbacks and coroutines. The only problem I see is that the future continuation in the answer requires std::async, which we probably want to avoid because of potential performance issues on platforms other than windows.

To remedy that I think we can use an unbounded thread pool in our sqlpp namespace, which grows on demand and lets its threads wait on a condition variable until new tasks arrive. The thread_pool we talked about earlier can be modified slightly so that it can expand and contract.

I think the reason why we can't allow users to give us a customized thread pool to work with is SQL queries are essentially blocking I/O, and if we have lots of concurrent and long queries, having only 4 threads in a pool is most likely going to cause us troubles. At the same time having a very big thread pool of fixed size doesn't sound right either. Rather than letting the users worry about separating cpu bound and io bound tasks, it might just be better to maintain our own thread pool so that we can implement responsive coroutines and callbacks with no requirement for third party libraries like asio or libevent.

from sqlpp11.

Heck if we're going to do all that, why not make an sqlpp::async which uses a growing thread_pool? Then we could just call that instead of std::async. I've been working on another project lately, otherwise I'd be more help.

from sqlpp11.

I found a way to defer task enqueue with overloading co_await, this way we can put everything in one big async lambda call without future.then. I have a working version of coroutine query using a growing thread_pool exclusively for io bound tasks. The interface something like this:

std::shared_ptr<sqlpp::connection_pool<sqlpp::mysql::connection>> connection_pool;
SQLTable::Users users;

resumable_function test()
{
  auto& pool = *connection_pool.get();
  auto query = select(all_of(users)).from(users).unconditionally();

  auto async_query = sqlpp::async(pool, query);

  std::cerr << "Enqueue async query, thread # " << thread_id() << std::endl;

  co_await async_query;

  std::cerr << "Coroutine resume, thread # " << thread_id() << std::endl;

  try
  {
    auto conn = async_query.get_connection();

    for (const auto& row : async_query.get_result())
    {
      if (row.AccountName.is_null())
        std::cerr << "AccountName is null" << std::endl;
      else
        std::cerr << "AccountName: " << row.AccountName << std::endl;
    }
  }
  catch (sqlpp::exception e)
  {
    std::cerr << e.what() << std::endl;
  }
}

The code above would result in:

Enqueue async query, thread # 89068
Execute async query, thread # 87824
Coroutine resume, thread # 87824
AccountName: yellowtail
AccountName: tuna
AccountName: salmon

As you can see, the actual query and continuation are both called on the same thread from our thread_pool, which is dramatically faster than letting std::future spawn default observer threads with std::async. I have implemented it so that we can use the same sqlpp::async call for coroutine query and callback query. I also morphed Aaron's future based error handling code into what we have right now. async_query is essentially a big query handle that combines promise, future, connection_pool, query, and callback right now, which I don't particularly like. I'll break up async_query into separate parts soon. I used separate promises for pool_connection and result, because if we only use one promise, we are forced to retrieve both values at the same time and create a local variable to extend the lifetime of the result struct, which isn't always desirable for the users.

Now that we have our own thread_pool, it is also unnecessary to use third party solutions like asio and etc.

Also, I noticed std::system_error appends error messages on construction:

	static string _Makestr(error_code _Errcode, string _Message)
		{	// compose error message
		if (!_Message.empty())
			_Message.append(": ");
		_Message.append(_Errcode.message());
		return (_Message);
		}

It is quite frustrating when you see this when system errors happen to occur at the same time:

Invalid connection_future or result has already been retrieved.: no such file or directory

It might be better to implement our own error code in std::exception instead of inheriting std::system_error.

Let me know what you think.

from sqlpp11.

@fpark12 Sorry for the late response. I'll admit that I do very little async work, so most of my experience stops with std::future. Maybe I misunderstood, but I'm not sure why you think it wouldn't beany different than synchronous.

For example, from what I've seen of the co_await proposal, you can make an Awaitable concept around a std::mutex and get asynchronous waiting on locking. Code that awaits a lock() will be suspended until the mutex is unlocked rather than synchronously blocked waiting for the unlock.

co_await my_mutex.lock();
do_something();`

The function will be suspended and do_something() won't be called until a lock has been acquired. So, with that, I imagined something like:

for co_await(auto row : async_db(select(...))) { ... }

I've absolutely no clue of the details of how things can be fetched asynchronously, but I imagine here the for loop will suspend until one or more rows from the query are available. The select on an async_db yields each row as they're available (eg, it's a generator). Even if rows come in batches from the database, the select would yield them one at a time to the awaiting loop.

So, only the execution of each iteration would be synchronous. But, as I understand the awaitable coroutine proposal (admittedly with cursory knowledge), an implementation can resume a suspended coroutine from a different thread (or a fiber on Windows). I assume that means each iteration may (or may not) be performed asynchronously to the thread that called the function.

If I understand this correctly, then it seems like just picking an async_db and sprinkling co_await when fetching records would give sqlpp11 users a very simple and easily reasoned with approach to asynchronously fetch data from databases. I predict once co_await lands in C++(20), it will be a pretty popular way to do things asynchronously, thus my comment that it would probably be good to keep it in mind for sqlpp11.

from sqlpp11.

@bkuhns Thanks for the feedback. I wasn't being very clear at all when I asked you the question. I was trying to express how using coroutine to replace callbacks would not yield any performance benefits except a bit of syntax sugar. I do agree that implementing a generator would be quite handy. It might take a while before we break everything down to that small though, since I am still working on making everything work on the query/callback level.

from sqlpp11.

Coming in 2 years late here :-)

Having done a bit of single-threaded async projects with asio, my dream is a database driver that integrates its I/O into a single-threaded server for resource-constrained platforms. It has not appeared yet. :-) My ask would be:

• allow access to the file descriptor of the database connection socket, so it can be given to the epoll in an asio (or libuv) event loop
• set the file descriptor to non-blocking
• provide a callback that should be called when new data is available to read on the socket
• provide a different callback for when the socket is ready for write

Then asio can epoll the database connection alongside other sockets and file descriptors, and we can manage threads at the application level - down to 1 thread if desired.

On low-spec cloud VMs and linux embedded platforms, thread creation and context switching are costly and have to be managed carefully.

from sqlpp11.

Nick,

That sounds nice indeed, but goes way beyond the current connector implementations, which are based on the C APIs offered by the respective vendors.

That being said: You could write such a connector, of course.

Cheers,

Roland

from sqlpp11.

I am closing this one. I won't work on it myself. I'd look into pull requests, though.

(and would try to help if questions arise, of course)

from sqlpp11.