当通过JDBC将DDL与SELECT混合时，“ RROR：缓存的计划不得更改结果类型”

Question

我在PostgreSQL上通过JDBC遇到了一个有趣的问题（目前还无法在JDBC之外重现），

``RROR：缓存的计划不得更改结果类型’‘。

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

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

我遇到另一个错误消息

“ RROR：已准备好的语句“ S_1”不存在”。

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

Answer 1

这似乎与PostgreSQLPREPARE_THRESHOLD有关，后者的JDBC驱动程序默认为5。

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

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

此堆栈溢出问题中也提供了更多信息