修改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緩存的連接配接都抛出一次錯誤後會自動恢複)