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.

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:
psycopg.errors.FeatureNotSupported: cached plan must not change result type
PG::FeatureNotSupported: ERROR: cached plan must not change result typeCause. 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 typeThe same session, after discarding plans, works again:
DISCARD PLANS;
SELECT * FROM invoice_totals(); -- fineWith 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:
| Source | Default | Disable or reset |
|---|---|---|
| psycopg 3 | prepares after 5 executions | prepare_threshold=None on connect |
| JDBC + pgjdbc | prepareThreshold=5, cache 256 | prepareThreshold=0, preparedStatementCacheQueries=0 |
| pgx (Go) | statement cache enabled | default_query_exec_mode=simple_protocol or QueryExecModeExec |
Rails / pg gem | prepared_statements: true | prepared_statements: false in database.yml |
| PL/pgSQL functions | plan cache per session | SET 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:
| Change | Breaks cached plans | Note |
|---|---|---|
ADD COLUMN to a table read with SELECT * | Yes | Also through a view or a RETURNS TABLE function |
DROP COLUMN | Yes | Even if the column is not referenced |
ALTER COLUMN ... TYPE | Yes | Descriptor type changes |
CREATE OR REPLACE VIEW with new columns | Yes | Same mechanism, no table change involved |
CREATE INDEX | No | Plan changes, result shape does not |
COMMENT, GRANT | No | No 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:
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:
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:
-- 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
# HikariCP
dataSource.cachePrepStmts=false
dataSource.prepStmtCacheSize=0Restarting 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:
- Migrate first with additive-only DDL (
ADD COLUMN ... NULLor with a default), so no cached descriptor has to change mid-flight. - Then deploy code that references the new column.
- 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:
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 excFeatureNotSupported 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_activityrather than from the migration file. DISCARD ALLon 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_modeafter 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 documentationThe SQL documentation that defines the behaviour this guide explains.
Try matching browser tools
All toolsSmall utilities that fit SQL work — they run entirely in this browser tab, so nothing you paste leaves your device.
Related SQL guides
All SQL guidesPostgres permission denied for sequence: GRANT USAGE and nextval
Sequences carry their own ACL, so table grants never cover nextval. The USAGE grant, default privileges, and why identity columns behave differently.
Postgres RLS 42P17: infinite recursion detected in policy
A policy on members that reads members re-enters itself. One security definer function with a pinned search_path ends the recursion and leaves the rest of the policy intact.
Postgres: could not serialize access due to concurrent update
At REPEATABLE READ and SERIALIZABLE a conflicting write aborts with SQLSTATE 40001. Retry the whole transaction with backoff, then remove the conflict instead of retrying it.
POSTGRES_PASSWORD Is Ignored: Docker Volume Init Rules
The official image reads POSTGRES_PASSWORD once, at initdb. Why changed credentials and users do nothing on an existing volume, and how to rotate safely.
More in SQL
All SQL guidesThe guides filed next to this one in SQL, in library order.