problem

There is business feedback that Java applications keep reporting the following errors after modifying the length of a table field, but the fewer errors that follow, the fewer errors that will not occur in 15 minutes.

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

Reason

The investigation determined that the execution plan cache was invalidated by modifying the field length and that continuing with the precompiled statement execution would fail.

Many people have encountered similar errors, such as:

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

However, there are two questions that are not clearly explained.

1. Business changed field length before, but why didn't this error trigger?
2. Can this error heal itself?

Here is a further analysis

The code that throws this exception in PostgreSQL is as follows:

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")));
...
}

The exception is judged in the pgjdbc code, and subsequent executions will recompile it after the exception occurs and will not continue to use the precompiled statements that are no longer valid.This indicates that pgjdbc is fault-tolerant or self-healing against this error.

  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
  }

Occurrence Conditions

This failure has been proven to occur with Java applications under the following conditions:

1. Use non-autocommit mode
2. Use prepareStatement to execute the same SQL more than five times
3. Modify table field length
4. Use prepareStatement to execute the same SQL for the first time after the table field length has been modified

Test Validation

The following code simulates a Java connection leaving the pool multiple times - > executing - > entering the pool, modifying the field len gt h midway.Can reproduce this problem

         Connection conn = DriverManager.getConnection(...);   
         conn.setAutoCommit(false); //In autocommit mode, no errors will occur and pgjdbc will be disposed of internally
         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();
         }
         
         //Set a breakpoint here to manually modify the field length: 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(); 

The test program execution results are as follows:

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

avoid

1. Use automatic commit mode whenever possible without affecting business logic
2. Restart the application after modifying the table field length, or try again after the SQL error occurs in the business (it will recover automatically after every Jbox cached connection throws an error)