Re: cached plan must not change result type
От | Dave Cramer |
---|---|
Тема | Re: cached plan must not change result type |
Дата | |
Msg-id | CADK3HHKNoX4ZMtmMvNfTN8_UunkNsv6GYq77R8vd59FgRToTCQ@mail.gmail.com обсуждение исходный текст |
Ответ на | Re: cached plan must not change result type (James Pang <jamespang886@gmail.com>) |
Список | pgsql-jdbc |
On Sat, 30 Mar 2024 at 23:30, James Pang <jamespang886@gmail.com> wrote:
1) turn off server side prepared statements with prepareThreshold=0with 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,JamesLaurenz Albe <laurenz.albe@cybertec.at> 於 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 <jamespang886@gmail.com> 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
В списке pgsql-jdbc по дате отправления: