Thread: PGSQL 7.4.8 : idle in transaction problem
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 !
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/
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
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)
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
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
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.
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)
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