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 1rqVjT-00CoX7-CD for pgsql-jdbc@arkaria.postgresql.org; Sat, 30 Mar 2024 10:14:39 +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 1rqVjR-007nji-Jo for pgsql-jdbc@arkaria.postgresql.org; Sat, 30 Mar 2024 10:14:37 +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 1rqVjR-007njJ-8H for pgsql-jdbc@lists.postgresql.org; Sat, 30 Mar 2024 10:14:37 +0000 Received: from pgintl.fastcrypt.com ([149.56.129.164]) by makus.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1rqVjI-007ExX-9t for pgsql-jdbc@lists.postgresql.org; Sat, 30 Mar 2024 10:14:35 +0000 Received: from mail-ej1-f43.google.com (mail-ej1-f43.google.com [209.85.218.43]) by pgintl.fastcrypt.com (Postfix) with ESMTPSA id 56F79201B6 for ; Sat, 30 Mar 2024 06:14:28 -0400 (EDT) Received: by mail-ej1-f43.google.com with SMTP id a640c23a62f3a-a44ad785a44so304343866b.3 for ; Sat, 30 Mar 2024 03:14:28 -0700 (PDT) X-Forwarded-Encrypted: i=1; AJvYcCVND3x3ElPvZdZABlANpplM4iSUH5y5A3KeHpGJcKiizGJU8Qs7Adywe7dj+M4BOpuTAU8aTgXplsbyYDFhhOffdN81vVTvDrXiHsMMm2vK X-Gm-Message-State: AOJu0Yy5oaB8D/7/7t2Xqb9vtEGPsXxrS5RpN/rU6c1uNEQNQFNbII6J AX7azijdx6OsKK9MIqZJH9CDsd7It5F6CVEJBf6Zr+qUHi1L/e3HwiYTAM5Yp0O3exHaTecUtrN rqLIp0+o2Rip93GNBYM2guzSzkOw= X-Google-Smtp-Source: AGHT+IGv7v4qLQ0t/Dm4hoeeWoNacTBTP4Z37x3n5z6mAS7NsS0qS5sQYyDVFK9r0KMFduXSNZMWKxHs8g30EbSifuc= X-Received: by 2002:a17:906:578b:b0:a4e:4deb:12d3 with SMTP id k11-20020a170906578b00b00a4e4deb12d3mr515748ejq.65.1711793667199; Sat, 30 Mar 2024 03:14:27 -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 06:14:09 -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="00000000000076637f0614de05b8" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --00000000000076637f0614de05b8 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 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 =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 > } > > This only works if there was no transaction. https://github.com/pgjdbc/pgjdbc/blob/0fbd31d43b1013829da3a8f21120d8bb94df8= 03d/pgjdbc/src/main/java/org/postgresql/core/QueryExecutorBase.java#L442 Dave > Thanks, > > > James > > Dave Cramer =E6=96=BC 2024=E5=B9=B43=E6=9C=88= 30=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 wrote: >> >>> Dave Cramer 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 >>> alter 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 >>> are 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 of= f >> a message, just refuse to run the Execute in a way that doesn't abort th= e >> transaction. >> >> Dave >> > --00000000000076637f0614de05b8 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable


On Fri, 29 M= ar 2024 at 19:42, James Pang <= jamespang886@gmail.com> wrote:
=C2=A0 =C2=A0we 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 er= ror.=C2=A0 From this=C2=A0ht= tps://jdbc.postgresql.org/documentation/server-prepare/#re-execution-of-fai= led-statements , looks like pgjdbc try to handle this exception and ret= ry, but in our case, it did not happen.=C2=A0 Could you direct me how to ma= ke this retry work? we only want new transactions,queries=C2=A0work that af= ter the DDL changes.=C2=A0

protected boolean willHealViaRepars= e(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 // "prepared statement \"S_2\" does not ex= ist"
=C2=A0 =C2=A0 if (PSQLState.INVALID_SQL_STATEMENT_NAME.getStat= e().equals(e.getSQLState())) {
=C2=A0 =C2=A0 =C2=A0 return true;
=C2= =A0 =C2=A0 }
=C2=A0 =C2=A0 if (!PSQLState.NOT_IMPLEMENTED.getState().equ= als(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 false;
=C2=A0 =C2=A0 }

=C2=A0 =C2=A0 PSQLExc= eption pe =3D (PSQLException) e;

=C2=A0 =C2=A0 ServerErrorMessage se= rverErrorMessage =3D pe.getServerErrorMessage();
=C2=A0 =C2=A0 if (serve= rErrorMessage =3D=3D null) {
=C2=A0 =C2=A0 =C2=A0 return false;
=C2= =A0 =C2=A0 }
=C2=A0 =C2=A0 // "cached plan must not change result t= ype"
=C2=A0 =C2=A0 String routine =3D serverErrorMessage.getRoutine= ();
=C2=A0 =C2=A0 return "RevalidateCachedQuery".equals(routin= e) // 9.2+
=C2=A0 =C2=A0 =C2=A0 =C2=A0 || "RevalidateCachedPlan&quo= t;.equals(routine); // <=3D 9.1
=C2=A0 }


This only works if there was no tr= ansaction.


<= div>Dave
Thanks,

= =C2=A0

James

Dave Cramer <davecramer@postgres.rock= s> =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&= gt; writes:
> This is really an issue that needs to be solved in the backend. The er= ror
> 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 se= nd 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?=C2=A0 It mig= ht
be a lie now" at random times.=C2=A0

= I agree, but it's a known=C2=A0issue. I'm just pointing that it wou= ld be nice to have.
We'd have to figure out the details.
=C2=A0
Also, w= hat 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 ab= ort the
transaction, and that's a concept that doesn't exist in the
protocol at all.

This actually sounds l= ike the best option as we wouldn't have to fire off a message, just ref= use to run the Execute in a way that doesn't abort the transaction.

Dave
--00000000000076637f0614de05b8--