Java – PostgreSQL error: undeclared due to user request

What causes this error in PostgreSQL?

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

My software version:

Postgresql 9.1. 6 on x86_ 64 RedHat Linux GNU, by GCC (GCC) 4.7 2 20120921 (red hat 4.7.2-2) 64 bit compilation

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 drive. This error occurs randomly and sometimes not at all

Solution

We have found out the cause of the problem It is explained by executing setquerytimeout () in the latest jdbc driver 9.2-100x If you manually open / close the connection, it may not happen, but it usually happens when the connection pool is in place and autocommit is set to false In this case, setquerytimeout () should call a non-zero value (for example, using the spring framework @ transactional (timeout = XXX) annotation)

It turns out that when an SQL exception occurs during statement execution, the cancel timer has not been cancelled and remains active (this is how it is implemented) Because of the pool, the connection is not closed, but returned to the pool Then, when the cancel timer is triggered, it randomly cancels the query of the connection currently associated with the timer At this moment, this is a completely different query that explains the random effect

The suggested solution is to abandon setquerytimeout () and use PostgreSQL configuration (statement_timeout) It does not provide the same flexibility, but at least it is always effective