pgx Prepared Statement Cache Surprises and How to Fix Them

The error looks like this, from a service that has been running for days and has just had a migration applied:

ERROR: cached plan must not change result type (SQLSTATE 0A000)

It appears on the first call after the migration, once per connection, and then goes away. It is confusing because the SQL did not change, the code did not change, and the next retry works. It comes from how pgx and PostgreSQL cooperate on prepared statements, and understanding it explains a whole family of "prepared statement" errors. This article reproduces it on PostgreSQL 18.6 from Go 1.25.6 with pgx v5.11.0, shows which of pgx's execution modes are affected, and gives the fixes. The related problem behind a transaction pooler is in pgx with PgBouncer, and the connection mechanics are in connection lifecycle. It belongs to the database cornerstone.

Why it happens

By default pgx uses the extended protocol with a prepared statement cache. The first time a connection runs a SQL text, pgx sends a Parse and names the statement stmtcache_<hash>; the server plans and remembers it for that session. Later calls send only a Bind and an Execute with the name, skipping parsing and planning. That is faster, and it is what the QueryExecModeCacheStatement mode does.

A prepared statement fixes its result shape when it is prepared: the number, names and types of the columns. If someone then changes a table so that select * would return a different shape, as alter table add column does, the server refuses to reuse the old plan with the old shape: cached plan must not change result type. The pgx cache does not know the table changed, because the change was made in another session. So the first call after the migration fails.

Reproducing it

package main

import (
	"context"
	"errors"
	"fmt"
	"log"

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

const dsn = "postgres://postgres:[email protected]:55432/lab?sslmode=disable"

func must(err error) {
	if err != nil {
		log.Fatal(err)
	}
}

func code(err error) string {
	if err == nil {
		return "ok"
	}
	var pg *pgconn.PgError
	if errors.As(err, &pg) {
		return pg.Code + " " + pg.Message
	}
	return err.Error()
}

func try(mode pgx.QueryExecMode) {
	ctx := context.Background()
	cfg, err := pgxpool.ParseConfig(dsn)
	must(err)
	cfg.MaxConns = 1 // one connection, so the cache that matters is always the one in use
	cfg.ConnConfig.DefaultQueryExecMode = mode
	pool, err := pgxpool.NewWithConfig(ctx, cfg)
	must(err)
	defer pool.Close()
	for _, q := range []string{"drop table if exists cache676", "create table cache676 (id int primary key, a text)", "insert into cache676 values (1, 'one')"} {
		_, err := pool.Exec(ctx, q)
		must(err)
	}
	read := func() error {
		rows, err := pool.Query(ctx, "select * from cache676 where id = $1", 1)
		if err != nil {
			return err
		}
		_, err = pgx.CollectRows(rows, pgx.RowToMap)
		return err
	}
	e1 := read() // prepares and caches the statement when the mode caches
	// Another session changes the table's shape, as a migration does.
	other, err := pgx.Connect(ctx, dsn)
	must(err)
	_, err = other.Exec(ctx, "alter table cache676 add column b int default 7")
	must(err)
	other.Close(ctx)
	e2 := read()
	e3 := read()
	fmt.Printf("%-28s first %-3s | after the ALTER: %s\n", mode, code(e1), code(e2))
	fmt.Printf("%-28s       %-3s | and the next call: %s\n", "", "", code(e3))
}

func main() {
	fmt.Println("one connection reads 'select *' before and after another session adds a column")
	for _, mode := range []pgx.QueryExecMode{
		pgx.QueryExecModeCacheStatement, pgx.QueryExecModeCacheDescribe, pgx.QueryExecModeDescribeExec, pgx.QueryExecModeExec, pgx.QueryExecModeSimpleProtocol,
	} {
		try(mode)
	}
}

one connection reads 'select *' before and after another session adds a column
cache statement              first ok  | after the ALTER: 0A000 cached plan must not change result type
                                       | and the next call: ok
cache describe               first ok  | after the ALTER: 08P01 bind message has 2 result formats but query has 3 columns
                                       | and the next call: ok
describe exec                first ok  | after the ALTER: ok
                                       | and the next call: ok
exec                         first ok  | after the ALTER: ok
                                       | and the next call: ok
simple protocol              first ok  | after the ALTER: ok
                                       | and the next call: ok

For each execution mode the program builds a pool with one connection, so the cache that matters is always the one in use. It reads select * from cache676 where id = $1, then a second connection adds a column to the table, then it reads the same statement twice more.

What each mode does

The default fails once. The error is exactly the one above, and the very next call succeeded: pgx notices that a cached statement failed with this class of error, throws the entry away, and prepares again. So the failure is a single error per connection per affected statement, not a permanent state, and a retry fixes it. In a service with ten pooled connections that means up to ten failed requests after a migration.

CacheDescribe caches the description of the statement (the column types, from a Describe round trip) rather than a named prepared statement, and then runs it unnamed. It failed once as well, with a different message: bind message has 2 result formats but query has 3 columns, because the cached description said two columns and the server now returns three. Same cause, same recovery on the next call.

DescribeExec, Exec and SimpleProtocol did not fail. DescribeExec describes the statement on every call, so it always knows the current shape, at the cost of an extra round trip per query. Exec sends the statement unnamed with its parameters in each call, with no cache. SimpleProtocol sends the whole statement as text, which also has no plan to go stale (and has its own rules for how arguments are interpolated, so it is not a drop in replacement for everything).

Fixes, in order of preference

1. Name your columns. select id, a from cache676 returns the same two columns after someone adds b, so the cached plan stays valid. This is the real fix: select * in application code is the root of the problem, and it is also a bad habit for performance and for clarity. The error only occurs for statements whose result shape changes: a changed column list, a renamed column, a changed type.
2. Retry once. Treat SQLSTATE 0A000 with that message, and 08P01 with the bind message, as retryable for reads and for writes that have not been sent. pgx has already evicted the cache entry, so the retry works. A small retry wrapper around the repository functions covers it, and since an error class means "the schema changed under me", log it at warn level.
3. Deploy migrations in two steps when they change a result shape that code reads with select *: add the column, deploy code that no longer relies on the old shape, and restart the pool (or the service) when convenient. Expand and contract migrations, as in zero downtime migrations, avoid the problem by design.
4. Change the execution mode if prepared statements are not worth their cost or are not supported: default_query_exec_mode=exec in the connection string (or cfg.ConnConfig.DefaultQueryExecMode) keeps parameters out of the SQL text and has no cache to invalidate, at the price of parsing each statement every time. describe_exec is the middle option. For PgBouncer in transaction mode, exec or simple_protocol is the standard answer.
5. Reset the cache with DEALLOCATE ALL on a connection, or by closing connections after a migration (pool.Reset() closes all idle connections and marks the others to be closed on release). It is the blunt tool after a migration that you know changed shapes.

You can also change the cache size, statement_cache_capacity (default 512 statements per connection), or disable it by setting the mode. A very large number of distinct statement texts, for example from building SQL with values inlined, floods the cache and the server's memory; the fix there is to use placeholders.

Related errors

• prepared statement "stmtcache_..." already exists (42P05) or does not exist (26000): usually a pooler in transaction mode giving a different server connection from the one that prepared the statement. See pgx with PgBouncer.
• cached plan must not change result type appearing on every call, not once: something keeps changing the shape, such as a query that uses a function returning different columns, or a view recreated in a loop.
• unnamed prepared statement does not exist, again a pooler, in the unnamed form.

What to remember

The error is a symptom of a good optimisation meeting a schema change, not a bug in your code or in pgx. The three questions to ask when you see it are: did a migration just run, does the failing SQL use select * or a function with a changing result, and does a retry succeed. If yes to all three, name the columns and add a retry. The execution modes are documented in the pgx reference for QueryExecMode, and the server behaviour in the PostgreSQL manual's page on PREPARE.