Thread: Prepared statement invalidation

Hi All,

I'm running a site that has a reasonable amount of traffic 24/7 , writes and reads.

I've found that when I add a new column, I sometimes get a spike of errors on the application side due to prepared
statementquery plans getting invalidated, causing transactions to rollback.
 

The error is:

ERROR: cached plan must not change result type 

Is there a way to make postgres recalculate the query plan instead of failing the transaction when a new column is
added?

Thanks,
Daniel Heath

"Daniel Heath" <daniel@heath.cc> writes:
> I've found that when I add a new column, I sometimes get a spike of errors on the application side due to prepared
statementquery plans getting invalidated, causing transactions to rollback. 
> The error is:
> ERROR: cached plan must not change result type
> Is there a way to make postgres recalculate the query plan instead of failing the transaction when a new column is
added?

No.  It'd be easy enough to remove that restriction on the server side,
but we're afraid that it would break applications, which probably aren't
expecting the result rowtype of a prepared query to be different from what
they were told (perhaps only milliseconds earlier).

I'd say the short answer is "don't use prepared queries, or if you do,
spell out the columns you want instead of saying SELECT *".

            regards, tom lane




> Thanks,
> Daniel Heath

Is there a transaction isolation mode that would help with the specific case of adding a column to a table? EG to
preventa prepared statement from seeing the new schema?
 

Thanks,
Daniel Heath

On Fri, Apr 16, 2021, at 10:17 AM, Tom Lane wrote:
> "Daniel Heath" <daniel@heath.cc> writes:
> > I've found that when I add a new column, I sometimes get a spike of errors on the application side due to prepared
statementquery plans getting invalidated, causing transactions to rollback.
 
> > The error is:
> > ERROR: cached plan must not change result type 
> > Is there a way to make postgres recalculate the query plan instead of failing the transaction when a new column is
added?
> 
> No.  It'd be easy enough to remove that restriction on the server side,
> but we're afraid that it would break applications, which probably aren't
> expecting the result rowtype of a prepared query to be different from what
> they were told (perhaps only milliseconds earlier).
> 
> I'd say the short answer is "don't use prepared queries, or if you do,
> spell out the columns you want instead of saying SELECT *".
> 
>             regards, tom lane
> 
> 
> 
> 
> > Thanks,
> > Daniel Heath
> 
>