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 1rq82d-00Ai8V-GY for pgsql-jdbc@arkaria.postgresql.org; Fri, 29 Mar 2024 08:56:52 +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 1rq82c-00EgFG-8H for pgsql-jdbc@arkaria.postgresql.org; Fri, 29 Mar 2024 08:56:50 +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 1rq82c-00EgF7-11 for pgsql-jdbc@lists.postgresql.org; Fri, 29 Mar 2024 08:56:50 +0000 Received: from mail-oi1-x22f.google.com ([2607:f8b0:4864:20::22f]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1rq82T-0071cc-TP for pgsql-jdbc@lists.postgresql.org; Fri, 29 Mar 2024 08:56:48 +0000 Received: by mail-oi1-x22f.google.com with SMTP id 5614622812f47-3c397193878so1124287b6e.3 for ; Fri, 29 Mar 2024 01:56:42 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1711702602; x=1712307402; 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=mXdqTnbMFcEW1pBbXDQ1A9sYWFOUwAW+9qBQgLocn4s=; b=AFhRoD+iypw+zJC8reE7/MVxfNVxPhuCFhY/jSgwF08QxHKE+JNxf2KWU+kNisFVwm +dMnMtsVHRyhnvDAoYBmublpB2NIpV5K6Swe7ou0LdcpQT47ceNEwEBSpRAXlq1Bxvd8 oElJrTy/FisK95b+wR6aDYsFVlCsiZRuBmQod+HiZrkNLKXKE5BKrcxFGr8j9HGjewyV +nl64r4cL2u6epRa8D9Xa4cb32Gcyjr7EBktnRYx/YInCDNRdyOvUTMuNSqb/oFp1yxY lixNbVvqakYPc8eHL3l1tPFxWw2lB7gFNGNkjRyYxlt6BKJG1bMWySkOediYPGeV8BhO Sbnw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1711702602; x=1712307402; 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=mXdqTnbMFcEW1pBbXDQ1A9sYWFOUwAW+9qBQgLocn4s=; b=L1KnYMXimlcgW76QYwSVCH9CE0ADgkt/fL+QVBHtcydBDvwGoufo5dZllJ3JtajMHL syqqCVTBq4t5X7E+r4W5/7ETHiys0ynTNaq0ZBk/uiKK9uQ7rIKBWNb64PXDvdmed2pw Y7C41x27HdoAJ6aGpc/j5bPz3L3zJ++kXZwFO/x67QNnrZREt4uA79JeQ0fWIdpW0qRD mOEZCbsx9WFKVCXP1Sn+O+WorAUhvMlXqLAjTEn45eqiYlm9wesCMO7QMp5UcN1irUkr E5xR7KC4OmSz+dxaPN4xTd2hxTssTdcrogvpql8TCtLQZGqiSX0DhHYSj8KC16Gc21xX +jVw== X-Gm-Message-State: AOJu0Yz1ds6YxdkqpgR+8t8Nbco32eWo20wNFL+gsSoiga40+KsksAhG s4Qhw1vHW0i50TUQSAJALo2pqVLW+lII5Bjzd7MGdCt/Wh/iWHh6DKIBoPOY9zmdDNA9OkHcKLe lfnic9QVow6JvO60Gn0aDX45+I2ZPKRWu2l0= X-Google-Smtp-Source: AGHT+IH81R0QkOOI9n1KF2u2uqW12KWIa1XQUiJCXqQ/6zlfBta9bprtQNSnunOG8wYpc6M1tTAg5Lhe6gtxknB2Kik= X-Received: by 2002:a05:6808:2190:b0:3c3:d84d:57e8 with SMTP id be16-20020a056808219000b003c3d84d57e8mr1917345oib.12.1711702602297; Fri, 29 Mar 2024 01:56:42 -0700 (PDT) MIME-Version: 1.0 References: <60ab210d02748b5da813007ab2504606d0fecaa1.camel@cybertec.at> In-Reply-To: <60ab210d02748b5da813007ab2504606d0fecaa1.camel@cybertec.at> From: James Pang Date: Fri, 29 Mar 2024 16:56:31 +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="000000000000923b420614c8d141" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000923b420614c8d141 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Thanks Laurenz, 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 . Thanks, James Laurenz Albe =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 > > "alter table alter column type ..." changes done, a lot of JDBC clients > > 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 driv= er > > will clear this caching-plan and following same SQL statement got parsi= ng > > 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 > --000000000000923b420614c8d141 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable

Thanks Laurenz,=C2=A0 forwarded 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.pos= tgresql.org/documentation/server-prepare/#re-execution-of-failed-statements= .

=C2=A0

Thanks,

=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
--000000000000923b420614c8d141--