Postgres: "ERROR: cached plan must not change result type"

Question

This exception is being thrown by the PostgreSQL 8.3.7 server to my application. Does anyone know what this error means and what I can do about it?

ERROR:  cached plan must not change result type
STATEMENT:  select code,is_deprecated from country where code=$1

Answer 1

If you are using PgBouncer, you can refer to this document:

The reuse of prepared statements has one downside. If the return or argument types of a prepared statement changes across executions then PostgreSQL currently throws an error such as:

ERROR: cached plan must not change result type

You can avoid such errors by not having multiple clients that use the exact same query string in a prepared statement, but expecting different argument or result types. One of the most common ways of running into this issue is during a DDL migration where you add a new column or change a column type on an existing table. In those cases you can run RECONNECT on the PgBouncer admin console after doing the migration to force a re-prepare of the query and make the error go away.

Answer 2

Prisma's users restart your application. The errors happen when you alter prisma/schema.prisma file and update your database without restarting your application.

Answer 3

If you have a JPA application with hibernate or other vendor and you change a column type in the domain, for example increase the column length of a single attribute, you have to manually change the column in the database in order to reflect that change:

alter table table_name alter column column_name type character varying(2000);

Answer 4

I got this error when developing a plpgsql (dropping and creating a function) in my local database without others connections.

I think that the cause is because I've used a function OUT parameter with same name of a column in table inside a LOOP.

I need disconnect and reconnect to fix.

PostgreSQL 16.

Answer 5

I got this error, I manually ran the failing select query and it fixed the error.

Answer 6

I'm adding this answer for anyone landing here by googling ERROR: cached plan must not change result type when trying to solve the problem in the context of a Java / JDBC application.

I was able to reliably reproduce the error by running schema upgrades (i.e. DDL statements) while my back-end app that used the DB was running. If the app was querying a table that had been changed by the schema upgrade (i.e. the app ran queries before and after the upgrade on a changed table) - the postgres driver would return this error because apparently it does caching of some schema details.

You can avoid the problem by configuring your pgjdbc driver with autosave=conservative. With this option, the driver will be able to flush whatever details it is caching and you shouldn't have to bounce your server or flush your connection pool or whatever workaround you may have come up with.

Reproduced on Postgres 9.6 (AWS RDS) and my initial testing seems to indicate the problem is completely resolved with this option.

Documentation: https://jdbc.postgresql.org/documentation/head/connect.html#connection-parameters

You can look at the pgjdbc Github issue 451 for more details and history of the issue.

---

JRuby ActiveRecords users see this: https://github.com/jruby/activerecord-jdbc-adapter/blob/master/lib/arjdbc/postgresql/connection_methods.rb#L60

---

Note on performance:

As per the reported performance issues in the above link - you should do some performance / load / soak testing of your application before switching this on blindly.

On doing performance testing on my own app running on an AWS RDS Postgres 10 instance, enabling the conservative setting does result in extra CPU usage on the database server. It wasn't much though, I could only even see the autosave functionality show up as using a measurable amount of CPU after I'd tuned every single query my load test was using and started pushing the load test hard.

Answer 7

For us, we were facing similar issue. Our application works on multiple schema. Whenever we were doing schema changes, this issue started occruding.

Setting up prepareThreshold=0 parameter inside JDBC parameter disables statement caching at database level. This solved it for us.

Answer 8

I figured out what was causing this error.

My application opened a database connection and prepared a SELECT statement for execution.

Meanwhile, another script was modifying the database table, changing the data type of one of the columns being returned in the above SELECT statement.

I resolved this by restarting the application after the database table was modified. This reset the database connection, allowing the prepared statement to execute without errors.