MIME-Version: 1.0
References: 
 <CAHgTRfcg2jGnjSqq1NVuMDtCjRU3aOXWrhXn1L=92Ka_Ug6euw@mail.gmail.com>
 <60ab210d02748b5da813007ab2504606d0fecaa1.camel@cybertec.at>
 <CAHgTRfe+REHqYoQJ_ykj_p416PbdHz+UN06-R3PVr6auMx4w2g@mail.gmail.com>
 <CAHgTRfeXGN9d7vgg8LtwpNYw_+f-zmMYsOc1U9rhXkGcwWtNng@mail.gmail.com>
In-Reply-To: 
 <CAHgTRfeXGN9d7vgg8LtwpNYw_+f-zmMYsOc1U9rhXkGcwWtNng@mail.gmail.com>
From: Dave Cramer <davecramer@postgres.rocks>
Date: Fri, 29 Mar 2024 11:45:09 -0400
Message-ID: 
 <CADK3HHLkuDpfVKfzckH5z-e1_Z8DH-9WdOWaafbJ4HRuRqnsdg@mail.gmail.com>
Subject: Re: cached plan must not change result type
To: James Pang <jamespang886@gmail.com>
Cc: pgsql-jdbc@lists.postgresql.org, Laurenz Albe <laurenz.albe@cybertec.at>
Content-Type: multipart/alternative; boundary="00000000000049ee6a0614ce8746"
Archived-At: 
 <https://www.postgresql.org/message-id/CADK3HHLkuDpfVKfzckH5z-e1_Z8DH-9WdOWaafbJ4HRuRqnsdg%40mail.gmail.com>
Precedence: bulk

--00000000000049ee6a0614ce8746
Content-Type: text/plain; charset="UTF-8"

On Fri, 29 Mar 2024 at 05:09, James Pang <jamespang886@gmail.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

--00000000000049ee6a0614ce8746
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr"><br></div><br><div class=3D"gmail_quote">=
<div dir=3D"ltr" class=3D"gmail_attr">On Fri, 29 Mar 2024 at 05:09, James P=
ang &lt;<a href=3D"mailto:jamespang886@gmail.com">jamespang886@gmail.com</a=
>&gt; wrote:<br></div><blockquote class=3D"gmail_quote" style=3D"margin:0px=
 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><di=
v dir=3D"ltr"><div><p style=3D"margin:0in;font-size:11pt;font-family:Calibr=
i,sans-serif">=C2=A0forwarded to pgjdbc,=C2=A0 we want to understand why JD=
BC failed to reexecute the SQL instead of throw error out. Like this docume=
nt=C2=A0<a href=3D"https://jdbc.postgresql.org/documentation/server-prepare=
/#re-execution-of-failed-statements" style=3D"color:rgb(5,99,193)" target=
=3D"_blank">https://jdbc.postgresql.org/documentation/server-prepare/#re-ex=
ecution-of-failed-statements</a>=C2=A0.</p><p style=3D"margin:0in;font-size=
:11pt;font-family:Calibri,sans-serif">=C2=A0</p></div><div><br></div>protec=
ted final void execute(CachedQuery cachedQuery,<br>=C2=A0 =C2=A0 =C2=A0 @Nu=
llable ParameterList queryParameters, int flags)<br>=C2=A0 =C2=A0 =C2=A0 th=
rows SQLException {<br>=C2=A0 =C2=A0 try {<br>=C2=A0 =C2=A0 =C2=A0 executeI=
nternal(cachedQuery, queryParameters, flags);<br>=C2=A0 =C2=A0 } catch (SQL=
Exception e) {<br>=C2=A0 =C2=A0 =C2=A0 // Don&#39;t retry composite queries=
 as it might get partially executed<br>=C2=A0 =C2=A0 =C2=A0 if (cachedQuery=
.query.getSubqueries() !=3D null=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0&lt;&lt;&lt; no idea how this=C2=A0

cachedQuery.query.getSubqueries() !=3D null<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 || !connection.getQueryExecutor().willHealOnRetry(e)) {<br>=C2=A0 =
=C2=A0 =C2=A0 =C2=A0 throw e;<br>=C2=A0 =C2=A0 =C2=A0 }<br>=C2=A0 =C2=A0 =
=C2=A0 cachedQuery.query.close();<br>=C2=A0 =C2=A0 =C2=A0 // Execute the qu=
ery one more time<br>=C2=A0 =C2=A0 =C2=A0 executeInternal(cachedQuery, quer=
yParameters, flags);<br>=C2=A0 =C2=A0 }<br>=C2=A0 }<br><div><br></div><div>=
cachedQuery.query.getSubqueries() !=3D null, how this code decide composite=
 queries=C2=A0 here ?=C2=A0 =C2=A0that mean some query having subquery or h=
aving many JOIN or LEFT JOINs like=C2=A0 select .... A left join B ...<br><=
/div><div><br></div><div>Thanks,</div><div><br></div><div>James=C2=A0</div>=
</div></blockquote><div><br></div><div>This is really an issue that needs t=
o be solved in the backend. The error is coming from PostgreSQL and what sh=
ould 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 pre=
pared statements that rely on are now invalid and we can reprepare them. Cu=
rrently the driver has no idea that you changed the table and the prepared =
statement will fail so it just continues to use it.</div><div><br></div><di=
v>Dave</div></div></div>

--00000000000049ee6a0614ce8746--