Pgbench: remove synchronous prepare

Add docs, fix a function number.

Rebase.

Regards,
Dmitrii Bondar.

On Mon, Mar 23, 2026 at 11:45 AM Dmitrii Bondar <d.bondar@postgrespro.ru>
wrote:

Hi!

Thank you for reviewing my patch! Should I consider your review complete
and move the patch to ‘ready for committer’?

Hi Dmitrii,

Yes, my review is complete. The patch works well in my testing and resolves
the blocking issue without any regressions.

You can move it to 'Ready for Committer.'

Regards,
Lakshmi G

On Thu, Apr 16, 2026 at 12:45 PM Dmitrii Bondar <d.bondar@postgrespro.ru>
wrote:

On Mon, Mar 16, 2026 at 3:46 AM Dmitrii Bondar <d.bondar@postgrespro.ru> wrote:

Rebase.

Hi,

I think that this patch is changing more behavior than is explained in
the commit message. The existing code calls PQsendQueryPrepared, which
only tries to execute an already-prepared query. The replacement code
tries to prepare the query. It is not clear to me what's going on
here. I would have expected that we would only ever reach that point
in the code with the query already prepared; otherwise, the existing
code would presumably fail. But if that is the case then how is the
new code managing to do anything different than the old code?

Another way to see that the patch must be changing more behavior than
advertised is the change to 001_pgbench_with_server.pl. That change
comes with no comment changes and no explanation of any kind.

If this patch were just about doing something asynchronously instead
of synchronously, I think that would be fine, but I don't think that's
all that is happening here. The original post explains the problem
behavior (pgbench freezing under certain circumstances) but I don't
understand what causes that behavior. I think I would understand
better if the original complaint were about something other than
session pooling mode: then, I might expect that we might unexpectedly
discover that our session does not have something prepared which we
expected to find prepared, and maybe this revised logic in
sendCommand() would somehow fix that. But in session pooling mode,
shouldn't everything be the same as if connection pooling is not in
use at all? What's actually different?

--
Robert Haas
EDB: http://www.enterprisedb.com

On 4/27/26 12:01 AM, Robert Haas wrote:

On Mon, Mar 16, 2026 at 3:46 AM Dmitrii Bondar<d.bondar@postgrespro.ru> wrote:

Rebase.

Hi,

I think that this patch is changing more behavior than is explained in
the commit message. The existing code calls PQsendQueryPrepared, which
only tries to execute an already-prepared query. The replacement code
tries to prepare the query. It is not clear to me what's going on
here. I would have expected that we would only ever reach that point
in the code with the query already prepared; otherwise, the existing
code would presumably fail. But if that is the case then how is the
new code managing to do anything different than the old code?

Another way to see that the patch must be changing more behavior than
advertised is the change to 001_pgbench_with_server.pl. That change
comes with no comment changes and no explanation of any kind.

If this patch were just about doing something asynchronously instead
of synchronously, I think that would be fine, but I don't think that's
all that is happening here. The original post explains the problem
behavior (pgbench freezing under certain circumstances) but I don't
understand what causes that behavior. I think I would understand
better if the original complaint were about something other than
session pooling mode: then, I might expect that we might unexpectedly
discover that our session does not have something prepared which we
expected to find prepared, and maybe this revised logic in
sendCommand() would somehow fix that. But in session pooling mode,
shouldn't everything be the same as if connection pooling is not in
use at all? What's actually different?

Hi,

The patch does not change the existing behavior when a query has already
been prepared. Both the old and new code paths use
|PQsendQueryPrepared|, which sends a bind-execute-sync packet sequence
without waiting for a response.

The main difference appears when the query has not yet been prepared. In
the old code, |PQprepare| is called, which sends a parse message and
then waits for the result via |PQexecFinish|. Since |PQexecFinish|
blocks until a response arrives, it can block the entire thread if the
server has not responded yet.

I replaced the call to |PQprepare| with a call to the new |PQsendPBES|
function. Like |PQsendQueryPrepared|, it works asynchronously, but it
sends a parse-bind-execute-sync sequence instead. This change avoids
thread blocking because it eliminates the need to call |PQexecFinish|.

I chose to send a parse-bind-execute-sync sequence to match the behavior
of extended query mode, in which pgbench sends the same sequence, but
with an unnamed statement.

The expected output for |001_pgbench_with_server.pl| was changed for the
following reason. In the old pgbench code, |prepareCommand| is called
and receives |ERROR: syntax error|. Since |prepareCommand| does not
return a status, pgbench continues execution and then attempts to run
the command with |PQsendQueryPrepared|. This leads to the error
|prepared statement .* does not exist|, which is caused by the bind packet.

In the new code, |PQsendPBES| sends a parse-bind-execute-sync packet
sequence. If the parse step fails with |ERROR: syntax error|, all
subsequent messages are ignored until the sync packet is processed. That
is why the additional |prepared statement .* does not exist| error from
the bind packet no longer appears.

Session mode is indeed the most transparent way to use a pooler.
However, pgbench can become stuck when the number of clients exceeds the
pool size. If the pooler cannot reserve a backend for a client, it
places the client in a waiting queue. In that case, pgbench may wait
indefinitely because it is blocked in |PQprepare|, and the pgbench
thread cannot process responses for other clients.

Regards,
Dmitrii Bondar

On Tue, Apr 28, 2026 at 5:29 AM Dmitrii Bondar <d.bondar@postgrespro.ru> wrote:

Session mode is indeed the most transparent way to use a pooler. However, pgbench can become stuck when the number of clients exceeds the pool size. If the pooler cannot reserve a backend for a client, it places the client in a waiting queue. In that case, pgbench may wait indefinitely because it is blocked in PQprepare, and the pgbench thread cannot process responses for other clients.

Ah, I see! This is a key point I wasn't understanding previously.

Why isn't the solution to use the existing PQsendPrepare function
instead of adding a new libpq entrypoint?

Even if we stick with the design you propose here, I don't think we
can add a function with a name like PQsendPBES, and I think we need to
find a way to more clearly explain what it does. It's kind of
unfortunate that there's such a large gap between the names of the
functions and the protocol messages that they send, but if all the
other functions are named without reference to the underlying protocol
messages, and this one is an exception, then it seems like it's going
to be hard to understand.

--
Robert Haas
EDB: http://www.enterprisedb.com

On 4/30/26 4:46 AM, Robert Haas wrote:

Why isn't the solution to use the existing PQsendPrepare function
instead of adding a new libpq entrypoint?

pgbench is not designed to process a response to a Parse message alone,
because of meta-commands. For example, the \gset command requires a
tuple to be stored, but a response to a Parse message does not provide
one. This leads to the error: pgbench: error: client 0 script 0 command
0 query 0: expected one row, got 0. Sending all additional messages with
PQsendQueryPrepared may look like the exact solution, but it is not.
PQsendQueryStart does not allow more than one command to be sent unless
pipeline mode is enabled. This could be fixed in two ways: either by
allowing libpq to send more than one command when pipeline is disabled,
or by adding a new state-machine state to pgbench. Both options seem
more invasive than the current solution. Adding a new libpq function
just for pgbench (at least for now) does not seem ideal either, but it
may be simpler and safer.

I don't think we
can add a function with a name like PQsendPBES, and I think we need to

I have other suggestions:
"PQsendQueryPrepare" but it is too close to the existing name
"PQsendQueryPrepared".
"PQsendPrepareQuery" similar to "PQsendPrepare" but it also executes the
query.
"PQsendPrepareExecute" is not especially well aligned with the existing
naming scheme, but it may describe the intent quite well.

On Tue, May 5, 2026 at 3:48 AM Dmitrii Bondar <d.bondar@postgrespro.ru> wrote:

pgbench is not designed to process a response to a Parse message alone, because of meta-commands. For example, the \gset command requires a tuple to be stored, but a response to a Parse message does not provide one. This leads to the error: pgbench: error: client 0 script 0 command 0 query 0: expected one row, got 0. Sending all additional messages with PQsendQueryPrepared may look like the exact solution, but it is not. PQsendQueryStart does not allow more than one command to be sent unless pipeline mode is enabled. This could be fixed in two ways: either by allowing libpq to send more than one command when pipeline is disabled, or by adding a new state-machine state to pgbench. Both options seem more invasive than the current solution. Adding a new libpq function just for pgbench (at least for now) does not seem ideal either, but it may be simpler and safer.

[ Catching up after pgconf.dev ]

I think there's something I'm still not understanding here. What you
said was that PQprepare can block, and I suggested using
PQsendPrepare. But your respond seems to be about PQsendQueryPrepared,
which is something different. I feel like if pgbench is using a
blocking call (like PQprepare) there should be a solution possible by
using the non-blocking variant of the same function (which in this
case is PQsendPrepare). If using that causes pgbench to die with some
weird error, that seems like a sign that other parts of pgbench also
need a bit of adjustment, rather than a sign that we need a new libpq
entrypoint.

--
Robert Haas
EDB: http://www.enterprisedb.com

On 5/27/26 7:39 PM, Robert Haas wrote:

On Tue, May 5, 2026 at 3:48 AM Dmitrii Bondar <d.bondar@postgrespro.ru> wrote:
[ Catching up after pgconf.dev ]

... there should be a solution possible by
using the non-blocking variant of the same function (which in this
case is PQsendPrepare). If using that causes pgbench to die with some
weird error, that seems like a sign that other parts of pgbench also
need a bit of adjustment, rather than a sign that we need a new libpq
entrypoint.

You are right that there is another solution, but it requires changes in other parts of pgbench. As I mentioned in my previous message, using PQsendPrepare is absolutely possible, but it would require changes to the pgbench state machine. In particular, a new state would be needed to wait asynchronously for the response to a Prepare message. The only reason I have not done that is that it would require rewriting code in several places, whereas adding a new libpq entry would mostly extend the code rather than modify the existing logic.

I see that we are kind of stuck at this point. I agree that it is possible to change pgbench without changing libpq, even though I am not sure that this is better. I would like to submit another approach, which introduces a new state into the pgbench state machine to wait for the response to a Parse message.
 

On 15 Jul 2026, at 16:03, Бондарь Дмитрий <d.bondar@postgrespro.ru> wrote:

<v4-0001-add-wait-prepare-state.patch>

Hi Dmitrii,

I think this is worth fixing in pgbench itself. The benefit is not
limited to poolers: a prepare waiting for a table lock can also stall
unrelated clients on the same worker thread. That introduces a
dependency between clients into the generated workload, rather than
measuring how the server handles their independent requests.

The additional state seems a reasonable maintenance cost for fixing
that. It fits pgbench's existing model of waiting for each connection
through the event loop, and keeps the change local instead of extending
libpq's public API. I think this is the right direction, with a couple
of issues in the state transitions below.

Returning to CSTATE_START_COMMAND after preparing resets stmt_begin.
This drops the prepare time from --report-per-command. In a local test,
blocking Prepare on a table lock gave 4436 ms transaction latency
but only 0.401 ms statement latency. Could we send the query without
resetting the command timer?

The second PQgetResult() in CSTATE_WAIT_PREPARE_RESULT can still block:
the first returns ParseComplete, but the next must wait for
ReadyForQuery if it has not arrived yet. The error path also drains
results synchronously through getSQLErrorStatus(). Similar reads
already exist elsewhere in pgbench, but this leaves a blocking window
in the new prepare path. Could we wait for completion through the
event loop? A connection failure between the two results can also
make the second call return an error rather than the asserted NULL.

I also found a way to test the main fix without PgBouncer. With
-c 2 -j 1, use client_id to make client 0 select from a table locked
by another session in ACCESS EXCLUSIVE mode, and client 1 insert into
a different table. In local runs, client 1 made progress while
client 0 was blocked in Prepare with v4, but not without the patch.
This would test the intended behavior more directly than the changed
syntax-error expectation.

I am fine with leaving prepareCommandsInPipeline() for a separate patch.
Could the commit message describe this as a scope choice? Preparing
before PQenterPipelineMode() preserves the required ordering, but does
not require blocking the worker thread. The prepared-flag fix is also
limited to the non-pipeline path: prepareCommand() still sets the flag
after an error.

Thank you!

Best regards, Andrey Borodin.

Hi, Andrey!

Returning to CSTATE_START_COMMAND after preparing resets stmt_begin.
This drops the prepare time from --report-per-command. In a local test,
blocking Prepare on a table lock gave 4436 ms transaction latency
but only 0.401 ms statement latency. Could we send the query without
resetting the command timer?

Removed a transfer to CSTATE_START_COMMAND and just used sendCommand
from the new state.

The second PQgetResult() in CSTATE_WAIT_PREPARE_RESULT can still block:
the first returns ParseComplete, but the next must wait for
ReadyForQuery if it has not arrived yet. The error path also drains
results synchronously through getSQLErrorStatus(). Similar reads
already exist elsewhere in pgbench, but this leaves a blocking window
in the new prepare path. Could we wait for completion through the
event loop? A connection failure between the two results can also
make the second call return an error rather than the asserted NULL.

Spit the state into two stages to avoid blocking. Added an event loop to
the error path.

I also found a way to test the main fix without PgBouncer. With
-c 2 -j 1, use client_id to make client 0 select from a table locked
by another session in ACCESS EXCLUSIVE mode, and client 1 insert into
a different table. In local runs, client 1 made progress while
client 0 was blocked in Prepare with v4, but not without the patch.
This would test the intended behavior more directly than the changed
syntax-error expectation.

Implemented.

I am fine with leaving prepareCommandsInPipeline() for a separate patch.
Could the commit message describe this as a scope choice? Preparing
before PQenterPipelineMode() preserves the required ordering, but does
not require blocking the worker thread. The prepared-flag fix is also
limited to the non-pipeline path: prepareCommand() still sets the flag
after an error.

Fixed the flag and added a note to the commit message.

Looks much better now, thank you for reviewing the patch!

--
Regards,
Dmitrii Bondar.