pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedcached plan must not change result type
13+ messages / 4 participants
[nested] [flat]
* cached plan must not change result type
@ 2024-03-29 02:05 James Pang <jamespang886@gmail.com>
0 siblings, 1 reply; 13+ messages in thread
From: James Pang @ 2024-03-29 02:05 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
hi,
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 driver will
clear this caching-plan and following same SQL statement got parsing again
and new cached plan right?
How to avoid this issue when DDL schema changes ?
Thanks,
James
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: cached plan must not change result type
@ 2024-03-29 08:40 Laurenz Albe <laurenz.albe@cybertec.at>
parent: James Pang <jamespang886@gmail.com>
0 siblings, 1 reply; 13+ messages in thread
From: Laurenz Albe @ 2024-03-29 08:40 UTC (permalink / raw)
To: James Pang <jamespang886@gmail.com>; pgsql-admin@lists.postgresql.org
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 driver
> will clear this caching-plan and following same SQL statement got parsing
> 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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: cached plan must not change result type
@ 2024-03-29 08:56 James Pang <jamespang886@gmail.com>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 1 reply; 13+ messages in thread
From: James Pang @ 2024-03-29 08:56 UTC (permalink / raw)
To: pgsql-jdbc@lists.postgresql.org, Laurenz Albe <laurenz.albe@cybertec.at>
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-failed-statements
.
Thanks,
James
Laurenz Albe <laurenz.albe@cybertec.at> 於 2024年3月29日週五 下午4:40寫道：
> 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 driver
> > will clear this caching-plan and following same SQL statement got parsing
> > 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
>
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: cached plan must not change result type
@ 2024-03-29 09:08 James Pang <jamespang886@gmail.com>
parent: James Pang <jamespang886@gmail.com>
0 siblings, 1 reply; 13+ messages in thread
From: James Pang @ 2024-03-29 09:08 UTC (permalink / raw)
To: pgsql-jdbc@lists.postgresql.org, Laurenz Albe <laurenz.albe@cybertec.at>
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-failed-statements
.
protected final void execute(CachedQuery cachedQuery,
@Nullable ParameterList queryParameters, int flags)
throws SQLException {
try {
executeInternal(cachedQuery, queryParameters, flags);
} catch (SQLException e) {
// Don't retry composite queries as it might get partially executed
if (cachedQuery.query.getSubqueries() != null
<<< no idea how this cachedQuery.query.getSubqueries() != null
|| !connection.getQueryExecutor().willHealOnRetry(e)) {
throw e;
}
cachedQuery.query.close();
// Execute the query one more time
executeInternal(cachedQuery, queryParameters, flags);
}
}
cachedQuery.query.getSubqueries() != null, how this code decide composite
queries here ? that mean some query having subquery or having many JOIN
or LEFT JOINs like select .... A left join B ...
Thanks,
James
James Pang <jamespang886@gmail.com> 於 2024年3月29日週五 下午4:56寫道：
> 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-failed-statements
> .
>
>
>
> Thanks,
>
>
>
> James
>
> Laurenz Albe <laurenz.albe@cybertec.at> 於 2024年3月29日週五 下午4:40寫道：
>
>> 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
>> driver
>> > will clear this caching-plan and following same SQL statement got
>> parsing
>> > 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
>>
>
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: cached plan must not change result type
@ 2024-03-29 15:45 Dave Cramer <davecramer@postgres.rocks>
parent: James Pang <jamespang886@gmail.com>
0 siblings, 1 reply; 13+ messages in thread
From: Dave Cramer @ 2024-03-29 15:45 UTC (permalink / raw)
To: James Pang <jamespang886@gmail.com>; +Cc: pgsql-jdbc@lists.postgresql.org, Laurenz Albe <laurenz.albe@cybertec.at>
On Fri, 29 Mar 2024 at 05:09, James Pang <jamespang886@gmail.com> wrote:
> 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-failed-statements
> .
>
>
>
> protected final void execute(CachedQuery cachedQuery,
> @Nullable ParameterList queryParameters, int flags)
> throws SQLException {
> try {
> executeInternal(cachedQuery, queryParameters, flags);
> } catch (SQLException e) {
> // Don't retry composite queries as it might get partially executed
> if (cachedQuery.query.getSubqueries() != null
> <<< no idea how this cachedQuery.query.getSubqueries() != null
> || !connection.getQueryExecutor().willHealOnRetry(e)) {
> throw e;
> }
> cachedQuery.query.close();
> // Execute the query one more time
> executeInternal(cachedQuery, queryParameters, flags);
> }
> }
>
> cachedQuery.query.getSubqueries() != null, how this code decide composite
> queries here ? that mean some query having subquery or having many JOIN
> or LEFT JOINs like select .... A left join B ...
>
> Thanks,
>
> James
>
This is really an issue that needs to be solved in the backend. The error
is coming from PostgreSQL and what should happen is that when you alter a
table that a server prepared statement relies on the backend should send a
message to tell us that all of the prepared statements that rely on are now
invalid and we can reprepare them. Currently the driver has no idea that
you changed the table and the prepared statement will fail so it just
continues to use it.
Dave
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: cached plan must not change result type
@ 2024-03-29 16:21 Tom Lane <tgl@sss.pgh.pa.us>
parent: Dave Cramer <davecramer@postgres.rocks>
0 siblings, 1 reply; 13+ messages in thread
From: Tom Lane @ 2024-03-29 16:21 UTC (permalink / raw)
To: Dave Cramer <davecramer@postgres.rocks>; +Cc: James Pang <jamespang886@gmail.com>; pgsql-jdbc@lists.postgresql.org, Laurenz Albe <laurenz.albe@cybertec.at>
Dave Cramer <davecramer@postgres.rocks> writes:
> This is really an issue that needs to be solved in the backend. The error
> is coming from PostgreSQL and what should happen is that when you alter a
> table that a server prepared statement relies on the backend should send a
> message to tell us that all of the prepared statements that rely on are now
> invalid and we can reprepare them.
This is something that can't change without a wire protocol change.
There is nothing in the protocol that allows the backend to send out
a message like "oh, that Describe I sent you awhile back? It might
be a lie now" at random times. Also, what do you want to do about
race conditions --- that is, what if you fire off an Execute only
to find that one of those messages was already in flight to you?
A non-racy way to handle it might be for Bind/Execute to refuse to
run the query if its output has changed since the last Describe,
which we could check after acquiring table locks during Bind.
But we'd want to define "refuse" in a way that doesn't abort the
transaction, and that's a concept that doesn't exist in the
protocol at all.
regards, tom lane
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: cached plan must not change result type
@ 2024-03-29 16:48 Dave Cramer <davecramer@postgres.rocks>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 13+ messages in thread
From: Dave Cramer @ 2024-03-29 16:48 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: James Pang <jamespang886@gmail.com>; pgsql-jdbc@lists.postgresql.org, Laurenz Albe <laurenz.albe@cybertec.at>
On Fri, 29 Mar 2024 at 12:21, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Dave Cramer <davecramer@postgres.rocks> writes:
> > This is really an issue that needs to be solved in the backend. The error
> > is coming from PostgreSQL and what should happen is that when you alter a
> > table that a server prepared statement relies on the backend should send
> a
> > message to tell us that all of the prepared statements that rely on are
> now
> > invalid and we can reprepare them.
>
> This is something that can't change without a wire protocol change.
> There is nothing in the protocol that allows the backend to send out
> a message like "oh, that Describe I sent you awhile back? It might
> be a lie now" at random times.
I agree, but it's a known issue. I'm just pointing that it would be nice to
have.
We'd have to figure out the details.
> Also, what do you want to do about
> race conditions --- that is, what if you fire off an Execute only
> to find that one of those messages was already in flight to you?
> A non-racy way to handle it might be for Bind/Execute to refuse to
> run the query if its output has changed since the last Describe,
> which we could check after acquiring table locks during Bind.
> But we'd want to define "refuse" in a way that doesn't abort the
> transaction, and that's a concept that doesn't exist in the
> protocol at all.
>
This actually sounds like the best option as we wouldn't have to fire off a
message, just refuse to run the Execute in a way that doesn't abort the
transaction.
Dave
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: cached plan must not change result type
@ 2024-03-29 23:42 James Pang <jamespang886@gmail.com>
parent: Dave Cramer <davecramer@postgres.rocks>
0 siblings, 1 reply; 13+ messages in thread
From: James Pang @ 2024-03-29 23:42 UTC (permalink / raw)
To: Dave Cramer <davecramer@postgres.rocks>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-jdbc@lists.postgresql.org, Laurenz Albe <laurenz.albe@cybertec.at>
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.
protected boolean willHealViaReparse(SQLException e) {
if (e == null || e.getSQLState() == null) {
return false;
}
// "prepared statement \"S_2\" does not exist"
if
(PSQLState.INVALID_SQL_STATEMENT_NAME.getState().equals(e.getSQLState())) {
return true;
}
if (!PSQLState.NOT_IMPLEMENTED.getState().equals(e.getSQLState())) {
return false;
}
if (!(e instanceof PSQLException)) {
return false;
}
PSQLException pe = (PSQLException) e;
ServerErrorMessage serverErrorMessage = pe.getServerErrorMessage();
if (serverErrorMessage == null) {
return false;
}
// "cached plan must not change result type"
String routine = serverErrorMessage.getRoutine();
return "RevalidateCachedQuery".equals(routine) // 9.2+
|| "RevalidateCachedPlan".equals(routine); // <= 9.1
}
Thanks,
James
Dave Cramer <davecramer@postgres.rocks> 於 2024年3月30日週六 上午12:48寫道：
>
>
> On Fri, 29 Mar 2024 at 12:21, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
>> Dave Cramer <davecramer@postgres.rocks> writes:
>> > This is really an issue that needs to be solved in the backend. The
>> error
>> > is coming from PostgreSQL and what should happen is that when you alter
>> a
>> > table that a server prepared statement relies on the backend should
>> send a
>> > message to tell us that all of the prepared statements that rely on are
>> now
>> > invalid and we can reprepare them.
>>
>> This is something that can't change without a wire protocol change.
>> There is nothing in the protocol that allows the backend to send out
>> a message like "oh, that Describe I sent you awhile back? It might
>> be a lie now" at random times.
>
>
> I agree, but it's a known issue. I'm just pointing that it would be nice
> to have.
> We'd have to figure out the details.
>
>
>> Also, what do you want to do about
>> race conditions --- that is, what if you fire off an Execute only
>> to find that one of those messages was already in flight to you?
>
>
>> A non-racy way to handle it might be for Bind/Execute to refuse to
>> run the query if its output has changed since the last Describe,
>> which we could check after acquiring table locks during Bind.
>> But we'd want to define "refuse" in a way that doesn't abort the
>> transaction, and that's a concept that doesn't exist in the
>> protocol at all.
>>
>
> This actually sounds like the best option as we wouldn't have to fire off
> a message, just refuse to run the Execute in a way that doesn't abort the
> transaction.
>
> Dave
>
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: cached plan must not change result type
@ 2024-03-30 10:14 Dave Cramer <davecramer@postgres.rocks>
parent: James Pang <jamespang886@gmail.com>
0 siblings, 1 reply; 13+ messages in thread
From: Dave Cramer @ 2024-03-30 10:14 UTC (permalink / raw)
To: James Pang <jamespang886@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-jdbc@lists.postgresql.org, Laurenz Albe <laurenz.albe@cybertec.at>
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.
>
> protected boolean willHealViaReparse(SQLException e) {
> if (e == null || e.getSQLState() == null) {
> return false;
> }
>
> // "prepared statement \"S_2\" does not exist"
> if
> (PSQLState.INVALID_SQL_STATEMENT_NAME.getState().equals(e.getSQLState())) {
> return true;
> }
> if (!PSQLState.NOT_IMPLEMENTED.getState().equals(e.getSQLState())) {
> return false;
> }
>
> if (!(e instanceof PSQLException)) {
> return false;
> }
>
> PSQLException pe = (PSQLException) e;
>
> ServerErrorMessage serverErrorMessage = pe.getServerErrorMessage();
> if (serverErrorMessage == null) {
> return false;
> }
> // "cached plan must not change result type"
> String routine = serverErrorMessage.getRoutine();
> return "RevalidateCachedQuery".equals(routine) // 9.2+
> || "RevalidateCachedPlan".equals(routine); // <= 9.1
> }
>
>
This only works if there was no transaction.
https://github.com/pgjdbc/pgjdbc/blob/0fbd31d43b1013829da3a8f21120d8bb94df803d/pgjdbc/src/main/java/...
Dave
> Thanks,
>
>
> James
>
> Dave Cramer <davecramer@postgres.rocks> 於 2024年3月30日週六 上午12:48寫道：
>
>>
>>
>> On Fri, 29 Mar 2024 at 12:21, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>
>>> Dave Cramer <davecramer@postgres.rocks> writes:
>>> > This is really an issue that needs to be solved in the backend. The
>>> error
>>> > is coming from PostgreSQL and what should happen is that when you
>>> alter a
>>> > table that a server prepared statement relies on the backend should
>>> send a
>>> > message to tell us that all of the prepared statements that rely on
>>> are now
>>> > invalid and we can reprepare them.
>>>
>>> This is something that can't change without a wire protocol change.
>>> There is nothing in the protocol that allows the backend to send out
>>> a message like "oh, that Describe I sent you awhile back? It might
>>> be a lie now" at random times.
>>
>>
>> I agree, but it's a known issue. I'm just pointing that it would be nice
>> to have.
>> We'd have to figure out the details.
>>
>>
>>> Also, what do you want to do about
>>> race conditions --- that is, what if you fire off an Execute only
>>> to find that one of those messages was already in flight to you?
>>
>>
>>> A non-racy way to handle it might be for Bind/Execute to refuse to
>>> run the query if its output has changed since the last Describe,
>>> which we could check after acquiring table locks during Bind.
>>> But we'd want to define "refuse" in a way that doesn't abort the
>>> transaction, and that's a concept that doesn't exist in the
>>> protocol at all.
>>>
>>
>> This actually sounds like the best option as we wouldn't have to fire off
>> a message, just refuse to run the Execute in a way that doesn't abort the
>> transaction.
>>
>> Dave
>>
>
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: cached plan must not change result type
@ 2024-03-30 12:27 Dave Cramer <davecramer@postgres.rocks>
parent: Dave Cramer <davecramer@postgres.rocks>
0 siblings, 1 reply; 13+ messages in thread
From: Dave Cramer @ 2024-03-30 12:27 UTC (permalink / raw)
To: James Pang <jamespang886@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-jdbc@lists.postgresql.org, Laurenz Albe <laurenz.albe@cybertec.at>
On Sat, 30 Mar 2024 at 06:14, Dave Cramer <davecramer@postgres.rocks> 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.
>>
>> protected boolean willHealViaReparse(SQLException e) {
>> if (e == null || e.getSQLState() == null) {
>> return false;
>> }
>>
>> // "prepared statement \"S_2\" does not exist"
>> if
>> (PSQLState.INVALID_SQL_STATEMENT_NAME.getState().equals(e.getSQLState())) {
>> return true;
>> }
>> if (!PSQLState.NOT_IMPLEMENTED.getState().equals(e.getSQLState())) {
>> return false;
>> }
>>
>> if (!(e instanceof PSQLException)) {
>> return false;
>> }
>>
>> PSQLException pe = (PSQLException) e;
>>
>> ServerErrorMessage serverErrorMessage = pe.getServerErrorMessage();
>> if (serverErrorMessage == null) {
>> return false;
>> }
>> // "cached plan must not change result type"
>> String routine = serverErrorMessage.getRoutine();
>> return "RevalidateCachedQuery".equals(routine) // 9.2+
>> || "RevalidateCachedPlan".equals(routine); // <= 9.1
>> }
>>
>>
> This only works if there was no transaction.
>
>
> https://github.com/pgjdbc/pgjdbc/blob/0fbd31d43b1013829da3a8f21120d8bb94df803d/pgjdbc/src/main/java/...
>
>
> Dave
>
>> Thanks,
>>
>
>
>
>>
>>
I think the best option for you is to turn off server side prepared
statements with prepareThreshold=0
Dave
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: cached plan must not change result type
@ 2024-03-30 14:06 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Dave Cramer <davecramer@postgres.rocks>
0 siblings, 1 reply; 13+ messages in thread
From: Laurenz Albe @ 2024-03-30 14:06 UTC (permalink / raw)
To: Dave Cramer <davecramer@postgres.rocks>; James Pang <jamespang886@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-jdbc@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: cached plan must not change result type
@ 2024-03-31 03:30 James Pang <jamespang886@gmail.com>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 1 reply; 13+ messages in thread
From: James Pang @ 2024-03-31 03:30 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: Dave Cramer <davecramer@postgres.rocks>; Tom Lane <tgl@sss.pgh.pa.us>; pgsql-jdbc@lists.postgresql.org
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
>
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: cached plan must not change result type
@ 2024-03-31 10:05 Dave Cramer <davecramer@postgres.rocks>
parent: James Pang <jamespang886@gmail.com>
0 siblings, 0 replies; 13+ messages in thread
From: Dave Cramer @ 2024-03-31 10:05 UTC (permalink / raw)
To: James Pang <jamespang886@gmail.com>; +Cc: Laurenz Albe <laurenz.albe@cybertec.at>; Tom Lane <tgl@sss.pgh.pa.us>; pgsql-jdbc@lists.postgresql.org
On Sat, 30 Mar 2024 at 23:30, James Pang <jamespang886@gmail.com> 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 <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
>>
>
^ permalink raw reply [nested|flat] 13+ messages in thread
end of thread, other threads:[~2024-03-31 10:05 UTC | newest]
Thread overview: 13+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-03-29 02:05 cached plan must not change result type James Pang <jamespang886@gmail.com>
2024-03-29 08:40 ` Laurenz Albe <laurenz.albe@cybertec.at>
2024-03-29 08:56 ` James Pang <jamespang886@gmail.com>
2024-03-29 09:08 ` James Pang <jamespang886@gmail.com>
2024-03-29 15:45 ` Dave Cramer <davecramer@postgres.rocks>
2024-03-29 16:21 ` Tom Lane <tgl@sss.pgh.pa.us>
2024-03-29 16:48 ` Dave Cramer <davecramer@postgres.rocks>
2024-03-29 23:42 ` James Pang <jamespang886@gmail.com>
2024-03-30 10:14 ` Dave Cramer <davecramer@postgres.rocks>
2024-03-30 12:27 ` Dave Cramer <davecramer@postgres.rocks>
2024-03-30 14:06 ` Laurenz Albe <laurenz.albe@cybertec.at>
2024-03-31 03:30 ` James Pang <jamespang886@gmail.com>
2024-03-31 10:05 ` Dave Cramer <davecramer@postgres.rocks>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox