Subject: Prepared statement invalidation

From: "Daniel Heath" <[email-masked]>
Date: Fri, 16 Apr 2021 10:09:38 +1000

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

---

From: Tom Lane <[email-masked]>
Date: Thu, 15 Apr 2021 20:17:01 -0400

> 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

---

From: "Daniel Heath" <[email-masked]>
Date: Fri, 16 Apr 2021 16:16:45 +1000

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