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>
 <CAHgTRfeJop+-t8b1v3U85_hhAL2o15R1cHqkVyhxWLj7B4KWLg@mail.gmail.com>
 <CADK3HHKj=UEr_veXPs+XgD6=0GLJG6pAUN66Vcx0mxYCeuJVHA@mail.gmail.com>
 <CADK3HHJn79rvd0Si7XtQ8EfsaGK04A4fPhoBDng8A8aJkR8hrQ@mail.gmail.com>
 <29759a95d5194d1a5a468e2585e363a313c7d287.camel@cybertec.at>
In-Reply-To: <29759a95d5194d1a5a468e2585e363a313c7d287.camel@cybertec.at>
From: James Pang <jamespang886@gmail.com>
Date: Sun, 31 Mar 2024 11:30:35 +0800
Message-ID: 
 <CAHgTRfceGtfm9g1dZgZo0P1ixp9+sS98MXP6daeNLy7ypFvUqw@mail.gmail.com>
Subject: Re: cached plan must not change result type
To: Laurenz Albe <laurenz.albe@cybertec.at>
Cc: Dave Cramer <davecramer@postgres.rocks>, Tom Lane <tgl@sss.pgh.pa.us>,
	pgsql-jdbc@lists.postgresql.org
Content-Type: multipart/alternative; boundary="000000000000a08a1e0614ec7f6c"
Archived-At: 
 <https://www.postgresql.org/message-id/CAHgTRfceGtfm9g1dZgZo0P1ixp9%2BsS98MXP6daeNLy7ypFvUqw%40mail.gmail.com>
Precedence: bulk

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

1) turn off server side prepared statements with prepareThreshold=3D0
       with this threshold=3D0, if it's still possible to avoid parsing,
planning from Postgresql server side, like set
plan_cache_mode=3Dforce_generic_plan or with default "auto" mode.  Postgres=
ql
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 <laurenz.albe@cybertec.at> =E6=96=BC 2024=E5=B9=B43=E6=9C=8830=
=E6=97=A5=E9=80=B1=E5=85=AD =E4=B8=8B=E5=8D=8810:06=E5=AF=AB=E9=81=93=EF=BC=
=9A

> On Sat, 2024-03-30 at 08:27 -0400, Dave Cramer wrote:
> > > On Fri, 29 Mar 2024 at 19:42, James Pang <jamespang886@gmail.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 ou=
r
> 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.
> >
> > I think the best option for you is to turn off server side prepared
> statements with prepareThreshold=3D0
>
> 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 wou=
ld
> have been "text".
>
> Yours,
> Laurenz Albe
>

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

<div dir=3D"ltr">1) turn off server side prepared statements with prepareTh=
reshold=3D0<br><div>=C2=A0 =C2=A0 =C2=A0 =C2=A0with this threshold=3D0, if =
it&#39;s still possible to avoid parsing, planning from Postgresql server s=
ide, like set plan_cache_mode=3Dforce_generic_plan or with default &quot;au=
to&quot; mode.=C2=A0 Postgresql server can make the plan cached and reused.=
</div><div><br></div><div>2) change varchar(n) to varchar or text, then res=
tart application, so avoid future similar increase varchar(n) changes.</div=
><div>=C2=A0 =C2=A0 =C2=A0 it&#39;s safe to performance or optimizer ,right=
?=C2=A0</div><div><br></div><div>Thanks,</div><div><br></div><div>James</di=
v></div><br><div class=3D"gmail_quote"><div dir=3D"ltr" class=3D"gmail_attr=
">Laurenz Albe &lt;<a href=3D"mailto:laurenz.albe@cybertec.at">laurenz.albe=
@cybertec.at</a>&gt; =E6=96=BC 2024=E5=B9=B43=E6=9C=8830=E6=97=A5=E9=80=B1=
=E5=85=AD =E4=B8=8B=E5=8D=8810:06=E5=AF=AB=E9=81=93=EF=BC=9A<br></div><bloc=
kquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:=
1px solid rgb(204,204,204);padding-left:1ex">On Sat, 2024-03-30 at 08:27 -0=
400, Dave Cramer wrote:<br>
&gt; &gt; On Fri, 29 Mar 2024 at 19:42, James Pang &lt;<a href=3D"mailto:ja=
mespang886@gmail.com" target=3D"_blank">jamespang886@gmail.com</a>&gt; wrot=
e:<br>
&gt; &gt; &gt; we did DDL &quot;alter table ... alter column increase varch=
ar(512) to varchar(1024)&quot;,<br>
&gt; &gt; &gt; after that done, hours later, new query select on this table=
 still failed this error.<br>
&gt; &gt; &gt; From this=C2=A0<a href=3D"https://jdbc.postgresql.org/docume=
ntation/server-prepare/#re-execution-of-failed-statements" rel=3D"noreferre=
r" target=3D"_blank">https://jdbc.postgresql.org/documentation/server-prepa=
re/#re-execution-of-failed-statements</a> ,<br>
&gt; &gt; &gt; looks like pgjdbc try to handle this exception and retry, bu=
t in our case, it did not happen.<br>
&gt; &gt; &gt; Could you direct me how to make this retry work?<br>
&gt; &gt; &gt; we only want new transactions,queries=C2=A0work that after t=
he DDL changes.=C2=A0<br>
&gt; <br>
&gt; I think the best option for you is to turn off server side prepared st=
atements with prepareThreshold=3D0<br>
<br>
An alternative option is to take downtime for DDL and restart the applicati=
on.<br>
Or to chppse the appropriate data type right away: in your case, that would=
<br>
have been &quot;text&quot;.<br>
<br>
Yours,<br>
Laurenz Albe<br>
</blockquote></div>

--000000000000a08a1e0614ec7f6c--