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

Question

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

Answer 1

This seems to be related to PostgreSQL's PREPARE_THRESHOLD, which defaults to 5 for the JDBC driver.

Setting it to zero will solve / work around this particular issue:

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

More info is also available in this stack overflow question

Answer 2

Disabling prepared statements is too drastic an action to fix this issue. You can now solve the specific problem by setting autosave=conservative on the pgjdbc connection settings, see: https://stackoverflow.com/a/48536394/924597