fix(postgres): roll back a transaction cancelled during BEGIN - #4394

`Floating::return_to_pool` validates a connection on release with
`Connection::ping`, and the Postgres impl was a bare `wait_until_ready`: it
drains the `ReadyForQuery` but never looks at its transaction-status byte. A
session sitting inside a transaction block is therefore reported healthy and
handed to the next borrower, whose statements run inside it and hold its locks.

Two shapes reach that point with a client-side `transaction_depth` of zero, so
neither drop guard has queued a `ROLLBACK`:

- A future cancelled while `BEGIN`'s round trip is in flight (transact-rs#4393). transact-rs#4394
  fixed this one at the source by claiming the depth before the await.
- A statement that fails inside a block opened by a multi-statement `raw_sql`,
  where the block is opened and aborted within a single simple-query message
  and the depth is never raised at all. This one does not self-heal: the next
  borrower's `BEGIN` fails because the block is already aborted, leaving the
  depth at zero again, so every subsequent checkout fails with 25P02 until
  `max_lifetime` recycles the connection.

Check the server-reported status in `ping` and roll back when it disagrees.
The status byte is already decoded into `PgConnection::transaction_status` on
every `ReadyForQuery`, so the check itself is free, and the `ROLLBACK` is only
sent on a connection that is actually dirty.

The check is gated on `transaction_depth == 0`. `ping` is public API and a
caller may be holding a transaction deliberately; a non-zero depth means the
client knows about the block and owns its lifetime, so it is left alone. Only a
block the client has no record of is ended here -- which also means there is
never a savepoint to restore to, hence a plain `ROLLBACK`.

Also exposes `PgConnection::transaction_status`, so pool users can make the
same distinction themselves. `Connection::is_in_transaction` reports the
client-side depth, which is precisely the value that is wrong in both shapes
above, and the server's view was not reachable from outside the crate.

Tests: `it_does_not_return_a_connection_inside_a_transaction_to_the_pool`
covers both shapes plus the leaked `BEGIN READ ONLY` case, where the damage
surfaces as a failing write rather than 25P02, and
`it_does_not_roll_back_a_transaction_the_caller_owns` pins the depth guard.
The first fails on main with `left: Error, right: Idle`.

…ct-rs#4394)

`PgTransactionManager::begin` raises `transaction_depth` only after the BEGIN
round trip returns, but `start_rollback` -- which both its own `Rollback`
drop guard and `Transaction`'s drop guard call -- is a no-op while that depth
is zero. A future cancelled during the await therefore queues nothing, and the
session is left inside a transaction.

`Floating::return_to_pool` then validates the connection with `ping()`, which
for Postgres is a bare `wait_until_ready`: it drains the `ReadyForQuery` but
never inspects its transaction-status byte, so the connection is judged healthy
and handed to the next borrower. Their statements run inside the stale
transaction and hold its locks, and the first error turns the session into
`idle in transaction (aborted)`, after which every unrelated query on that
connection fails with 25P02 until `max_lifetime` recycles it.

Claim the depth before the round trip and unwind it if the BEGIN did not take,
so the drop guards have something to act on. The queued ROLLBACK is written to
the same buffer as the BEGIN and so is always flushed after it.

Closes transact-rs#4393. Refs transact-rs#2054, transact-rs#2819, transact-rs#3980.

Cherry-pick transact-rs#4394 onto 0.9.0: roll back a transaction cancelled during BEGIN

See transact-rs#4394

`PgTransactionManager::begin` raises `transaction_depth` only after the
BEGIN round trip returns, but `start_rollback` -- which both its own
`Rollback` drop guard and `Transaction`'s drop guard call -- is a no-op
while that depth is zero. A future cancelled during the await therefore
queues nothing, and the session is left inside a transaction.

`Floating::return_to_pool` then validates the connection with `ping()`,
which for Postgres is a bare `wait_until_ready`: it drains the
`ReadyForQuery` but never inspects its transaction-status byte, so the
connection is judged healthy and handed to the next borrower. Their
statements run inside the stale transaction and hold its locks, and the
first error turns the session into `idle in transaction (aborted)`,
after which every unrelated query on that connection fails with 25P02
until `max_lifetime` recycles it.

Claim the depth before the round trip and unwind it if the BEGIN did not
take, so the drop guards have something to act on. The queued ROLLBACK
is written to the same buffer as the BEGIN and so is always flushed
after it.

Closes transact-rs#4393. Refs transact-rs#2054, transact-rs#2819, transact-rs#3980.

Backported from upstream f1e94ec onto
v0.8.6. v0.8.6 predates transact-rs#3980, so `Transaction` arms no drop guard
during `begin`; the code comment is adjusted accordingly. The upstream
regression test (0.9 APIs, timing-based) is superseded by
tests/postgres/transaction.rs.

See transact-rs#4394

`PgTransactionManager::begin` raises `transaction_depth` only after the
BEGIN round trip returns, but `start_rollback` -- which both its own
`Rollback` drop guard and `Transaction`'s drop guard call -- is a no-op
while that depth is zero. A future cancelled during the await therefore
queues nothing, and the session is left inside a transaction.

`Floating::return_to_pool` then validates the connection with `ping()`,
which for Postgres is a bare `wait_until_ready`: it drains the
`ReadyForQuery` but never inspects its transaction-status byte, so the
connection is judged healthy and handed to the next borrower. Their
statements run inside the stale transaction and hold its locks, and the
first error turns the session into `idle in transaction (aborted)`,
after which every unrelated query on that connection fails with 25P02
until `max_lifetime` recycles it.

Claim the depth before the round trip and unwind it if the BEGIN did not
take, so the drop guards have something to act on. The queued ROLLBACK
is written to the same buffer as the BEGIN and so is always flushed
after it.

Closes transact-rs#4393. Refs transact-rs#2054, transact-rs#2819, transact-rs#3980.

Backported from upstream f1e94ec onto
v0.8.6. v0.8.6 predates transact-rs#3980, so `Transaction` arms no drop guard
during `begin`; the code comment is adjusted accordingly. The upstream
regression test (0.9 APIs, timing-based) is superseded by
tests/postgres/transaction.rs.

## Scope

Backport [SQLx #4394](transact-rs/sqlx#4394) to
the published `sqlx-postgres` 0.8.6 crate used by Waddle. A cancelled
ordinary PostgreSQL `BEGIN` now queues rollback before the connection
can return to the pool. This is a bounded P2.1 safety slice for #2116
under #2046; it does not activate transaction authority or close either
issue.

## Plan and change

- Vendor the unmodified 0.8.6 crate except for the upstream
`PgTransactionManager::begin` backport, with provenance in `PATCHES.md`.
Retain that patched crate's real source in Nix dependency stubs so SQLx
macros can compile. Exclude this copied upstream crate from SonarCloud
source analysis; its PostgreSQL MD5 authentication branch triggered a
new-code alert despite being unchanged.
- Add a deterministic regression that holds an ordinary `BEGIN` reply,
proves the backend entered a transaction, cancels the caller, and checks
the reused connection while checked out.
- Exercise nested and rejected `BEGIN` controls. Include both tests in
the required PostgreSQL inventory and serialized Nextest group.

## Verification

- Disposable PostgreSQL 17.11 with CI's Unix-socket-only configuration:
original SQLx code **failed** the regression with the reused backend
`idle in transaction` (exit 101); restored backport **passed 2/2**
tests. The same red/green result was observed with a local TCP test
instance.
- `cargo fmt --all --check` passed; `nix-instantiate --parse flake.nix`
passed after the Nix follow-up.
- `cargo clippy -p waddle-server --test sqlx_cancelled_begin --locked --
-D warnings` passed.
- `python3 scripts/check_postgres_test_group.py .` passed; the
required-test inventory parsed with both new names.
- Independent read-only architecture and final diff reviews found no
remaining in-scope blockers. The published crate comparison found only
`src/transaction.rs` changed, plus the local provenance and Git metadata
files.
- Hosted PR CI passed all 69 reported checks at head
`999de4c491c856031069c2a0c8d257d6867a56e1`, including Nix server build,
Clippy, XMPP server tests, sharded Rust tests, integration workspace
tests, and SonarCloud.

## Limits

This addresses interrupted `BEGIN` in SQLx 0.8.6. SQLx's separate
dirty-transaction release case in
[#4409](transact-rs/sqlx#4409) is outside this
patch. Full P2.1 authority, runtime role/epoch transition, mailbox and
actor work, capacity qualification, full release qualification, and
deployment remain separate gates. No production activation or live
deployment check was performed.

sqlx 0.9.0 counts a transaction only once BEGIN is acknowledged. A BEGIN
whose future is dropped in flight queues no ROLLBACK, and the release
ping consumes the late reply, so the writer pool hands the connection to
the next borrower still inside the transaction. Load tests logged it as
25001 on the next event read's BEGIN and, when a statement in the
leftover transaction failed, bursts of 25P02 on every request that got
the connection.

The fix is merged upstream (transact-rs/sqlx#4394) but unreleased. Pin
every sqlx crate to that commit; they share types, so they move
together. It already contains the libsqlite3-sys fix sqlx-sqlite was
pinned for, and the lockfile drops the second sqlx-core copy.

Add a writer-pool regression test that cancels a BEGIN behind a paused
proxy, for the event read's BEGIN ... READ ONLY and a plain begin(), and
checks that the next checkout is outside a transaction and its write
commits.

Co-authored-by: Elrond <28d6302a099e5225b02c4155ac4236e4912603df2ab08dbfc2f4fef08ce598c8@buzz.block.builderlab.xyz>
Co-Authored-By: Claude Opus 5.5 <[email protected]>
Signed-off-by: Luke Tornquist <[email protected]>

With sqlx at transact-rs/sqlx#4394, a reader proof that times out mid-BEGIN
comes back to the pool clean, so the reader release check no longer closes it.
The test now asserts the release check finds no open transaction, which also
catches a sqlx regression the backstop would otherwise hide. Refresh the
comments that described the 0.9.0 behavior as current.

Co-authored-by: Elrond <28d6302a099e5225b02c4155ac4236e4912603df2ab08dbfc2f4fef08ce598c8@buzz.block.builderlab.xyz>
Co-Authored-By: Claude Opus 5.5 <[email protected]>
Signed-off-by: Luke Tornquist <[email protected]>

## Problem

sqlx 0.9.0 counts a transaction only after Postgres acknowledges its
`BEGIN`. If the future is dropped while `BEGIN` is still in flight (a
cancelled REQ history read, a client that disconnects, a timeout), sqlx
doesn't queue a `ROLLBACK`. The pool's release ping then reads the late
`BEGIN` reply, and the writer pool hands the connection to the next
borrower while it is still inside that transaction. The reader pool
already closes such connections in its release check
(`Db::read_pool_options`). The writer pool has no release check.

Load tests showed what happens next:

- `there is already a transaction in progress` (25001) on the next event
read's `BEGIN`.
- Sometimes a burst of `current transaction is aborted` (25P02). The
leftover from the event read is `READ ONLY`, so the next write fails and
aborts it, and every later statement on that connection fails until the
connection is closed. During a burst, ban checks failed closed with 503
and window reads returned 500.
- A read-write leftover would also leave autocommit writes uncommitted,
and the next read's `ROLLBACK` would discard them.

## Change

- Pin every sqlx crate to transact-rs/sqlx#4394 (merged, not yet
released). That commit takes the transaction count before the `BEGIN`
round trip, so a cancelled begin queues its `ROLLBACK`. The crates share
types, so they have to move together. The pin also picks up the other 23
commits between v0.9.0 and that fix, including the libsqlite3-sys fix
`sqlx-sqlite` was already pinned for.
- Add a regression test,
`writer_pool_postgres_tests::begin_cancelled_in_flight_leaves_no_writer_transaction_open`.
It holds `BEGIN` in flight behind a paused proxy and times it out, for
both the event read's `BEGIN ... READ ONLY` and a plain `begin()`. It
then asserts that the next borrower's autocommit write commits and that
its backend is `idle`, not inside a transaction.

Other upstream changes in the pin that reach the Postgres path:
`TCP_NODELAY` on sockets (transact-rs/sqlx#4336), reuse of the TLS
connector across connection attempts (transact-rs/sqlx#4242), a fix for
a memory leak when the statement cache is disabled
(transact-rs/sqlx#4337), a describe-time generic-plan change used only
by the macros (transact-rs/sqlx#4286), and the `whoami` default-user fix
(transact-rs/sqlx#4303). The rest are SQLite, MySQL, CLI, or docs
changes.

Remove the pin once sqlx 0.9.1 ships.

## What got simpler

The lockfile loses its second `sqlx-core` copy, which the
sqlx-sqlite-only pin used to pull in. No runtime code changes. A
pool-level release check would have fixed the same bug, but it costs a
round trip on every writer release.

Comments that described the 0.9.0 behavior as current are refreshed: the
reader pool's release check (`Db::read_pool_options`) and the lease
heartbeat (`EntityDeletionStore::heartbeat`). The reader test
`proof_timeout_mid_begin_leaves_no_reader_transaction_open` asserted
that the release check closed the connection left mid-`BEGIN`. With the
pin, sqlx rolls that transaction back itself, so the test now asserts
the check finds nothing to close. That also catches a sqlx regression
the check would otherwise hide.

Identified debt (not in this PR): the release check's open-transaction
arm, and the reader-session `default_transaction_isolation` pin it
depends on, are now only a backstop against a sqlx regression. They
could be removed in a follow-up.

## Testing

- Against sqlx 0.9.0 (main's `Cargo.toml`/`Cargo.lock` at 962b3fd), the
new writer test fails for each `BEGIN` form on its own. The event read
fails with `cannot execute INSERT in a read-only transaction`, and after
a plain `begin()` the next borrower's backend is not `idle`. With the
pin it passes 5/5.
- Single-machine load-test A/B against main (962b3fd), with three main
runs and two pin runs. Main logged 25001 and 25P02 in every run (41/80,
10/9, 12/18); the pin logged none. Client error rate, latency, and
throughput matched within run-to-run noise.
- Full suite on the pin: every `just ci` recipe, the PostgreSQL lane,
and `just test`. The only failure from the pin was the reader test
above. Reds that don't involve sqlx: the desktop Tauri recipes
(`alsa-sys` needs ALSA headers on the build host),
`mobile-check`/`mobile-test` (a Flutter SDK `ColorScheme` type clash),
and two `buzz-acp` `git_bootstrap` tests (no git credentials on the
host). Two bridge search-timeout tests failed only under the full
parallel lane; run alone they pass on both the pin and 0.9.0.
- Two later commits change only tests, comments, and `Cargo.lock` (the
lock now moves only the sqlx crates). At those heads: the PostgreSQL
lane, the unit lane, and workspace clippy pass. Three tests failed once
under a contended full lane, and each passed 3/3 alone on both the pin
and 0.9.0. The two transaction tests fail on 0.9.0 and pass on the pin.

🤖 Generated with [Claude Code](https://claude.com/claude-code)

---------

Signed-off-by: Luke Tornquist <[email protected]>
Co-authored-by: Elrond <28d6302a099e5225b02c4155ac4236e4912603df2ab08dbfc2f4fef08ce598c8@buzz.block.builderlab.xyz>
Co-authored-by: Claude Opus 5.5 <[email protected]>

> **The pin is a temporary workaround for this week's public release.
The SQLx fix is still unmerged.**
>
> 1. We considered a maintained fork, but repository permissions blocked
it.
> 2. We will move to an official SQLx release once it includes the
required fixes and is verified with Buzz. Revisit the fork if needed.

## Bug


https://linear.app/squareup/issue/BUZZ-656/relay-writer-pool-connections-stranded-after-a-burst-bb-public-2026-11

A short database interruption can leave the relay unable to handle
database requests after traffic returns to normal. All connection slots
can remain occupied even though no request is using them and PostgreSQL
is idle. New requests then time out waiting for a slot.

## Cause

SQLx, our database library, returns connections to the pool in a
background task. If a socket stays open but stops answering, the final
health check has no timeout. That task keeps its slot, and
idle-connection cleanup cannot reclaim it
([#4349](transact-rs/sqlx#4349)).

## Fix

Pin SQLx to
[`c3735abf`](transact-rs/sqlx@c3735ab)
from [PR #4407](transact-rs/sqlx#4407). After
five seconds, it stops a stalled return operation, discards the
connection, and frees the slot so new requests can open a replacement.

Preserve the existing [transaction rollback
(#4394)](transact-rs/sqlx#4394) and [SQLite
compatibility (#4402)](transact-rs/sqlx#4402)
fixes. Give the reader's own check four seconds so it can record its
timeout before the outer deadline.

## Critical risk

**This fix depends on a SQLx patch that has not been merged.** Its
five-second limit affects every pool using this revision, so it can also
discard slow but healthy connections and cause more reconnections. The
local reproduction proves recovery from the injected failure; it does
not establish production behavior.

**Prevent accidental replacement:** require SQLx `=0.9.0`, disable
Renovate updates for all seven crates, and fail required CI if the
approved revision, lockfile source, or update restriction changes. These
guards prevent version drift; they do not remove the runtime risk of the
patch. An upgrade must explicitly update the policy after verifying all
required fixes.

## Before and after evidence

With real PostgreSQL 17.6 on macOS arm64, a proxy silenced 100 existing
connections while allowing new connections. All borrowers dropped their
connections; PostgreSQL showed 100 idle sessions.

| Observation | Before | After |
| --- | --- | --- |
| Pool slots still occupied | 100 after 10 seconds | 0 after 5.008
seconds |
| New work | Next checkout timed out | All 100 replacement connections
served `SELECT 1` |

<details>
<summary>Captured before and after output</summary>

**Before**

```text
REPRO burst: 100 dropped borrowers, all release pings held: size=100 idle=0 active=100
REPRO burst DB sessions: [("idle", "ClientRead", 100)]
REPRO burst after 10.000680583s: size=100 idle=0 active=100
REPRO burst: later checkout timed out despite PostgreSQL being healthy
```

**After**

```text
REPRO burst: 100 dropped borrowers, all release pings held: size=100 idle=0 active=100
REPRO burst DB sessions: [("idle", "ClientRead", 100)]
REPRO burst after 5.008456917s: size=0 idle=0 active=0
REPRO burst: all 100 replacement slots serve SELECT 1 while old sessions stay silent
```

Before evidence used Buzz `96af488d968a1ae9d961c2132bea27822735f8da`
with SQLx `f1e94ec3`. After evidence used Buzz
`4bfe60197e8c390a8701053228cd50c5391f37d3` with the SQLx pin committed
in `f253760085e24358728fa4f4fef059ccf892157f`. These are local
fault-injection results: they establish the stranded-slot mechanism and
recovery, not what originally interrupted production database traffic.

</details>

---------

Signed-off-by: Helen Tian <[email protected]>
Co-authored-by: OpenAI Codex <[email protected]>

Purpose and non-goals
- Unblock v3.44.0 publication without suppressing RUSTSEC-2026-0459.
  Cargo audit checks the whole lockfile, including the optional SQLite driver;
  libsqlite3-sys 0.37.0 is affected and the advisory requires >=0.39.0.
  The gateway continues using PostgreSQL with SQLite features disabled.
- Update only the SQLx source pin and its lockfile entries plus
  libsqlite3-sys 0.39.1. Keep application behavior, migrations, schema,
  release metadata and the existing five patch topics unchanged.

Ownership and contracts
- SQLx owns driver, pool, transaction and migration behavior. The pinned
  revision 10462fa4414c6c7f1ed1d39bb31678e3b726878c permits libsqlite3-sys
  0.39.x and descends from f1e94ec34dd5f11ad6488a3a8ac7988439a3f757,
  preserving the BEGIN cancellation/rollback fix from transact-rs/sqlx#4394.
- Preserve cancellation cleanup, query-budget resource lifetimes, saved
  observations and frozen migration ledgers on fresh and existing fork
  databases. No migration checksums or audit exceptions may be rewritten.
  Contracts: docs/architecture.md (observations/background tasks),
  backend/migrations/README.md and cpr-fork-patch/references/sql.md.

Provenance and replay
- Use the same focused dependency repair as upstream application commit
  092789f, without merging main's unrelated
  unreleased changes. SQLx compatibility change: transact-rs/sqlx#4502.
- On upgrade, keep upstream's safe dependency versions when they already
  retain the transaction fix and resolve this advisory; then retire this
  topic. Never downgrade its SQLx pin or restore the vulnerable lock entry.
  A new driver pin also requires real database lifecycle validation.

Verification
- Strict workspace Clippy passed with all targets/features and -D warnings.
- Real PostgreSQL tests cover transaction BEGIN cancellation, query-budget
  rollback/cleanup, saved account insights and usage observation projections.
- Fresh and v3.42.1 fork databases preserve ledgers and saved records across
  target initialization/restart, including independent client/upstream status
  values and missing values. Migrations and upstream release metadata have
  no diff. The package set is unchanged apart from libsqlite3-sys's version.
- Branch Security and the Release quality gates must pass before publication;
  no audit rule or workflow gate is disabled by this change.

Co-authored-by: Codex <[email protected]>

…ection inside it

Issue #47: under a load of requests that are cut off halfway, PostgreSQL
told the server "there is already a transaction in progress", and a device
that /login had just handed out was not there for the next request.

sqlx 0.9.0 cannot be interrupted between sending BEGIN and reading the
answer. It counts the transaction only when the answer is in, and a
transaction it has not counted is one it does not roll back when the call
is dropped: the connection goes back into the pool inside a transaction.
What the next users write on it is acknowledged and not committed, so it
is not there for any other connection, until a later user of that
connection begins a transaction of its own (the notice) and commits, or
rolls back and takes those rows with it. A client that hangs up takes its
request down wherever it is, so this is where the server was dropped.

Every transaction of the store now begins through one function, which
begins in a task of its own. The task sees BEGIN through whatever becomes
of the caller, and a transaction nobody waits for any more is rolled back
like any other that is dropped. sqlx has fixed this (transact-rs/sqlx#4394)
in no release yet.

The test drops a call at each of its first steps on a store with a single
connection, and reads what is written afterwards over another connection.
Without the change it fails at the second step.

Co-Authored-By: Claude <[email protected]>