Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 132AA9FBEF1 for ; Thu, 1 Nov 2007 12:01:11 -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 64050-01 for ; Thu, 1 Nov 2007 12:00:47 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from ug-out-1314.google.com (ug-out-1314.google.com [66.249.92.171]) by postgresql.org (Postfix) with ESMTP id 7C2749FB1EF for ; Thu, 1 Nov 2007 12:00:25 -0300 (ADT) Received: by ug-out-1314.google.com with SMTP id j3so485820ugf for ; Thu, 01 Nov 2007 08:00:25 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; bh=bgxEHcwGzFNnEPWuNosdHAeYddMEz1Puv2T5v+m01ag=; b=FumI9vUTVkSSNV06iY04yldInVTi20xb2RAEdsAVFxZy20MFNhcK97X2lhKjOnnR0u8DSB0X/osvRtnSbB8OnKRYKxBpEWI0mXbKPGil3FRioEOZExmHbvTm8DDg9kZnCgcDp4JPfdetrJKi46cP/fQISTqemcViod6RlOdlAdo= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=rQuZwP3mbzOcBuNNU9cF1qIo2QGpKKRz7cwxetciNi4F0zOzgu4T84u9k7Ef8c+VM3qSiU+8Pl2b+R2prLWaLFQFqb8WMDLnYnlQKpxI/03UM1IBNITnm1nA1ZHq/4c+aaM/ZzhsH9nn/XFv8sn49xFpSIDhEKaTXgZ0gwuD1p0= Received: by 10.67.196.2 with SMTP id y2mr1455726ugp.1193929224991; Thu, 01 Nov 2007 08:00:24 -0700 (PDT) Received: by 10.67.40.10 with HTTP; Thu, 1 Nov 2007 08:00:24 -0700 (PDT) Message-ID: Date: Thu, 1 Nov 2007 10:00:24 -0500 From: "Scott Marlowe" To: "Achilleas Mantzios" Subject: Re: How to kill process "idle in transaction" Cc: pgsql-admin@postgresql.org, "Patrick De Zlio" In-Reply-To: <200711011024.08386.achill@matrix.gatewaynet.com> MIME-Version: 1.0 Content-Type: text/plain; charset=WINDOWS-1252 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline References: <58729a240710300145m4457f63euc15007893e8a805c@mail.gmail.com> <200710301111.02475.achill@matrix.gatewaynet.com> <200711011024.08386.achill@matrix.gatewaynet.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200711/7 X-Sequence-Number: 27250 On 11/1/07, Achilleas Mantzios wrote: > =CE=A3=CF=84=CE=B9=CF=82 =CE=A4=CE=B5=CF=84=CE=AC=CF =CF=84=CE=B7 31 =CE= =9F=CE=BA=CF=84=CF=8E=CE=B2=CF =CE=B9=CE=BF=CF=82 2007 23:47, =CE=BF/=CE=B7= Scott Marlowe =CE=AD=CE=B3=CF =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:4= 5, =EF/=E7 Patrick De > Zlio =DD=E3=F1=E1=F8=E5: > > > > 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, whi= ch > > > > 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 so= me > > > > worse problems. > > > > > > > > Now we are able to detect these "idle in transaction" processes, bu= t > > > > what to do with them ? > > > > > > > > What to do to avoid such situation? How to find the bad request whi= ch > > > > put the process in this state? From the time it is idle in transact= ion, > > > > we can't see the query in the pg_lock table anymore. > > > > > > > > How to deal with such process when it is already idle in transactio= n? > > > > 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? Because you're not really IN a transaction. Plus, in some snapshot systems, you could pick up an idle connection that is idle in transaction that gives you a three day old snapshot. The time to issue a begin is when you are beginning your transaction, not right after finishing the last one. Also, the connection that's idle in transaction will prevent postgresql from being able to properly vacuum (in versions before 8.3) meaning that it can cause your datastore to suffer from bloat.