pg.ddx.io  pgbouncer/pgbouncer GitHub issues and pull requests  
help / color / mirror / Atom feed
[pgbouncer/pgbouncer] issue #1706: `DISCARD ALL` reset pipelined with the next Parse wipes the client's prepared statement map
6+ messages / 2 participants
[nested] [flat]

* [pgbouncer/pgbouncer] issue #1706: `DISCARD ALL` reset pipelined with the next Parse wipes the client's prepared statement map
@ 2026-11-13 09:25 "vadv (@vadv)" <noreply+vadv@github.com>
  0 siblings, 0 replies; 6+ messages in thread

From: vadv (@vadv) @ 2026-11-13 09:25 UTC (permalink / raw)
  To: pgbouncer/pgbouncer <noreply+pgbouncer-pgbouncer@github.com>

### Description

In transaction pooling mode, PgBouncer appears to drop prepared statements that are prepared after a pipelined `DISCARD ALL` command. This leads to `08P01` protocol errors and session termination, as the client expects the statement to be present, while PgBouncer's internal tracking seemingly fails to associate the newly prepared statement with the current session state.

### Reproducer
The following Python script (using `psycopg` in pipeline mode) demonstrates the issue. It performs a `DISCARD ALL`, followed by a `PREPARE` statement within a transaction.

```python
from psycopg import pq

def _read_pipeline(pg, results):
    # Get results until PIPELINE_SYNC
    while True:
        pg.consume_input()
        if pg.is_busy(): continue
        res = pg.get_result()
        if res is None: continue
        results.append(res)
        if res.status == pq.ExecStatus.PIPELINE_SYNC: break

def _scenario(pg):
    # Pipeline DISCARD ALL and PREPARE in one batch
    pg.enter_pipeline_mode()
    pg.send_prepare(b"", b"DISCARD ALL")
    pg.send_prepare(b"s0", b"SELECT 1")
    pg.pipeline_sync()
    pg.flush()
    results = []
    _read_pipeline(pg, results)
    pg.exit_pipeline_mode()
    assert all(r.status != pq.ExecStatus.FATAL_ERROR for r in results)

    # Attempt to execute the prepared statement
    pg.enter_pipeline_mode()
    pg.send_query_prepared(b"s0", ())
    pg.pipeline_sync()
    pg.flush()
    results = []
    _read_pipeline(pg, results)
    pg.exit_pipeline_mode()
    
    # Assert successful execution
    assert any(r.status == pq.ExecStatus.TUPLES_OK for r in results)

# Test function within the PgBouncer test suite
def test_discard_all_pipelined_with_prepare(bouncer):
    bouncer.admin("set pool_mode=transaction")
    bouncer.admin("set max_prepared_statements=10000")
    with bouncer.conn() as conn:
        _scenario(conn.pgconn)
```

### Observations
The same test was executed against various connection poolers in transaction mode with prepared statement tracking enabled:

| Pooler | Result |
| :--- | :--- |
| Odyssey 1.7.0-rc1 | PASS |
| pg_doorman 3.11.2 | PASS |
| **PgBouncer 1.26.1** | **FAILED (08P01)** |

In contrast to other solutions, PgBouncer terminates the client session upon the first attempt to execute the prepared statement, reporting `prepared statement 's0' not found`.

### Impact
This behavior significantly impacts applications that utilize prepared statements within transaction pooling mode. Specifically, scenarios where frameworks perform state cleanup (`DISCARD ALL`) followed by statement preparation within the same transaction lifecycle are prone to failure. The resulting protocol error renders the session unusable, necessitating a client-side reconnection.

^ permalink  raw  [nested|flat] 6+ messages in thread

* Re: [pgbouncer/pgbouncer] issue #1706: `DISCARD ALL` reset pipelined with the next Parse wipes the client's prepared statement map
@ 2026-11-14 04:29 ` "cosgroveb (@cosgroveb)" <noreply+cosgroveb@github.com>
  4 siblings, 0 replies; 6+ messages in thread

From: cosgroveb (@cosgroveb) @ 2026-11-14 04:29 UTC (permalink / raw)
  To: pgbouncer/pgbouncer <noreply+pgbouncer-pgbouncer@github.com>

Hi @vadv. Your issue description says:

> Client 1 prepares statement s1 and executes it. Client 1 disconnects

But the reproducer you've provided doesn't execute it. I find libpq naming a bit function naming confusing at times but in here:

```
        pg.enter_pipeline_mode()
        pg.send_prepare(b"s1", b"SELECT 1")
        pg.send_query_prepared(b"s1", [])
        pg.pipeline_sync()
        assert pg.get_result().status == pq.ExecStatus.COMMAND_OK
        assert pg.get_result() is None
```

... you would need to [call](https://github.com/pgbouncer/pgbouncer/blob/ac3bb6d748cefc9470c2c66e061549a59a072ce3/test/test_prepa...) `pg.send_prepare(...)`. After making this adjustment do you still see any issue?


^ permalink  raw  [nested|flat] 6+ messages in thread

* Re: [pgbouncer/pgbouncer] issue #1706: `DISCARD ALL` reset pipelined with the next Parse wipes the client's prepared statement map
@ 2026-11-14 19:02 ` "vadv (@vadv)" <noreply+vadv@github.com>
  4 siblings, 0 replies; 6+ messages in thread

From: vadv (@vadv) @ 2026-11-14 19:02 UTC (permalink / raw)
  To: pgbouncer/pgbouncer <noreply+pgbouncer-pgbouncer@github.com>

Thanks @cosgroveb  for taking a look. My reproducer was incomplete: it prepared `DISCARD ALL` but never executed it, because the call to `pg.send_query_prepared(b"", ())` was missing — sorry about that.

I restored that call and reran the test with the upstream `bouncer` fixture, without my external harness, on PgBouncer 1.26.1 (PostgreSQL 16.15, psycopg 3.3.6, libpq 18.6).

With `DISCARD ALL` executed and the new Parse for `s0` sent in the same pipeline batch, I still reproduce the issue: the reset completes, the new Parse is acknowledged, and `PIPELINE_SYNC` is received, but the subsequent Bind/Execute of `s0` fails with `08P01`: `prepared statement did not exist`.

As a control, if I complete the reset in a separate pipeline batch before sending the new Parse, executing `s0` succeeds and returns `1`. The original Parse-only snippet also passes, but does not execute a reset.

There is no explicit `BEGIN`/`COMMIT` block in this test; it uses transaction pooling mode.

The corrected test and both controls are below. Could you take another look?

<details>
<summary>Reproducer using the upstream bouncer fixture (three cases)</summary>

Save as `test/test_issue1706_review.py` in the upstream checkout and run with its normal pytest setup. On 1.26.1, the middle case fails and the other two pass.

```python
import select
import time

import pytest
from psycopg import pq


def finish_batch(pg, label):
    pg.pipeline_sync()
    pg.flush()
    results = []
    deadline = time.monotonic() + 5
    while True:
        remaining = deadline - time.monotonic()
        assert remaining > 0, (label, "no PIPELINE_SYNC within five seconds")
        pg.consume_input()
        if pg.is_busy():
            select.select([pg.socket], [], [], remaining)
            continue
        result = pg.get_result()
        if result is None:
            assert pg.status != pq.ConnStatus.BAD, pg.error_message
            continue
        results.append(result)
        print(label, pq.ExecStatus(result.status).name, result.command_status,
              result.error_field(pq.DiagnosticField.SQLSTATE), result.error_message,
              flush=True)
        assert result.status not in (pq.ExecStatus.FATAL_ERROR, pq.ExecStatus.PIPELINE_ABORTED), (
            label, result.error_field(pq.DiagnosticField.SQLSTATE), result.error_message
        )
        if result.status == pq.ExecStatus.PIPELINE_SYNC:
            assert pg.get_result() is None
            return results


@pytest.mark.parametrize(
    "execute_reset,separate_sync",
    [(False, False), (True, False), (True, True)],
    ids=["posted-parse-only", "execute-reset-pipelined", "execute-reset-then-sync"],
)
def test_issue1706(bouncer, execute_reset, separate_sync):
    bouncer.admin("set pool_mode=transaction")
    bouncer.admin("set max_prepared_statements=10000")
    with bouncer.conn(autocommit=True) as conn:
        pg = conn.pgconn
        pg.enter_pipeline_mode()
        pg.send_prepare(b"", b"DISCARD ALL")
        if execute_reset:
            pg.send_query_prepared(b"", ())
        if separate_sync:
            reset_results = finish_batch(pg, "reset")
            assert any(r.command_status == b"DISCARD ALL" for r in reset_results)
        pg.send_prepare(b"s0", b"SELECT 1")
        prepared = finish_batch(pg, "prepare")
        if not separate_sync:
            assert any(r.command_status == b"DISCARD ALL" for r in prepared) == execute_reset
        pg.send_query_prepared(b"s0", ())
        executed = finish_batch(pg, "execute-s0")
        rows = [r for r in executed if r.status == pq.ExecStatus.TUPLES_OK]
        assert len(rows) == 1 and rows[0].get_value(0, 0) == b"1"
        pg.exit_pipeline_mode()
```

</details>

I checked the max_prepared_statements docs (https://www.pgbouncer.org/config.html#max_prepared_statements): they only cover SQL-level DEALLOCATE ALL / DISCARD ALL handling and say nothing about batching order. The reproducer shows the cache wipe is not scoped to statements prepared before the DISCARD ALL — a Parse registered after it was sent, but before its CommandComplete is processed, is wiped too, which the docs don't mention.

thank you! 

^ permalink  raw  [nested|flat] 6+ messages in thread

* Re: [pgbouncer/pgbouncer] issue #1706: `DISCARD ALL` reset pipelined with the next Parse wipes the client's prepared statement map
@ 2026-11-14 21:15 ` "cosgroveb (@cosgroveb)" <noreply+cosgroveb@github.com>
  4 siblings, 0 replies; 6+ messages in thread

From: cosgroveb (@cosgroveb) @ 2026-11-14 21:15 UTC (permalink / raw)
  To: pgbouncer/pgbouncer <noreply+pgbouncer-pgbouncer@github.com>

Thanks @vadv your adjusted reproducer looks correct.

The root cause as you explain it checks out, I think. Took a look at open issues and I think your report is a duplicate of #1535 which someone attempted to fix in #1543.

Is this an issue you ran into in production?

Cheers

^ permalink  raw  [nested|flat] 6+ messages in thread

* Re: [pgbouncer/pgbouncer] issue #1706: `DISCARD ALL` reset pipelined with the next Parse wipes the client's prepared statement map
@ 2026-11-14 23:50 ` "cosgroveb (@cosgroveb)" <noreply+cosgroveb@github.com>
  4 siblings, 0 replies; 6+ messages in thread

From: cosgroveb (@cosgroveb) @ 2026-11-14 23:50 UTC (permalink / raw)
  To: pgbouncer/pgbouncer <noreply+pgbouncer-pgbouncer@github.com>

I only ask because the previous time this surfaced it was a protocol-fuzzing finding and, while I think valid on some level, the previous attempt at a fix added substantial complexity.

If this is causing you pain I could look into taking a new crack at it. lmk

^ permalink  raw  [nested|flat] 6+ messages in thread

* Re: [pgbouncer/pgbouncer] issue #1706: `DISCARD ALL` reset pipelined with the next Parse wipes the client's prepared statement map
@ 2026-11-15 05:39 ` "vadv (@vadv)" <noreply+vadv@github.com>
  4 siblings, 0 replies; 6+ messages in thread

From: vadv (@vadv) @ 2026-11-15 05:39 UTC (permalink / raw)
  To: pgbouncer/pgbouncer <noreply+pgbouncer-pgbouncer@github.com>

Fair point - the Python repro does read like hand-crafted protocol fuzzing. Here is the same bug from a plain Npgsql program:

```csharp
// Life-like repro: pooled Npgsql connection, reset pipelined with next command.
// Usage: dotnet run -- "<connection-string>"
using Npgsql;

var cs = args[0];

// 1. Normal app usage: open a pooled connection, change session state, query, return it.
await using (var c1 = new NpgsqlConnection(cs))
{
    await c1.OpenAsync();
    await using (var cmd = new NpgsqlCommand("SET application_name = 'demo'", c1))
        await cmd.ExecuteNonQueryAsync();
    await using (var cmd = new NpgsqlCommand("SELECT 1", c1))
        await cmd.ExecuteScalarAsync();
} // returned to pool: Npgsql queues a fire-and-forget DISCARD ALL in its write buffer

// 2. Next open gets the same pooled connector: the pending DISCARD ALL is
//    pipelined in the same write with the next command. Prepare + run.
await using (var c2 = new NpgsqlConnection(cs))
{
    await c2.OpenAsync();
    await using var cmd = new NpgsqlCommand("SELECT @p::int + 1", c2);
    cmd.Parameters.AddWithValue("p", 1);
    await cmd.PrepareAsync();               // Parse goes out after the DISCARD ALL
    var v = await cmd.ExecuteScalarAsync(); // Bind: direct PG ok, pgbouncer kills the session
    Console.WriteLine($"OK value={v}");
}
```

On PgBouncer 1.26.1 (`pool_mode=transaction`, `max_prepared_statements=10000`, Npgsql 9.0.4) it reproduces every time: `ExecuteScalarAsync()` fails with

```
Npgsql.PostgresException (0x80004005): 08P01: prepared statement did not exist
```

and `pgbouncer.log` records `prepared statement '_p1' not found` -> `closing because: prepared statement did not exist`.

The same wire batch works fine through other poolers with server-side prepared-statement tracking: odyssey 1.7.0-rc1 and pg_doorman 3.11.2 both serve the programm. That suggests the batch itself is legitimate and the difference is in the statement-cache handling.

Apologies for the confusion with the Python file: I generated it with an AI to replay the sequence inside pgbouncer's pytest suite, and I should have presented it differently. Message for message it is the same batch the program above sends, plus one control with a `Sync` boundary between the reset and the next `Parse`; that control passes, which is what pins the shared flush as the trigger.

The `DISCARD ALL` comes from Npgsql itself: a connector returned to the pool dirty (changed session state, no prepared statements) gets a bare `DISCARD ALL` as its reset, prepended into the connector's write buffer (`Reset()` -> `PrependInternalMessage(PregeneratedMessages.DiscardAll, 2)` in `src/Npgsql/Internal/NpgsqlConnector.cs`), so it leaves the socket in the same flush, ahead of the first command the connector's next owner writes.


^ permalink  raw  [nested|flat] 6+ messages in thread


end of thread, other threads:[~2026-11-15 05:39 UTC | newest]

Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-11-13 09:25 [pgbouncer/pgbouncer] issue #1706: `DISCARD ALL` reset pipelined with the next Parse wipes the client's prepared statement map "vadv (@vadv)" <noreply+vadv@github.com>
2026-11-14 04:29 ` "cosgroveb (@cosgroveb)" <noreply+cosgroveb@github.com>
2026-11-14 19:02 ` "vadv (@vadv)" <noreply+vadv@github.com>
2026-11-14 21:15 ` "cosgroveb (@cosgroveb)" <noreply+cosgroveb@github.com>
2026-11-14 23:50 ` "cosgroveb (@cosgroveb)" <noreply+cosgroveb@github.com>
2026-11-15 05:39 ` "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