From sdandapani@counterpane.com Thu Sep 28 21:51:39 2006 Received: from localhost (wm.hub.org [200.46.204.128]) by postgresql.org (Postfix) with ESMTP id 3453D9FB37B for ; Thu, 28 Sep 2006 18:51:39 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024) with ESMTP id 66630-02 for ; Thu, 28 Sep 2006 21:51:32 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey- Received: from natasha.counterpane.com (natasha.hq.counterpane.com [208.162.171.162]) by postgresql.org (Postfix) with SMTP id DCCD49FB36F for ; Thu, 28 Sep 2006 18:51:31 -0300 (ADT) Received: from kraken.hq.counterpane.com ([172.16.2.55]) by natasha.counterpane.com; Thu, 28 Sep 2006 21:51:29 +0000 (UTC) Received: From bluefish.counterpane.com ([172.16.2.51]) by kraken.cis.local (WebShield SMTP v4.5 MR2); id 1159480288932; Thu, 28 Sep 2006 14:51:28 -0700 Received: from achilles.cis.local ([172.16.2.26]) by bluefish.counterpane.com (PGP Universal service); Thu, 28 Sep 2006 14:51:28 -0700 X-PGP-Universal: processed; by bluefish.counterpane.com on Thu, 28 Sep 2006 14:51:28 -0700 Received: From ca-mail1.cis.local ([172.16.2.54]) by achilles.cis.local (WebShield SMTP v4.5 MR2); id 1159480283567; Thu, 28 Sep 2006 14:51:23 -0700 Content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----_=_NextPart_001_01C6E348.3D6D347F" X-MimeOLE: Produced By Microsoft Exchange V6.5 Subject: number of transactions doubling Date: Thu, 28 Sep 2006 14:51:24 -0700 Message-ID: <6992E470F12A444BB787B5C937B9D4DF060E5AD6@ca-mail1.cis.local> X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: number of transactions doubling Thread-Index: AcbjSD4sosqmTWw9SSy/erkTIoJlww== From: "Sriram Dandapani" To: Cc: X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=HTML_MESSAGE X-Spam-Level: X-Archive-Number: 200609/344 X-Sequence-Number: 23258 This is a multi-part message in MIME format. ------_=_NextPart_001_01C6E348.3D6D347F Content-Type: text/plain; charset="US-ASCII" Content-Transfer-Encoding: quoted-printable Hi =20 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. =20 I noticed the following: =20 When I run the following query =20 select current_timestamp,datname,age(datfrozenxid) from pg_database; =20 The age columns shows say 1,500,000,000 =20 When I run the same query after say 10 minutes, it shows 1,500,600,000 =20 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 =20 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) =20 I expect JDBC Batching to generate fewer transactions depending on batch size. =20 The mystery is why am I seeing double the number of transactions being generated. =20 This is causing transaction id wraparound limits to be approached quicker than I would like. ------_=_NextPart_001_01C6E348.3D6D347F Content-Type: text/html; charset="US-ASCII" Content-Transfer-Encoding: quoted-printable

Hi

 

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

 

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)

 

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.

------_=_NextPart_001_01C6E348.3D6D347F-- From oliver@opencloud.com Thu Sep 28 22:24:46 2006 Received: from localhost (wm.hub.org [200.46.204.128]) by postgresql.org (Postfix) with ESMTP id 926789FB248; Thu, 28 Sep 2006 19:24:46 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024) with ESMTP id 67434-08; Thu, 28 Sep 2006 22:24:42 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey- X-Greylist: from auto-whitelisted by SQLgrey- Received: from yass.opencloud.co.nz (yass.opencloud.co.nz [131.203.101.226]) by postgresql.org (Postfix) with ESMTP id F2CAA9FA17D; Thu, 28 Sep 2006 19:24:41 -0300 (ADT) Received: from 222-153-172-120.jetstream.xtra.co.nz ([222.153.172.120] helo=[10.1.1.3]) by yass.opencloud.co.nz with asmtp (TLS-1.0:DHE_RSA_AES_128_CBC_SHA:16) (Exim 4.34) id 1GT4J2-00055W-Q6; Fri, 29 Sep 2006 10:24:36 +1200 Message-ID: <451C4B9C.9060108@opencloud.com> Date: Thu, 28 Sep 2006 22:24:28 +0000 From: Oliver Jowett User-Agent: Mozilla Thunderbird 0.8 (Windows/20040913) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Sriram Dandapani CC: pgsql-jdbc@postgresql.org, pgsql-admin@postgresql.org Subject: Re: [JDBC] number of transactions doubling References: <6992E470F12A444BB787B5C937B9D4DF060E5AD6@ca-mail1.cis.local> In-Reply-To: <6992E470F12A444BB787B5C937B9D4DF060E5AD6@ca-mail1.cis.local> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests= X-Spam-Level: X-Archive-Number: 200609/348 X-Sequence-Number: 23262 Sriram Dandapani wrote: > 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) > > I expect JDBC Batching to generate fewer transactions depending on > batch size. Have you turned autocommit off? -O From sdandapani@counterpane.com Thu Sep 28 22:53:50 2006 Received: from localhost (wm.hub.org [200.46.204.128]) by postgresql.org (Postfix) with ESMTP id 69CCD9FB2C9 for ; Thu, 28 Sep 2006 19:53:50 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024) with ESMTP id 90961-06 for ; Thu, 28 Sep 2006 22:53:44 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey- Received: from natasha.counterpane.com (natasha.hq.counterpane.com [208.162.171.162]) by postgresql.org (Postfix) with SMTP id 1E6669FA666 for ; Thu, 28 Sep 2006 19:53:43 -0300 (ADT) Received: from kraken.hq.counterpane.com ([172.16.2.55]) by natasha.counterpane.com; Thu, 28 Sep 2006 22:52:21 +0000 (UTC) Received: From bluefish.counterpane.com ([172.16.2.51]) by kraken.cis.local (WebShield SMTP v4.5 MR2); id 1159483940849; Thu, 28 Sep 2006 15:52:20 -0700 Received: from achilles.cis.local ([172.16.2.26]) by bluefish.counterpane.com (PGP Universal service); Thu, 28 Sep 2006 15:52:20 -0700 X-PGP-Universal: processed; by bluefish.counterpane.com on Thu, 28 Sep 2006 15:52:20 -0700 Received: From ca-mail1.cis.local ([172.16.2.54]) by achilles.cis.local (WebShield SMTP v4.5 MR2); id 1159483935552; Thu, 28 Sep 2006 15:52:15 -0700 Content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: text/plain; charset="US-ASCII" Content-Transfer-Encoding: quoted-printable X-MimeOLE: Produced By Microsoft Exchange V6.5 Subject: Re: [JDBC] number of transactions doubling Date: Thu, 28 Sep 2006 15:52:15 -0700 Message-ID: <6992E470F12A444BB787B5C937B9D4DF060E5B05@ca-mail1.cis.local> X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: [JDBC] number of transactions doubling Thread-Index: AcbjTOqC5yeudyztT6eW6eVWr/LWeAAA31dA From: "Sriram Dandapani" To: "Oliver Jowett" Cc: , X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests= X-Spam-Level: X-Archive-Number: 200609/349 X-Sequence-Number: 23263 Autocommit is set to off once a connection is obtained from the jboss pool. It is turned back on when it is closed (so that any idle transactions are committed). I had the following 2 lines in my postgres-ds.xml which I commented Even after this, I am seeing twice the number of transactions. -----Original Message----- From: Oliver Jowett [mailto:oliver@opencloud.com]=20 Sent: Thursday, September 28, 2006 3:24 PM To: Sriram Dandapani Cc: pgsql-jdbc@postgresql.org; pgsql-admin@postgresql.org Subject: Re: [JDBC] number of transactions doubling Sriram Dandapani wrote: > The target table has triggers that route data to appropriate tables. The=20 > tables to which data is routed has check constraints that do further=20 > inserts. (All of this happens in 1 jdbc transaction) >=20 > I expect JDBC Batching to generate fewer transactions depending on=20 > batch size. Have you turned autocommit off? -O From sdandapani@counterpane.com Thu Sep 28 23:04:40 2006 Received: from localhost (wm.hub.org [200.46.204.128]) by postgresql.org (Postfix) with ESMTP id 9DBD09FB2C9 for ; Thu, 28 Sep 2006 20:04:40 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024) with ESMTP id 91919-08 for ; Thu, 28 Sep 2006 23:04:35 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey- Received: from natasha.counterpane.com (natasha.hq.counterpane.com [208.162.171.162]) by postgresql.org (Postfix) with SMTP id 8014D9FB248 for ; Thu, 28 Sep 2006 20:04:35 -0300 (ADT) Received: from kraken.hq.counterpane.com ([172.16.2.55]) by natasha.counterpane.com; Thu, 28 Sep 2006 23:03:44 +0000 (UTC) Received: From bluefish.counterpane.com ([172.16.2.51]) by kraken.cis.local (WebShield SMTP v4.5 MR2); id 1159484623953; Thu, 28 Sep 2006 16:03:43 -0700 Received: from achilles.cis.local ([172.16.2.26]) by bluefish.counterpane.com (PGP Universal service); Thu, 28 Sep 2006 16:03:43 -0700 X-PGP-Universal: processed; by bluefish.counterpane.com on Thu, 28 Sep 2006 16:03:43 -0700 Received: From ca-mail1.cis.local ([172.16.2.54]) by achilles.cis.local (WebShield SMTP v4.5 MR2); id 1159484618822; Thu, 28 Sep 2006 16:03:38 -0700 Content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: text/plain; charset="US-ASCII" Content-Transfer-Encoding: quoted-printable X-MimeOLE: Produced By Microsoft Exchange V6.5 Subject: Re: [JDBC] number of transactions doubling Date: Thu, 28 Sep 2006 16:03:38 -0700 Message-ID: <6992E470F12A444BB787B5C937B9D4DF060E5B0D@ca-mail1.cis.local> X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: [JDBC] number of transactions doubling Thread-Index: AcbjTOqC5yeudyztT6eW6eVWr/LWeAABVrQg From: "Sriram Dandapani" To: "Oliver Jowett" Cc: , X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests= X-Spam-Level: X-Archive-Number: 200609/350 X-Sequence-Number: 23264 FYI..I use the postgres8.1.404 jdbc driver -----Original Message----- From: Oliver Jowett [mailto:oliver@opencloud.com]=20 Sent: Thursday, September 28, 2006 3:24 PM To: Sriram Dandapani Cc: pgsql-jdbc@postgresql.org; pgsql-admin@postgresql.org Subject: Re: [JDBC] number of transactions doubling Sriram Dandapani wrote: > The target table has triggers that route data to appropriate tables. The=20 > tables to which data is routed has check constraints that do further=20 > inserts. (All of this happens in 1 jdbc transaction) >=20 > I expect JDBC Batching to generate fewer transactions depending on=20 > batch size. Have you turned autocommit off? -O From oliver@opencloud.com Thu Sep 28 23:14:01 2006 Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 65CCE9FB248; Thu, 28 Sep 2006 20:14:01 -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 81713-08; Thu, 28 Sep 2006 20:13:57 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- X-Greylist: from auto-whitelisted by SQLgrey- Received: from yass.opencloud.co.nz (yass.opencloud.co.nz [131.203.101.226]) by postgresql.org (Postfix) with ESMTP id 5E78D9FA666; Thu, 28 Sep 2006 20:13:54 -0300 (ADT) Received: from 222-153-172-120.jetstream.xtra.co.nz ([222.153.172.120] helo=[10.1.1.3]) by yass.opencloud.co.nz with asmtp (TLS-1.0:DHE_RSA_AES_128_CBC_SHA:16) (Exim 4.34) id 1GT54f-0005an-QC; Fri, 29 Sep 2006 11:13:50 +1200 Message-ID: <451C5725.4070905@opencloud.com> Date: Thu, 28 Sep 2006 23:13:41 +0000 From: Oliver Jowett User-Agent: Mozilla Thunderbird 0.8 (Windows/20040913) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Sriram Dandapani CC: pgsql-jdbc@postgresql.org, pgsql-admin@postgresql.org Subject: Re: [JDBC] number of transactions doubling References: <6992E470F12A444BB787B5C937B9D4DF060E5B05@ca-mail1.cis.local> In-Reply-To: <6992E470F12A444BB787B5C937B9D4DF060E5B05@ca-mail1.cis.local> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests= X-Spam-Level: X-Archive-Number: 200609/351 X-Sequence-Number: 23265 Sriram Dandapani wrote: > Autocommit is set to off once a connection is obtained from the jboss > pool. It is turned back on when it is closed (so that any idle > transactions are committed). Don't know what to suggest then -- if autocommit is off then the JDBC driver should be sending BEGIN before the first query & COMMIT or ROLLBACK when you explicitly delimit a transaction. There's nothing special about batches. You could set loglevel=2 as a JDBC URL parameter to see exactly what the JDBC driver is sending, perhaps. Or turn on logging on the server side, though I'm not sure at exactly which server version the logging of the extended query protocol became useful. -O From jim@nasby.net Fri Sep 29 00:47:46 2006 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) From heikki@enterprisedb.com Fri Sep 29 08:56:18 2006 Received: from localhost (wm.hub.org [200.46.204.128]) by postgresql.org (Postfix) with ESMTP id B294F9FB1FB; Fri, 29 Sep 2006 05:56:18 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024) with ESMTP id 83365-04; Fri, 29 Sep 2006 08:56:14 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey- X-Greylist: from auto-whitelisted by SQLgrey- Received: from webmail.enterprisedb.com (edb04.managed.contegix.com [63.246.7.176]) by postgresql.org (Postfix) with ESMTP id 2C7519FB1D6; Fri, 29 Sep 2006 05:56:13 -0300 (ADT) Received: from [192.168.0.20] (unknown [62.232.55.86]) (using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits)) (Client did not present a certificate) by webmail.enterprisedb.com (Postfix) with ESMTP id B0F41A24A83; Fri, 29 Sep 2006 03:56:09 -0500 (CDT) Message-ID: <451CDFAF.5050804@enterprisedb.com> Date: Fri, 29 Sep 2006 09:56:15 +0100 From: Heikki Linnakangas User-Agent: Thunderbird 1.5.0.5 (X11/20060812) MIME-Version: 1.0 To: "Jim C. Nasby" CC: Sriram Dandapani , pgsql-jdbc@postgresql.org, pgsql-admin@postgresql.org Subject: Re: [JDBC] number of transactions doubling References: <6992E470F12A444BB787B5C937B9D4DF060E5AD6@ca-mail1.cis.local> <20060929004736.GM34238@nasby.net> In-Reply-To: <20060929004736.GM34238@nasby.net> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.135 tagged_above=0 required=5 tests=FORGED_RCVD_HELO X-Spam-Level: X-Archive-Number: 200609/361 X-Sequence-Number: 23275 Jim C. Nasby wrote: > On Thu, Sep 28, 2006 at 02:51:24PM -0700, Sriram Dandapani wrote: > >> 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. > The triggers might use subtransactions. You get implicit subtransactions if have an EXCEPTION clause in a plpgsql function. I'm not sure if there's other things that do that as well. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com From jim@nasby.net Fri Sep 29 18:22:04 2006 Received: from localhost (wm.hub.org [200.46.204.128]) by postgresql.org (Postfix) with ESMTP id 05C999FB2F6; Fri, 29 Sep 2006 15:22:04 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024) with ESMTP id 49719-01; Fri, 29 Sep 2006 18:21:58 +0000 (UTC) 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 139609FB21C; Fri, 29 Sep 2006 15:21:57 -0300 (ADT) Received: from [172.28.169.224] (unknown [12.14.65.2]) (using TLSv1 with cipher RC4-SHA (128/128 bits)) (No client certificate requested) by noel.decibel.org (Postfix) with ESMTP id DBA4656427; Fri, 29 Sep 2006 13:21:55 -0500 (CDT) In-Reply-To: <451CDFAF.5050804@enterprisedb.com> References: <6992E470F12A444BB787B5C937B9D4DF060E5AD6@ca-mail1.cis.local> <20060929004736.GM34238@nasby.net> <451CDFAF.5050804@enterprisedb.com> Mime-Version: 1.0 (Apple Message framework v752.2) Content-Type: text/plain; charset=US-ASCII; delsp=yes; format=flowed Message-Id: <932B486A-84EA-493E-A78E-6FB28871642F@nasby.net> Cc: Sriram Dandapani , pgsql-jdbc@postgresql.org, pgsql-admin@postgresql.org Content-Transfer-Encoding: 7bit From: Jim Nasby Subject: Re: [JDBC] number of transactions doubling Date: Fri, 29 Sep 2006 13:21:52 -0400 To: Heikki Linnakangas X-Mailer: Apple Mail (2.752.2) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests= X-Spam-Level: X-Archive-Number: 200609/376 X-Sequence-Number: 23290 On Sep 29, 2006, at 4:56 AM, Heikki Linnakangas wrote: > Jim C. Nasby wrote: >> On Thu, Sep 28, 2006 at 02:51:24PM -0700, Sriram Dandapani wrote: >> >>> 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. >> > > The triggers might use subtransactions. You get implicit > subtransactions if have an EXCEPTION clause in a plpgsql function. > I'm not sure if there's other things that do that as well. Ahh, I thought that was handled within cmin/cmax. -- Jim Nasby jim@nasby.net EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) From sdandapani@counterpane.com Fri Sep 29 18:36:43 2006 Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 73F229FB2B8 for ; Fri, 29 Sep 2006 15:36:43 -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 92028-10 for ; Fri, 29 Sep 2006 15:36:39 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from natasha.counterpane.com (natasha.hq.counterpane.com [208.162.171.162]) by postgresql.org (Postfix) with SMTP id D611C9FB222 for ; Fri, 29 Sep 2006 15:36:38 -0300 (ADT) Received: from kraken.hq.counterpane.com ([172.16.2.55]) by natasha.counterpane.com; Fri, 29 Sep 2006 18:31:17 +0000 (UTC) Received: From bluefish.counterpane.com ([172.16.2.51]) by kraken.cis.local (WebShield SMTP v4.5 MR2); id 1159554677265; Fri, 29 Sep 2006 11:31:17 -0700 Received: from achilles.cis.local ([172.16.2.26]) by bluefish.counterpane.com (PGP Universal service); Fri, 29 Sep 2006 11:31:17 -0700 X-PGP-Universal: processed; by bluefish.counterpane.com on Fri, 29 Sep 2006 11:31:17 -0700 Received: From ca-mail1.cis.local ([172.16.2.54]) by achilles.cis.local (WebShield SMTP v4.5 MR2); id 1159554671650; Fri, 29 Sep 2006 11:31:11 -0700 Content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: text/plain; charset="US-ASCII" Content-Transfer-Encoding: quoted-printable X-MimeOLE: Produced By Microsoft Exchange V6.5 Subject: Re: [JDBC] number of transactions doubling Date: Fri, 29 Sep 2006 11:31:11 -0700 Message-ID: <6992E470F12A444BB787B5C937B9D4DF060E5C0E@ca-mail1.cis.local> X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: [JDBC] [ADMIN] number of transactions doubling Thread-Index: Acbj9DMC4giGbiXxTdSbZFhZIIp11QAAREgw From: "Sriram Dandapani" To: "Jim Nasby" , "Heikki Linnakangas" Cc: , X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.029 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200609/378 X-Sequence-Number: 23292 The inserts are applied to a table that has triggers. The trigger has an exception clause. Each insert is routed based on certain conditions. Does the presence of an exception clause generate subtransactions regardless of whether exceptions occurred? If so, do subtransactions count towards the transaction id wraparound limit counter? -----Original Message----- From: Jim Nasby [mailto:jim@nasby.net]=20 Sent: Friday, September 29, 2006 10:22 AM To: Heikki Linnakangas Cc: Sriram Dandapani; pgsql-jdbc@postgresql.org; pgsql-admin@postgresql.org Subject: Re: [JDBC] [ADMIN] number of transactions doubling On Sep 29, 2006, at 4:56 AM, Heikki Linnakangas wrote: > Jim C. Nasby wrote: >> On Thu, Sep 28, 2006 at 02:51:24PM -0700, Sriram Dandapani wrote: >> >>> The target table has triggers that route data to appropriate =20 >>> 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 =20 >> within a >> single transaction on the database (unless you're using something =20 >> 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. >> > > The triggers might use subtransactions. You get implicit =20 > subtransactions if have an EXCEPTION clause in a plpgsql function. =20 > I'm not sure if there's other things that do that as well. Ahh, I thought that was handled within cmin/cmax. -- Jim Nasby jim@nasby.net EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) From tgl@sss.pgh.pa.us Fri Sep 29 21:39:29 2006 Received: from localhost (wm.hub.org [200.46.204.128]) by postgresql.org (Postfix) with ESMTP id C6CCB9FA5F7; Fri, 29 Sep 2006 18:39:29 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024) with ESMTP id 93436-03; Fri, 29 Sep 2006 21:39:23 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey- X-Greylist: from auto-whitelisted by SQLgrey- Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 090EB9FB210; Fri, 29 Sep 2006 18:39:22 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.6/8.13.6) with ESMTP id k8TLdHfM003852; Fri, 29 Sep 2006 17:39:17 -0400 (EDT) To: "Sriram Dandapani" cc: "Jim Nasby" , "Heikki Linnakangas" , pgsql-jdbc@postgresql.org, pgsql-admin@postgresql.org Subject: Re: [JDBC] number of transactions doubling In-reply-to: <6992E470F12A444BB787B5C937B9D4DF060E5C0E@ca-mail1.cis.local> References: <6992E470F12A444BB787B5C937B9D4DF060E5C0E@ca-mail1.cis.local> Comments: In-reply-to "Sriram Dandapani" message dated "Fri, 29 Sep 2006 11:31:11 -0700" Date: Fri, 29 Sep 2006 17:39:17 -0400 Message-ID: <3851.1159565957@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200609/382 X-Sequence-Number: 23296 "Sriram Dandapani" writes: > Does the presence of an exception clause generate subtransactions > regardless of whether exceptions occurred? If so, do subtransactions > count towards the transaction id wraparound limit counter? Yes, and yes. regards, tom lane From sdandapani@counterpane.com Fri Sep 29 21:44:31 2006 Received: from localhost (wm.hub.org [200.46.204.128]) by postgresql.org (Postfix) with ESMTP id 194B29FB222 for ; Fri, 29 Sep 2006 18:44:31 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024) with ESMTP id 91110-07 for ; Fri, 29 Sep 2006 21:44:27 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey- Received: from natasha.counterpane.com (natasha.hq.counterpane.com [208.162.171.162]) by postgresql.org (Postfix) with SMTP id 71C899FB210 for ; Fri, 29 Sep 2006 18:44:26 -0300 (ADT) Received: from kraken.hq.counterpane.com ([172.16.2.55]) by natasha.counterpane.com; Fri, 29 Sep 2006 21:44:22 +0000 (UTC) Received: From bluefish.counterpane.com ([172.16.2.51]) by kraken.cis.local (WebShield SMTP v4.5 MR2); id 1159566261974; Fri, 29 Sep 2006 14:44:21 -0700 Received: from achilles.cis.local ([172.16.2.26]) by bluefish.counterpane.com (PGP Universal service); Fri, 29 Sep 2006 14:44:21 -0700 X-PGP-Universal: processed; by bluefish.counterpane.com on Fri, 29 Sep 2006 14:44:21 -0700 Received: From ca-mail1.cis.local ([172.16.2.54]) by achilles.cis.local (WebShield SMTP v4.5 MR2); id 1159566256705; Fri, 29 Sep 2006 14:44:16 -0700 Content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: text/plain; charset="US-ASCII" Content-Transfer-Encoding: quoted-printable X-MimeOLE: Produced By Microsoft Exchange V6.5 Subject: Re: [JDBC] number of transactions doubling Date: Fri, 29 Sep 2006 14:44:15 -0700 Message-ID: <6992E470F12A444BB787B5C937B9D4DF060E5C96@ca-mail1.cis.local> X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: [JDBC] [ADMIN] number of transactions doubling Thread-Index: AcbkD73jaIboJ8+JSXK7EivKs85oJQAAIaow From: "Sriram Dandapani" To: "Tom Lane" Cc: "Jim Nasby" , "Heikki Linnakangas" , , X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests= X-Spam-Level: X-Archive-Number: 200609/384 X-Sequence-Number: 23298 Thanks for the clarification(I was puzzled on why the xid count was really high) That probably explains why my xid counter rapidly increases. -----Original Message----- From: Tom Lane [mailto:tgl@sss.pgh.pa.us]=20 Sent: Friday, September 29, 2006 2:39 PM To: Sriram Dandapani Cc: Jim Nasby; Heikki Linnakangas; pgsql-jdbc@postgresql.org; pgsql-admin@postgresql.org Subject: Re: [JDBC] [ADMIN] number of transactions doubling=20 "Sriram Dandapani" writes: > Does the presence of an exception clause generate subtransactions > regardless of whether exceptions occurred? If so, do subtransactions > count towards the transaction id wraparound limit counter? Yes, and yes. regards, tom lane