postgresql Postgres：“错误：缓存计划不得更改结果类型”
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2783813/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Postgres: "ERROR: cached plan must not change result type"
提问by Jin Kim
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?
PostgreSQL 8.3.7 服务器向我的应用程序抛出此异常。有谁知道这个错误意味着什么以及我能做些什么？
ERROR: cached plan must not change result type
STATEMENT: select code,is_deprecated from country where code=
回答by Jin Kim
I figured out what was causing this error.
我想出了是什么导致了这个错误。
My application opened a database connection and prepared a SELECT statement for execution.
我的应用程序打开了一个数据库连接并准备了一个 SELECT 语句以供执行。
Meanwhile, another script was modifying the database table, changing the data type of one of the columns being returned in the above SELECT statement.
同时，另一个脚本正在修改数据库表，更改上述 SELECT 语句中返回的列的数据类型。
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.
我通过在修改数据库表后重新启动应用程序解决了这个问题。这将重置数据库连接，允许准备好的语句执行而不会出错。
回答by Shorn
I'm adding this answer for anyone landing here by googling ERROR: cached plan must not change result typewhen trying to solve the problem in the context of a Java / JDBC application.
ERROR: cached plan must not change result type在尝试在 Java / JDBC 应用程序的上下文中解决问题时，我正在为通过谷歌搜索登陆这里的任何人添加这个答案。
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.
当我使用数据库的后端应用程序正在运行时，我能够通过运行模式升级（即 DDL 语句）可靠地重现错误。如果应用程序正在查询因架构升级而更改的表（即应用程序在升级前后对已更改的表运行查询） - postgres 驱动程序将返回此错误，因为显然它确实缓存了一些架构详细信息。
You can avoid the problem by configuring your pgjdbcdriver 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.
您可以通过pgjdbc使用autosave=conservative. 使用此选项，驱动程序将能够刷新它正在缓存的任何详细信息，并且您不必重新启动服务器或刷新连接池或您可能想出的任何解决方法。
Reproduced on Postgres 9.6 (AWS RDS) and my initial testing seems to indicate the problem is completely resolved with this option.
在 Postgres 9.6 (AWS RDS) 上重现，我的初步测试似乎表明此选项已完全解决问题。
Documentation: https://jdbc.postgresql.org/documentation/head/connect.html#connection-parameters
文档：https: //jdbc.postgresql.org/documentation/head/connect.html#connection-parameters
You can look at the pgjdbcGithub issue 451for more details and history of the issue.
您可以查看pgjdbcGithub 问题 451以了解该问题的更多详细信息和历史记录。
JRuby ActiveRecords users see this: https://github.com/jruby/activerecord-jdbc-adapter/blob/master/lib/arjdbc/postgresql/connection_methods.rb#L60
JRuby ActiveRecords 用户看到这个：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 10instance, enabling the conservativesetting does result in extra CPU usage on the database server. It wasn't much though, I could only even see the autosavefunctionality 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.
在对运行在 AWS RDSPostgres 10实例上的我自己的应用程序进行性能测试时，启用该conservative设置确实会导致数据库服务器上的 CPU 使用率增加。但这并不多，我什至只能autosave在我调整了负载测试使用的每个查询并开始努力推动负载测试后，才看到功能显示为使用可测量的 CPU 数量。
回答by irscomp
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=0parameter inside JDBC parameter disables statement caching at database level. This solved it for us.
在 JDBC 参数中设置prepareThreshold=0参数会禁用数据库级别的语句缓存。这为我们解决了它。
