pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feednumber of transactions doubling
11+ messages / 6 participants
[nested] [flat]
* number of transactions doubling
@ 2006-09-28 21:51 Sriram Dandapani <sdandapani@counterpane.com>
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 22:24 Oliver Jowett <oliver@opencloud.com>
parent: Sriram Dandapani <sdandapani@counterpane.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: [JDBC] number of transactions doubling
@ 2006-09-28 22:52 Sriram Dandapani <sdandapani@counterpane.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 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-28 23:13 Oliver Jowett <oliver@opencloud.com>
parent: Sriram Dandapani <sdandapani@counterpane.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: number of transactions doubling
@ 2006-09-29 00:47 Jim C. Nasby <jim@nasby.net>
parent: Sriram Dandapani <sdandapani@counterpane.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-29 08:56 Heikki Linnakangas <heikki@enterprisedb.com>
parent: Jim C. 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-29 17:21 Jim Nasby <jim@nasby.net>
parent: Heikki Linnakangas <heikki@enterprisedb.com>
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-29 18:31 Sriram Dandapani <sdandapani@counterpane.com>
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 21:39 Tom Lane <tgl@sss.pgh.pa.us>
parent: Sriram Dandapani <sdandapani@counterpane.com>
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