postgresql错误：由于用户请求取消声明 [英] postgresql error: canceling statement due to user request

问题描述

在postgresql中导致此错误的原因

  org.postgresql.util.PSQLException：ERROR ：由于用户请求取消声明
  

我的软件版本

PostgreSQL 由gcc（GCC）编译的x86_64-redhat-linux-gnu上的9.1.6 4.7.2 20120921（Red Hat 4.7.2-2） ，64位。

我的postgresql驱动程序是： postgresql-9.2-1000.jdbc4.jar

使用java版本： Java 1.7

$ b $线索：我的postgresql数据库是在一个固态硬盘驱动器，这个错误是随机发生的，有的时候根本就没有。

解决方案

p>我们已经弄清楚了这个问题的原因，它是通过在最新的JDBC驱动程序9.2-100x中执行setQueryTimeout（）来解释的。如果您手动打开/关闭连接可能不会发生，但是通常会发生连接池地点和自动提交设置为 false 。在这种情况下，应该使用非零值调用setQueryTimeout（）（例如，使用Spring框架@Transactional（timeout = xxx）注释）。

在执行语句时，每当发生SQL异常时，取消定时器尚未被取消并保持活动（这就是实现）。因为池，连接后面没有关闭，而是返回到池。
以后，当取消定时器触发时，它会随机取消当前与该定时器相关联的连接的查询。在这一刻，这是一个完全不同的查询，它解释了随机效应。

建议的解决方法是放弃setQueryTimeout（）并使用PostgreSQL配置（statement_timeout）。它不提供相同的灵活性，但至少总是有效。

What causes this error in postgresql?

org.postgresql.util.PSQLException: ERROR: canceling statement due to user request

My Software Versions:

PostgreSQL 9.1.6 on x86_64-redhat-linux-gnu, compiled by gcc (GCC) 4.7.2 20120921 (Red Hat 4.7.2-2), 64-bit".

My postgresql driver is: postgresql-9.2-1000.jdbc4.jar

Using java version: Java 1.7

Clue: My postgresql database is on a solid state hard drive and this error happens randomly and sometimes not at all.

解决方案

We have figured out the the cause of this issue. It's explained by buggy implementation of setQueryTimeout() in latest JDBC drivers 9.2-100x. It might not happen if you open / close connection manually, but very often happens with connection pooling in place and autocommit set to false. In this case, setQueryTimeout() should be called with non-zero value (as an example, using Spring framework @Transactional( timeout = xxx ) annotation).

It turns out, whenever SQL exception is raised during the statement execution, the cancellation timer hasn't been cancelled and stays alive (that's how it is implemented). Because of pooling, connection behind is not closed but is returned to the pool. Later on, when cancellation timer triggers, it randomly cancels the query currently associated with the connection this timer has been created with. At this moment, it's a totally different query which explains the randomness effect.

The suggested workaround is to give up on setQueryTimeout() and use PostgreSQL configuration instead (statement_timeout). It doesn't provide same level of flexibility but at least always works.

这篇关于postgresql错误：由于用户请求取消声明的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！