“错误：缓存计划不得更改结果类型”通过JDBC将DDL与SELECT混合时 [英] "ERROR: cached plan must not change result type" when mixing DDL with SELECT via JDBC

问题描述

我正在通过JDBC遇到一个有趣的PostgreSQL问题（无法在JDBC之外再现它），我得到的是

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

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

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

似乎通过 DISCARD ALL 应该有效，但是它会让事情变得更糟：

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

我遇到了另一条错误消息

错误：预备语句S_1不存在

"ERROR: prepared statement "S_1" doesn't exist"

有没有人知道解决方法？还是一个记录这个bug的指针？有趣的是，它似乎与默认准备门槛为5 有关

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

推荐答案

这似乎与 PostgreSQL的 PREPARE_THRESHOLD ，对于JDBC驱动程序，默认为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);

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

这篇关于“错误：缓存计划不得更改结果类型”通过JDBC将DDL与SELECT混合时的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！