On Thu, May 12, 2022 at 4:25 PM Phil Endecott via Boost
Ruben Perez wrote (in the doc):
Warning Because of how the MySQL protocol works, once you obtain a resultset, you must read it entirely (i.e. until it's complete) before engaging in any subsequent operation that implies communication with the server (e.g. issuing another query, preparing a statement, closing a statement...). Failing to do so results in undefined behavior.
You are explicitly blaming the protocol for this restriction in that quote. It seems that that is not true, and that it is a limitation of the library.
That's not quite how I interpret it. The (basic) MySQL protocol does indeed require the client to fully consume a resultset, and not interleave any other operations until that's done, from what Ruben described. The alternate sequence-diagram Ruben presents, shows writing the next query *before* starting to read the first one. It's once you start reading that you can't do anything else. And that mode is essentially like having the two queries in the same packet, semicolons-separated, something also not supported at this point, but which probably should be.
Pipeline modes usually specify an option on what to do when a query in the pipeline fails. Here, you don't have that - subsequent queries will be executed regardless of the result of previous ones.
Transactions help here though. For example:
Transaction t(conn); for (auto&& a: lots_of_things) { execute_insert_stmt(a); } t.commit();
That's the pattern I also use, because my APIs throw, yielding the implicit-rollback. Although I'm not sure it will translate well to async code.
I would expect pipelining to significantly improve the performance of that, especially if there were significant network latency.
It depends Phil. It really helps on slow, high-latency networks. In a demo example, manually adding 50ms latency, Larenz Albe got a 4x speedup [1]. But if you run on the LAN with sub-1ms latencies, and/or fast Multi-Gb/sec (Cloud) networks, it's less likely to matter that much I suspect. [1]: https://www.cybertec-postgresql.com/en/pipeline-mode-better-performance-on-s...