Describe the bug
We started getting ERROR: prepared statement does not exist (SQLSTATE 26000) because of a connection pooler bug, but the issue was that we couldn't recover the connection since pgx assumed it was still prepared because of the cache.

To Reproduce
Steps to reproduce the behavior:

package main

import (
	"context"
	"fmt"
	"os"

	"github.com/jackc/pgx/v5"
	"github.com/jackc/pgx/v5/internal/stmtcache"
)

func main() {
	conn, err := pgx.Connect(context.Background(), os.Getenv("DATABASE_URL"))
	if err != nil {
		panic(err)
	}
	defer conn.Close(context.Background())

	_, err = conn.Exec(context.Background(), "SELECT $1", "1")
	if err != nil {
		panic(err)
	}

	// simulate an out-of-band deallocation
	_, err = conn.Exec(context.Background(), fmt.Sprintf("deallocate %s", stmtcache.StatementName("SELECT $1")))
	if err != nil {
		panic(err)
	}

	_, err = conn.Exec(context.Background(), "SELECT $1", "1")
	fmt.Println(err)

	_, err = conn.Exec(context.Background(), "SELECT $1", "1")
	fmt.Println(err)
}

Please run your example with the race detector enabled. For example, go run -race main.go or go test -race.

Expected behavior
I expected pgx to recognize that the statement isn't prepared anymore and invalidate the internal cache.

Actual behavior
Instead the query is forever tainted and cannot be used

Version

• Go: go version go1.25.4 linux/amd64
• PostgreSQL: PostgreSQL 11.2-YB-2024.1.3.1-b0 on x86_64-pc-linux-gnu, compiled by clang version 17.0.6 (https://github.com/yugabyte/llvm-project.git 9b881774e40024e901fc6f3d313607b071c08631), 64-bit
• pgx: v5.7.6

Additional context
Ideally we would recognize this error, invalid the cache, and automatically retry the query. I'm ready to make a PR to fix this but wanted to open an issue first.