pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedPGSQL 7.4.8 : idle in transaction problem
8+ messages / 6 participants
[nested] [flat]
* PGSQL 7.4.8 : idle in transaction problem
@ 2005-07-19 19:49 FM <dist-list@LEXUM.UMontreal.CA>
2005-07-19 20:30 ` Re: PGSQL 7.4.8 : idle in transaction problem Peter Eisentraut <peter_e@gmx.net>
2005-07-20 02:44 ` Re: PGSQL 7.4.8 : idle in transaction problem Tom Lane <tgl@sss.pgh.pa.us>
2005-07-20 06:28 ` Re: PGSQL 7.4.8 : idle in transaction problem imi <linimi@gmail.com>
0 siblings, 3 replies; 8+ messages in thread
From: FM @ 2005-07-19 19:49 UTC (permalink / raw)
To: pgsql-admin
Hello,
We are using JBOSS and postgresql on 1 database (DB1). From time to time
the JBOSS process <idle in transaction>.
What is strange is that users on all DB (not only DB1) are affected by
the JBOSS <idle in transaction>.
I understand that it locked DB1 but why it also locked users on others DB ?
Thanks !
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: PGSQL 7.4.8 : idle in transaction problem
2005-07-19 19:49 PGSQL 7.4.8 : idle in transaction problem FM <dist-list@LEXUM.UMontreal.CA>
@ 2005-07-19 20:30 ` Peter Eisentraut <peter_e@gmx.net>
2 siblings, 0 replies; 8+ messages in thread
From: Peter Eisentraut @ 2005-07-19 20:30 UTC (permalink / raw)
To: FM <dist-list@lexum.umontreal.ca>; +Cc: pgsql-admin
FM wrote:
> What is strange is that users on all DB (not only DB1) are affected
> by the JBOSS <idle in transaction>.
> I understand that it locked DB1 but why it also locked users on
> others DB ?
This is fairly impossible. Please show us more concrete evidence of
what you are observing.
--
Peter Eisentraut
http://developer.postgresql.org/~petere/
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: PGSQL 7.4.8 : idle in transaction problem
2005-07-19 19:49 PGSQL 7.4.8 : idle in transaction problem FM <dist-list@LEXUM.UMontreal.CA>
@ 2005-07-20 02:44 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-07-20 05:11 ` Re: PGSQL 7.4.8 : idle in transaction problem Alvaro Herrera <alvherre@alvh.no-ip.org>
2 siblings, 1 reply; 8+ messages in thread
From: Tom Lane @ 2005-07-20 02:44 UTC (permalink / raw)
To: FM <dist-list@LEXUM.UMontreal.CA>; +Cc: pgsql-admin
FM <dist-list@LEXUM.UMontreal.CA> writes:
> What is strange is that users on all DB (not only DB1) are affected by
> the JBOSS <idle in transaction>.
> I understand that it locked DB1 but why it also locked users on others DB ?
You should define "locked" more carefully: exactly what behavior are you
seeing that you don't like?
If it's a matter of whether VACUUM removes rows or not, that's expected:
open-transaction bookkeeping is done across the whole cluster. (This is
not something simple to change, since we may have to consider
transaction IDs in shared catalogs.)
regards, tom lane
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: PGSQL 7.4.8 : idle in transaction problem
2005-07-19 19:49 PGSQL 7.4.8 : idle in transaction problem FM <dist-list@LEXUM.UMontreal.CA>
2005-07-20 02:44 ` Re: PGSQL 7.4.8 : idle in transaction problem Tom Lane <tgl@sss.pgh.pa.us>
@ 2005-07-20 05:11 ` Alvaro Herrera <alvherre@alvh.no-ip.org>
2005-07-20 13:35 ` Re: PGSQL 7.4.8 : idle in transaction problem Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 8+ messages in thread
From: Alvaro Herrera @ 2005-07-20 05:11 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: FM <dist-list@LEXUM.UMontreal.CA>; pgsql-admin
On Tue, Jul 19, 2005 at 10:44:35PM -0400, Tom Lane wrote:
> If it's a matter of whether VACUUM removes rows or not, that's expected:
> open-transaction bookkeeping is done across the whole cluster. (This is
> not something simple to change, since we may have to consider
> transaction IDs in shared catalogs.)
I wonder if this restriction could be lifted if we tracked last-vacuum-
Xid per relation?
--
Alvaro Herrera (<alvherre[a]alvh.no-ip.org>)
"Cuando mañana llegue pelearemos segun lo que mañana exija" (Mowgli)
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: PGSQL 7.4.8 : idle in transaction problem
2005-07-19 19:49 PGSQL 7.4.8 : idle in transaction problem FM <dist-list@LEXUM.UMontreal.CA>
2005-07-20 02:44 ` Re: PGSQL 7.4.8 : idle in transaction problem Tom Lane <tgl@sss.pgh.pa.us>
2005-07-20 05:11 ` Re: PGSQL 7.4.8 : idle in transaction problem Alvaro Herrera <alvherre@alvh.no-ip.org>
@ 2005-07-20 13:35 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-07-21 14:46 ` Re: PGSQL 7.4.8 : idle in transaction problem Brad Nicholson <bnichols@ca.afilias.info>
0 siblings, 1 reply; 8+ messages in thread
From: Tom Lane @ 2005-07-20 13:35 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@alvh.no-ip.org>; +Cc: FM <dist-list@LEXUM.UMontreal.CA>; pgsql-admin
Alvaro Herrera <alvherre@alvh.no-ip.org> writes:
> On Tue, Jul 19, 2005 at 10:44:35PM -0400, Tom Lane wrote:
>> If it's a matter of whether VACUUM removes rows or not, that's expected:
>> open-transaction bookkeeping is done across the whole cluster. (This is
>> not something simple to change, since we may have to consider
>> transaction IDs in shared catalogs.)
> I wonder if this restriction could be lifted if we tracked last-vacuum-
> Xid per relation?
No, that's unrelated. What is of concern is the open-transaction
horizon. We could tighten things up by keeping a more complicated set
of information in the PGPROC array --- rather than advertising a single
transaction XMIN, each transaction would have to calculate and advertise
both a "global" XMIN and a "local" XMIN (the latter counting only open
transactions within your own database). This would then allow a more
refined calculation in GetOldestXmin().
I'm inclined to think that the benefit wouldn't be worth the distributed
cost of doing this in every GetSnapshotData() call ...
regards, tom lane
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: PGSQL 7.4.8 : idle in transaction problem
2005-07-19 19:49 PGSQL 7.4.8 : idle in transaction problem FM <dist-list@LEXUM.UMontreal.CA>
2005-07-20 02:44 ` Re: PGSQL 7.4.8 : idle in transaction problem Tom Lane <tgl@sss.pgh.pa.us>
2005-07-20 05:11 ` Re: PGSQL 7.4.8 : idle in transaction problem Alvaro Herrera <alvherre@alvh.no-ip.org>
2005-07-20 13:35 ` Re: PGSQL 7.4.8 : idle in transaction problem Tom Lane <tgl@sss.pgh.pa.us>
@ 2005-07-21 14:46 ` Brad Nicholson <bnichols@ca.afilias.info>
2005-07-21 16:11 ` Re: PGSQL 7.4.8 : idle in transaction problem Alvaro Herrera <alvherre@alvh.no-ip.org>
0 siblings, 1 reply; 8+ messages in thread
From: Brad Nicholson @ 2005-07-21 14:46 UTC (permalink / raw)
To: pgsql-admin
Tom Lane wrote:
>Alvaro Herrera <alvherre@alvh.no-ip.org> writes:
>
>
>>On Tue, Jul 19, 2005 at 10:44:35PM -0400, Tom Lane wrote:
>>
>>
>>>If it's a matter of whether VACUUM removes rows or not, that's expected:
>>>open-transaction bookkeeping is done across the whole cluster. (This is
>>>not something simple to change, since we may have to consider
>>>transaction IDs in shared catalogs.)
>>>
>>>
>
>
>
>>I wonder if this restriction could be lifted if we tracked last-vacuum-
>>Xid per relation?
>>
>>
>
>No, that's unrelated. What is of concern is the open-transaction
>horizon.
>
From a Slony-I users standpoint - the idle transactions cause
pg_listener bloat, which can impact replication performance on busy sites.
--
Brad Nicholson 416-673-4106
Database Administrator, Afilias Canada Corp.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: PGSQL 7.4.8 : idle in transaction problem
2005-07-19 19:49 PGSQL 7.4.8 : idle in transaction problem FM <dist-list@LEXUM.UMontreal.CA>
2005-07-20 02:44 ` Re: PGSQL 7.4.8 : idle in transaction problem Tom Lane <tgl@sss.pgh.pa.us>
2005-07-20 05:11 ` Re: PGSQL 7.4.8 : idle in transaction problem Alvaro Herrera <alvherre@alvh.no-ip.org>
2005-07-20 13:35 ` Re: PGSQL 7.4.8 : idle in transaction problem Tom Lane <tgl@sss.pgh.pa.us>
2005-07-21 14:46 ` Re: PGSQL 7.4.8 : idle in transaction problem Brad Nicholson <bnichols@ca.afilias.info>
@ 2005-07-21 16:11 ` Alvaro Herrera <alvherre@alvh.no-ip.org>
0 siblings, 0 replies; 8+ messages in thread
From: Alvaro Herrera @ 2005-07-21 16:11 UTC (permalink / raw)
To: Brad Nicholson <bnichols@ca.afilias.info>; +Cc: pgsql-admin
On Thu, Jul 21, 2005 at 10:46:25AM -0400, Brad Nicholson wrote:
> From a Slony-I users standpoint - the idle transactions cause
> pg_listener bloat, which can impact replication performance on busy sites.
I think that problem has a different solution, which is to rewrite the
listen/notify mechanism.
--
Alvaro Herrera (<alvherre[a]alvh.no-ip.org>)
"Find a bug in a program, and fix it, and the program will work today.
Show the program how to find and fix a bug, and the program
will work forever" (Oliver Silfridge)
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: PGSQL 7.4.8 : idle in transaction problem
2005-07-19 19:49 PGSQL 7.4.8 : idle in transaction problem FM <dist-list@LEXUM.UMontreal.CA>
@ 2005-07-20 06:28 ` imi <linimi@gmail.com>
2 siblings, 0 replies; 8+ messages in thread
From: imi @ 2005-07-20 06:28 UTC (permalink / raw)
To: FM <dist-list@lexum.umontreal.ca>; +Cc: pgsql-admin
Hello,
(Sorry my english)
I saw this words in my 'ps -Af'. JBOSS use jdbc driver for postgres.
In java the Connection has a method: setAutoCommit(boolen).
Those connections where autocommit=false, and after the last sql
command (select, insert, update) NOT have rollback or commit, state
will "idle in transaction".
I'm sorry but I don't know how can you setup autocommit feature in the JBOSS.
linimi
On 7/19/05, FM <dist-list@lexum.umontreal.ca> wrote:
> Hello,
>
> We are using JBOSS and postgresql on 1 database (DB1). From time to time
> the JBOSS process <idle in transaction>.
>
> What is strange is that users on all DB (not only DB1) are affected by
> the JBOSS <idle in transaction>.
> I understand that it locked DB1 but why it also locked users on others DB ?
>
> Thanks !
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2005-07-21 16:11 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-07-19 19:49 PGSQL 7.4.8 : idle in transaction problem FM <dist-list@LEXUM.UMontreal.CA>
2005-07-19 20:30 ` Peter Eisentraut <peter_e@gmx.net>
2005-07-20 02:44 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-07-20 05:11 ` Alvaro Herrera <alvherre@alvh.no-ip.org>
2005-07-20 13:35 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-07-21 14:46 ` Brad Nicholson <bnichols@ca.afilias.info>
2005-07-21 16:11 ` Alvaro Herrera <alvherre@alvh.no-ip.org>
2005-07-20 06:28 ` imi <linimi@gmail.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