pg.ddx.io pgbouncer/pgbouncer GitHub issues and pull requests
help / color / mirror / Atom feed[pgbouncer/pgbouncer] issue #1705: `DEALLOCATE` of a single prepared statement name breaks the session
4+ messages / 2 participants
[nested] [flat]
* [pgbouncer/pgbouncer] issue #1705: `DEALLOCATE` of a single prepared statement name breaks the session
@ 2026-11-13 07:43 "vadv (@vadv)" <noreply+vadv@github.com>
0 siblings, 0 replies; 4+ messages in thread
From: vadv (@vadv) @ 2026-11-13 07:43 UTC (permalink / raw)
To: pgbouncer/pgbouncer <noreply+pgbouncer-pgbouncer@github.com>
**What happened**
We run PgBouncer 1.26.1 in transaction pooling mode with `max_prepared_statements = 10`.
The client runs this sequence in one transaction:
1. `Parse("keep_stmt", "SELECT $1::int + 100")` — OK.
2. `Parse("remove_stmt", "SELECT $1::int + 200")` — OK.
3. Simple query `DEALLOCATE remove_stmt` — the server frees that one name.
4. `Parse("remove_stmt", "SELECT $1::int * 10")` — the server returns an error:
```
psycopg.errors.InvalidSqlStatementName: prepared statement "remove_stmt" does not exist
```
**Expected behavior**
A direct connection to PostgreSQL runs the same sequence without errors. Step 4 must work. `DEALLOCATE` with one name frees only that name. The session stays usable and the name is reusable.
Odyssey 1.7.0-rc1 and pg_doorman 3.11.0 handle this correctly in transaction pooling mode with prepared statement tracking enabled.
**Configuration**
```ini
[databases]
p0 = host=127.0.0.1 port=5432 dbname=postgres
[pgbouncer]
listen_addr = 127.0.0.1
listen_port = 6432
pool_mode = transaction
default_pool_size = 1
max_prepared_statements = 10
```
**Why we think it happens**
The `DEALLOCATE` query text reaches the server. The server frees `remove_stmt`. But the client-side prepared statement map of PgBouncer keeps the name. On the next Parse, PgBouncer thinks the statement is already deployed. It does not send the Parse to the server. The server does not have the statement and answers with error code 26000.
We found code that clears the map only for `DEALLOCATE ALL` and `DISCARD ALL` (`src/server.c`). A `DEALLOCATE` with a single name is not tracked.
**Reproducer**
Pytest test in your test suite format (verified on PgBouncer 1.26.1 and master `ac3bb6d`):
```python
import pytest
from psycopg import pq
def _scenario(conn):
with conn.transaction():
pg = conn.pgconn
# Prepare two named statements.
for name, sql in [
(b"keep_stmt", b"SELECT $1::int + 100"),
(b"remove_stmt", b"SELECT $1::int + 200"),
]:
res = pg.prepare(name, sql)
assert res.status == pq.ExecStatus.COMMAND_OK, res.error_message
# DEALLOCATE a single name as SQL text, same transaction.
conn.execute("DEALLOCATE remove_stmt")
# Direct PG semantics: only that name is freed, the session stays
# usable and the name is reusable.
res = pg.prepare(b"remove_stmt", b"SELECT $1::int * 10")
assert res.status == pq.ExecStatus.COMMAND_OK, res.error_message
assert conn.execute("SELECT 1").fetchone() == (1,)
@pytest.mark.parametrize("target", ["postgres", "bouncer"])
def test_deallocate_one_name(bouncer, target):
if target == "bouncer":
bouncer.admin("set pool_mode=transaction")
bouncer.admin("set default_pool_size=1")
bouncer.admin("set max_prepared_statements=10")
with bouncer.conn() as conn:
_scenario(conn)
else:
with bouncer.pg.conn() as conn:
_scenario(conn)
```
**Impact**
The driver's protocol-level `Close` statement is tracked by PgBouncer. But an application managing statements with SQL text (`DEALLOCATE name`) gets a broken session. The session stays broken until the client disconnects.
Libraries using SQL `DEALLOCATE` (for example, psycopg `cursor.close()` with server-side cursors, ORMs managing prepared statement lifecycle via SQL) hit this silently.
^ permalink raw [nested|flat] 4+ messages in thread
* Re: [pgbouncer/pgbouncer] issue #1705: `DEALLOCATE` of a single prepared statement name breaks the session
@ 2026-11-13 07:44 "vadv (@vadv)" <noreply+vadv@github.com>
2 siblings, 0 replies; 4+ messages in thread
From: vadv (@vadv) @ 2026-11-13 07:44 UTC (permalink / raw)
To: pgbouncer/pgbouncer <noreply+pgbouncer-pgbouncer@github.com>
@vadv renamed this.
^ permalink raw [nested|flat] 4+ messages in thread
* Re: [pgbouncer/pgbouncer] issue #1705: `DEALLOCATE` of a single prepared statement name breaks the session
@ 2026-11-13 18:38 "cosgroveb (@cosgroveb)" <noreply+cosgroveb@github.com>
2 siblings, 0 replies; 4+ messages in thread
From: cosgroveb (@cosgroveb) @ 2026-11-13 18:38 UTC (permalink / raw)
To: pgbouncer/pgbouncer <noreply+pgbouncer-pgbouncer@github.com>
Thanks for the report.
PgBouncer forwards SQL `DEALLOCATE` to Postgres without rewriting the statement name, which is a [documented limitation of max_prepared_statements](https://www.pgbouncer.org/config.html#max_prepared_statements).
^ permalink raw [nested|flat] 4+ messages in thread
* Re: [pgbouncer/pgbouncer] issue #1705: `DEALLOCATE` of a single prepared statement name breaks the session
@ 2026-11-14 05:11 "vadv (@vadv)" <noreply+vadv@github.com>
2 siblings, 0 replies; 4+ messages in thread
From: vadv (@vadv) @ 2026-11-14 05:11 UTC (permalink / raw)
To: pgbouncer/pgbouncer <noreply+pgbouncer-pgbouncer@github.com>
@vadv closed this.
^ permalink raw [nested|flat] 4+ messages in thread
end of thread, other threads:[~2026-11-14 05:11 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-11-13 07:43 [pgbouncer/pgbouncer] issue #1705: `DEALLOCATE` of a single prepared statement name breaks the session "vadv (@vadv)" <noreply+vadv@github.com>
2026-11-13 07:44 ` "vadv (@vadv)" <noreply+vadv@github.com>
2026-11-13 18:38 ` "cosgroveb (@cosgroveb)" <noreply+cosgroveb@github.com>
2026-11-14 05:11 ` "vadv (@vadv)" <noreply+vadv@github.com>
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