dangling transactions after 'failed to deallocate cached statement' errors

Hey there. First of all, thank you for all your work on this library :+1:

**Describe the bug**

We are using pgx in a fairly typical backend application, that serves requests to clients via grpc. We use pgx as a driver via sqlx. Starting with version 5.5.4, we started to notice some problems:
* Our production logs contained lines "failed to deallocate cached statement: conn closed". Roughly it was a couple hundred times a day, for a couple hundred thousand requests a day where each request is a transaction.
* This happened predominantly (but not exclusively) in streaming requests. Those are ended when cancelled by the user, so we assume it's some race condition around cancelled context behavior.
* The logged error happens from a call to `tx.Rollback()`, which is called from a `defer` that is set up with the transaction and run at the end of the cancelled rpc method.
* We also started to see dangling transactions left in an `idle in transaction` state, indefinitely (hours), causing issues particularly when they held on to locks. While this is not definitely related to the log entries, it started happening at the same time and the log entries indicate an error during transaction cleanup.

It seems like something goes wrong around transaction cleanup, but we can't tell what's going on at all.

**To Reproduce**

Unfortunately, we were unable to reproduce this bug outside our production environment, which also isn't source available. :( Rolling back to 5.5.3 "fixed" the issue for us, so it might be caused by 046f497efb4e92caa9575a0e9c351e4906af14c6. We tried some things in our codebase around transaction behavior and made sure we begin/end transactions consistently throughout, to no avail. If you have any ideas, we'd be happy to try.

**Version**
- Go: `$ go version` -> go version go1.21.5 linux/amd64
- PostgreSQL: `$ psql --no-psqlrc --tuples-only -c 'select version()'` -> PostgreSQL 15.7 on x86_64-pc-linux-musl, compiled by gcc (Alpine 13.2.1_git20240309) 13.2.1 20240309, 64-bit
- pgx: `$ grep 'github.com/jackc/pgx/v[0-9]' go.mod` -> v5.5.4

Start with tx.Rollback and the change introduced by commit 046f497efb4e92caa9575a0e9c351e4906af14c6, comparing pgx v5.5.3 and v5.5.4 under cancelled contexts. The report could not reproduce the problem outside production, so first investigate the transaction-cleanup path and the "failed to deallocate cached statement" error. Done means the cause is established and cleanup no longer leaves transactions idle in transaction.

Written by the indexing model from the issue text.