修改PostgreSQL字段长度导致cached plan must not change result type错误

问题

有业务反馈在修改一个表字段长度后，Java应用不停的报下面的错误，但是越往后错误越少，过了15分钟错误就没有再发生。

### Error querying database.  Cause: org.postgresql.util.PSQLException: ERROR: cached plan must not change result type 

原因

调查判断原因是修改字段长度导致执行计划缓存失效，继续使用之前的预编译语句执行会失败。

很多人遇到过类似错误，比如：

• https://blog.csdn.net/qq_27791709/article/details/81198571

但是，有两个疑问没有解释清楚。

1. 以前业务也改过字段长度，但为什么没有触发这个错误?
2. 这个错误能否自愈?

下面是进一步的分析

PostgreSQL中抛出此异常的代码如下:

						


							


								

									static List * 
								

								

									RevalidateCachedQuery(CachedPlanSource *plansource,
								

								

									                      QueryEnvironment *queryEnv)
								

								

									{
								

								

									        if (plansource->fixed_result)
								

								

									            ereport(ERROR,
								

								

									                    (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
								

								

									                     errmsg("cached plan must not change result type")));
								

								

									...
								

								

									}
								

							

						

pgjdbc代码里有对该异常的判断，发生异常后，后续的执行会重新预编译，不会继续使用已经失效的预编译语句。这说明pgjdbc对这个错误有容错或自愈能力。

							


								


									

										protected boolean willHealViaReparse(SQLException e) { 
									

									

										...
									

									

										    // "cached plan must not change result type"
									

									

										    String routine = pe.getServerErrorMessage().getRoutine();
									

									

										    return "RevalidateCachedQuery".equals(routine) // 9.2+
									

									

										        || "RevalidateCachedPlan".equals(routine); // <= 9.1
									

									

										  }
									

								

							

发生条件

经验证，使用Java应用时本故障的发生条件如下:

1. 使用非自动提交模式
2. 使用prepareStatement执行相同SQL 5次以上
3. 修改表字段长度
4. 表字段长度修改后第一次使用prepareStatement执行相同SQL

测试验证

以下代码模拟Java连接多次出池->执行->入池，中途修改字段长度。可以复现本问题

												


													


														

															Connection conn = DriverManager.getConnection(...); 
														

														

															         conn.setAutoCommit(false); //自动提交模式下，不会出错，pgjdbc内部会处理掉
														

														

															     String sql = "select c1 from tb1 where id=1";
														

														

															         PreparedStatement prest =conn.prepareStatement(sql);
														

														

															    
														

														

															     for(int i=0;i<5;i++)
														

														

															     {
														

														

															             System.out.println("i: " + i);
														

														

															             prest =conn.prepareStatement(sql);
														

														

															             ResultSet rs = prest.executeQuery();
														

														

															             prest.close();
														

														

															             conn.commit();
														

														

															     }
														

														

															    
														

														

															     //在这里设置断点，手动修改字段长度: alter table tb1 alter c1 type varchar(118);
														

														

															    
														

														

															     for(int i=5;i<10;i++)
														

														

															     {
														

														

															             System.out.println("i: " + i);
														

														

															             try {
														

														

															             prest =conn.prepareStatement(sql);
														

														

															             ResultSet rs = prest.executeQuery();
														

														

															             prest.close();
														

														

															             conn.commit();
														

														

															             } catch (SQLException e) {
														

														

															                 System.out.println(e.getMessage());
														

														

															                 conn.rollback();
														

														

															             }
														

														

															     }
														

														

															        conn.close();
														

													

												

测试程序执行结果如下:

											


												


													

														i: 0 
													

													

														i: 1
													

													

														i: 2
													

													

														i: 3
													

													

														i: 4
													

													

														i: 5
													

													

														ERROR: cached plan must not change result type
													

													

														i: 6
													

													

														i: 7
													

													

														i: 8
													

													

														i: 9
													

												

											

回避

1. 在不影响业务逻辑的前提下，尽量使用自动提交模式
2. 修改表字段长度后重启应用，或者在业务发生该SQL错误后重试(等每个Jboss缓存的连接都抛出一次错误后会自动恢复)