pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
PGSQL 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