1

Let's say I want to add a column in my table. If I add column in DB without restarting the application, my application starts failing with "cached plan must not change result type" because return type changes for queries doing wildcard select after this column addition.

jdbc postgres driver automatically creates prepared statement after a certain threshold, default threshold value is 5.

Either I can disable it by setting prepareThreshold to 0 which is bad since I'm losing on the benefits of prepared statement optimization driver was doing for me.

Or I'll have to change all my SQL statements to specify the exact list of columns it will operate upon. So, writing statements like "SELECT * FROM TABLE" is not feasible.

Is there any other approach to solve this problem?

aamir
  • 3,753
  • 4
  • 23
  • 34
  • I have the same problem. Do You found any solution? Maybe there is some solution to manually clear cached plan when the error occurs? – Jacek Gzel Mar 10 '17 at 08:46

1 Answers1

4

It is a bad habit to use * in SQL queries (except in certain cases like count(*)) for the reason that queries can suddenly fail or behave differently when a column is added.

If you really want that, you can catch the exception, close the prepared statement and recreate a new one with the same query string.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263