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>
 <CADK3HHLkuDpfVKfzckH5z-e1_Z8DH-9WdOWaafbJ4HRuRqnsdg@mail.gmail.com>
 <2950.1711729266@sss.pgh.pa.us>
 <CADK3HHLx1u-RbR_v5UbmK_v4_sOfzHxdYwxtn5QRRWV-=Re6MQ@mail.gmail.com>
In-Reply-To: 
 <CADK3HHLx1u-RbR_v5UbmK_v4_sOfzHxdYwxtn5QRRWV-=Re6MQ@mail.gmail.com>
From: James Pang <jamespang886@gmail.com>
Date: Sat, 30 Mar 2024 07:42:26 +0800
Message-ID: 
 <CAHgTRfeJop+-t8b1v3U85_hhAL2o15R1cHqkVyhxWLj7B4KWLg@mail.gmail.com>
Subject: Re: cached plan must not change result type
To: Dave Cramer <davecramer@postgres.rocks>
Cc: Tom Lane <tgl@sss.pgh.pa.us>, pgsql-jdbc@lists.postgresql.org,
	Laurenz Albe <laurenz.albe@cybertec.at>
Content-Type: multipart/alternative; boundary="000000000000d505210614d531dc"
Archived-At: 
 <https://www.postgresql.org/message-id/CAHgTRfeJop%2B-t8b1v3U85_hhAL2o15R1cHqkVyhxWLj7B4KWLg%40mail.gmail.com>
Precedence: bulk

--000000000000d505210614d531dc
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

   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-f=
ailed-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 =3D=3D null || e.getSQLState() =3D=3D 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 =3D (PSQLException) e;

    ServerErrorMessage serverErrorMessage =3D pe.getServerErrorMessage();
    if (serverErrorMessage =3D=3D null) {
      return false;
    }
    // "cached plan must not change result type"
    String routine =3D serverErrorMessage.getRoutine();
    return "RevalidateCachedQuery".equals(routine) // 9.2+
        || "RevalidateCachedPlan".equals(routine); // <=3D 9.1
  }

Thanks,

James

Dave Cramer <davecramer@postgres.rocks> =E6=96=BC 2024=E5=B9=B43=E6=9C=8830=
=E6=97=A5=E9=80=B1=E5=85=AD =E4=B8=8A=E5=8D=8812:48=E5=AF=AB=E9=81=93=EF=BC=
=9A

>
>
> On Fri, 29 Mar 2024 at 12:21, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
>> Dave Cramer <davecramer@postgres.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 alte=
r
>> 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 ar=
e
>> 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
>

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

<div dir=3D"ltr">=C2=A0 =C2=A0we did DDL &quot;alter table ... alter column=
 increase varchar(512) to varchar(1024)&quot;, after that done, hours later=
, new query select on this table still failed this error.=C2=A0 From this=
=C2=A0<a href=3D"https://jdbc.postgresql.org/documentation/server-prepare/#=
re-execution-of-failed-statements">https://jdbc.postgresql.org/documentatio=
n/server-prepare/#re-execution-of-failed-statements</a> , looks like pgjdbc=
 try to handle this exception and retry, but in our case, it did not happen=
.=C2=A0 Could you direct me how to make this retry work? we only want new t=
ransactions,queries=C2=A0work that after the DDL changes.=C2=A0<div><br><di=
v>protected boolean willHealViaReparse(SQLException e) {<br>=C2=A0 =C2=A0 i=
f (e =3D=3D null || e.getSQLState() =3D=3D null) {<br>=C2=A0 =C2=A0 =C2=A0 =
return false;<br>=C2=A0 =C2=A0 }<br><br>=C2=A0 =C2=A0 // &quot;prepared sta=
tement \&quot;S_2\&quot; does not exist&quot;<br>=C2=A0 =C2=A0 if (PSQLStat=
e.INVALID_SQL_STATEMENT_NAME.getState().equals(e.getSQLState())) {<br>=C2=
=A0 =C2=A0 =C2=A0 return true;<br>=C2=A0 =C2=A0 }<br>=C2=A0 =C2=A0 if (!PSQ=
LState.NOT_IMPLEMENTED.getState().equals(e.getSQLState())) {<br>=C2=A0 =C2=
=A0 =C2=A0 return false;<br>=C2=A0 =C2=A0 }<br><br>=C2=A0 =C2=A0 if (!(e in=
stanceof PSQLException)) {<br>=C2=A0 =C2=A0 =C2=A0 return false;<br>=C2=A0 =
=C2=A0 }<br><br>=C2=A0 =C2=A0 PSQLException pe =3D (PSQLException) e;<br><b=
r>=C2=A0 =C2=A0 ServerErrorMessage serverErrorMessage =3D pe.getServerError=
Message();<br>=C2=A0 =C2=A0 if (serverErrorMessage =3D=3D null) {<br>=C2=A0=
 =C2=A0 =C2=A0 return false;<br>=C2=A0 =C2=A0 }<br>=C2=A0 =C2=A0 // &quot;c=
ached plan must not change result type&quot;<br>=C2=A0 =C2=A0 String routin=
e =3D serverErrorMessage.getRoutine();<br>=C2=A0 =C2=A0 return &quot;Revali=
dateCachedQuery&quot;.equals(routine) // 9.2+<br>=C2=A0 =C2=A0 =C2=A0 =C2=
=A0 || &quot;RevalidateCachedPlan&quot;.equals(routine); // &lt;=3D 9.1<br>=
=C2=A0 }<br></div></div><div><br></div><div>Thanks,</div><div><br></div><di=
v>James</div></div><br><div class=3D"gmail_quote"><div dir=3D"ltr" class=3D=
"gmail_attr">Dave Cramer &lt;davecramer@postgres.rocks&gt; =E6=96=BC 2024=
=E5=B9=B43=E6=9C=8830=E6=97=A5=E9=80=B1=E5=85=AD =E4=B8=8A=E5=8D=8812:48=E5=
=AF=AB=E9=81=93=EF=BC=9A<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"><div dir=3D"ltr"><div dir=3D"ltr"><br></div><br><div class=3D"gm=
ail_quote"><div dir=3D"ltr" class=3D"gmail_attr">On Fri, 29 Mar 2024 at 12:=
21, Tom Lane &lt;<a href=3D"mailto:tgl@sss.pgh.pa.us" target=3D"_blank">tgl=
@sss.pgh.pa.us</a>&gt; wrote:<br></div><blockquote class=3D"gmail_quote" st=
yle=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padd=
ing-left:1ex">Dave Cramer &lt;davecramer@postgres.rocks&gt; writes:<br>
&gt; This is really an issue that needs to be solved in the backend. The er=
ror<br>
&gt; is coming from PostgreSQL and what should happen is that when you alte=
r a<br>
&gt; table that a server prepared statement relies on the backend should se=
nd a<br>
&gt; message to tell us that all of the prepared statements that rely on ar=
e now<br>
&gt; invalid and we can reprepare them.<br>
<br>
This is something that can&#39;t change without a wire protocol change.<br>
There is nothing in the protocol that allows the backend to send out<br>
a message like &quot;oh, that Describe I sent you awhile back?=C2=A0 It mig=
ht<br>
be a lie now&quot; at random times.=C2=A0 </blockquote><div><br></div><div>=
I agree, but it&#39;s a known=C2=A0issue. I&#39;m just pointing that it wou=
ld be nice to have.</div><div>We&#39;d have to figure out the details.</div=
><div>=C2=A0</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">Also, w=
hat do you want to do about<br>
race conditions --- that is, what if you fire off an Execute only<br>
to find that one of those messages was already in flight to you?</blockquot=
e><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;borde=
r-left:1px solid rgb(204,204,204);padding-left:1ex">
<br>
A non-racy way to handle it might be for Bind/Execute to refuse to<br>
run the query if its output has changed since the last Describe,<br>
which we could check after acquiring table locks during Bind.<br>
But we&#39;d want to define &quot;refuse&quot; in a way that doesn&#39;t ab=
ort the<br>
transaction, and that&#39;s a concept that doesn&#39;t exist in the<br>
protocol at all.<br></blockquote><div><br></div><div>This actually sounds l=
ike the best option as we wouldn&#39;t have to fire off a message, just ref=
use to run the Execute in a way that doesn&#39;t abort the transaction.</di=
v><div><br></div><div>Dave</div></div></div>
</blockquote></div>

--000000000000d505210614d531dc--