Christian Decker made a side comment on my PR about a potential further optimization for Postgres. I spent an hour on it, trying to get it to fit in our db API.
Since my wife was out with friends last night, so I dived into the implementation in earnest after the kids in bed.
Man, this is a horrible API which forces you to understand the design and history of PostgreSQL.
Until v14, the async API only supports one request at a time. (Surprise!!) You must explicitly turn on pipelining. Then you get one or more responses per request, with NULL between them. This is because you can have multiple SQL statements per string, or other modes which split responses like this (?).
You can no longer use non-pipelining APIs, once you turn this on.
You can't use PQsendQuery in pipeline mode. You need to use the lower level PQsendQueryParams with NULL params. This is documented as literally "You can't use this in pipeline mode" without explanation (the docs otherwise imply it's just a convenient wrapper). This is why you should always check your error returns!
And your code will still block forever. You need to explicitly flush the pipeline, otherwise it's cached locally. There are multiple different APIs to do this, and I'm not sure which to use yet.
Also, if you get an error in a SQL query, you need to drain the pipeline, turn pipeline mode off and on again.
Finally, the documentation warns that you are in danger of deadlock unless you turn on non-blocking mode. This makes some sense: the server won't read more commands if you're not reading, but I would prefer it to buffer somewhere.
This whole API seems to be implemented by and for people who have deep familiarity with PostgreSQL internals.
Hope the latency gain for CLN is worth it!
