cached plan must not change result type after an ALTER TABLE
ERROR: cached plan must not change result typeShort answer
A prepared statement that returns a fixed set of columns was kept by the server, then a table it reads was altered so the result shape changed. PostgreSQL re-plans but refuses to change the result type. Name columns instead of SELECT *, avoid type changes on live tables, and make the client re-prepare or retry.
Fix it fast
1Find out which cause you have
Find which table changed shortly before the first error, and whether the failing statement uses SELECT *. That tells you which cause below matches.
SELECT column_name, data_type FROM information_schema.columns WHERE table_name = 'orders' ORDER BY ordinal_position;2Match what you see, then jump to its fix
- 1
The error starts after a migration that added or dropped a column of a table the query readsSELECT * in a server-prepared statement, then the table was alteredFix - 2
The failing query returns a column whose type was alteredA column type was changed while connections kept prepared statementsFix - 3
A Java stack trace with the same message, from a PostgreSQL JDBC connectionThe JDBC driver is not set to retry after the failureFix
- 1
What does the error look like?
ERROR: cached plan must not change result type
Cause 1 of 3
SELECT * in a server-prepared statement, then the table was altered
1Check
PostgreSQL re-analyzes a prepared statement after DDL on objects it uses. The error is raised in that re-analysis when the statement was created with a fixed result type and the new result shape differs. SELECT * changes shape when you add or drop a column.
grep -rn 'SELECT \*' src/ | head2Fix
Use explicit column names in the SELECT list, so adding a column does not change what the statement returns.
-- instead of: SELECT * FROM orders WHERE id = ?
SELECT id, customer, total FROM orders WHERE id = ?3Verify
Add a column on a test copy while the app is running; the query keeps working.
ALTER TABLE orders ADD COLUMN note text;Confirmed by jdbc.postgresql.org
Cause 2 of 3
A column type was changed while connections kept prepared statements
1Check
The JDBC documentation names column type alters as one of the two triggers, together with SELECT *. The driver switches to server-side prepared statements after prepareThreshold executions (5 by default), which is why the error shows up only after a query has run a few times.
SELECT column_name, data_type FROM information_schema.columns WHERE table_name = 'orders';2Fix
Avoid altering column types on tables in use. If you must, add a new column and migrate to it, or restart the application connections right after the change.
ALTER TABLE orders ADD COLUMN total_new numeric(12,2);
-- backfill, switch the code, then drop the old column3Verify
Run the query again from the application after the change.
SELECT id, total_new FROM orders LIMIT 1;Confirmed by jdbc.postgresql.org
Cause 3 of 3
The JDBC driver is not set to retry after the failure
1Check
The driver can re-execute a statement automatically if the transaction has not failed yet. Inside a failed transaction it needs autosave to roll back and retry; the default is never.
grep -rn "jdbc:postgresql" src/main/resources/2Fix
Add autosave=conservative to the JDBC URL so the driver retries for this error. The docs warn autosave can hurt long transactions. Setting prepareThreshold=0 turns server-prepared statements off completely.
jdbc:postgresql://db.example.com:5432/app?autosave=conservative
jdbc:postgresql://db.example.com:5432/app?prepareThreshold=03Verify
Run an ALTER TABLE on a test copy while the app serves requests; no request should fail.
ALTER TABLE orders ADD COLUMN note2 text;Confirmed by jdbc.postgresql.org
How do I stop it coming back?
- Never use SELECT * in application code that talks to prepared statements.
- Treat column type changes as a two-step migration.
- Test every migration that changes a table's columns against a running copy of the app before production.
Sources
How was this page made?
I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.
Which stack is behind this page?
Related fixes
Databases
DBeaver cannot run pg_restore: the utility is not found in the client home
Utility 'pg_restore' not found in client home '/usr/lib' (/usr/lib)
DBeaver runs pg_restore from a local PostgreSQL client folder; it does not bundle one.
3 causes, each with a fixDatabases
DBeaver cannot download the PostgreSQL driver from Maven
The PostgreSQL driver files are missing and the download does not complete
DBeaver downloads the PostgreSQL JDBC driver from a Maven repository the first time you connect.
3 causes, each with a fixDatabases
python manage.py migrate finishes but the tables for my app are missing
relation "myapp_book" does not exist
migrate only applies migration files that exist.
3 causes, each with a fixDatabases
Django save fails on PostgreSQL: null value in a column violates a not-null constraint
django.db.utils.IntegrityError: null value in column "price" of relation "shop_product" violates not-null constraint
A model field is NOT NULL in the database unless it has null=True.
3 causes, each with a fixDatabases
Django migration on PostgreSQL stops with 'pending trigger events'
django.db.utils.OperationalError: cannot ALTER TABLE "mytable" because it has pending trigger events
PostgreSQL runs a Django migration in one transaction.
2 causes, each with a fixDatabases
Django cannot connect to PostgreSQL: connection refused on port 5432, locally or in Docker
Is the server running on that host and accepting TCP/IP connections?
Connection refused means nothing accepted the connection at the host and port Django used.
4 causes, each with a fix