Subject: cached plan must not change result type

From: James Pang <[email-masked]>
Date: Fri, 29 Mar 2024 10:05:59 +0800

hi,
   PGV14, client use PGJDBC driver and prepared statements, when a table
"alter table alter column type ..." changes done, a lot of JDBC clients got
this error "cached plan must not change result type".  when a JDBC client
got this error first time after the DDL schema change, JDBC driver will
clear this caching-plan and following same SQL statement got parsing again
and new cached plan right?
  How to avoid this issue when DDL schema changes ?

Thanks,

James

---

From: Laurenz Albe <[email-masked]>
Date: Fri, 29 Mar 2024 09:40:14 +0100

On Fri, 2024-03-29 at 10:05 +0800, James Pang wrote:
> "alter table alter column type ..." changes done, a lot of JDBC clients
> got this error "cached plan must not change result type".  when a JDBC
> client got this error first time after the DDL schema change, JDBC driver
> will clear this caching-plan and following same SQL statement got parsing
> again and new cached plan right?

Not sure; what do you observe?
Anyway, this is a known problem:
https://jdbc.postgresql.org/documentation/server-prepare/#ddl

> How to avoid this issue when DDL schema changes ?

Don't use server-side prepared statements:
https://jdbc.postgresql.org/documentation/server-prepare/#deactivation
You'll lose some performance though.

Questions like this had better be asked on the pgsql-jdbc list.

Yours,
Laurenz Albe

---

From: James Pang <[email-masked]>
Date: Fri, 29 Mar 2024 16:56:31 +0800

Thanks Laurenz,  forwarded to pgjdbc,  we want to understand why JDBC
failed to reexecute the SQL instead of throw error out. Like this document
https://jdbc.postgresql.org/documentation/server-prepare/#re-execution-of-failed-statements
.

Thanks,

James

---

From: James Pang <[email-masked]>
Date: Fri, 29 Mar 2024 17:08:44 +0800

forwarded to pgjdbc,  we want to understand why JDBC failed to reexecute
the SQL instead of throw error out. Like this document
https://jdbc.postgresql.org/documentation/server-prepare/#re-execution-of-failed-statements
 .

protected final void execute(CachedQuery cachedQuery,
      @Nullable ParameterList queryParameters, int flags)
      throws SQLException {
    try {
      executeInternal(cachedQuery, queryParameters, flags);
    } catch (SQLException e) {
      // Don't retry composite queries as it might get partially executed
      if (cachedQuery.query.getSubqueries() != null
         <<< no idea how this  cachedQuery.query.getSubqueries() != null
          || !connection.getQueryExecutor().willHealOnRetry(e)) {
        throw e;
      }
      cachedQuery.query.close();
      // Execute the query one more time
      executeInternal(cachedQuery, queryParameters, flags);
    }
  }

cachedQuery.query.getSubqueries() != null, how this code decide composite
queries  here ?   that mean some query having subquery or having many JOIN
or LEFT JOINs like  select .... A left join B ...

Thanks,

James

---

From: Dave Cramer <[email-masked]>
Date: Fri, 29 Mar 2024 11:45:09 -0400

> or LEFT JOINs like  select .... A left join B ...

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. Currently the driver has no idea that
you changed the table and the prepared statement will fail so it just
continues to use it.

Dave

---

From: Tom Lane <[email-masked]>
Date: Fri, 29 Mar 2024 12:21:06 -0400

Dave Cramer <[email-masked]> writes:
> 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.  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.

			regards, tom lane

---

From: Dave Cramer <[email-masked]>
Date: Fri, 29 Mar 2024 12:48:26 -0400

On Fri, 29 Mar 2024 at 12:21, Tom Lane <[email-masked]> wrote:

> 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

> transaction, and that's a concept that doesn't exist in the

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

---

From: James Pang <[email-masked]>
Date: Sat, 30 Mar 2024 07:42:26 +0800

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
  }

Thanks,

James

Dave Cramer <[email-masked]> 於 2024年3月30日週六 上午12:48寫道：

> I agree, but it's a known issue. I'm just pointing that it would be nice
> to have.
> 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
>

---

From: Dave Cramer <[email-masked]>
Date: Sat, 30 Mar 2024 06:14:09 -0400

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

---

From: Dave Cramer <[email-masked]>
Date: Sat, 30 Mar 2024 08:27:07 -0400

> https://github.com/pgjdbc/pgjdbc/blob/0fbd31d43b1013829da3a8f21120d8bb94df803d/pgjdbc/src/main/java/org/postgresql/core/QueryExecutorBase.java#L442
I think the best option for you is to turn off server side prepared
statements with prepareThreshold=0

 Dave

---

From: Laurenz Albe <[email-masked]>
Date: Sat, 30 Mar 2024 15:06:12 +0100

On Sat, 2024-03-30 at 08:27 -0400, Dave Cramer 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?
> I think the best option for you is to turn off server side prepared statements with prepareThreshold=0

An alternative option is to take downtime for DDL and restart the application.
Or to chppse the appropriate data type right away: in your case, that would
have been "text".

Yours,
Laurenz Albe

---

From: James Pang <[email-masked]>
Date: Sun, 31 Mar 2024 11:30:35 +0800

1) turn off server side prepared statements with prepareThreshold=0
       with this threshold=0, if it's still possible to avoid parsing,
planning from Postgresql server side, like set
plan_cache_mode=force_generic_plan or with default "auto" mode.  Postgresql
server can make the plan cached and reused.

2) change varchar(n) to varchar or text, then restart application, so avoid
future similar increase varchar(n) changes.
      it's safe to performance or optimizer ,right?

Thanks,

James

Laurenz Albe <[email-masked]> 於 2024年3月30日週六 下午10:06寫道：

> varchar(1024)",
> > > > From this
> ,

---

From: Dave Cramer <[email-masked]>
Date: Sun, 31 Mar 2024 06:05:48 -0400

> server can make the plan cached and reused.

Unfortunately no, every plan will have to be parsed and planned. With this
setting we use the unnamed statement which is replanned for every use.

>
> 2) change varchar(n) to varchar or text, then restart application, so
> avoid future similar increase varchar(n) changes.
Yes, this is very safe, and in fact personally, I would never use
varchar(n) if you want to enforce the length use a constraint.

Dave