Using a batch within a transaction results in error: "failed to deallocate cached statement(s): conn busy"

[vkcku]

I am sending a batch using pgx.Tx.SendBatch, but whenever I rollback the transaction, I get the error: "failed to deallocate cached statement(s): conn busy". This only happens when I manually scan the batch results instead of using QueuedQuery (this is the code generated by sqlc). I would like to know what the reason is since this is either a bug in pgx or sqlc is generating incorrect code.

Also, the error only shows up when using QueryRow and does not happen when using Exec which made me think it may be related to #635.

Here is a reproduction:

package main

import (
	"context"
	"log"
	"os"

	"github.com/jackc/pgx/v5"
	"github.com/jackc/pgx/v5/pgxpool"
)

func main() {
	ctx := context.Background()
	pool, err := pgxpool.New(ctx, os.Getenv("DATABASE_URL"))
	if err != nil {
		log.Fatal(err)
	}
	defer pool.Close()

	_, err = pool.Exec(ctx, "DROP TABLE IF EXISTS conn_busy_test")
	if err != nil {
		log.Fatalf("drop table")
	}

	_, err = pool.Exec(
		ctx,
		"CREATE TABLE conn_busy_test(id int, value text not null check (value != ''))",
	)
	if err != nil {
		log.Fatal(err)
	}

	tx, err := pool.Begin(ctx)
	if err != nil {
		log.Panicf("transaction: %+v", err)
	}
	defer func() {
		err := tx.Rollback(ctx)
		if err != nil {
			log.Panicf("rollback: %+v", err)
		}

		log.Println("rollback done")
	}()

	values := []int{1, 2, 3, 4, 5, 6, 7, 8, 9, 10}
	err = insertMany(ctx, tx, values)

	if err != nil {
		log.Panicf("insert: %+v", err)
	}
}

// insertMany ends up will result in the rollback failing with the error:
// "failed to deallocate cached statement(s): conn busy"
func insertMany(ctx context.Context, tx pgx.Tx, values []int) error {
	b := pgx.Batch{}
	for _, v := range values {
		b.Queue("INSERT INTO conn_busy_test(id) VALUES ($1) RETURNING id", v)
	}

	br := tx.SendBatch(ctx, &b)
	defer br.Close()

	ret := make([]int, len(values))
	for i := range len(values) {
		row := br.QueryRow()
		err := row.Scan(&ret[i])
		if err != nil {
			return err
		}
	}

	return nil
}

// insertMany2 does not result in the rollback failing.
func insertMany2(ctx context.Context, tx pgx.Tx, values []int) error {
	ret := make([]int, len(values))
	b := pgx.Batch{}
	for i, v := range values {
		b.Queue("INSERT INTO conn_busy_test(id) VALUES ($1) RETURNING id", v).
			QueryRow(func(row pgx.Row) error {
				err := row.Scan(&ret[i])

				return err
			})
	}

	br := tx.SendBatch(ctx, &b)
	err := br.Close()
	if err != nil {
		return err
	}

	return nil
}

// insertMany3 does not result in the rollback failing either.
func insertMany3(ctx context.Context, tx pgx.Tx, values []int) error {
	b := pgx.Batch{}
	for _, v := range values {
		b.Queue("INSERT INTO conn_busy_test(id) VALUES ($1)", v)
	}

	br := tx.SendBatch(ctx, &b)
	defer br.Close()

	for range len(values) {
		_, err := br.Exec()
		if err != nil {
			return err
		}
	}

	return nil
}

[vkcku]

One more thing I forgot to mention: this only happens if any of the queries return an error. This is why I intentionally do not insert the value column. If the query is correct and the insertion is successful, then the rollback has no issues.

[jackc]

When I run your example I do not get a conn busy error. I get the following:

2025/12/13 09:24:37 insert: ERROR: null value in column "value" of relation "conn_busy_test" violates not-null constraint (SQLSTATE 23502)
2025/12/13 09:24:37 rollback done
panic: insert: ERROR: null value in column "value" of relation "conn_busy_test" violates not-null constraint (SQLSTATE 23502)

goroutine 1 [running]:
log.Panicf({0x104e46ede?, 0x105316940?}, {0x14000209f18?, 0x14000194240?, 0x14000209e80?})
	/usr/local/go/src/log/log.go:460 +0x70
main.main()
	/Users/jack/dev/pgx_issues/pgx-2446/main.go:50 +0x280
exit status 2

It's a little hard to tell the order things are happening with the defer and panic. But it appears the rollback succeeded.

I also rewrote the example to go straight line.

package main

import (
	"context"
	"log"
	"os"

	"github.com/jackc/pgx/v5"
	"github.com/jackc/pgx/v5/pgxpool"
)

func main() {
	ctx := context.Background()
	pool, err := pgxpool.New(ctx, os.Getenv("DATABASE_URL"))
	if err != nil {
		log.Fatal(err)
	}
	defer pool.Close()

	_, err = pool.Exec(ctx, "DROP TABLE IF EXISTS conn_busy_test")
	if err != nil {
		log.Fatalf("drop table")
	}

	_, err = pool.Exec(
		ctx,
		"CREATE TABLE conn_busy_test(id int, value text not null check (value != ''))",
	)
	if err != nil {
		log.Fatal(err)
	}

	tx, err := pool.Begin(ctx)
	if err != nil {
		log.Fatalf("transaction: %+v", err)
	}

	values := []int{1, 2, 3, 4, 5, 6, 7, 8, 9, 10}
	err = insertMany(ctx, tx, values)
	if err != nil {
		log.Println("insertMany failed:", err)
	} else {
		log.Println("insertMany succeeded")
	}

	err = tx.Rollback(ctx)
	if err != nil {
		log.Println("rollback failed:", err)
	} else {
		log.Println("rollback succeeded")
	}
}

// insertMany ends up will result in the rollback failing with the error:
// "failed to deallocate cached statement(s): conn busy"
func insertMany(ctx context.Context, tx pgx.Tx, values []int) error {
	b := pgx.Batch{}
	for _, v := range values {
		b.Queue("INSERT INTO conn_busy_test(id) VALUES ($1) RETURNING id", v)
	}

	br := tx.SendBatch(ctx, &b)
	defer br.Close()

	ret := make([]int, len(values))
	for i := range len(values) {
		row := br.QueryRow()
		err := row.Scan(&ret[i])
		if err != nil {
			return err
		}
	}

	return nil
}

// insertMany2 does not result in the rollback failing.
func insertMany2(ctx context.Context, tx pgx.Tx, values []int) error {
	ret := make([]int, len(values))
	b := pgx.Batch{}
	for i, v := range values {
		b.Queue("INSERT INTO conn_busy_test(id) VALUES ($1) RETURNING id", v).
			QueryRow(func(row pgx.Row) error {
				err := row.Scan(&ret[i])

				return err
			})
	}

	br := tx.SendBatch(ctx, &b)
	err := br.Close()
	if err != nil {
		return err
	}

	return nil
}

// insertMany3 does not result in the rollback failing either.
func insertMany3(ctx context.Context, tx pgx.Tx, values []int) error {
	b := pgx.Batch{}
	for _, v := range values {
		b.Queue("INSERT INTO conn_busy_test(id) VALUES ($1)", v)
	}

	br := tx.SendBatch(ctx, &b)
	defer br.Close()

	for range len(values) {
		_, err := br.Exec()
		if err != nil {
			return err
		}
	}

	return nil
}

It seems to work here too:

2025/12/13 09:30:03 insertMany failed: ERROR: null value in column "value" of relation "conn_busy_test" violates not-null constraint (SQLSTATE 23502)
2025/12/13 09:30:03 rollback succeeded

[vkcku]

Thanks for looking into this! Unfortunately, I am still getting the error with your rewritten example.

2025/12/15 19:17:00 insertMany failed: ERROR: null value in column "value" of relation "conn_busy_test" violates not-null constraint (SQLSTATE 23502)
2025/12/15 19:17:00 rollback failed: failed to deallocate cached statement(s): conn busy

Here are the versions I am using if that may help:

• go: go version go1.25.4 linux/amd64
• postgres: PostgreSQL 18.1 on x86_64-pc-linux-gnu, compiled by clang version 20.1.8, 64-bit
• pgx: github.com/jackc/pgx/v5 v5.7.6

[jackc]

I was testing on master. Can you try that?

[vkcku]

Aah it seems the issue is fixed in master. I am not getting the error anymore in master.