Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

cached plan must not change result type after an ALTER TABLE

ErrorERROR: cached plan must not change result type

Short 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.

  • Applies to PostgreSQL 16 to 18, PostgreSQL JDBC driver 42.x
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 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;
  2. 2Match what you see, then jump to its fix

    1. 1The 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. 2The failing query returns a column whose type was alteredA column type was changed while connections kept prepared statementsFix
    3. 3A Java stack trace with the same message, from a PostgreSQL JDBC connectionThe JDBC driver is not set to retry after the failureFix

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/ | head

2Fix

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

Did this fix it?

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 column

3Verify

Run the query again from the application after the change.

SELECT id, total_new FROM orders LIMIT 1;

Confirmed by jdbc.postgresql.org

Did this fix it?

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=0

3Verify

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

Did this fix it?

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

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.