openGauss/MogDB遇到报错：cached plan must not change result type

原创何放 2022-01-11

3066

客户跑业务时遇到报错，查看日志是关于数据库的报错，报错如下

org.postgresql.util.PSQLException: ERROR: cached plan must not change result type
	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2794)
	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2533)
	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:314)
	at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:453)
	at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:377)
	at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:146)
	at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:109)
复制

再从数据库日志中查看信息

[BACKEND] ERROR:  cached plan must not change result type
[BACKEND] STATEMENT:  SELECT * FROM xxxx WHERE op_id='1' AND action_id = 'Login' ORDER BY op_time DESC LIMIT 0,1
复制

网上查找原因，此错误的发生和表的DDL操作有关，而且在表使用了PREPARE语句情况下会发生。此场景也能复现出来。

一、模拟报错

1.1.1 创建基础数据test_cache

postgres=# create table test_cache(id int, name varchar);
CREATE TABLE
postgres=# insert into test_cache values (1,'a'),(2,'b'),(3,'c');
INSERT 0 3
postgres=# select * from test_cache ;
 id | name 
----+------
  1 | a
  2 | b
  3 | c
(3 rows)
复制

备注：创建基表test_cache

1.1.2 创建PREPARE SQL

postgres=# PREPARE select_1 (varchar) AS select * from test_cache where name=$1; 
PREPARE
postgres=# EXECUTE  select_1('a');
 id | name 
----+------
  1 | a
(1 row)
复制

备注：创建的PREPARE SQL能正常使用EXECUTE

1.1.3 DDL更改表结构（字段长度）

postgres=# \d test_cache 
       Table "public.test_cache"
 Column |       Type        | Modifiers 
--------+-------------------+-----------
 id     | integer           | 
 name   | character varying | 

postgres=# alter table test_cache alter column name type character varying(10);
ALTER TABLE
postgres=# \d test_cache 
         Table "public.test_cache"
 Column |         Type          | Modifiers 
--------+-----------------------+-----------
 id     | integer               | 
 name   | character varying(10) | 
复制

备注：此时修改name字段长度为character varying(10)

1.1.4 使用EXECUTE查询表

postgres=# EXECUTE select_1('a');
ERROR:  cached plan must not change result type
复制

备注：EXECUTE执行报错

1.1.5 查看数据库日志

[BACKEND] ERROR:  cached plan must not change result type
[BACKEND] STATEMENT:  EXECUTE  select_1('a');
复制

备注：能够重现标题错误

1.2.1 创建基础数据test_cache2

postgres=# create table test_cache2(id int, name varchar);
CREATE TABLE
postgres=# insert into test_cache2 values (1,'a'),(2,'b'),(3,'c');
INSERT 0 3
postgres=# select * from test_cache2 ;
 id | name 
----+------
  1 | a
  2 | b
  3 | c
(3 rows)
复制

备注：创建基表test_cache2

1.2.2 创建PREPARE SQL


postgres=# PREPARE select_2 (varchar) AS select * from test_cache2 where name=$1;
PREPARE
postgres=# EXECUTE  select_2('a');
 id | name 
----+------
  1 | a
(1 row)
复制

备注：创建的PREPARE SQL能正常使用EXECUTE

1.2.3 DDL更改表结构（增加一列）

postgres=# \d test_cache2
       Table "public.test_cache2"
 Column |       Type        | Modifiers 
--------+-------------------+-----------
 id     | integer           | 
 name   | character varying | 

postgres=# alter table test_cache2 add column city character varying(20);
ALTER TABLE
postgres=# \d test_cache2
         Table "public.test_cache2"
 Column |         Type          | Modifiers 
--------+-----------------------+-----------
 id     | integer               | 
 name   | character varying     | 
 city   | character varying(20) | 
复制

备注：此时增加表结构city列

1.2.4 使用EXECUTE查询表

postgres=# EXECUTE  select_2('a');
ERROR:  cached plan must not change result type
复制

备注：EXECUTE执行报错

1.2.5 查看数据库日志

[BACKEND] ERROR:  cached plan must not change result type
[BACKEND] STATEMENT:  EXECUTE  select_2('a');
复制

备注：能够重现标题错误

另外，删除列也能触发此报错

二、解决办法

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

postgres=# EXECUTE  select_2('a');
ERROR:  cached plan must not change result type
postgres=# \q
[omm@node1 ~]$ gsql -U omm postgres -r 
gsql ((MogDB 2.0.1 build de239dbd) compiled at 2021-12-20 14:12:16 commit 0 last mr  )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

postgres=# EXECUTE  select_1('a');
ERROR:  prepared statement "select_1" does not exist
postgres=# EXECUTE  select_2('a');
ERROR:  prepared statement "select_2" does not exist
复制

2.使用DEALLOCATE命令取消PREPARE语句，然后重新生成PREPARE语句

postgres=# DEALLOCATE select_1;
DEALLOCATE
postgres=# PREPARE select_1 (character varying) AS select * From test_cache where name=$1;
PREPARE
postgres=# EXECUTE  select_1('a');
 id | name 
----+------
  1 | a
(1 row)
复制

备注：DEALLOCATE语句只对本身单个会话生效，如果是应用程序批量报错，这个方法显然不合适。