Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 5A68D9FC00B for ; Tue, 30 Oct 2007 06:19:08 -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 57002-05 for ; Tue, 30 Oct 2007 06:18:58 -0300 (ADT) X-Greylist: delayed 00:07:52.912863 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 29FFA9F9FBE for ; Tue, 30 Oct 2007 06:19:00 -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 l9U9B3SL080648; Tue, 30 Oct 2007 11:11:03 +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 l9U9B2uQ080647; Tue, 30 Oct 2007 11:11:02 +0200 (EET) (envelope-from achill@matrix.gatewaynet.com) From: Achilleas Mantzios Organization: Dynacom Tankers Mgmt To: pgsql-admin@postgresql.org Subject: Re: How to kill process "idle in transaction" Date: Tue, 30 Oct 2007 12:11:01 +0300 User-Agent: KMail/1.9.3 Cc: "Patrick De Zlio" References: <58729a240710300145m4457f63euc15007893e8a805c@mail.gmail.com> In-Reply-To: <58729a240710300145m4457f63euc15007893e8a805c@mail.gmail.com> MIME-Version: 1.0 Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Message-Id: <200710301111.02475.achill@matrix.gatewaynet.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200710/303 X-Sequence-Number: 27217 =CE=A3=CF=84=CE=B9=CF=82 =CE=A4=CF=81=CE=AF=CF=84=CE=B7 30 =CE=9F=CE=BA=CF= =84=CF=8E=CE=B2=CF=81=CE=B9=CE=BF=CF=82 2007 10:45, =CE=BF/=CE=B7 Patrick D= e Zlio =CE=AD=CE=B3=CF=81=CE=B1=CF=88=CE=B5: > Hi, > > We are running PostgreSQL 8.2.4 with quite highly updated millions rows > 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 transaction, 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=20 after the previous user of the connection issues COMMIT and=20 then closes it (Jboss does it). So, in transaction is not=20 apriori bad. However if in your system you have lock problems,=20 and transactions that dont get rollbacked or commited,=20 then it is a problem with your application. Have you done any monitoring on the size of the pool? You should look at: 1) Connections get closed eventually 2) Transactions either have the autocommit flag on, are commited or are=20 rollbacked. > > Thanks for help =2D-=20 Achilleas Mantzios