pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Kevin Grittner <Kevin.Grittner@wicourts.gov>
To: pgsql-admin@postgresql.org
Subject: ERROR: canceling query due to user request
Date: Mon, 12 Sep 2005 15:54:43 -0500
Message-ID: <s325a4d1.095@gwmta.wicourts.gov> (raw)

I started with this issue on the pgsql-jdbc list, but was assured that this could not be a bug in the JDBC driver -- it is either a bug in the configuration or runtime environment or it is a server-side bug.  This list seemed like the logical next step.
 
We have seen this behavior in 8.0.3 with the 311 JDBC driver.  Today I tested with the latest snapshot of the 8.1 development code and the tip of the JDBC driver code from the root.  It still happens.
 
The environment for the latest test is:
SuSE Linux 9.3 (i586)
2.6.11.4-21.8-smp #1 SMP Tue Jul 19 12:42:37 UTC 2005
Dual Xeon
Disk on 7 spindle RAID 5 via SAN
All access through JDBC connections.
A single thread accessing the database.
A connection pool in use -- the thread may grab a different connection each time.
No other process concurrently executing against the database.
Happens with or without autovacuum running.
 
Failures have also occured on Windows 2000 and Windows XP servers with local hard drives.  Xeons in all cases.
 
The software involved "optimistically" tries to do a series of inserts, updates, and/or deletes in a single database transaction.  If an exception occurs (for example an insert of a row which already exists) or an update affects zero rows, the transaction is rolled back and the operations are attempted in "cautious" mode -- a commit after each insert update or delete of a single row.
 
The client is running full out, with a steady supply of ready-to-go requests.  Client CPU seems to be the bottleneck, rather than anything on the database server hardware.  We are only seeing this problem in "cautious" mode -- a database transaction has been rolled back and we're committing each statement as we go.
 
If we run the same set of data multiple times, the error occurs on different requests each time, indicating it is not data dependent.  If we turn on logging at the JDBC driver level, it never happens, indicating that it is timing sensitive.  We've never seen it while stepping through the debugger, but since it seems to happen randomly once every few thousand requests, that would be a unlikely anyway.
 
The error is manifest by this message and stack trace:
 
org.postgresql.util.PSQLException: ERROR: canceling query due to user request
	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1499)
	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1284)
	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:175)
	at org.postgresql.jdbc2.AbstractJdbc2Connection.executeTransactionCommand(AbstractJdbc2Connection.java:617)
	at org.postgresql.jdbc2.AbstractJdbc2Connection.commit(AbstractJdbc2Connection.java:637)
	at (our application code)
 
There is no method in the JDBC interface to cancel a commit from the client side, and there is nothing in the client code which is trying to do so.  There are no processes running on the server except services from the SuSE install and the processes started by pg_ctl start.
 
We tried setting the ulimit of everything to unlimited, where allowed.  We set the open files limit to 4096.  These ulimit changes had no affect on the problem.
 
Does anyone have any ideas on a possible cause, or any diagnostic steps we should take?
 
Thanks,
 
-Kevin
 





view thread (14+ messages)  latest in thread

Message-ID: <s325a4d1.095@gwmta.wicourts.gov>
Permalink:  ../s325a4d1.095@gwmta.wicourts.gov/
Also on:    postgresql.org/message-id/s325a4d1.095@gwmta.wicourts.gov

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-admin@postgresql.org
  Cc: Kevin.Grittner@wicourts.gov
  Subject: Re: ERROR: canceling query due to user request
  In-Reply-To: <s325a4d1.095@gwmta.wicourts.gov>
  References: <s325a4d1.095@gwmta.wicourts.gov>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox