public inbox for [email protected]help / color / mirror / Atom feed
Prepared statement invalidation 3+ messages / 2 participants [nested] [flat]
* Prepared statement invalidation @ 2021-04-16 00:09 Daniel Heath <[email protected]> 0 siblings, 1 reply; 3+ messages in thread From: Daniel Heath @ 2021-04-16 00:09 UTC (permalink / raw) To: [email protected] 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 statement query 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 ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Prepared statement invalidation @ 2021-04-16 00:17 Tom Lane <[email protected]> parent: Daniel Heath <[email protected]> 0 siblings, 1 reply; 3+ messages in thread From: Tom Lane @ 2021-04-16 00:17 UTC (permalink / raw) To: Daniel Heath <[email protected]>; +Cc: [email protected] "Daniel Heath" <[email protected]> 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 statement query 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 ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Prepared statement invalidation @ 2021-04-16 06:16 Daniel Heath <[email protected]> parent: Tom Lane <[email protected]> 0 siblings, 0 replies; 3+ messages in thread From: Daniel Heath @ 2021-04-16 06:16 UTC (permalink / raw) To: Tom Lane <[email protected]>; +Cc: [email protected] Is there a transaction isolation mode that would help with the specific case of adding a column to a table? EG to prevent a prepared statement from seeing the new schema? Thanks, Daniel Heath On Fri, Apr 16, 2021, at 10:17 AM, Tom Lane wrote: > "Daniel Heath" <[email protected]> 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 statement query 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 > > ^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2021-04-16 06:16 UTC | newest] Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2021-04-16 00:09 Prepared statement invalidation Daniel Heath <[email protected]> 2021-04-16 00:17 ` Tom Lane <[email protected]> 2021-04-16 06:16 ` Daniel Heath <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox