Pages

Topics

Tools

↑↓navigate↵openescclose
All guides

Postgres: cached plan must not change result type

A prepared statement or PL/pgSQL plan outlived a schema change. Disable statement caching, force custom plans, discard plans, and order the deploy so no connection straddles DDL.

~5 min readSQL
#PostgreSQL#Prepared Statements#psycopg#Migrations
3D diagram of a database cylinder with an outdated blueprint slab showing a mismatching column grid, headline CACHED PLAN

Introduction & Quick Summary

Symptom. Everything worked before the migration. Now a query fails with:

ERROR: cached plan must not change result type

or, through a driver:

code
psycopg.errors.FeatureNotSupported: cached plan must not change result type PG::FeatureNotSupported: ERROR: cached plan must not change result type

Cause. PostgreSQL caches a plan — including the shape of its result — for server-side prepared statements and for statements inside PL/pgSQL functions. A SELECT *, a view, or a function returning a row type ties that plan to a column list. When DDL changes the list (ADD COLUMN, DROP COLUMN, ALTER COLUMN ... TYPE, a recreated view, a changed function return type), the cached plan's result descriptor no longer matches what the server would produce, and execution aborts instead of silently returning a different shape.

Fix. Stop the connection from reusing the plan: disable client-side statement caching, or force custom plans with plan_cache_mode = force_custom_plan, or discard plans and reconnect after DDL. Then remove the fragility that allowed it — explicit column lists instead of SELECT *, and a deploy order where the migration does not run underneath live connections.

Core Mechanics, Signatures & Reproducible Setup

Two separate caches produce the same message. The driver prepares a statement server-side after several executions, and the server stores its plan with the output tuple descriptor. PL/pgSQL keeps its own per-session plan cache, and a generic plan for SELECT * FROM t breaks the same way.

Reproduction with a client that prepares statements (PL/pgSQL path shown, which needs no driver):

CREATE TABLE invoice (id bigint PRIMARY KEY, total numeric); CREATE OR REPLACE FUNCTION invoice_totals() RETURNS TABLE (id bigint, total numeric) AS $$ SELECT id, total FROM invoice; $$ LANGUAGE sql; SELECT * FROM invoice_totals(); -- plan cached for this session ALTER TABLE invoice ADD COLUMN currency text DEFAULT 'USD'; SELECT * FROM invoice_totals(); -- ERROR: cached plan must not change result type

The same session, after discarding plans, works again:

DISCARD PLANS; SELECT * FROM invoice_totals(); -- fine

With a real driver the sequence is a deploy: migration runs, old connection pool survives, the next checkout of a pooled connection executes a prepared statement whose descriptor is stale. Check what the server is holding:

SELECT pid, state, now() - state_change AS idle_for, left(query, 60) AS last_query FROM pg_stat_activity WHERE datname = current_database() ORDER BY state_change LIMIT 20;

Long-lived idle connections that predate the migration are the ones that will raise the error, so recycle pools on a schedule shorter than your deploy interval.

Key Comparison Table

Where the cached plan can live, and how to disable it:

SourceDefaultDisable or reset
psycopg 3prepares after 5 executionsprepare_threshold=None on connect
JDBC + pgjdbcprepareThreshold=5, cache 256prepareThreshold=0, preparedStatementCacheQueries=0
pgx (Go)statement cache enableddefault_query_exec_mode=simple_protocol or QueryExecModeExec
Rails / pg gemprepared_statements: trueprepared_statements: false in database.yml
PL/pgSQL functionsplan cache per sessionSET plan_cache_mode = force_custom_plan, DISCARD PLANS
PgBouncer (transaction pooling)prepared statements need v1.21+max_prepared_statements, or session pooling

DDL that triggers it versus DDL that does not:

ChangeBreaks cached plansNote
ADD COLUMN to a table read with SELECT *YesAlso through a view or a RETURNS TABLE function
DROP COLUMNYesEven if the column is not referenced
ALTER COLUMN ... TYPEYesDescriptor type changes
CREATE OR REPLACE VIEW with new columnsYesSame mechanism, no table change involved
CREATE INDEXNoPlan changes, result shape does not
COMMENT, GRANTNoNo descriptor change

Practical Edge Cases, Error Fixes & Deep Dive Code Examples

1. psycopg 3 and SQLAlchemy

psycopg 3 prepares a statement after it has been executed prepare_threshold times (5 by default) on the same connection. Set it to None for connections that will outlive a migration:

python
import psycopg conn = psycopg.connect( "postgresql://app:secret@db/app", prepare_threshold=None, # never prepare server-side autocommit=True, ) with conn.cursor() as cur: cur.execute("SELECT id, total FROM invoice") print(cur.fetchall())

Through SQLAlchemy 2.0, pass it as a connect arg and let the pool recycle connections so none survives a deploy:

python
from sqlalchemy import create_engine engine = create_engine( "postgresql+psycopg://app:secret@db/app", connect_args={"prepare_threshold": None}, pool_recycle=900, pool_pre_ping=True, )

pool_recycle alone is not enough if the deploy and the recycle interval disagree; combined with pool_pre_ping it removes most of the window.

2. The PL/pgSQL function that returns a row type

A function declared RETURNS TABLE (...) or returning a composite type caches a plan whose descriptor is the declared columns. plan_cache_mode = force_custom_plan makes each execution replan, which costs a little and removes the failure:

sql
-- for one session SET plan_cache_mode = force_custom_plan; -- for one function ALTER FUNCTION invoice_totals() SET plan_cache_mode = force_custom_plan; -- for one role, so pooled connections inherit it ALTER ROLE app IN DATABASE app SET plan_cache_mode = force_custom_plan;

DISCARD PLANS clears the cache for the current session only, which is why it fixes a psql session and not an application: the app's other connections keep their stale plans.

3. JDBC and connection pools

pgjdbc prepares after five executions per connection and keeps up to preparedStatementCacheQueries plans. For a service that runs migrations on deploy, disable both on the affected pool:

jdbc:postgresql://db:5432/app?prepareThreshold=0
code
# HikariCP dataSource.cachePrepStmts=false dataSource.prepStmtCacheSize=0

Restarting the pool after the migration is the alternative that keeps preparation on: the error only exists while old connections and new schema coexist, so a deployment that forces the pool to reopen connections removes it.

4. Deploy choreography that avoids the window

Order matters more than flags. Three rules cover most incidents:

  1. Migrate first with additive-only DDL (ADD COLUMN ... NULL or with a default), so no cached descriptor has to change mid-flight.
  2. Then deploy code that references the new column.
  3. Only after every old instance is gone, drop or rename the old column.
-- safe step 1: additive, no rewrite, no descriptor change for existing SELECT lists ALTER TABLE invoice ADD COLUMN IF NOT EXISTS currency text; -- later, after the old code is gone ALTER TABLE invoice DROP COLUMN legacy_total;

A backfill between the steps, as a separate batched job (UPDATE ... WHERE currency IS NULL), keeps the lock profile small on a large table.

5. Views and SELECT *

CREATE OR REPLACE VIEW that adds a column breaks every prepared statement selecting from the view with *, and the table underneath did not change at all — which is why this error often appears after a "harmless" view update. Explicit columns make the statement independent of the view's shape:

-- fragile: descriptor follows the view SELECT * FROM invoice_summary; -- stable: names the columns the code actually uses SELECT id, total, currency FROM invoice_summary;

The same applies to INSERT INTO ... SELECT *. Naming columns is what lets a migration run without a coordinated code deploy.

6. Handling the error when it is already happening

Catching it and retrying on the same connection does not help: the plan is still cached. The connection has to be discarded, then the query retried:

python
import psycopg def fetch_invoice(conn: psycopg.Connection, invoice_id: int): try: with conn.cursor() as cur: cur.execute("SELECT id, total FROM invoice WHERE id = %s", (invoice_id,)) return cur.fetchone() except psycopg.errors.FeatureNotSupported as exc: if "cached plan must not change result type" not in str(exc): raise conn.close() raise RuntimeError("stale plan: discard this connection and retry") from exc

FeatureNotSupported is SQLSTATE 0A000, also used by genuinely unsupported features, so check the message first.

Common Compilation/Runtime Mistakes & Version Gotchas

  • Blaming the migration. The DDL is correct; the failure is a connection that predates it. Start from pg_stat_activity rather than from the migration file.
  • DISCARD ALL on one connection. It fixes the psql session and nothing else. Pools have other connections, and some drivers keep their own plan cache above the server's.
  • Disabling prepared statements everywhere. It works and costs throughput on hot queries; scope it to services that migrate on deploy, or fix the ordering instead.
  • PgBouncer transaction pooling without max_prepared_statements. Named prepared statements cannot be relied on across backends before 1.21, and the error can surface as a plan attached to the wrong session. Session pooling removes the ambiguity.
  • ALTER ROLE ... SET plan_cache_mode after the fact. New sessions inherit it, existing ones do not; a rolling restart of the app is still required.
  • SELECT * in code that must survive schema changes. The descriptor is the contract; naming columns makes an additive migration invisible to running queries.
  • Assuming a retry loop is safe. A retry on the same connection raises again, and a naive retry that reuses the broken connection can turn one error into a request timeout.

Related tools. Format the failing statement with the SQL formatter before comparing it to the view or function definition — a column list that drifted from the declared return type is the usual culprit — and paste the CREATE TABLE/CREATE VIEW DDL into the SQL schema viewer to see the column order and types that the cached descriptor is compared against.

Official references

primary documentation

The SQL documentation that defines the behaviour this guide explains.

Try matching browser tools

All tools

Small utilities that fit SQL work — they run entirely in this browser tab, so nothing you paste leaves your device.

All SQL guides

More in SQL

All SQL guides

The guides filed next to this one in SQL, in library order.