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

原作者：何放

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

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语句只对本身单个会话生效，如果是应用程序批量报错，这个方法显然不合适。

三、总结

1. 模拟使用PREPARE语句，对表进行DDL修改字段长度，增加列字段，删除列字段，都会造成此报错。
2. 数据库维护过程中，对于DDL操作需要格外小心，一般在表执行DDL后，应用会升级重启，这时问题不大，如果应用程序不涉及升级重启，之后就会遇到之前的错，暂时还没有发现在数据库端有很好的规避这个错误的方法。