public inbox for [email protected]  

help / color / mirror / Atom feed

From: James Pang <[email protected]>
To: Laurenz Albe <[email protected]>
Cc: Dave Cramer <[email protected]>
Cc: Tom Lane <[email protected]>
Cc: [email protected]
Subject: Re: cached plan must not change result type
Date: Sun, 31 Mar 2024 11:30:35 +0800
Message-ID: <CAHgTRfceGtfm9g1dZgZo0P1ixp9+sS98MXP6daeNLy7ypFvUqw@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <CAHgTRfcg2jGnjSqq1NVuMDtCjRU3aOXWrhXn1L=92Ka_Ug6euw@mail.gmail.com>
	<[email protected]>
	<CAHgTRfe+REHqYoQJ_ykj_p416PbdHz+UN06-R3PVr6auMx4w2g@mail.gmail.com>
	<CAHgTRfeXGN9d7vgg8LtwpNYw_+f-zmMYsOc1U9rhXkGcwWtNng@mail.gmail.com>
	<CADK3HHLkuDpfVKfzckH5z-e1_Z8DH-9WdOWaafbJ4HRuRqnsdg@mail.gmail.com>
	<[email protected]>
	<CADK3HHLx1u-RbR_v5UbmK_v4_sOfzHxdYwxtn5QRRWV-=Re6MQ@mail.gmail.com>
	<CAHgTRfeJop+-t8b1v3U85_hhAL2o15R1cHqkVyhxWLj7B4KWLg@mail.gmail.com>
	<CADK3HHKj=UEr_veXPs+XgD6=0GLJG6pAUN66Vcx0mxYCeuJVHA@mail.gmail.com>
	<CADK3HHJn79rvd0Si7XtQ8EfsaGK04A4fPhoBDng8A8aJkR8hrQ@mail.gmail.com>
	<[email protected]>

1) turn off server side prepared statements with prepareThreshold=0
       with this threshold=0, if it's still possible to avoid parsing,
planning from Postgresql server side, like set
plan_cache_mode=force_generic_plan or with default "auto" mode.  Postgresql
server can make the plan cached and reused.

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?

Thanks,

James

Laurenz Albe <[email protected]> 於 2024年3月30日週六 下午10:06寫道：

> On Sat, 2024-03-30 at 08:27 -0400, Dave Cramer wrote:
> > > On Fri, 29 Mar 2024 at 19:42, James Pang <[email protected]>
> 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.
> >
> > I think the best option for you is to turn off server side prepared
> statements with prepareThreshold=0
>
> 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
>

view thread (13+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected], [email protected]
  Subject: Re: cached plan must not change result type
  In-Reply-To: <CAHgTRfceGtfm9g1dZgZo0P1ixp9+sS98MXP6daeNLy7ypFvUqw@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox