Asynchronous plans

[peter-facko]

Hello,

I am interested in an async C++ interface for PostgreSQL.
What are your current thoughts regarding the following?

• C++20 coroutines
• Boost.Asio (have you seen https://github.com/anarthal/postgres-asio?)
• newly accepted into C++26 std::execution (formerly senders/receivers, https://github.com/NVIDIA/stdexec)

[jtv]

I definitely want to do C++ coroutines, just not C++20 coroutines! I do not get the impression that they're quite complete enough and user-friendly enough yet in C++20.

Also, it looks to me like one of those features that I can only support once all supported compilers can deal with it. It's not one of those features that we can just #ifdef away when the compiler isn't up to it, or if you prefer to compile a slightly older version of the language.

As for Boost... I desperately want to avoid the dependency. Especially when standard C++ should be catching up eventually.

As for std::execution... I sometimes wish I could but for now I really don't see how it would fit into libpqxx. There's surprisingly little in the library that looks to me like it could really benefit from that sort of thing. The hard processing is supposed to be in your application and in the database!

[peter-facko]

Thank you for your answer.

By std::execution I meant the P2300 paper http://wg21.link/p2300.

I understand your position towards Boost, it makes sense.
But for coroutines to work, an executor library is needed. The standard library could provide one, but it doesn't yet. Boost.Asio is the closest thing to the standard library there is, so that's why I mentioned it.

Asio uses an execution model that was supposed to be standardized but was dropped in favor of P2300. By my understanding, NVIDIA implemented that proposal as stdexec. Since the proposal is now part of C++26, we can expect something similar to become part of standard library implementations soon. So stdexec is even closer to standard and could also be used to build the async features.

Also, could you elaborate on why new async features couldn't be #ifdefed away based on a feature test macro? Do you have to provide all features to all supported compilers?

[jtv]

I am looking at pipeline right now, but it doesn't seem to support parameters/prepared statements. Is there any way to use parameters with pipeline?

Not yet, no. The pipeline class is about 20 years old and I'm hoping to replace its innards with something based on the much newer implementation that's in libpq now.

[peter-facko]

And do you have any suggestions on how to do async parametrized queries?

BTW I am willing to contribute to the project if I can get my head around the code. So, if you can point me in the right direction, I could take a look.

[jtv]

I love contributions! Of course it's not usually easy to get things done in a mature project. I try to engineer for clarity, but it'll probably help if I explain the structure a bit.

Most of the code that actually calls libpq is (by design) concentrated in src/connection.cxx.

Unfortunately that does mean that a lot of calls get passed on from the transaction class to the connection class. This is painful to trace because these calls usually also go through a "call gate" — a category of classes which exists only to keep a handle on friend declarations. Mostly libpqxx classes don't have friends other than gates, so the gates show exactly how one class can call private methods of another.

So execution functions live in the transaction_base class, but from there all they do is call a matching function in the connection_transaction_base gate, which in turn does nothing but call a matching function in connection.

In the ongoing work for 8.0 I'm cleaning up the execution functions considerably, so you may want to start there. The branch is called start-8.

Then there's parameter-passing... The hard work here is done in the params class. It accepts parameter values, runs them through an extensible API to convert them to SQL strings (so the application can define its own conversion for types that libpqxx itself doesn't know about), stores those strings, and finally produces the data structure that the execution function needs to pass into the libpq code.

Finally, the best place to look for the asynchronous execution of course is the pipeline class itself. This is the part that's two decades old and hasn't seen much change, so clarity is not great. Maintaining a project for a quarter century can teach you a lot about maintainability, and the cost of learning is that you look back at your old self and cringe. I'd look for tests that cover the pipeline class — probably still in the old numbered test programmes so you'll have to grep around.

Beware of a terminology mismatch here: to do async work with the pipeline, you want the connection's socket not in async mode but in non-blocking mode.

Hope this helps!

[peter-facko]

Thank you for the explanation. I am looking through the code, trying to understand it.

I feel that adding parameters to pipeline would be the easiest option for me. But if you want to rewrite it using libpq, then it would be better to do that first. Although, how does that work with eventually dropping libpq?

Do you have any other communication channel besides Github? Slack or something similar?

[jtv]

The "eventually" part is no more than a vague wish for now. That's still years away, or it may never happen. But if it does, then any code we have for passing parameters is likely to be a useful start — though the wiring underneath will need to be re-done.

Good point about how implementing parameters will interact with the rewrite on top of libpq's pipelining. Normally I'd say "don't worry about it, build a new API first and we'll change the implementation later." But in this case, we've also got the problem of storing the parameters until the statement can be dispatched. Not too hard, I think (though I do care about optimising storage allocations), but not trivial either.

On the other hand, I was thinking merely to prototype the rewrite for now, to figure out what API changes the pipeline will need. Then I can mark deprecations as needed in 8.x, and actually switch to a new implementation in 9.0. So there is time! Maybe it makes the most sense to implement this feature in the class as it stands right now, if you don't mind the code possibly only living for one or two years.

(No other communications channels, sorry — I don't think I can handle more. 😄 )

[KayEss]

I've done an example project using libpqxx and my IO and coroutine libraries. I've not updated it for a bit, and don't have an example of pqxx::pipeline. I might update it tomorrow, if I get a chance. And at some point look into the pipeline API and see what that would look like.

I also did some work on a PoC for what a new async Postgres library might look like. I implemented the Postgres wire protocol using ASIO and showed that it's possible to get an order of magnitude better performance doing that than gong through libpq, mostly because it allows for much better management of memory allocations. At some point I'd like to redo that on top of my own libraries using io_uring and see how that performs.

[jtv]

Eventually I would like to get rid of libpq as well. My main concern however is the TLS support.

[peter-facko]

Maybe you could still depend on libpq but just use the connecting features and handle the messages yourself? I don't think libpq dependency is a big issue as it is distributed with postgres.

[jtv]

Funny — I have been saying exactly the same thing. I could use libpq to connect, but then "hijack" the connection.

For me one of the things I'd really prefer not to have to deal with myself would be TLS. All those different implementations, slightly incompatible. Another (though related) is dependencies. This arrangement would solve both.

[peter-facko]

Agreed. Also, the potential performance gain is probably negligible in the connecting phase anyway.

[jtv]

Exactly. The real gains I hope to get come from things like parsing loops with the text decoding call inlined; fewer compiler optimisation barriers from inter-library function calls; and perhaps data layout. Plus being able to make more assumptions.

Anyway, it's not something I'm planning for now. A feature list for 9.0 is already shaping up.