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 1rq8EO-00AjIs-0P for pgsql-jdbc@arkaria.postgresql.org; Fri, 29 Mar 2024 09:09:00 +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 1rq8EN-00Ex7H-0E for pgsql-jdbc@arkaria.postgresql.org; Fri, 29 Mar 2024 09:08:59 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rq8EM-00Ex79-Ku for pgsql-jdbc@lists.postgresql.org; Fri, 29 Mar 2024 09:08:58 +0000 Received: from mail-ot1-x331.google.com ([2607:f8b0:4864:20::331]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1rq8EI-0071il-TG for pgsql-jdbc@lists.postgresql.org; Fri, 29 Mar 2024 09:08:57 +0000 Received: by mail-ot1-x331.google.com with SMTP id 46e09a7af769-6e696ee8fa3so1530543a34.1 for ; Fri, 29 Mar 2024 02:08:55 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1711703335; x=1712308135; darn=lists.postgresql.org; h=to:subject:message-id:date:from:in-reply-to:references:mime-version :from:to:cc:subject:date:message-id:reply-to; bh=jcDlyPpm0bAm9aId+k37+SZ1lvjrBuUikvzOMB9TlEI=; b=nULquKR+w5/m0lyqqjhgtzCZC6mpOEtih/DVCBxORo8sfOqTBheM1BPRPPHQ4SHIWg 5Qh1wflIJazI+y4ylQdCoy5aTju+aVdevmDUSkLicgPxYhmV2FaaCHE/93GloGBE6w/F QPBXC4kbnR6kPj8pke+kNay5iymo0+elp0GAit2YQz8DqLaUgqM68nW+FKby72wMy/DC TaozE9YZPVOEN8bawItEErXNbaG3LXVM7ptDiFZhv0R4GCYmk3e6MF/PIZMNyHlhTY2f juytvyCgIWi4p83BvLuxilqnW7NW+arC5LiwMV8Cl04Ri92VOunNc8RDFIRc5koxoJF0 jyfw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1711703335; x=1712308135; h=to:subject:message-id:date:from:in-reply-to:references:mime-version :x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=jcDlyPpm0bAm9aId+k37+SZ1lvjrBuUikvzOMB9TlEI=; b=gMzK+oB495JQ8XlJU5IKSiiTzb9XXFyRYLKQLUHreEbLJGMU2ez9iGlu7S7onpSvL2 Nf6y4qc28GjxFEwfZILBgwwFhpYykPM1IS/ZGaaE/8p0neWMjUc09U73jLSVG7y9McUD TSDwdrAP7EZxurkGjFFHXiEcglHpvyMxqQqMVCWH8L9AnK3oX0Fleyde//n6M5mLog2X L9RedOhwkmTpLcZEogNlBbuMmnnTiMVfClWNrX8NEB3/4X8VY8Q5/CObi34KEo+c50BG V0U6QuuVa2oaAXesklpGZiYopuXvNQPZ7RYC2DSzSAFcAp6V46oBiWGy7A5XldWi9T8i CNHA== X-Gm-Message-State: AOJu0YyQhoz9DScbQUGeZ4xakYQ5kHOEvy0TZlf7L6fIuZOgzIh3SJND qNpiES+YDL8LjFoaFKAeeN+Wf40TtXwIN3vbgYi2gVEFTT1IUH+/phmUGppb3yvDx3acwM4giyL XiFDg9+4cJzLrb9X4rd/nLM5qfEVKtMQZio8= X-Google-Smtp-Source: AGHT+IGSTdWs7utsSKkMk/JqCYee9H8kl1r3oWU/FkETaueYviNyin5l3ToNhfur+J9hvtUn7Z3a74dxLJEtHah49vw= X-Received: by 2002:a05:6870:aa08:b0:222:6e0e:bca8 with SMTP id gv8-20020a056870aa0800b002226e0ebca8mr754709oab.10.1711703335103; Fri, 29 Mar 2024 02:08:55 -0700 (PDT) MIME-Version: 1.0 References: <60ab210d02748b5da813007ab2504606d0fecaa1.camel@cybertec.at> In-Reply-To: From: James Pang Date: Fri, 29 Mar 2024 17:08:44 +0800 Message-ID: Subject: Re: cached plan must not change result type To: pgsql-jdbc@lists.postgresql.org, Laurenz Albe Content-Type: multipart/alternative; boundary="0000000000003ff3810614c8fd76" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000003ff3810614c8fd76 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 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-f= ailed-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() !=3D null <<< no idea how this cachedQuery.query.getSubqueries() !=3D null || !connection.getQueryExecutor().willHealOnRetry(e)) { throw e; } cachedQuery.query.close(); // Execute the query one more time executeInternal(cachedQuery, queryParameters, flags); } } cachedQuery.query.getSubqueries() !=3D 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 James Pang =E6=96=BC 2024=E5=B9=B43=E6=9C=8829=E6= =97=A5=E9=80=B1=E4=BA=94 =E4=B8=8B=E5=8D=884:56=E5=AF=AB=E9=81=93=EF=BC=9A > Thanks Laurenz, forwarded to pgjdbc, we want to understand why JDBC > failed to reexecute the SQL instead of throw error out. Like this documen= t > https://jdbc.postgresql.org/documentation/server-prepare/#re-execution-of= -failed-statements > . > > > > Thanks, > > > > James > > Laurenz Albe =E6=96=BC 2024=E5=B9=B43=E6=9C=88= 29=E6=97=A5=E9=80=B1=E4=BA=94 =E4=B8=8B=E5=8D=884:40=E5=AF=AB=E9=81=93=EF= =BC=9A > >> On Fri, 2024-03-29 at 10:05 +0800, James Pang wrote: >> > PGV14, client use PGJDBC driver and prepared statements, when a table >> > "alter table alter column type ..." changes done, a lot of JDBC client= s >> > got this error "cached plan must not change result type". when a JDBC >> > client got this error first time after the DDL schema change, JDBC >> driver >> > will clear this caching-plan and following same SQL statement got >> parsing >> > again and new cached plan right? >> >> Not sure; what do you observe? >> Anyway, this is a known problem: >> https://jdbc.postgresql.org/documentation/server-prepare/#ddl >> >> > How to avoid this issue when DDL schema changes ? >> >> Don't use server-side prepared statements: >> https://jdbc.postgresql.org/documentation/server-prepare/#deactivation >> You'll lose some performance though. >> >> Questions like this had better be asked on the pgsql-jdbc list. >> >> Yours, >> Laurenz Albe >> > --0000000000003ff3810614c8fd76 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable

=C2=A0forwarded to pgjdbc,=C2=A0 we want to understand why= JDBC failed to reexecute the SQL instead of throw error out. Like this doc= ument=C2=A0https://jdbc.postgresql.org/documentation/server-prepare/#re-e= xecution-of-failed-statements=C2=A0.

=C2=A0


prote= cted final void execute(CachedQuery cachedQuery,
=C2=A0 =C2=A0 =C2=A0 @N= ullable ParameterList queryParameters, int flags)
=C2=A0 =C2=A0 =C2=A0 t= hrows SQLException {
=C2=A0 =C2=A0 try {
=C2=A0 =C2=A0 =C2=A0 execute= Internal(cachedQuery, queryParameters, flags);
=C2=A0 =C2=A0 } catch (SQ= LException e) {
=C2=A0 =C2=A0 =C2=A0 // Don't retry composite querie= s as it might get partially executed
=C2=A0 =C2=A0 =C2=A0 if (cachedQuer= y.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<<< no idea how this=C2=A0 cachedQuery.query.getSubqueries() !=3D null
=C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 || !connection.getQueryExecutor().willHealOnRetry(e)) {
=C2=A0 = =C2=A0 =C2=A0 =C2=A0 throw e;
=C2=A0 =C2=A0 =C2=A0 }
=C2=A0 =C2=A0 = =C2=A0 cachedQuery.query.close();
=C2=A0 =C2=A0 =C2=A0 // Execute the qu= ery one more time
=C2=A0 =C2=A0 =C2=A0 executeInternal(cachedQuery, quer= yParameters, flags);
=C2=A0 =C2=A0 }
=C2=A0 }

= 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 ...
<= /div>

Thanks,

James=C2=A0
=

= James Pang <jamespang886@gmail= .com> =E6=96=BC 2024=E5=B9=B43=E6=9C=8829=E6=97=A5=E9=80=B1=E4=BA=94= =E4=B8=8B=E5=8D=884:56=E5=AF=AB=E9=81=93=EF=BC=9A

Thanks Laurenz,=C2=A0 forwar= ded to pgjdbc,=C2=A0 we want to understand why JDBC failed to reexecute the SQL instead of throw error out. Like this docu= ment https://jdbc.postgresql.org/documentation/server-prepare/#re-execution-of= -failed-statements .

=C2= =A0

Thank= s,

=C2= =A0

James= =C2=A0


Laurenz Albe <laurenz.albe@cybertec.at> =E6=96=BC 2024=E5=B9=B43=E6= =9C=8829=E6=97=A5=E9=80=B1=E4=BA=94 =E4=B8=8B=E5=8D=884:40=E5=AF=AB=E9=81= =93=EF=BC=9A
On = Fri, 2024-03-29 at 10:05 +0800, James Pang wrote:
> PGV14, client use PGJDBC driver and prepared statements, when a table<= br> > "alter table alter column type ..." changes done, a lot of J= DBC clients
> got this error "cached plan must not change result type".=C2= =A0 when a JDBC
> client got this error first time after the DDL schema change, JDBC dri= ver
> will clear this caching-plan and following same SQL statement got pars= ing
> again and new cached plan right?

Not sure; what do you observe?
Anyway, this is a known problem:
https://jdbc.postgresql.org/documentati= on/server-prepare/#ddl

> How to avoid this issue when DDL schema changes ?

Don't use server-side prepared statements:
https://jdbc.postgresql.org/do= cumentation/server-prepare/#deactivation
You'll lose some performance though.

Questions like this had better be asked on the pgsql-jdbc list.

Yours,
Laurenz Albe
--0000000000003ff3810614c8fd76--