pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
number of transactions doubling
11+ messages / 6 participants
[nested] [flat]

* number of transactions doubling
@ 2006-09-28 21:51 Sriram Dandapani <sdandapani@counterpane.com>
  2006-09-28 22:24 ` Re: [JDBC] number of transactions doubling Oliver Jowett <oliver@opencloud.com>
  2006-09-29 00:47 ` Re: number of transactions doubling Jim C. Nasby <jim@nasby.net>
  0 siblings, 2 replies; 11+ messages in thread

From: Sriram Dandapani @ 2006-09-28 21:51 UTC (permalink / raw)
  To: pgsql-jdbc@postgresql.org; +Cc: pgsql-admin

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.

^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: [JDBC] number of transactions doubling
  2006-09-28 21:51 number of transactions doubling Sriram Dandapani <sdandapani@counterpane.com>
@ 2006-09-28 22:24 ` Oliver Jowett <oliver@opencloud.com>
  1 sibling, 0 replies; 11+ messages in thread

From: Oliver Jowett @ 2006-09-28 22:24 UTC (permalink / raw)
  To: Sriram Dandapani <sdandapani@counterpane.com>; +Cc: pgsql-jdbc@postgresql.org; pgsql-admin

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



^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: number of transactions doubling
  2006-09-28 21:51 number of transactions doubling Sriram Dandapani <sdandapani@counterpane.com>
@ 2006-09-29 00:47 ` Jim C. Nasby <jim@nasby.net>
  2006-09-29 08:56   ` Re: [JDBC] number of transactions doubling Heikki Linnakangas <heikki@enterprisedb.com>
  1 sibling, 1 reply; 11+ messages in thread

From: Jim C. Nasby @ 2006-09-29 00:47 UTC (permalink / raw)
  To: Sriram Dandapani <sdandapani@counterpane.com>; +Cc: pgsql-jdbc@postgresql.org; pgsql-admin

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)



^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: [JDBC] number of transactions doubling
  2006-09-28 21:51 number of transactions doubling Sriram Dandapani <sdandapani@counterpane.com>
  2006-09-29 00:47 ` Re: number of transactions doubling Jim C. Nasby <jim@nasby.net>
@ 2006-09-29 08:56   ` Heikki Linnakangas <heikki@enterprisedb.com>
  2006-09-29 17:21     ` Re: [JDBC] number of transactions doubling Jim Nasby <jim@nasby.net>
  0 siblings, 1 reply; 11+ messages in thread

From: Heikki Linnakangas @ 2006-09-29 08:56 UTC (permalink / raw)
  To: Jim C. Nasby <jim@nasby.net>; +Cc: Sriram Dandapani <sdandapani@counterpane.com>; pgsql-jdbc@postgresql.org; pgsql-admin

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




^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: [JDBC] number of transactions doubling
  2006-09-28 21:51 number of transactions doubling Sriram Dandapani <sdandapani@counterpane.com>
  2006-09-29 00:47 ` Re: number of transactions doubling Jim C. Nasby <jim@nasby.net>
  2006-09-29 08:56   ` Re: [JDBC] number of transactions doubling Heikki Linnakangas <heikki@enterprisedb.com>
@ 2006-09-29 17:21     ` Jim Nasby <jim@nasby.net>
  0 siblings, 0 replies; 11+ messages in thread

From: Jim Nasby @ 2006-09-29 17:21 UTC (permalink / raw)
  To: Heikki Linnakangas <heikki@enterprisedb.com>; +Cc: Sriram Dandapani <sdandapani@counterpane.com>; pgsql-jdbc@postgresql.org; pgsql-admin

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)





^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: [JDBC] number of transactions doubling
@ 2006-09-28 22:52 Sriram Dandapani <sdandapani@counterpane.com>
  2006-09-28 23:13 ` Re: [JDBC] number of transactions doubling Oliver Jowett <oliver@opencloud.com>
  0 siblings, 1 reply; 11+ messages in thread

From: Sriram Dandapani @ 2006-09-28 22:52 UTC (permalink / raw)
  To: Oliver Jowett <oliver@opencloud.com>; +Cc: pgsql-jdbc@postgresql.org; pgsql-admin

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

<!--
<new-connection-sql>select 1</new-connection-sql>
-->
<!--
<check-valid-connection-sql>select 1</check-valid-connection-sql>
-->

Even after this, I am seeing twice the number of transactions.

-----Original Message-----
From: Oliver Jowett [mailto:oliver@opencloud.com] 
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 
> 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



^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: [JDBC] number of transactions doubling
  2006-09-28 22:52 Re: [JDBC] number of transactions doubling Sriram Dandapani <sdandapani@counterpane.com>
@ 2006-09-28 23:13 ` Oliver Jowett <oliver@opencloud.com>
  0 siblings, 0 replies; 11+ messages in thread

From: Oliver Jowett @ 2006-09-28 23:13 UTC (permalink / raw)
  To: Sriram Dandapani <sdandapani@counterpane.com>; +Cc: pgsql-jdbc@postgresql.org; pgsql-admin

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



^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: [JDBC] number of transactions doubling
@ 2006-09-28 23:03 Sriram Dandapani <sdandapani@counterpane.com>
  0 siblings, 0 replies; 11+ messages in thread

From: Sriram Dandapani @ 2006-09-28 23:03 UTC (permalink / raw)
  To: Oliver Jowett <oliver@opencloud.com>; +Cc: pgsql-jdbc@postgresql.org; pgsql-admin

FYI..I use the postgres8.1.404 jdbc driver

-----Original Message-----
From: Oliver Jowett [mailto:oliver@opencloud.com] 
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 
> 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



^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: [JDBC] number of transactions doubling
@ 2006-09-29 18:31 Sriram Dandapani <sdandapani@counterpane.com>
  2006-09-29 21:39 ` Re: [JDBC] number of transactions doubling Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 11+ messages in thread

From: Sriram Dandapani @ 2006-09-29 18:31 UTC (permalink / raw)
  To: Jim Nasby <jim@nasby.net>; Heikki Linnakangas <heikki@enterprisedb.com>; +Cc: pgsql-jdbc@postgresql.org; pgsql-admin

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] 
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  
>>> 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)





^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: [JDBC] number of transactions doubling
  2006-09-29 18:31 Re: [JDBC] number of transactions doubling Sriram Dandapani <sdandapani@counterpane.com>
@ 2006-09-29 21:39 ` Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 11+ messages in thread

From: Tom Lane @ 2006-09-29 21:39 UTC (permalink / raw)
  To: Sriram Dandapani <sdandapani@counterpane.com>; +Cc: Jim Nasby <jim@nasby.net>; Heikki Linnakangas <heikki@enterprisedb.com>; pgsql-jdbc@postgresql.org; pgsql-admin

"Sriram Dandapani" <sdandapani@counterpane.com> 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



^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: [JDBC] number of transactions doubling
@ 2006-09-29 21:44 Sriram Dandapani <sdandapani@counterpane.com>
  0 siblings, 0 replies; 11+ messages in thread

From: Sriram Dandapani @ 2006-09-29 21:44 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Jim Nasby <jim@nasby.net>; Heikki Linnakangas <heikki@enterprisedb.com>; pgsql-jdbc@postgresql.org; pgsql-admin

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] 
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 

"Sriram Dandapani" <sdandapani@counterpane.com> 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



^ permalink  raw  reply  [nested|flat] 11+ messages in thread


end of thread, other threads:[~2006-09-29 21:44 UTC | newest]

Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-09-28 21:51 number of transactions doubling Sriram Dandapani <sdandapani@counterpane.com>
2006-09-28 22:24 ` Oliver Jowett <oliver@opencloud.com>
2006-09-29 00:47 ` Jim C. Nasby <jim@nasby.net>
2006-09-29 08:56   ` Heikki Linnakangas <heikki@enterprisedb.com>
2006-09-29 17:21     ` Jim Nasby <jim@nasby.net>
2006-09-28 22:52 Re: [JDBC] number of transactions doubling Sriram Dandapani <sdandapani@counterpane.com>
2006-09-28 23:13 ` Oliver Jowett <oliver@opencloud.com>
2006-09-28 23:03 Re: [JDBC] number of transactions doubling Sriram Dandapani <sdandapani@counterpane.com>
2006-09-29 18:31 Re: [JDBC] number of transactions doubling Sriram Dandapani <sdandapani@counterpane.com>
2006-09-29 21:39 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-09-29 21:44 Re: [JDBC] number of transactions doubling Sriram Dandapani <sdandapani@counterpane.com>

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