postgres修改字段错误：cached plan must not change result type

最新推荐文章于 2024-09-16 07:36:26 发布

sea3389

最新推荐文章于 2024-09-16 07:36:26 发布

阅读量9.4k

点赞数

分类专栏：数据库文章标签：数据库 postgres postgresql

数据库专栏收录该内容

1 篇文章 0 订阅

订阅专栏

postgres数据库某张表修改了一个字段的长度之后，程序出现这个错误：

org.postgresql.util.PSQLException: ERROR: cached plan must not change result type
	org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2161)
	org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1890)
	org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)
	org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:547)
	org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:417)
	org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:410)
	com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.execute(NewProxyPreparedStatement.java:989)
	com.ibatis.sqlmap.engine.execution.SqlExecutor.executeQuery(SqlExecutor.java:185)
	com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.sqlExecuteQuery(MappedStatement.java:221)
	com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.executeQueryWithCallback(MappedStatement.java:189)
	com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.executeQueryForList(MappedStatement.java:139)
	com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForList(SqlMapExecutorDelegate.java:567)
	com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForList(SqlMapExecutorDelegate.java:541)
	com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.queryForList(SqlMapSessionImpl.java:118)
	org.springframework.orm.ibatis.SqlMapClientTemplate$3.doInSqlMapClient(SqlMapClientTemplate.java:298)
	org.springframework.orm.ibatis.SqlMapClientTemplate$3.doInSqlMapClient(SqlMapClientTemplate.java:296)

网上查询解答原因：

--数据库错误日志

2013-07-24 15:11:35.895 CST,"db_test","db_test",17526,"192.168.100.221:40188",51ed7b10.4476,1555,"BIND",2013-07-23 02:33:52 CST,236/820620,0,ERROR,0A000,"cached plan must not change result type",,,,,,"SELECT pkg_name FROM tbl_test WHERE channel_no = $1 AND store_id = $2",,"RevalidateCachedPlan, plancache.c:589",""

备注：网上查了下资料，这个错误与 DDL 操作有关，并且在使用了 PREPARE 语句的情况下会发生，

接下来模拟下这个错误。

一模拟异常

--1.1 创建测试表并插入数据

francs=> create table test_cache (id int4, name character varying(32));

CREATE TABLE

francs=> insert into test_cache values (1,'a'),(2,'b'),(3,'c');

INSERT 0 3

francs=> select * from test_cache ;

id | name

----+------

1 | a

2 | b

3 | c

(3 rows)

备注：创建完表后，接着开启两个会话，顺序按以下操作进行。

--1.2 会话1: 创建 PREPARE SQL

francs=> PREPARE select_1 (character varying) AS

francs-> select * From test_cache where name=$1;

PREPARE

francs=> EXECUTE select_1('a');

id | name

----+------

1 | a

(1 row)

备注：正常使用 EXECUTE 语句。

--1.3 会话2: DDL 更改表结构

francs=> \d test_cache

Table "francs.test_cache"

Column | Type | Modifiers

--------+-----------------------+-----------

id | integer |

name | character varying(32) |

francs=> alter table test_cache alter column name type character varying;

ALTER TABLE

备注：这里更改字段长度。

--1.4 再次回到会话1:

francs=> EXECUTE select_1('a');

ERROR: cached plan must not change result type

--1.5 数据库日志如下：

2013-07-24 15:19:36.139 CST,"francs","francs",23261,"[local]",51ef7f33.5add,1,"EXECUTE",2013-07-24 15:16:03 CST,3/10083,0,ERROR,0A000,"cached plan must not change result type",,,,,,"EXECUTE select_1('a');",,,"psql"

备注：正好重现了标题出现的错误。

二解决方法

由于 PREPARE 语句在会话结束后会自动消失，因此解决方式有多种，可以断开会话，重启

应用程序，也可以使用 DEALLOCATE 命令取消 PREPARE 语句，然后重新生成 PREPARE 语句，

今天出现的异常后来通知项目组重启这个模块后，问题解决。

--2.1 DEALLOCATE

francs=> DEALLOCATE select_1;

DEALLOCATE

francs=> PREPARE select_1 (character varying) AS

francs-> select * From test_cache where name=$1;

PREPARE

francs=> EXECUTE select_1('a');