Re: cached plan must not change result type

On Fri, 29 Mar 2024 at 19:42, James Pang <jamespang886@gmail.com> wrote:

   we did DDL "alter table ... alter column increase varchar(512) to varchar(1024)", after that done, hours later, new query select on this table still failed this error.  From this https://jdbc.postgresql.org/documentation/server-prepare/#re-execution-of-failed-statements , looks like pgjdbc try to handle this exception and retry, but in our case, it did not happen.  Could you direct me how to make this retry work? we only want new transactions,queries work that after the DDL changes. 

protected boolean willHealViaReparse(SQLException e) {
    if (e == null || e.getSQLState() == null) {
      return false;
    }

    // "prepared statement \"S_2\" does not exist"
    if (PSQLState.INVALID_SQL_STATEMENT_NAME.getState().equals(e.getSQLState())) {
      return true;
    }
    if (!PSQLState.NOT_IMPLEMENTED.getState().equals(e.getSQLState())) {
      return false;
    }

    if (!(e instanceof PSQLException)) {
      return false;
    }

    PSQLException pe = (PSQLException) e;

    ServerErrorMessage serverErrorMessage = pe.getServerErrorMessage();
    if (serverErrorMessage == null) {
      return false;
    }
    // "cached plan must not change result type"
    String routine = serverErrorMessage.getRoutine();
    return "RevalidateCachedQuery".equals(routine) // 9.2+
        || "RevalidateCachedPlan".equals(routine); // <= 9.1
  }

This only works if there was no transaction.

https://github.com/pgjdbc/pgjdbc/blob/0fbd31d43b1013829da3a8f21120d8bb94df803d/pgjdbc/src/main/java/org/postgresql/core/QueryExecutorBase.java#L442 

Dave

Thanks,