Fix PostgreSQL cached plan must not change result type

PostgreSQL's cached plan must not change result type error usually appears after a schema change while an application is reusing a prepared statement. The query worked before the deploy, the table changed, and an existing connection tried to execute a cached plan whose result columns no longer match what PostgreSQL planned earlier.

The fast fix is to refresh the affected sessions or discard their prepared statements. The durable fix is to avoid fragile SELECT * prepared statements in application code, roll schema changes in an order that old and new code can both tolerate, and make pooler behavior part of the migration plan.

Why this error happens

PostgreSQL supports prepared statements so a session can parse and plan SQL once, then execute it repeatedly. The official PREPARE documentation explains that prepared statements live for the duration of the database session unless they are explicitly deallocated. That session scope is the key detail. If a long-lived application connection prepares a statement before a deploy, the statement can survive past the migration that changed a table, view, or function result.

PostgreSQL can re-plan prepared statements when database objects change, but the result type is part of the contract between the database and client. If the statement used to return one shape and now returns another, PostgreSQL cannot quietly pretend the old result still applies. A common trigger is SELECT * against a table that had a column added, removed, reordered through a table rebuild, or changed through a view replacement.

Clear the bad plan safely

If production is failing now, start by limiting the blast radius. Restarting the affected application workers forces their database sessions to reconnect and drops session-scoped prepared statements. In a small deployment, that may be enough. In a larger deployment, drain instances gradually so the database is not hit by a synchronized reconnect storm.

From a database session, DEALLOCATE ALL removes prepared statements in that session. DISCARD ALL is broader: it resets session state, releases temporary resources, closes portals, and removes prepared statements. It is useful for connection cleanup, but do not run it in the middle of an application transaction. If PgBouncer is involved, use the pooler's operational commands or restart/recycle the affected pool in a controlled way rather than sending cleanup SQL blindly through application traffic.

The important practical distinction is scope. DEALLOCATE ALL or DISCARD ALL affects the current backend session, not every application connection in the fleet. If twenty web workers each hold a pool of connections, every affected session needs to be refreshed or allowed to die naturally.

Stop using SELECT * in prepared application queries

SELECT * is convenient in a console and fragile in an application protocol. Prepared statements, typed clients, code generators, and ORMs all benefit from a stable result shape. When application code needs five columns, ask for those five columns by name. Adding a column to the table should not change the query contract.

This matters most on hot paths that run through connection pools. A web request that prepares SELECT * FROM accounts WHERE id = $1 might look harmless until a migration adds a column and some old sessions keep executing the cached version. Rewriting the query as SELECT id, email, plan, created_at FROM accounts WHERE id = $1 makes the schema change less likely to affect that prepared result.

Views need the same discipline. If a view is used as an application API, treat its column list like a versioned contract. Add compatible columns deliberately, avoid replacing it with a different shape during a mixed deploy, and consider creating a new view name when the application contract really changes.

Make migrations compatible with long-lived sessions

A safe migration assumes old code, new code, old connections, and new connections may overlap. For result-shape changes, that usually means adding nullable columns before code reads them, deploying code that names columns explicitly, backfilling separately, and only later removing old columns after all old code is gone. The pattern is slower than a single migration, but it avoids turning a schema deploy into an application-wide plan-cache incident.

If your driver automatically prepares statements after a query is executed several times, include that in migration testing. Some drivers and ORMs call this server-side prepare, statement caching, or prepared statement threshold. For services behind PgBouncer, verify whether the chosen pooling mode supports the driver's prepared statement behavior. ArmorDB includes PgBouncer, so application compatibility with PgBouncer pooling modes is worth checking before a schema-heavy launch.

A quick diagnosis path

First, identify the query text and service that raised the error. Application logs are usually better than database logs because they show the endpoint, deploy version, and driver behavior. Next, check whether the query uses SELECT *, references a recently changed view, or calls a function whose return type changed. Then confirm whether the failing instances are old workers, long-lived background jobs, or pooled sessions that survived the migration.

Once traffic is stable, add a regression test for the migration shape. A useful test opens a connection, prepares the old query, applies the migration in a separate connection, and then executes the prepared statement again. If that reproduces the error, the migration is not safe for rolling deploys yet.

Takeaway

cached plan must not change result type is a deployment compatibility problem, not a random PostgreSQL failure. Clear affected sessions to restore service, then remove the underlying fragility: explicit result columns, rolling-safe migrations, and pooler settings that match the driver's prepared statement behavior. If a schema change cannot be made compatible with old sessions, schedule it as a coordinated restart rather than a routine background migration.

Sources and further reading

• PostgreSQL documentation: PREPARE
• PostgreSQL documentation: DEALLOCATE
• PostgreSQL documentation: DISCARD
• PostgreSQL documentation: CREATE VIEW