Learning center

Fix "cached plan must not change result type" after adding a PostgreSQL column

Adding a nullable column changes only the catalog, and it can still make the running application fail. A connection that prepared SELECT before the migration gets ERROR: cached plan must not change result type on its next execution, and on every one after that until the statement is prepared again. Rehearse column additions with the previous release holding warm connections.

Reproduce it with two sessions#

We ran this on PostgreSQL 16.15. Set up a table:

CREATE TABLE profiles (id integer PRIMARY KEY, display_name text NOT NULL);
INSERT INTO profiles VALUES (7, 'Mara');

Session 1 stands in for a pooled application connection. Many drivers prepare repeated queries on the server and keep them for the life of the connection.

\set VERBOSITY verbose
PREPARE load_profile(integer) AS SELECT * FROM profiles WHERE id = $1;
PREPARE load_name(integer) AS SELECT id, display_name FROM profiles WHERE id = $1;
EXECUTE load_profile(7);

Session 2 runs the migration:

ALTER TABLE profiles ADD COLUMN pronouns text;

Back in session 1:

EXECUTE load_profile(7);
-- ERROR:  0A000: cached plan must not change result type
-- LOCATION:  RevalidateCachedQuery, plancache.c:730

EXECUTE load_name(7);
--  id | display_name
--   7 | Mara

EXECUTE load_profile(7);
-- ERROR:  0A000: cached plan must not change result type

Why only one statement fails#

When a table changes, PostgreSQL marks the plans that depend on it as invalid and plans them again on the next execution. For load_name, the new plan returns the same two columns, so the replan is invisible. For load_profile, * now expands to three columns. The client was told the statement returns two when it was prepared, so PostgreSQL refuses to return a different shape and raises SQLSTATE 0A000 instead. The statement stays broken: the error repeats until the client deallocates it and prepares it again. The PREPARE documentation describes the revalidation.

The same applies to dropping a column, changing a column's type, and to a view whose definition changes. An explicit column list in the query avoids it whenever the listed columns keep their types.

What it looks like in production#

Each pooled connection that prepared the statement fails once it next runs it, and keeps failing until the pool closes that connection or the driver prepares the statement again. Drivers handle this differently. The PostgreSQL JDBC driver, for example, starts using server-side prepared statements on the fifth execution by default and only rolls back and retries this error itself with autosave=conservative, according to its connection documentation. Inside an application transaction, the error aborts the transaction, so even a driver that re-prepares cannot retry it transparently.

The failure appears only on connections that were already open, so a deployment check against fresh connections from the new release will not see it. It looks instead like a burst of errors on the old release during the rollout, which stops when its tasks are replaced.

Choose a fix#

  • Select explicit columns in application queries, including those an ORM generates. This removes the dependency on the table's shape.
  • Configure the driver to re-prepare after this error, or to use only unnamed statements, if you cannot change the queries. Check the driver's documentation for the exact behavior, including what happens inside a transaction.
  • Close the old release's pooled connections right after the migration, for example by running the migration as the first step of a deployment that replaces every task. This shortens the window without removing it.

What to rehearse#

Run the previous release against a staging database with its connection pool warm: send enough traffic that every repeated query has run at least as many times as the driver's preparation threshold. Apply the migration while that traffic continues, and count errors with SQLSTATE 0A000 in the old release's logs. The rehearsal passes with zero. Column renames, which change what the old release reads rather than how it is prepared, need their own rollback exercise.

Find the other changes that need a rehearsal in which PostgreSQL migration needs a rehearsal.

One month of free code review.

New organizations get one calendar month of hosted code review for private and public GitHub repositories. No card or cloud connection required. Eligibility and fair-use terms.

Start free with GitHub

Related reading