Re: cached plan must not change result type

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,
>

>
> James
>
> Dave Cramer <davecramer(at)postgres(dot)rocks> 於 2024年3月30日週六 上午12:48寫道：
>
>>
>>
>> On Fri, 29 Mar 2024 at 12:21, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>
>>> Dave Cramer <davecramer(at)postgres(dot)rocks> writes:
>>> > This is really an issue that needs to be solved in the backend. The
>>> error
>>> > is coming from PostgreSQL and what should happen is that when you
>>> alter a
>>> > table that a server prepared statement relies on the backend should
>>> send a
>>> > message to tell us that all of the prepared statements that rely on
>>> are now
>>> > invalid and we can reprepare them.
>>>
>>> This is something that can't change without a wire protocol change.
>>> There is nothing in the protocol that allows the backend to send out
>>> a message like "oh, that Describe I sent you awhile back? It might
>>> be a lie now" at random times.
>>
>>
>> I agree, but it's a known issue. I'm just pointing that it would be nice
>> to have.
>> We'd have to figure out the details.
>>
>>
>>> Also, what do you want to do about
>>> race conditions --- that is, what if you fire off an Execute only
>>> to find that one of those messages was already in flight to you?
>>
>>
>>> A non-racy way to handle it might be for Bind/Execute to refuse to
>>> run the query if its output has changed since the last Describe,
>>> which we could check after acquiring table locks during Bind.
>>> But we'd want to define "refuse" in a way that doesn't abort the
>>> transaction, and that's a concept that doesn't exist in the
>>> protocol at all.
>>>
>>
>> This actually sounds like the best option as we wouldn't have to fire off
>> a message, just refuse to run the Execute in a way that doesn't abort the
>> transaction.
>>
>> Dave
>>
>