From: Dmitrii Bondar <d.bondar@postgrespro.ru>
To: pgsql-hackers@lists.postgresql.org
Subject: Pgbench: remove synchronous prepare
Date: Tue, 27 Jan 2026 10:34:51 +0700
Message-ID: <2da56b59-acb6-4ee3-af6b-6e800115dc7f@postgrespro.ru> (raw)
Hi, Hackers!
I was testing a connection pooler with pgbench and pgbench froze. I
checked the traffic and noticed that pgbench just blocks the execution
while it is waiting the response to the prepare command.
To reproduce the problem, it is enough to run pgbouncer with the session
pooling mode and use more clients than the pool size. With the pool size
of 20:
pgbench -h localhost -p 6432 --client=21 --jobs=1 -S -T 1000 -P 1
postgres --protocol=prepared
Pgbench with the extended protocol flag does not have this issue because
pgbench sends the whole parse/bind/execute/sync packet sequence at once
and waits for the result asynchronously. I suggest implementing this
behavior for the prepared protocol too.
I attached the pgbouncer configuration to reproduce the issue and the
proposed fix. I prefer to add a new function to libpqfe instead of
changing the existing behavior or adding a new state to pgbench.
Although it is largely duplicated code, it looks to be as non-invasive
as possible. Implementation and naming need to be discussed.
Tests for pgbench passed. I made small changes to the expected output.
Regards,
Dmitrii Bondar.
[databases]
* = host=127.0.0.1 port=5432
[pgbouncer]
listen_addr = 127.0.0.1
listen_port = 6432
logfile = ./pgbouncer.log
pidfile = ./pgbouncer.pid
admin_users = postgres
auth_type = trust
auth_file = ./userlist.txt
pool_mode=session
"postgres" ""
"regress_user" ""
"test" ""
"postgres" ""
Attachments:
[text/plain] pgbouncer.ini (235B, ../2da56b59-acb6-4ee3-af6b-6e800115dc7f@postgrespro.ru/2-pgbouncer.ini)
download | inline:
[databases]
* = host=127.0.0.1 port=5432
[pgbouncer]
listen_addr = 127.0.0.1
listen_port = 6432
logfile = ./pgbouncer.log
pidfile = ./pgbouncer.pid
admin_users = postgres
auth_type = trust
auth_file = ./userlist.txt
pool_mode=session
[text/plain] userlist.txt (56B, ../2da56b59-acb6-4ee3-af6b-6e800115dc7f@postgrespro.ru/3-userlist.txt)
download | inline:
"postgres" ""
"regress_user" ""
"test" ""
"postgres" ""
[text/x-patch] 0001-Remove-synchronous-prepare-from-pgbench.patch (5.0K, ../2da56b59-acb6-4ee3-af6b-6e800115dc7f@postgrespro.ru/4-0001-Remove-synchronous-prepare-from-pgbench.patch)
download | inline diff:
From 1eaed7dc94359315c80f8a38e4b2b158cecf1b06 Mon Sep 17 00:00:00 2001
From: Dmitrii Bondar <d.bondar@postgrespro.ru>
Date: Tue, 27 Jan 2026 08:29:46 +0700
Subject: [PATCH] Remove synchronous prepare from pgbench
Pgbench waits for the result of a prepare packet synchronously
when the prepared protocol flag is provided. For this reason
it is impossible to use it with some poolers (such as PgBouncer)
in the session pooling mode.
It is replaced by the full sequence of parse/bind/execute/sync
packets. This behaviour is similar to pgbench with the extended
protocol flag.
---
src/bin/pgbench/pgbench.c | 22 ++++++++--
src/bin/pgbench/t/001_pgbench_with_server.pl | 1 -
src/interfaces/libpq/exports.txt | 1 +
src/interfaces/libpq/fe-exec.c | 46 ++++++++++++++++++++
src/interfaces/libpq/libpq-fe.h | 9 ++++
5 files changed, 74 insertions(+), 5 deletions(-)
diff --git a/src/bin/pgbench/pgbench.c b/src/bin/pgbench/pgbench.cindex 58735871c17..8e11bc39de2 100644--- a/src/bin/pgbench/pgbench.c+++ b/src/bin/pgbench/pgbench.c@@ -3179,12 +3179,26 @@ sendCommand(CState *st, Command *command)
{
const char *params[MAX_ARGS];
- prepareCommand(st, st->command);
getQueryParams(&st->variables, command, params);
- pg_log_debug("client %d sending %s", st->id, command->prepname);- r = PQsendQueryPrepared(st->con, command->prepname, command->argc - 1,- params, NULL, NULL, 0);+ if (!st->prepared)+ allocCStatePrepared(st);++ if (!st->prepared[st->use_file][st->command])+ {+ r = PQsendPBES(st->con, command->prepname,+ command->argv[0], command->argc - 1, NULL,+ params, NULL, NULL, 0);+ if (!r)+ pg_log_error("%s", PQerrorMessage(st->con));+ st->prepared[st->use_file][st->command] = true;+ }+ else+ {+ pg_log_debug("client %d sending %s", st->id, command->prepname);+ r = PQsendQueryPrepared(st->con, command->prepname, command->argc - 1,+ params, NULL, NULL, 0);+ }
}
else /* unknown sql mode */
r = 0;
diff --git a/src/bin/pgbench/t/001_pgbench_with_server.pl b/src/bin/pgbench/t/001_pgbench_with_server.plindex b7685ea5d20..c047b5d0460 100644--- a/src/bin/pgbench/t/001_pgbench_with_server.pl+++ b/src/bin/pgbench/t/001_pgbench_with_server.pl@@ -1231,7 +1231,6 @@ my @errors = (
2,
[
qr{ERROR: syntax error},
- qr{prepared statement .* does not exist}
],
q{-- SQL syntax error
SELECT 1 + ;
diff --git a/src/interfaces/libpq/exports.txt b/src/interfaces/libpq/exports.txtindex dbbae642d76..ebebbacd5cd 100644--- a/src/interfaces/libpq/exports.txt+++ b/src/interfaces/libpq/exports.txt@@ -210,3 +210,4 @@ PQgetAuthDataHook 207
PQdefaultAuthDataHook 208
PQfullProtocolVersion 209
appendPQExpBufferVA 210
+PQsendPBES 210
\ No newline at end of file
diff --git a/src/interfaces/libpq/fe-exec.c b/src/interfaces/libpq/fe-exec.cindex 203d388bdbf..bef103ac3f3 100644--- a/src/interfaces/libpq/fe-exec.c+++ b/src/interfaces/libpq/fe-exec.c@@ -1682,6 +1682,52 @@ PQsendQueryPrepared(PGconn *conn,
resultFormat);
}
+/* PQsendPBES+ * Like PQsendQueryPrepared, but prepare a query+ */+int+PQsendPBES(PGconn *conn,+ const char *stmtName,+ const char *query,+ int nParams,+ const Oid *paramTypes,+ const char *const *paramValues,+ const int *paramLengths,+ const int *paramFormats,+ int resultFormat)+{+ if (!PQsendQueryStart(conn, true))+ return 0;++ /* check the arguments */+ if (!stmtName)+ {+ libpq_append_conn_error(conn, "statement name is a null pointer");+ return 0;+ }+ if (!query)+ {+ libpq_append_conn_error(conn, "command string is a null pointer");+ return 0;+ }+ if (nParams < 0 || nParams > PQ_QUERY_PARAM_MAX_LIMIT)+ {+ libpq_append_conn_error(conn, "number of parameters must be between 0 and %d",+ PQ_QUERY_PARAM_MAX_LIMIT);+ return 0;+ }++ return PQsendQueryGuts(conn,+ query,+ stmtName,+ nParams,+ paramTypes,+ paramValues,+ paramLengths,+ paramFormats,+ resultFormat);+}+
/*
* PQsendQueryStart
* Common startup code for PQsendQuery and sibling routines
diff --git a/src/interfaces/libpq/libpq-fe.h b/src/interfaces/libpq/libpq-fe.hindex 905f2f33ab8..772583dcea9 100644--- a/src/interfaces/libpq/libpq-fe.h+++ b/src/interfaces/libpq/libpq-fe.h@@ -525,6 +525,15 @@ extern int PQsendQueryPrepared(PGconn *conn,
const int *paramLengths,
const int *paramFormats,
int resultFormat);
+extern int PQsendPBES(PGconn *conn,+ const char *stmtName,+ const char *query,+ int nParams,+ const Oid *paramTypes,+ const char *const *paramValues,+ const int *paramLengths,+ const int *paramFormats,+ int resultFormat);
extern int PQsetSingleRowMode(PGconn *conn);
extern int PQsetChunkedRowsMode(PGconn *conn, int chunkSize);
extern PGresult *PQgetResult(PGconn *conn);
--
2.52.0
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: d.bondar@postgrespro.ru, pgsql-hackers@lists.postgresql.org
Subject: Re: Pgbench: remove synchronous prepare
In-Reply-To: <2da56b59-acb6-4ee3-af6b-6e800115dc7f@postgrespro.ru>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox