From: vadv (@vadv) <noreply+vadv@github.com>
To: pgbouncer/pgbouncer <noreply+pgbouncer-pgbouncer@github.com>
Subject: [pgbouncer/pgbouncer] issue #1705: `DEALLOCATE` of a single prepared statement name breaks the session
Date: Fri, 13 Nov 2026 07:43:51 +0000
Message-ID: <gh-pgbouncer-pgbouncer-1705@github.com> (raw)
**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.