"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

Have you tried with other variable names? Perhaps `f` is some sort of reserved keyword? Also, complete stack traces would be helpful. — Andy Guibert, Dec 09 '15 at 14:30
@a_horse_with_no_name: It is, but this is the simplified version of the real problem where removing the prepared statement from the game is less easy — Lukas Eder, Dec 09 '15 at 14:31
@aguibert ;) Good point. I tried, but no. The real version uses quotes around names anyway... — Lukas Eder, Dec 09 '15 at 14:32
have you checked this question? Looks like a possible dup: http://stackoverflow.com/questions/2783813/postgres-8-3-error-cached-plan-must-not-change-result-type — Andy Guibert, Dec 09 '15 at 14:39
@aguibert: I have indeed, and it isn't a duplicate, at least not from the accepted answer (by the OP) which claims that stuff happened in parallel. In my case, all statements are executed in a single session. — Lukas Eder, Dec 09 '15 at 14:41

score 5 · Accepted Answer · edited May 23 '17 at 12:24

5

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

edited May 23 '17 at 12:24

Community

1
1

answered Dec 09 '15 at 15:08

Lukas Eder

211,314
129
689
1,509

1

You can also change this through an URL parameter: https://jdbc.postgresql.org/documentation/94/connect.html#connection-parameters – Dec 09 '15 at 15:55

score 3 · Answer 2 · answered Jan 31 '18 at 07:26

3

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

answered Jan 31 '18 at 07:26

Shorn

19,077
15
90
168

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

2 Answers2

Linked

Related