【转】PostgreSQL 修改字段类型程序 一会报错，一会正常，闹鬼了？

原作者： Austindatabases

在使用PostgreSQL的过程中，修改varchar到text字段我们并未遇到什么问题，从PG本身的原理上，众人认为也不会产生问题，从PG的原理上varchar（N）修改成text这二者都是长字符串类型，本质上存储结构是一致的，主要的差别在长度的约束。

所以在大部分情况，我们任务PostgreSQL在修改varchar 到 text的情况下是安全的，也是PostgreSQL中最安全的DDL修改之一。

但是最近一次故障处理，发现这是有问题的，在修改了测试数据库的某张表的字段从varchar到text后，数据库的访问变得不正常，而这个不正常中，并不是所有的应用模块访问这个表的时候，会报错，而是一会报错，一会不报错。

- [0][17470807300237440] 未知异常
org.springframework.jdbc.UncategorizedSQLException: 
### Error querying database.  Cause: org.postgresql.util.PSQLException: ERROR: cached plan must not change result type
### The error may exist in file [/usr/locaebapps/s1saasg/WEB-INF/classes/cartyPushMapper.xml]
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: SELECT          _papush          INNER JOIN sps ON tps.sysh_id = t.ID          WHERE ts.id=?          order by t.id asc
### 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: cached plan must not change result type
 at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:84)
 at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)

开发在提供错误，后我们开始分析这里有几个点

1 为什么修改前没有报错

2 为什么修改后有报错，但是并不是所有的访问都报错，有的不报错，有的报错

3 报错中提到了 cached plan must not cahnge result type

大部分DBA对于PG的数据库还是很熟悉的，可只要提到PG的 JDBC，也就是JAVA 访问PG的数据库的接口部分，熟悉得人就少多了。

这里我们需要提到 PostgreSQL JDBC 驱动和数据库接到SQL和返回数据的一个过程。

1 Parse (解析)：

这个阶段，PostgreSQL 数据库会接收一条带有参数占位符的 SQL 语句（比如 SELECT * FROM users WHERE id = ?）。

数据库会为这条 SQL 语句生成一个通用的执行计划。这个计划是固定的，并且会缓存起来。

在这个过程中，数据库已经确定了这条 SQL 语句会返回哪些列，以及这些列的数据类型是什么。这些信息也会被固定在缓存的执行计划里。

2 Bind (绑定)：

当程序实际执行 SQL 时，会将具体的参数值（比如 id = 1）绑定到之前解析好的执行计划中的占位符上。

3 Execute (执行)：

数据库使用绑定的参数值，执行缓存好的执行计划，并返回结果。

这里我们需要注意，在如插入数据的情况下，使用了这个从varchar 到 text的字段，链接分为两种

1 一些链接是之前存在的老的执行计划缓存在老的链接中存在

2 一些是数据插入中产生的新链接，那么新的链接就会产生新的执行计划的缓存。

这里我们强调，缓存的执行不允许改变返回结果的列和类型，那么这里报错的必然是缓存计划没有更新的老的链接，因为数据类型已经不匹配了。

这里我也查询了一下，关于JDBC的参数

其中在 JDBC URL 中加入 prepareThreshold=0 参数。这会告诉驱动不要使用预编译的执行计划，而是每次都直接发送 SQL 文本到数据库执行。

但这里我们要提醒开发人员，如果你在链接中添加了prepareThreshold=0 将会导致所有的SQL都没有执行计划缓存，每个SQL执行都需要进行编译，那么会影响在高并发，复杂SQL运行的效率。

jdbc:postgresql://localhost:5432/mydb?prepareThreshold=0

紧急的情况下，我们可以将老的SQL的链接进行terminate，具体的时间根据你修改DDL的时间进行计算，就可以解决问题，程序会报链接失败，后续建立新的链接，问题就解决了。

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE
    state = 'idle'
    AND now() - query_start > INTERVAL '5 minutes'
    AND pid <> pg_backend_pid()
   --  为了避免kill其它会话，这里还可以指定具体的账号或者库名
   --  AND datname='xxxxx' 
   -- AND usename='xxxx'
;

总结：

1 DBA 不应该光关注数据库本身，对于Java，Python ,Go 等语言链接数据库的方式应该进行梳理，把容易出现的问题找到，尤其一些参数的含义和问题。

2 在出现问题后，不要光想是数据库本身的问题，不少问题都是数据库周边，以及程序模块的问题，由于DBA对于这块知识的缺乏，导致架构，开发会将出现的问题一股脑的扣到数据库本身。

最后DBA应该提高自己知识，应对复杂的工作环境和各种看上去怪异的问题，但捋清逻辑后，就可以找到根本原因的能力。