Postgres - 错误:准备好的语句“S_1”已经存在

发布于 12-07 04:09 字数 1456 浏览 1 评论 0原文

当通过 JDBC 到 pgbouncer 执行批量查询时,我收到以下错误:

org.postgresql.util.PSQLException: ERROR: prepared statement "S_1" already exists

,但它们似乎都处理 Postgres 8.3 或更低版本,而我们正在使用 Postgres 9。

我在网络上发现了错误报告 触发错误:

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

有人以前见过这个吗?

编辑 1:

事实证明,这是使用会话池以外的任何内容时发生的 pgBouncer 问题。我们使用的是事务池,它显然不能支持准备好的语句。通过切换到会话池,我们解决了这个问题。

不幸的是,这对于我们的用例来说并不是一个好的解决方案。我们对 pgBouncer 有两种不同的用途:我们系统的一部分进行批量更新,这是作为准备好的语句最有效的,而另一部分则需要非常快速地连续进行许多连接。由于 pgBouncer 不允许在会话池事务池之间来回切换,因此我们被迫在不同端口上运行两个单独的实例来满足我们的需求。

编辑2:

我遇到了此链接,发布者在其中推出了自己的补丁。如果证明它是安全有效的,我们目前正在考虑将其用于我们自己的用途。

When executing batch queries via JDBC to pgbouncer, I get the following error:

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.

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:

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.

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 pooling and transaction pooling, we're forced to run two separate instances on different ports just to support our needs.

Edit 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.

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(6

私野2024-12-14 04:09:35

禁用 JDBC 中的预准备语句。
对于 JDBC 来说,正确的方法是在连接字符串中添加“prepareThreshold=0”参数。

jdbc:postgresql://ip:port/db_name?prepareThreshold=0

Disabling prepared statements in JDBC.
The proper way to do it for JDBC is adding "prepareThreshold=0" parameter to connect string.

jdbc:postgresql://ip:port/db_name?prepareThreshold=0
来世叙缘2024-12-14 04:09:35

事实证明,这是使用会话池以外的任何其他方式时发生的 pgBouncer 问题。我们使用的是事务池,它显然不能支持准备好的语句。通过切换到会话池,我们解决了这个问题。

不幸的是,这对于我们的用例来说并不是一个好的解决方案。我们对 pgBouncer 有两种不同的用途:我们系统的一部分进行批量更新,这是作为准备好的语句最有效的,而另一部分则需要非常快速地连续进行许多连接。由于 pgBouncer 不允许在会话池事务池之间来回切换,我们被迫在不同端口上运行两个单独的实例来满足我们的需求,或实施此补丁。初步测试表明它效果良好,但时间会证明它是否安全有效。

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.

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 pooling and 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.

梦与时光遇2024-12-14 04:09:35

新的、更好的答案

要放弃会话状态并有效地忘记“S_1”准备好的语句,请在 PgBouncer 配置中使用 server_reset_query 选项。

旧答案

请参阅http://pgbouncer.projects.postgresql.org/doc/faq。 html#_how_to_use_prepared_statements_with_transaction_pooling

切换到会话模式并不是理想的选择 解决方案。交易池的效率要高得多。但对于事务池,您需要无状态数据库调用。

我认为你有三个选择:

  1. 在 jdbc 驱动程序中禁用 PS,
  2. 在 Java 代码中手动取消分配它们,
  3. 配置 pgbouncer 在事务结束时丢弃它们。

我会尝试选项 1 或选项 3 - 取决于您的应用程序使用它们的实际方式。

有关更多信息,请阅读文档:

http://pgbouncer.projects.postgresql.org/doc /config.html (搜索 server_reset_query),

或通过 google 搜索:

postgresql jdbc +preparethreshold

New, Better Answer

To discard session state and effectively forget the "S_1" prepared statement, use server_reset_query option in PgBouncer config.

Old Answer

See http://pgbouncer.projects.postgresql.org/doc/faq.html#_how_to_use_prepared_statements_with_transaction_pooling

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:

  1. Disable PS in jdbc driver,
  2. manually deallocate them in your Java code,
  3. configure pgbouncer to discard them on transaction end.

I would try option 1 or option 3 - depending on actual way in which your app uses them.

For more info, read the docs:

http://pgbouncer.projects.postgresql.org/doc/config.html (search for server_reset_query),

or google for this:

postgresql jdbc +preparethreshold
饮惑2024-12-14 04:09:35

在我们的案例中,该问题与 pgbouncer 无关。由于我们无法将 prepareThreshold=0 附加到 URL,因此我们修复了以下内容:

查看准备好的语句

select * from pg_prepared_statements;

取消分配有问题的表

select * from pg_prepared_statements;
deallocate "S_1";

In our case the issue was not related to pgbouncer. Since we did were not able to append prepareThreshold=0 to the URL what we did to fix was the following

View the prepared statements

select * from pg_prepared_statements;

Deallocate the faulty table

select * from pg_prepared_statements;
deallocate "S_1";
不顾2024-12-14 04:09:35

从 PgBouncer 1.21.0 开始,它支持事务池模式下名为准备好的语句的协议级别。通过将 PgBouncer 配置文件中的 max_prepared_statements 设置更改为非零值,您可以打开此支持。这应该会使这些错误消失,并允许您仅运行 PgBouncer 的单个实例。有关详细信息,请查看文档: https://www.pgbouncer.org/config.html#max_prepared_statements< /a>

Since PgBouncer 1.21.0 it supports protocol level named prepared statements in transaction pooling mode. By changing the max_prepared_statements setting to a non-zero value in the PgBouncer configuration file, you can turn this support on. That should make these errors go away, and allow yo to run only a single instance of PgBouncer. For details check the docs: https://www.pgbouncer.org/config.html#max_prepared_statements

[旋木]2024-12-14 04:09:35

我遇到了这个问题,我们在事务级别配置了 pgbouncer,我们使用的是 psql 11.8,我们刚刚将 psql jar 升级到最新版本,它得到了修复。

I had this problem, we have pgbouncer configurated in transaction level, we were using psql 11.8, we just upgraded the psql jar to the latest version, it got fixed.

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文