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