public inbox for [email protected]
help / color / mirror / Atom feedFrom: Dave Cramer <[email protected]>
To: James Pang <[email protected]>
Cc: Laurenz Albe <[email protected]>
Cc: Tom Lane <[email protected]>
Cc: [email protected]
Subject: Re: cached plan must not change result type
Date: Sun, 31 Mar 2024 06:05:48 -0400
Message-ID: <CADK3HHKNoX4ZMtmMvNfTN8_UunkNsv6GYq77R8vd59FgRToTCQ@mail.gmail.com> (raw)
In-Reply-To: <CAHgTRfceGtfm9g1dZgZo0P1ixp9+sS98MXP6daeNLy7ypFvUqw@mail.gmail.com>
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]>
<CAHgTRfceGtfm9g1dZgZo0P1ixp9+sS98MXP6daeNLy7ypFvUqw@mail.gmail.com>
On Sat, 30 Mar 2024 at 23:30, James Pang <[email protected]> wrote:
> 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.
>
Unfortunately no, every plan will have to be parsed and planned. With this
setting we use the unnamed statement which is replanned for every use.
>
> 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?
>
>
Yes, this is very safe, and in fact personally, I would never use
varchar(n) if you want to enforce the length use a constraint.
Dave
> 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)
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: <CADK3HHKNoX4ZMtmMvNfTN8_UunkNsv6GYq77R8vd59FgRToTCQ@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