Postgresql Error: Canceling Statement Due to User Request

This assumes that the race condition bug in the jdbc jar file for postgresql is responsible for the above error.

Workaround 1, refresh connection to database periodically

One workaround is to close the connection to the database and create a new connection to the database periodically. After every few thousand sql statements just close the connection and re-create it. Then for some reason this error is no longer thrown.

Workaround 2, turn on logging

If you turn on logging at the JDBC driver level when you are setting the driver, then in some situations the race condition problem is neutralized:

Class.forName("org.postgresql.Driver");
org.postgresql.Driver.setLogLevel(org.postgresql.Driver.DEBUG);

Workaround 3, catch the exception and re-initialize connection

You could also try catching the specific exception, re-initializing the connection and trying the query again.

Workaround 4, wait until postgresql jdbc jar comes out with a bug fix

I think the problem may be associated with the speed of my SSD hard drive. If you get this error, please post how to reproduce it consistently here, there are devs very interested in squashing this bug.

If you are getting this error without using transactions

The user has requested the statement be cancelled. The statement is doing exactly what it is told to do. The question is, who requested this statement be cancelled?

Look at every line in your code which prepares the SQL for execution. You could have some method that applies to the statement which cancels the statement under some circumstances, like this:

statement = conn.createStatement();
conn.setAutoCommit(true);
statement.setQueryTimeout(25);
my_insert_statement.setString(1, "moobars");

my_insert_statement.executeUpdate();
statement.close();

In my case, what happened was I had set the query timeout to 25 seconds, and when the insert took longer than that. It passed the 'canceling statement due to user request' exception.

If you are getting this error while using transactions:

If you receive this Exception, double check all your code that does SQL transactions.

If you have a query that is in a transaction and you forget to commit, and then you use that connection to do something else where you operate as if you are not in a transaction, there could be undefined behavior which produces this Exception.

Make sure all code that does a transaction is cleaning up after itself. Make sure the transaction begins, work is done, more work is done, and the transaction is rolled back or committed, then make sure the connection is left in the autocommit=true state.

If this is your problem, then the Exception is not thrown where you have forgotten to clean up after yourself, it happens somewhere long after you have failed to clean up after a transaction, making this an elusive exception to track down. Refreshing the connection (closing it and getting a new one) will clear it up.

In addition to Eric's suggestions, you can see statement cancels when:

• An adminisrator or another connection logged in as the same user uses pg_cancel_backend to ask your session to cancel its current statement
• The administrator sends a signal to the PostgreSQL backend that's running your statement
• The administrator requests a fast shutdown or restart of the PostgreSQL server

Check for cron jobs or load management tools that might be cancelling long-running queries.