Re: <IDLE> connections and cpu consumption

From: "Gauthier, Dave" <dave(dot)gauthier(at)intel(dot)com>
To: John R Pierce <pierce(at)hogranch(dot)com>, "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: Re: <IDLE> connections and cpu consumption
Date: 2011-11-09 21:18:20
Message-ID: 482E80323A35A54498B8B70FF2B8798004D5DE1706@azsmsx504.amr.corp.intel.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-general

ALas, there is no "application_name" column in that table. But I do like the "now()-xact_start" !

-----Original Message-----
From: pgsql-general-owner(at)postgresql(dot)org [mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of John R Pierce
Sent: Wednesday, November 09, 2011 2:58 PM
To: pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] <IDLE> connections and cpu consumption

On 11/09/11 11:35 AM, Gauthier, Dave wrote:
> Ahhhh... so if the script that has the connection open would only terminate the transaction, then vacuum wouldn't get behind?
>
> I actually made a change in that script to rollback when the script doesn't need the changes in the transaction, hopefully allowing vacuum to do its thing.

(from memory, I might have details wrong here)...

select now()-xact_start as "Transaction Age", usename, datname,
procpid, application_name
from pg_stat_activity where current_query='<IDLE> in
transaction" order by 1 desc;

that will list the age of those idle-in-transactions, oldest on top.

--
john r pierce N 37, W 122
santa cruz ca mid-left coast

In response to

Browse pgsql-general by date

  From Date Subject
Next Message hubert depesz lubaczewski 2011-11-09 21:18:44 Why does autovacuum run in so small blocks?
Previous Message Guillaume Lelarge 2011-11-09 20:24:39 Re: Insufficient privilege when initiating backup