Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 851B29FA5CE; Thu, 28 Sep 2006 21:47:46 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 08425-10; Thu, 28 Sep 2006 21:47:41 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- X-Greylist: from auto-whitelisted by SQLgrey- Received: from noel.decibel.org (noel.decibel.org [67.100.216.10]) by postgresql.org (Postfix) with ESMTP id CC4D49F9EF3; Thu, 28 Sep 2006 21:47:40 -0300 (ADT) Received: by noel.decibel.org (Postfix, from userid 1001) id 3A09156427; Thu, 28 Sep 2006 19:47:39 -0500 (CDT) Received: (hashcash-sendmail, from uid 1001); Thu, 28 Sep 2006 19:47:36 -0500 Date: Thu, 28 Sep 2006 19:47:36 -0500 From: "Jim C. Nasby" To: Sriram Dandapani Cc: pgsql-jdbc@postgresql.org, pgsql-admin@postgresql.org Subject: Re: number of transactions doubling Message-ID: <20060929004736.GM34238@nasby.net> References: <6992E470F12A444BB787B5C937B9D4DF060E5AD6@ca-mail1.cis.local> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <6992E470F12A444BB787B5C937B9D4DF060E5AD6@ca-mail1.cis.local> X-Operating-System: FreeBSD 6.0-RELEASE-p4 amd64 X-Distributed: Join the Effort! http://www.distributed.net User-Agent: Mutt/1.5.11 X-Hashcash: 1:20:060929:sdandapani@counterpane.com::GH/GZHwfYiefXPtG:00000000000 000000000000000000000000DyY/ X-Hashcash: 1:20:060929:pgsql-jdbc@postgresql.org::V4PjOGMT7O7nI9Jq:000000000000 000000000000000000000000AkCA X-Hashcash: 1:20:060929:pgsql-admin@postgresql.org::0CNvDbjR5IrcbuUB:00000000000 0000000000000000000000005XfT X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.053 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200609/353 X-Sequence-Number: 23267 On Thu, Sep 28, 2006 at 02:51:24PM -0700, Sriram Dandapani wrote: > I have a strange problem with the number of transactions generated > within my application. I use jdbc batching to submit insert statements > from a jboss app server to postgres 8.1.2. > > A batch can have from 100 to 3000 inserts. > > I noticed the following: > > When I run the following query > > select current_timestamp,datname,age(datfrozenxid) from pg_database; > > The age columns shows say 1,500,000,000 > > When I run the same query after say 10 minutes, it shows 1,500,600,000 > > I have issued about 40,000 inserts via jdbc batching in 10 minutes. > > It appears that the jdbc driver is generating twice the number of > transactions as inserts. This behaviour appears consistent with the > number of inserts that I generate Uh... you're inserting 40k rows and getting 600k transactions... how are you concluding that the jdbc driver is generating 80k transactions? > The target table has triggers that route data to appropriate tables. The > tables to which data is routed has check constraints that do further > inserts. (All of this happens in 1 jdbc transaction) Actually, no matter what JDBC is doing, all of that will happen within a single transaction on the database (unless you're using something like dblink from within the triggers). So even if you were issuing insert statements with autocommit on, you'd see at most one transaction per insert. > I expect JDBC Batching to generate fewer transactions depending on > batch size. > > The mystery is why am I seeing double the number of transactions being > generated. > > This is causing transaction id wraparound limits to be approached > quicker than I would like. As for your autocommit bit-flipping, why don't you just issue either a rollback or a commit when you release the connection? -- Jim Nasby jim@nasby.net EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)