"ERROR: cached plan should not change result type" when mixing DDL with SELECT via JDBC

Question

I am having an interesting problem with PostgreSQL through JDBC (I cannot play it outside of JDBC), where I get

"ERROR: cached plan should not change the type of result"

The easiest way to reproduce this problem is to use 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 the fact that this is a very subtle error in the JDBC driver (note, I just deleted the sixth DDL statement in the package):

 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 seems that dropping all cached plans using DISCARD ALL should work, but this makes the situation 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 came across another error message

"ERROR: prepared statement" S_1 "does not exist"

Does anyone know a workaround? Or a pointer documenting this error? An interesting bit seems to be related to the default training threshold of 5

Answer 1

This is similar to PostgreSQL PREPARE_THRESHOLD , the default for the JDBC driver is 5.

By setting it to zero, it will solve / apply this problem:

  ((PGConnection) connection).setPrepareThreshold(0); 

Further information is also available on this stack overflow question.

Answer 2

Disabling prepared statements is too radical to solve this problem. Now you can solve a specific problem by setting autosave=conservative in the pgjdbc connection settings: fooobar.com/questions/81202 / ...