通过JDBC将DDL与SELECT混合时出现“错误：缓存的计划不得更改结果类型”

Question

我在JDBC上遇到了一个与PostgreSQL有关的有趣问题（暂时无法在JDBC之外重现它），

“错误：缓存的计划不得更改结果类型”

重现此问题的最简单方法是使用以下代码：

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();
    }

以下代码可以正常工作的事实使我假设这是JDBC驱动程序中的一个非常微妙的错误（请注意，我只是在批处理中删除了第六条DDL语句）：

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();
    }

似乎可以通过丢弃所有缓存的计划DISCARD ALL，但这会使情况变得更糟：

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();
    }

我遇到另一个错误消息

“错误：准备好的语句“ S_1”不存在”

有谁知道解决方法？还是记录此错误的指针？有趣的是，它似乎与默认的准备阈值5有关

Answer 1

将其设置为零将解决/解决此特定问题：

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