Re: cached plan must not change result type - Mailing list pgsql-jdbc

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 <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