Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 909F29FA55D for ; Thu, 1 Nov 2007 05:24:19 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 88963-01 for ; Thu, 1 Nov 2007 05:24:10 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from smadev.internal.net (host3.dynacom.ondsl.gr [62.103.35.211]) by postgresql.org (Postfix) with ESMTP id 8FDB69FA50B for ; Thu, 1 Nov 2007 05:24:13 -0300 (ADT) Received: from smadev.internal.net (localhost [127.0.0.1]) by smadev.internal.net (8.13.6/8.13.6) with ESMTP id lA18O9oC095932; Thu, 1 Nov 2007 10:24:09 +0200 (EET) (envelope-from achill@matrix.gatewaynet.com) Received: from localhost (localhost [[UNIX: localhost]]) by smadev.internal.net (8.13.6/8.13.6/Submit) id lA18O8PT095931; Thu, 1 Nov 2007 10:24:08 +0200 (EET) (envelope-from achill@matrix.gatewaynet.com) From: Achilleas Mantzios Organization: Dynacom Tankers Mgmt To: "Scott Marlowe" Subject: Re: How to kill process "idle in transaction" Date: Thu, 1 Nov 2007 11:24:07 +0300 User-Agent: KMail/1.9.3 Cc: pgsql-admin@postgresql.org, "Patrick De Zlio" References: <58729a240710300145m4457f63euc15007893e8a805c@mail.gmail.com> <200710301111.02475.achill@matrix.gatewaynet.com> In-Reply-To: MIME-Version: 1.0 Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Message-Id: <200711011024.08386.achill@matrix.gatewaynet.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200711/3 X-Sequence-Number: 27246 =CE=A3=CF=84=CE=B9=CF=82 =CE=A4=CE=B5=CF=84=CE=AC=CF=81=CF=84=CE=B7 31 =CE= =9F=CE=BA=CF=84=CF=8E=CE=B2=CF=81=CE=B9=CE=BF=CF=82 2007 23:47, =CE=BF/=CE= =B7 Scott Marlowe =CE=AD=CE=B3=CF=81=CE=B1=CF=88=CE=B5: > On 10/30/07, Achilleas Mantzios wrote: > > =D3=F4=E9=F2 =D4=F1=DF=F4=E7 30 =CF=EA=F4=FE=E2=F1=E9=EF=F2 2007 10:45,= =EF/=E7 Patrick De=20 Zlio =DD=E3=F1=E1=F8=E5: > > > Hi, > > > > > > We are running PostgreSQL 8.2.4 with quite highly updated millions ro= ws > > > tables. > > > > > > It comes sometimes to get some "idle in transaction" processes, which > > > doesn't have first really viewable effect out of giving very bad > > > estimated numbers for pgclass.reltuples and, if we let them run as it > > > is, block the autovacuum from releasing rows, and after few days some > > > worse problems. > > > > > > Now we are able to detect these "idle in transaction" processes, but > > > what to do with them ? > > > > > > What to do to avoid such situation? How to find the bad request which > > > put the process in this state? From the time it is idle in transactio= n, > > > we can't see the query in the pg_lock table anymore. > > > > > > How to deal with such process when it is already idle in transaction? > > > can we simply kill it? It is a backend from pgpool. Can we simply > > > restart it (how) without restarting all the database? > > > > Some connection pools issue a BEGIN on the connection just > > after the previous user of the connection issues COMMIT and > > then closes it (Jboss does it). So, in transaction is not > > apriori bad. > > You can turn off that behaviour, IIRC. It is broken behaviour as far > as pgsql is concerned, I believe. Note nice surely, but why broken? =2D-=20 Achilleas Mantzios