162

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
Shorn
  • 19,077
  • 15
  • 90
  • 168
Jin Kim
  • 16,562
  • 18
  • 60
  • 86

4 Answers4

272

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.

Jin Kim
  • 16,562
  • 18
  • 60
  • 86
  • 2
    I got the same problem on Postgres 10 while running JUnit tests for spring+jpa application. Exception message: `org.postgresql.util.PSQLException: ERROR: cached plan must not change result type`. And all tests work like a charm, but only `Repository.findById()`. I don't change the schema in my tests, but I'm using `@FlywayTest` to prepare a test init database for each test. If I remove `@FlywayTest` annotation, it works well. – Binakot Mar 10 '19 at 09:25
  • 1
    I solved the Binakot issue adding "?preparedStatementCacheQueries=0" to the JDBC URL to disable prepared statements caching. Error is due to @Flytest clearing and recreating the schema between caching the statement and reusing it. – numéro6 Nov 24 '20 at 17:27
  • 1
    I found a problem while debugging and ran a database migration without stopping the debugger first. This caused my error. Restarting fixed the problem but thought I would share my context in case anyone else is wondering what might cause this problem – Jay Killeen Aug 23 '22 at 23:57
62

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.

Shorn
  • 19,077
  • 15
  • 90
  • 168
  • 10
    Why isn't this the default? – cdmckay Mar 30 '18 at 15:12
  • 1
    @Hrishi Your comment made me realise the original question didn't actually specify Java (because I found it when dealing with the problem in a Java context). I'd say you might want to post a whole new question explicitly looking for a solution in a Ruby context. – Shorn Mar 27 '19 at 22:58
  • @cdmckay Because it was new functionality introduced into the driver around version 9.4-ish timeframe. I for one would be very unhappy if a some new version of the pgjdbc broke my application because it default-enabled new, un-proven, performance degrading functionality I didn't need. (That said, this is now a new entry on my "always do this when creating a new application" checklist). – Shorn Mar 27 '19 at 23:24
  • The error is from postgres - you find it on the console of the server. It's not specific to Java. But the explanation helps! – cljk Nov 27 '19 at 13:29
  • @cdmckay it's not default because it hampers performance. e.g. see https://www.cybertec-postgresql.com/en/subtransactions-and-performance-in-postgresql/ – Paku Nov 07 '22 at 08:15
6

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.

irscomp
  • 2,900
  • 1
  • 16
  • 12
0

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

Sumant Dey
  • 1