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 1rqXo8-00D0Lv-0l for pgsql-jdbc@arkaria.postgresql.org; Sat, 30 Mar 2024 12:27:36 +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 1rqXo5-008FEM-RK for pgsql-jdbc@arkaria.postgresql.org; Sat, 30 Mar 2024 12:27:33 +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 1rqXo5-008FED-IS for pgsql-jdbc@lists.postgresql.org; Sat, 30 Mar 2024 12:27:33 +0000 Received: from pgintl.fastcrypt.com ([149.56.129.164]) by magus.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1rqXo1-007dv3-4P for pgsql-jdbc@lists.postgresql.org; Sat, 30 Mar 2024 12:27:32 +0000 Received: from mail-ed1-f50.google.com (mail-ed1-f50.google.com [209.85.208.50]) by pgintl.fastcrypt.com (Postfix) with ESMTPSA id 93948202BA for ; Sat, 30 Mar 2024 08:27:26 -0400 (EDT) Received: by mail-ed1-f50.google.com with SMTP id 4fb4d7f45d1cf-56c1364ff79so3235360a12.1 for ; Sat, 30 Mar 2024 05:27:26 -0700 (PDT) X-Forwarded-Encrypted: i=1; AJvYcCVfcIV8kRZiPNuRu2vTt9fxuQWSJiUj9ywiEyxpa9I4O2YZN8UYR0f/BlTEWLNu5phNyW6WJpRmGRAysCbCxdFgiMy2sImvMSI/Zgw8kvaB X-Gm-Message-State: AOJu0Yx1l7XzW0ZWg0SMY2pW3Ltcj5a8/u/hjb7Y9GheLWxeCtSMP6Vl 0qyuhuF8IGFS5hEPREcEMoZ7/og9O6gBlBd9Y0hN6BoBukd7GgqGx7R1mJLBGtV6Ddef832HcUl 0TS5slzsSiejnO4xD/zM3uH1DqyI= X-Google-Smtp-Source: AGHT+IHf2BUY1PazBA0192pYbP3hKwko5rw9zkn/+aekhgnvwCJnu6U9+cVrw6N6Y/hTEH3Pw0IsNyv0ch6O0aHggAI= X-Received: by 2002:a17:907:7e9a:b0:a47:4ae0:3bb9 with SMTP id qb26-20020a1709077e9a00b00a474ae03bb9mr3680338ejc.23.1711801645421; Sat, 30 Mar 2024 05:27:25 -0700 (PDT) MIME-Version: 1.0 References: <60ab210d02748b5da813007ab2504606d0fecaa1.camel@cybertec.at> <2950.1711729266@sss.pgh.pa.us> In-Reply-To: From: Dave Cramer Date: Sat, 30 Mar 2024 08:27:07 -0400 X-Gmail-Original-Message-ID: Message-ID: Subject: Re: cached plan must not change result type To: James Pang Cc: Tom Lane , pgsql-jdbc@lists.postgresql.org, Laurenz Albe Content-Type: multipart/alternative; boundary="0000000000000068970614dfe10c" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000000068970614dfe10c Content-Type: text/plain; charset="UTF-8" On Sat, 30 Mar 2024 at 06:14, 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-of-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. >> >> protected boolean willHealViaReparse(SQLException e) { >> if (e == null || e.getSQLState() == 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 = (PSQLException) e; >> >> ServerErrorMessage serverErrorMessage = pe.getServerErrorMessage(); >> if (serverErrorMessage == null) { >> return false; >> } >> // "cached plan must not change result type" >> String routine = serverErrorMessage.getRoutine(); >> return "RevalidateCachedQuery".equals(routine) // 9.2+ >> || "RevalidateCachedPlan".equals(routine); // <= 9.1 >> } >> >> > This only works if there was no transaction. > > > https://github.com/pgjdbc/pgjdbc/blob/0fbd31d43b1013829da3a8f21120d8bb94df803d/pgjdbc/src/main/java/org/postgresql/core/QueryExecutorBase.java#L442 > > > Dave > >> Thanks, >> > > > >> >> I think the best option for you is to turn off server side prepared statements with prepareThreshold=0 Dave --0000000000000068970614dfe10c Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable


=
On Sat, 30 Mar 2024 at 06:14, Dave Cr= amer <davecramer@postgres.rocks> wrote:

=

On Fri, 29 Mar 2024 at 19:42, James Pang <jamespang886@gmail.com&= gt; wrote:
=C2=A0 =C2=A0we did DDL "alter table ... alter column incr= ease varchar(512) to varchar(1024)", 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-exec= ution-of-failed-statements" target=3D"_blank">https://jdbc.postgresql.org/d= ocumentation/server-prepare/#re-execution-of-failed-statements , 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 transactions,queries=C2=A0work that after the DDL changes.=C2=A0<= div>
protected boolean willHealViaReparse(SQLException e) {
=C2= =A0 =C2=A0 if (e =3D=3D null || e.getSQLState() =3D=3D null) {
=C2=A0 = =C2=A0 =C2=A0 return false;
=C2=A0 =C2=A0 }

=C2=A0 =C2=A0 // &quo= t;prepared statement \"S_2\" does not exist"
=C2=A0 =C2= =A0 if (PSQLState.INVALID_SQL_STATEMENT_NAME.getState().equals(e.getSQLStat= e())) {
=C2=A0 =C2=A0 =C2=A0 return true;
=C2=A0 =C2=A0 }
=C2=A0 = =C2=A0 if (!PSQLState.NOT_IMPLEMENTED.getState().equals(e.getSQLState())) {=
=C2=A0 =C2=A0 =C2=A0 return false;
=C2=A0 =C2=A0 }

=C2=A0 =C2= =A0 if (!(e instanceof PSQLException)) {
=C2=A0 =C2=A0 =C2=A0 return fal= se;
=C2=A0 =C2=A0 }

=C2=A0 =C2=A0 PSQLException pe =3D (PSQLExcep= tion) e;

=C2=A0 =C2=A0 ServerErrorMessage serverErrorMessage =3D pe.= getServerErrorMessage();
=C2=A0 =C2=A0 if (serverErrorMessage =3D=3D nul= l) {
=C2=A0 =C2=A0 =C2=A0 return false;
=C2=A0 =C2=A0 }
=C2=A0 =C2= =A0 // "cached plan must not change result type"
=C2=A0 =C2=A0= String routine =3D serverErrorMessage.getRoutine();
=C2=A0 =C2=A0 retur= n "RevalidateCachedQuery".equals(routine) // 9.2+
=C2=A0 =C2= =A0 =C2=A0 =C2=A0 || "RevalidateCachedPlan".equals(routine); // &= lt;=3D 9.1
=C2=A0 }


This only works if there was no transaction.

Da= ve
=
Thanks,

=C2=A0<= /div>

I th= ink the best option for you is to turn off server side prepared statements = with prepareThreshold=3D0

=C2=A0Dave
--0000000000000068970614dfe10c--