Re: cached plan must not change result type

On Sat, 30 Mar 2024 at 06:14, Dave Cramer <davecramer(at)postgres(dot)rocks> wrote:

>
>
> On Fri, 29 Mar 2024 at 19:42, James Pang <jamespang886(at)gmail(dot)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,
>>
>
>
>
>>
>>
I think the best option for you is to turn off server side prepared
statements with prepareThreshold=0

Dave