Cause: org.postgresql.util.PSQLException: ERROR: cached plan must not change result type的前因后果

时间:2022-08-11 23:04:48

首先说明一下遇到的问题:
PG数据库,对其中的某张表增加一列后,应用报错,信息如下:
应用使用相关框架如下:SpringBoot、MyBatis。

### Cause: org.postgresql.util.PSQLException: ERROR: cached plan must not change result type
; uncategorized SQLException for SQL []; SQL state [0A000]; error code [0]; ERROR: cached plan must not change result type; nested exception is org.postgresql.util.PSQLException: ERROR: cach
ed plan must not change result type] with root cause
org.postgresql.util.PSQLException: ERROR: cached plan must not change result type
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2198) ~[postgresql-9.3-1103-jdbc4.jar:?]
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1927) ~[postgresql-9.3-1103-jdbc4.jar:?]
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255) ~[postgresql-9.3-1103-jdbc4.jar:?]
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:562) ~[postgresql-9.3-1103-jdbc4.jar:?]
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:420) ~[postgresql-9.3-1103-jdbc4.jar:?]
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:413) ~[postgresql-9.3-1103-jdbc4.jar:?]
at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:2931) ~[druid-1.0.25.jar:1.0.25]
at com.alibaba.druid.filter.FilterEventAdapter.preparedStatement_execute(FilterEventAdapter.java:440) ~[druid-1.0.25.jar:1.0.25]
at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:2929) ~[druid-1.0.25.jar:1.0.25]
at com.alibaba.druid.proxy.jdbc.PreparedStatementProxyImpl.execute(PreparedStatementProxyImpl.java:131) ~[druid-1.0.25.jar:1.0.25]
at com.alibaba.druid.pool.DruidPooledPreparedStatement.execute(DruidPooledPreparedStatement.java:493) ~[druid-1.0.25.jar:1.0.25]
at org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:63) ~[mybatis-3.4.0.jar:3.4.0]
at org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:79) ~[mybatis-3.4.0.jar:3.4.0]
at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:63) ~[mybatis-3.4.0.jar:3.4.0]
at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:325) ~[mybatis-3.4.0.jar:3.4.0]
at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:156) ~[mybatis-3.4.0.jar:3.4.0]
at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:109) ~[mybatis-3.4.0.jar:3.4.0]
at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:83) ~[mybatis-3.4.0.jar:3.4.0]
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:148) ~[mybatis-3.4.0.jar:3.4.0]
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:141) ~[mybatis-3.4.0.jar:3.4.0]
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectOne(DefaultSqlSession.java:77) ~[mybatis-3.4.0.jar:3.4.0]
at sun.reflect.GeneratedMethodAccessor106.invoke(Unknown Source) ~[?:?]
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:1.8.0_121]
at java.lang.reflect.Method.invoke(Method.java:498) ~[?:1.8.0_121]

查找原因:cached plan must not change result type是与数据库的DDL操作有关,并且在使用了prepare语句情况下会发生。
参考链接:http://blog.csdn.net/sea3389/article/details/38657345

增加表的字段确实是DDL操作,但是我的sql中并没有使用prepare语句。
继续查找原因:使用语法#{}会导致MyBatis创建预处理语句属性
参考链接:http://blog.csdn.net/huazb4815/article/details/30289297

解决办法:

重启应用

一些参考:
https://*.com/questions/34180932/error-cached-plan-must-not-change-result-type-when-mixing-ddl-with-select-via
https://*.com/questions/2783813/postgres-8-3-error-cached-plan-must-not-change-result-type
http://www.cnblogs.com/chenmh/articles/5308085.html
https://baike.baidu.com/item/DDL/21997?fr=aladdin