admin管理员组文章数量:1658727
os: ubuntu 16.04
db: postgresql 9.6.8
postgresql 日志文件出现如下报错信息:
"BIND",ERROR,0A000,"cached plan must not change result type",,,,,,"select
c0,c1
from ""public"".tmp_t0
WHERE ( c0 = $1 )",,,""
略去了无关的信息,sql 也做了模拟替换.
查看日志后发现是前面有进行 alter table alter column 操作,从而引发了再次执行时使用了 prepare .
演示下
session 1
postgres=# create table tmp_t0(c0 varchar(10));
CREATE TABLE
postgres=# insert into tmp_t0(c0)select '1';
INSERT 0 1
postgres=# select * from tmp_t0;
c0
----
1
(1 row)
postgres=# deallocate prepare select_tmp_t0_plan;
ERROR: prepared statement "select_tmp_t0_plan" does not exist
postgres=#
postgres=# prepare select_tmp_t0_plan as select * from tmp_t0 where c0=$1;
PREPARE
postgres=#
postgres=# select * from pg_prepared_statements;
name | statement | prepare_time | parameter_types | from_sql
--------------------+-----------------------------------------------------------------+------------------------------+-----------------+----------
select_tmp_t0_plan | prepare select_tmp_t0_plan as select * from tmp_t0 where c0=$1; | 2019-04-18 15:26:27.90271+08 | {text} | t
(1 row)
postgres=# execute select_tmp_t0_plan('1');
c0
----
1
(1 row)
postgres=#
session 2
postgres=# alter table public.tmp_t0 alter column c0 type varchar(100);
ALTER TABLE
session 1
postgres=# execute select_tmp_t0_plan('1');
ERROR: cached plan must not change result type
确实是这样啊!
解决办法
- discard all 后，重新 prepare
- configuring your pgjdbc driver with autosave=conservative
参考:
https://github/pgjdbc/pgjdbc/issues/496
https://github/pgjdbc/pgjdbc/pull/451
https://stackoverflow/questions/2783813/postgres-error-cached-plan-must-not-change-result-type
http://stackoverflow/questions/34180932/error-cached-plan-must-not-change-result-type-when-mixing-ddl-with-select-via
https://jdbc.postgresql/documentation/head/connect.html#connection-parameters
https://segmentfault/a/1190000012444511
https://jdbc.postgresql/documentation/head/connect.html#connection-parameters
https://github/digoal/blog/blob/master/201904/20190417_01.md
本文标签： 报错quotpostgresqlErrorresult
版权声明：本文标题：postgresql 报错 ERROR,0A000,"cached plan must not change result type" 内容由热心网友自发贡献，该文观点仅代表作者本人， 转载请联系作者并注明出处：https://m.elefans.com/xitong/1729813963a1213676.html， 本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容，一经查实，本站将立刻删除。
发表评论