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

Meza

Can you please share the exact version of PostreSQL? 8.3.X? – Penchant

Answer 1

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.

Meza

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. – Franklyn

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. – Outstrip

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 – Hermineherminia

Answer 2

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.

Andee

Why isn't this the default? – Adar

@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. – Andee

@Adar 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). – Andee

The error is from postgres - you find it on the console of the server. It's not specific to Java. But the explanation helps! – Lo

@Adar it's not default because it hampers performance. e.g. see cybertec-postgresql.com/en/… – Bind

Answer 3

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.

Halflife

That's the idea. I used "preparedStatementCacheQueries=0" instead. – Outstrip

Answer 4

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

Inveigh

Answer 5

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

Helix

Answer 6

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);

Patinous

Answer 7

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.

Herophilus