Thread: PGSQL 7.4.8 : idle in transaction problem

PGSQL 7.4.8 : idle in transaction problem

From
FM
Date:
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 !


Re: PGSQL 7.4.8 : idle in transaction problem

From
Peter Eisentraut
Date:
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/

Re: PGSQL 7.4.8 : idle in transaction problem

From
Tom Lane
Date:
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

Re: PGSQL 7.4.8 : idle in transaction problem

From
Alvaro Herrera
Date:
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)

Re: PGSQL 7.4.8 : idle in transaction problem

From
imi
Date:
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
>

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


Re: PGSQL 7.4.8 : idle in transaction problem

From
Tom Lane
Date:
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

Re: PGSQL 7.4.8 : idle in transaction problem

From
Brad Nicholson
Date:
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.



Re: PGSQL 7.4.8 : idle in transaction problem

From
Alvaro Herrera
Date:
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)

Re: PGSQL 7.4.8 : idle in transaction problem

From
imi
Date:
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
>

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