postgresql Postgres - 错误：准备好的语句“S_1”已经存在
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7611926/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Postgres - ERROR: prepared statement "S_1" already exists
提问by Chris Cashwell
When executing batch queries via JDBC to pgbouncer, I get the following error:
通过 JDBC 对 pgbouncer 执行批处理查询时，出现以下错误：
org.postgresql.util.PSQLException: ERROR: prepared statement "S_1" already exists
I've found bug reports around the web, but they all seem to deal with Postgres 8.3 or below, whereas we're working with Postgres 9.
我在网上找到了错误报告，但它们似乎都处理 Postgres 8.3 或更低版本，而我们正在使用 Postgres 9。
Here's the code that triggers the error:
这是触发错误的代码：
this.getJdbcTemplate().update("delete from xx where username = ?", username);
this.getJdbcTemplate().batchUpdate( "INSERT INTO xx(a, b, c, d, e) " +
"VALUES (?, ?, ?, ?, ?)", new BatchPreparedStatementSetter() {
@Override
public void setValues(PreparedStatement ps, int i) throws SQLException {
ps.setString(1, value1);
ps.setString(2, value2);
ps.setString(3, value3);
ps.setString(4, value4);
ps.setBoolean(5, value5);
}
@Override
public int getBatchSize() {
return something();
}
});
Anyone seen this before?
有人见过这个吗？
Edit 1:
编辑1：
This turned out to be a pgBouncer issue that occurs when using anything other than session pooling. We were using transaction pooling, which apparently can't support prepared statements. By switching to session pooling, we got around the issue.
结果证明这是一个 pgBouncer 问题，在使用session pooling以外的任何东西时都会发生。我们正在使用事务池，它显然不能支持准备好的语句。通过切换到会话池，我们解决了这个问题。
Unfortunately, this isn't a good fix for our use case. We have two separate uses for pgBouncer: one part of our system does bulk updates which are most efficient as prepared statements, and another part needs many connections in very rapid succession. Since pgBouncer doesn't allow switching back and forth between session poolingand transaction pooling, we're forced to run two separate instances on different ports just to support our needs.
不幸的是，这对我们的用例来说不是一个好的解决方案。我们对 pgBouncer 有两种不同的用途：我们系统的一部分进行批量更新，作为准备好的语句最有效，另一部分需要非常快速的连续连接。由于 pgBouncer 不允许在会话池和事务池之间来回切换，我们被迫在不同的端口上运行两个单独的实例来满足我们的需求。
Edit 2:
编辑2：
I ran across this link, where the poster has rolled a patch of his own. We're currently looking at implementing it for our own uses if it proves to be safe and effective.
我看到了这个链接，海报在那里滚动了他自己的补丁。如果它被证明是安全有效的，我们目前正在考虑将其实施为我们自己的用途。
采纳答案by Chris Cashwell
This turned out to be a pgBouncer issue that occurs when using anything other than session pooling. We were using transaction pooling, which apparently can't support prepared statements. By switching to session pooling, we got around the issue.
结果证明这是一个 pgBouncer 问题，在使用session pooling以外的任何东西时都会发生。我们正在使用事务池，它显然不能支持准备好的语句。通过切换到会话池，我们解决了这个问题。
Unfortunately, this isn't a good fix for our use case. We have two separate uses for pgBouncer: one part of our system does bulk updates which are most efficient as prepared statements, and another part needs many connections in very rapid succession. Since pgBouncer doesn't allow switching back and forth between session poolingand transaction pooling, we're forced to either run two separate instances on different ports just to support our needs, or to implement this patch. Preliminary testing shows it to work well, but time will tell if it proves to be safe and effective.
不幸的是，这对我们的用例来说不是一个好的解决方案。我们对 pgBouncer 有两种不同的用途：我们系统的一部分进行批量更新，作为准备好的语句最有效，另一部分需要非常快速的连续连接。由于 pgBouncer 不允许在会话池和事务池之间来回切换，我们被迫在不同的端口上运行两个单独的实例来满足我们的需求，或者实现这个补丁。初步测试表明它运行良好，但时间会证明它是否安全有效。
回答by filiprem
New, Better Answer
新的、更好的答案
To discard session state and effectively forget the "S_1"prepared statement, use server_reset_query option in PgBouncer config.
要丢弃会话状态并有效地忘记“S_1”准备好的语句，请在 PgBouncer 配置中使用 server_reset_query 选项。
Old Answer
旧答案
Switching into session mode is not an ideal solution. Transacion pooling is much more efficient. But for transaction pooling you need stateless DB calls.
切换到会话模式不是理想的解决方案。事务池化效率更高。但是对于事务池，您需要无状态的数据库调用。
I think you have three options:
我认为你有三个选择：
- Disable PS in jdbc driver,
- manually deallocate them in your Java code,
- configure pgbouncer to discard them on transaction end.
- 在 jdbc 驱动程序中禁用 PS，
- 在你的 Java 代码中手动释放它们，
- 配置 pgbouncer 以在事务结束时丢弃它们。
I would try option 1 or option 3 - depending on actual way in which your app uses them.
我会尝试选项 1 或选项 3 - 取决于您的应用程序使用它们的实际方式。
For more info, read the docs:
有关更多信息，请阅读文档：
http://pgbouncer.projects.postgresql.org/doc/config.html(search for server_reset_query),
http://pgbouncer.projects.postgresql.org/doc/config.html（搜索 server_reset_query），
or google for this:
或谷歌这个：
postgresql jdbc +preparethreshold
回答by xuehui
Disabling prepared statements in JDBC. The proper way to do it for JDBC is adding "prepareThreshold=0" parameter to connect string.
在 JDBC 中禁用准备好的语句。为 JDBC 执行此操作的正确方法是将“prepareThreshold=0”参数添加到连接字符串。
jdbc:postgresql://ip:port/db_name?useAffectedRows=true&prepareThreshold=0
