Re: cached plan must not change result type

On Fri, 29 Mar 2024 at 05:09, James Pang <jamespang886(at)gmail(dot)com> wrote:

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

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