通过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

禁用准备好的语句对于解决此问题来说是一个过于激烈的操作。您现在可以通过在pgjdbc连接设置上设置autosave=conservative来解决特定问题，请参阅：https://stackoverflow.com/a/48536394/924597

Answer 2

这似乎与PostgreSQL的PREPARE_THRESHOLD有关，JDBC驱动程序的默认值为5。

将其设置为零将解决/解决这个特殊问题：

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

更多信息也可在此堆栈溢出问题