PGSQL 7.4.8 : idle in transaction problem

Поиск
Список
Период
Сортировка
Искать

PGSQL 7.4.8 : idle in transaction problem

От:
FM <dist-list@LEXUM.UMontreal.CA>
Дата:
Hello,

We are using JBOSS and postgresql on 1 database (DB1). From time to time 
the JBOSS process  .

What is strange is that users on all DB (not only DB1) are affected by 
the JBOSS .
I understand that it locked DB1 but why it also locked users on others DB ?

Thanks !

Re: PGSQL 7.4.8 : idle in transaction problem

От:
Peter Eisentraut <peter_e@gmx.net>
Дата:
FM wrote:
> What is strange is that users on all DB (not only DB1) are affected
> by the JBOSS .
> 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/

Re: PGSQL 7.4.8 : idle in transaction problem

От:
Tom Lane <tgl@sss.pgh.pa.us>
Дата:
FM  writes:
> What is strange is that users on all DB (not only DB1) are affected by 
> the JBOSS .
> 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

Re: PGSQL 7.4.8 : idle in transaction problem

От:
Alvaro Herrera <alvherre@alvh.no-ip.org>
Дата:
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 ()
"Cuando mañana llegue pelearemos segun lo que mañana exija" (Mowgli)

Re: PGSQL 7.4.8 : idle in transaction problem

От:
imi <linimi@gmail.com>
Дата:
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  wrote:
> Hello,
> 
> We are using JBOSS and postgresql on 1 database (DB1). From time to time
> the JBOSS process  .
> 
> What is strange is that users on all DB (not only DB1) are affected by
> the JBOSS .
> 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
>

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Re: PGSQL 7.4.8 : idle in transaction problem

От:
Tom Lane <tgl@sss.pgh.pa.us>
Дата:
Alvaro Herrera  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

Re: PGSQL 7.4.8 : idle in transaction problem

От:
Brad Nicholson <bnichols@ca.afilias.info>
Дата:
Tom Lane wrote:

>Alvaro Herrera  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.


Re: PGSQL 7.4.8 : idle in transaction problem

От:
Alvaro Herrera <alvherre@alvh.no-ip.org>
Дата:
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 ()
"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)

Re: PGSQL 7.4.8 : idle in transaction problem

От:
imi <linimi@gmail.com>
Дата:
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  wrote:
> Hello,
> 
> We are using JBOSS and postgresql on 1 database (DB1). From time to time
> the JBOSS process  .
> 
> What is strange is that users on all DB (not only DB1) are affected by
> the JBOSS .
> 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
>

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

FAQ