Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id A6B689F9385 for ; Tue, 30 Oct 2007 05:45:28 -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 84945-02 for ; Tue, 30 Oct 2007 05:45:18 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from fk-out-0910.google.com (fk-out-0910.google.com [209.85.128.186]) by postgresql.org (Postfix) with ESMTP id 91F509F98B0 for ; Tue, 30 Oct 2007 05:45:18 -0300 (ADT) Received: by fk-out-0910.google.com with SMTP id b27so1955007fka for ; Tue, 30 Oct 2007 01:45:16 -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:mime-version:content-type; bh=YOyZo5Gc+zoDf7rBnHFs8z0x+S3YTbQ5sudNK15JF+Y=; b=NPX116A23EW3K6V62ae3k+iCS8msBIBjEDm+fMb5W03fp3wQMBW+UTVYzg9LsH8qmDobDchT9VFwH119yh/IxGVHWoyn7sxhOsN/BxLQ27aNTo1he2rPEpMAmNiqgsMUcpSPRsvSdatX+BLVBeAAgzlM0vM3KQoueZFnvnEEth0= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:mime-version:content-type; b=B5y6eYcV7HYFUdZZQu0BR7GeV6B5Q4bXO7hqBhBWKhUpvExb6gJkQuwEBsY1Zz+96uaSVuR+alBQ6Dqbj+76FbO97le2l+mTBOvuUIT7XG7BTmWcqKSTYnanTMCwHCqM8iUYy/CNilK4gSRMA6P8yOJ2cezO2+gaYvhq/hBiSHE= Received: by 10.82.171.16 with SMTP id t16mr10721855bue.1193733916293; Tue, 30 Oct 2007 01:45:16 -0700 (PDT) Received: by 10.82.121.17 with HTTP; Tue, 30 Oct 2007 01:45:16 -0700 (PDT) Message-ID: <58729a240710300145m4457f63euc15007893e8a805c@mail.gmail.com> Date: Tue, 30 Oct 2007 10:45:16 +0200 From: "Patrick De Zlio" To: pgsql-admin@postgresql.org Subject: How to kill process "idle in transaction" MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_1619_19357268.1193733916280" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200710/302 X-Sequence-Number: 27216 ------=_Part_1619_19357268.1193733916280 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline 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? Thanks for help ------=_Part_1619_19357268.1193733916280 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline 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?

Thanks for help
------=_Part_1619_19357268.1193733916280--