I'm experiencing an interesting issue with PostgreSQL via JDBC (couldn't reproduce it outside of JDBC yet) where I'm getting an

“ERROR: cached plan must not change result type”

The simplest way to reproduce this issue is by using the following code:

Connection c = getConnection(); c.setAutoCommit(true); List<String> statements = Arrays.asList( "create table t(a int)", "select * from t", "alter table t add b int", "select * from t", "alter table t add c int", "select * from t", "alter table t add d int", "select * from t", "alter table t add e int", "select * from t", "alter table t add f int", "select * from t" ); for (String statement : statements) try (PreparedStatement s = c.prepareStatement(statement)) { System.out.println(s); s.execute(); }

The fact that the following code works fine leads to me assuming this is a very subtle bug in the JDBC driver (note, I've simply removed the sixth DDL statement in the batch):

Connection c = getConnection(); c.setAutoCommit(true); List<String> statements = Arrays.asList( "create table t(a int)", "select * from t", "alter table t add b int", "select * from t", "alter table t add c int", "select * from t", "alter table t add d int", "select * from t", "alter table t add e int", "select * from t" ); for (String statement : statements) try (PreparedStatement s = c.prepareStatement(statement)) { System.out.println(s); s.execute(); }

It would appear that discarding all cached plans via DISCARD ALL should work, but it makes things worse:

Connection c = getConnection(); c.setAutoCommit(true); List<String> statements = Arrays.asList( "create table t(a int)", "select * from t", "alter table t add b int", "select * from t", "alter table t add c int", "select * from t", "alter table t add d int", "select * from t", "alter table t add e int", "select * from t", "alter table t add f int", "discard all", "select * from t" ); for (String statement : statements) try (PreparedStatement s = c.prepareStatement(statement)) { System.out.println(s); s.execute(); }

I'm running into another error message

“ERROR: prepared statement "S_1" doesn't exist”

Does anyone know a workaround? Or a pointer documenting this bug? Interesting bit, it seems to be related to the default prepare threshold of 5