X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id D75DCD8C20 for ; Mon, 12 Sep 2005 17:55:06 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 80222-05 for ; Mon, 12 Sep 2005 20:55:02 +0000 (GMT) Received: from gwmta.wicourts.gov (gwmta.wicourts.gov [165.219.244.91]) by svr1.postgresql.org (Postfix) with ESMTP id 81975D8C28 for ; Mon, 12 Sep 2005 17:54:56 -0300 (ADT) Received: from Courts-MTA by gwmta.wicourts.gov with Novell_GroupWise; Mon, 12 Sep 2005 15:54:57 -0500 Message-Id: X-Mailer: Novell GroupWise Internet Agent 6.5.4 Date: Mon, 12 Sep 2005 15:54:43 -0500 From: "Kevin Grittner" To: Subject: ERROR: canceling query due to user request Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: quoted-printable Content-Disposition: inline X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.005 required=5 tests=[AWL=0.005] X-Spam-Level: X-Archive-Number: 200509/111 X-Sequence-Number: 18848 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. =20 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. =20 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. =20 Failures have also occured on Windows 2000 and Windows XP servers with = local hard drives. Xeons in all cases. =20 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. =20 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. =20 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. =20 The error is manifest by this message and stack trace: =20 org.postgresql.util.PSQLException: ERROR: canceling query due to user = request at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(Qu= eryExecutorImpl.java:1499) at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExe= cutorImpl.java:1284) at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorIm= pl.java:175) at org.postgresql.jdbc2.AbstractJdbc2Connection.executeTransactionC= ommand(AbstractJdbc2Connection.java:617) at org.postgresql.jdbc2.AbstractJdbc2Connection.commit(AbstractJdbc= 2Connection.java:637) at (our application code) =20 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. =20 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. =20 Does anyone have any ideas on a possible cause, or any diagnostic steps we = should take? =20 Thanks, =20 -Kevin =20