Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rqs5u-00ESAN-WD for pgsql-jdbc@arkaria.postgresql.org; Sun, 31 Mar 2024 10:07:19 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1rqs4u-00CFbW-GG for pgsql-jdbc@arkaria.postgresql.org; Sun, 31 Mar 2024 10:06:16 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rqs4u-00CFbO-6E for pgsql-jdbc@lists.postgresql.org; Sun, 31 Mar 2024 10:06:16 +0000 Received: from pgintl.fastcrypt.com ([149.56.129.164]) by magus.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1rqs4o-007n3L-DY for pgsql-jdbc@lists.postgresql.org; Sun, 31 Mar 2024 10:06:14 +0000 Received: from mail-lf1-f46.google.com (mail-lf1-f46.google.com [209.85.167.46]) by pgintl.fastcrypt.com (Postfix) with ESMTPSA id 98238202BA for ; Sun, 31 Mar 2024 06:06:07 -0400 (EDT) Received: by mail-lf1-f46.google.com with SMTP id 2adb3069b0e04-513ccc70a6dso5269857e87.1 for ; Sun, 31 Mar 2024 03:06:07 -0700 (PDT) X-Forwarded-Encrypted: i=1; AJvYcCXaYSZNdRzK5vNQ58Zka0VKdefmgTfH3wL4edswA7OIw9Z0OLAFZjMzs+ipTa41QlxlVzjelMMWaxcMAGPsXwIej8ckmCzjDPn6Wmrg3wC6 X-Gm-Message-State: AOJu0Yz3rkaGCIMRtWeo0ck3jTqVDe7u6PrLQlQQDyUdYY9JD9H7BGuG sySRkeNGm+gGnWQvAnh45kOT0MGupHxbsInboi7Es6Flr4c0Sogq16+n7Zc0pEV6QW80eLEEGDF UaQXgPY40B9j0cDo0cfIOALJtfe0= X-Google-Smtp-Source: AGHT+IEp230FM4H1HPtGJ6nWl8kuGNA1L7MWiQ2FVNCyTB5xX7rz0+B4/UTi2oJisyi7A4MkUgeDbVDcf+QSII/0cEY= X-Received: by 2002:a05:6512:3d11:b0:516:a7a4:9951 with SMTP id d17-20020a0565123d1100b00516a7a49951mr936138lfv.2.1711879566077; Sun, 31 Mar 2024 03:06:06 -0700 (PDT) MIME-Version: 1.0 References: <60ab210d02748b5da813007ab2504606d0fecaa1.camel@cybertec.at> <2950.1711729266@sss.pgh.pa.us> <29759a95d5194d1a5a468e2585e363a313c7d287.camel@cybertec.at> In-Reply-To: From: Dave Cramer Date: Sun, 31 Mar 2024 06:05:48 -0400 X-Gmail-Original-Message-ID: Message-ID: Subject: Re: cached plan must not change result type To: James Pang Cc: Laurenz Albe , Tom Lane , pgsql-jdbc@lists.postgresql.org Content-Type: multipart/alternative; boundary="0000000000006f76950614f205da" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000006f76950614f205da Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Sat, 30 Mar 2024 at 23:30, James Pang wrote: > 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. Postgr= esql > 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. > it's safe to performance or optimizer ,right? > > 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 > Thanks, > > James > > Laurenz Albe =E6=96=BC 2024=E5=B9=B43=E6=9C=88= 30=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 >> 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-o= f-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. >> > >> > 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 >> would >> have been "text". >> >> Yours, >> Laurenz Albe >> > --0000000000006f76950614f205da Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable

On Sat, 30 Mar 2024 at 23:30, Jam= es Pang <jamespang886@gmail.co= m> wrote:
1) turn off server side prepared statements with prepareT= hreshold=3D0
=C2=A0 =C2=A0 =C2=A0 =C2=A0with 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 "a= uto" mode.=C2=A0 Postgresql server can make the plan cached and reused= .

Unfortunately=C2=A0no, every = plan will have to be parsed and planned. With this setting we use the unnam= ed statement which is replanned for every use.

2) change v= archar(n) to varchar or text, then restart application, so avoid future sim= ilar increase varchar(n) changes.
=C2=A0 =C2=A0 =C2=A0 it's s= afe to performance or optimizer ,right?=C2=A0


Yes, this is very safe, and in fact personall= y, I would never use varchar(n) if you want to enforce the length use a con= straint.

Dave=C2=A0
Thanks,

James

Laurenz Albe <laurenz.albe@cybertec.at> =E6=96=BC 2= 024=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> wrot= e:
> > > we did DDL "alter table ... alter column increase varch= ar(512) to varchar(1024)",
> > > after that done, hours later, new query select on this table= still failed this error.
> > > From this=C2=A0https://jdbc.postgresql.org/documentation/server-prepa= re/#re-execution-of-failed-statements ,
> > > looks like pgjdbc try to handle this exception and retry, bu= t in our case, it did not happen.
> > > Could you direct me how to make this retry work?
> > > we only want new transactions,queries=C2=A0work that after t= he DDL changes.=C2=A0
>
> I think the best option for you is to turn off server side prepared st= atements with prepareThreshold=3D0

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

Yours,
Laurenz Albe
--0000000000006f76950614f205da--