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

Question

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

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

Answer 1

I realized what caused 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 returned in the SELECT statement above.

I solved this by restarting the application after changing the database table. This reset the database connection, allowing the finished statement to execute without errors.

Answer 2

I am adding this answer for anyone who lands here resorting to ERROR: cached plan must not change result type when trying to solve a problem in the context of a Java / JDBC application.

I was able to reliably reproduce the error by running schema updates (e.g. DDL statements) when my internal application that used the DB was working. If the application requests a table that was changed during the schema update (i.e., the application performed queries before and after the update for the changed table), the postgres driver would return this error, since it obviously caches some details of the schema.

You can avoid this problem by configuring your pgjdbc driver with pgjdbc autosave=conservative . With this option, the driver will be able to dump any data that it caches, and you do not need to dump the load from your server, reset the connection pool or any workaround that you may have encountered.

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

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

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

Performance Note:

According to the performance issues reported in the link above - you should do some performance / load / shutter speed testing of the application before turning it on blindly.

When performing performance testing in my own application running on an AWS RDS Postgres 10 instance, turning on the conservative tuning causes additional CPU utilization. Although it was not much, I could see that the autosave functionality showed that it uses a measurable amount of CPU after I configured every single request that was used in my load test and started to load the load test heavily.