Received: from localhost (maia-5.hub.org [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id C88B49F9764 for ; Wed, 22 Aug 2007 18:58:51 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024) with ESMTP id 16613-07 for ; Wed, 22 Aug 2007 18:58:41 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from web26304.mail.ukl.yahoo.com (web26304.mail.ukl.yahoo.com [217.146.176.15]) by postgresql.org (Postfix) with SMTP id AAC899F958A for ; Wed, 22 Aug 2007 18:58:44 -0300 (ADT) Received: (qmail 20769 invoked by uid 60001); 22 Aug 2007 21:58:40 -0000 DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.co.uk; h=X-YMail-OSG:Received:X-Mailer:Date:From:Subject:To:MIME-Version:Content-Type:Message-ID; b=zrmb8BwoKTpMXiY20Lq0QQ1/DBtBCKYiRM9lEAetC89H07N141+7cpK7wkSm0NpjT78v3yLJD9xJnOn/rufRtSPKaKoS1L84K/HkQrXbEdYL6HwSzN3BZ9vwlkip5bkxVojkjvtPtrdQdqkrF0pMkRAU9Dcf2Z4V3boHW2Xfrcc=; X-YMail-OSG: kbduq5gVM1lUAPt_tQ5Rk.lxKEDR79_IY3Wt.0tz9UrJ9.uK_FOXLVlxo2YAf0zH36HFcN_XDbkSrJHROAHx0UGMMPVh3PX_cfAIbDzK_0L2urZVgXFuh4LP_do- Received: from [203.215.160.146] by web26304.mail.ukl.yahoo.com via HTTP; Wed, 22 Aug 2007 21:58:40 GMT X-Mailer: YahooMailRC/651.48 YahooMailWebService/0.7.119.3 Date: Wed, 22 Aug 2007 21:58:40 +0000 (GMT) From: Farhan Mughal Subject: Re: Long running transaction in pg_activity_log To: Sachchida Ojha , pgsql-performance@postgresql.org MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="0-1501159279-1187819920=:20445" Message-ID: <819574.20445.qm@web26304.mail.ukl.yahoo.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.563 tagged_above=0 required=5 tests=AWL=0.561, BAYES_50=0.001, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200708/254 X-Sequence-Number: 26384 --0-1501159279-1187819920=:20445 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable =0AIf I am not wrong, you must be asking about this:=0A=0Aselect * from pg_= stat_activity;=0A=0AIf you see in the que= ry column. =0A =0A=0ATurn on the stats_command_string option in postgresq= l.conf.=0A=0A=0ARegards, =0A=0AFarhan =0A=0A=0A =0A=0A=0A=0A----- Original = Message ----=0AFrom: Sachchida Ojha =0ATo: pgsql= -performance@postgresql.org=0ASent: Thursday, 23 August, 2007 1:40:09 AM=0A= Subject: [PERFORM] Long running transaction in pg_activity_log=0A=0ALong ru= nning transaction in pg_activity_log=0A=0A=0A =0A =0A=0A=0A=0A=0AI see some= long running transaction in my pg_activity_log table. My app becomes almos= t unusable. My question is =0A=0A=0AHow can I query the database to see wha= t sql these transactions are running. =0A=0A=0A=0A=0A=0A16385;"em_db";20893= ;16386;"em_user";" in transaction";f;"2007-08-22 20:38:06.527792+00";= "2007-08-22 20:37:33.937193+00";"127.0.0.1";52466=0A=0A=0A16385;"em_db";151= 10;16386;"em_user";" in transaction";f;"2007-08-22 20:05:08.580643+00= ";"2007-08-22 19:39:11.670572+00";"127.0.0.1";50961=0A=0A=0A16385;"em_db";1= 5113;16386;"em_user";" in transaction";f;"2007-08-22 20:06:01.53394+0= 0";"2007-08-22 19:39:11.704564+00";"127.0.0.1";50964=0A=0A=0AThanks=0A=0A= =0ARegards=0A=0A=0ASachi=0A=0A=0A=0A=0A=0A=0A=0A=0A=0A _______________= ____________________________________________ =0AWant ideas for reducing you= r carbon footprint? Visit Yahoo! For Good http://uk.promotions.yahoo.com/f= orgood/environment.html --0-1501159279-1187819920=:20445 Content-Type: text/html; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable

If I am not wrong, you must be asking about thi= s:

select * from pg_stat_activity;

If you see <c= ommand string =0A not enabled> in the query column. <= /font>=0A

 =

=0A

Turn on the stats_command_string op= tion in postgresql.conf.


R= egards,

Farhan
<= /font>

=
=0A

----- Original Message ----
From: Sachchida Ojha <so= jha@secure-elements.com>
To: pgsql-performance@postgresql.org
Sent= : Thursday, 23 August, 2007 1:40:09 AM
Subject: [PERFORM] Long running t= ransaction in pg_activity_log

Long running transaction in pg_= activity_log=0A=0A=0A =0A =0A=0A=0A=0A=0A

I see some long running transaction in my pg_activity_log table. M= y app becomes almost unusable. My question is =0A=0A
How can I query the database to see what sql these tr= ansactions are running. =0A

=0A
=0A=0A

16385;"em_db";20893;16386;"em_user";"<IDLE> in transaction= ";f;"2007-08-22 20:38:06.527792+00";"2007-08-22 20:37:33.937193+00";"127.0.= 0.1";52466

=0A=0A

16385;"em_db"= ;15110;16386;"em_user";"<IDLE> in transaction";f;"2007-08-22 20:05:08= .580643+00";"2007-08-22 19:39:11.670572+00";"127.0.0.1";50961

=0A= =0A

16385;"em_db";15113;16386;"em_user";"= <IDLE> in transaction";f;"2007-08-22 20:06:01.53394+00";"2007-08-22 1= 9:39:11.704564+00";"127.0.0.1";50964

=0A=0A

Thanks=0A=0A
Regards= =0A=0A
Sachi=0A

=0A=0A<= /div>

=0A
=0AYahoo! Answers - Get bet= ter answers from someone who knows. = Try=0Ait now. --0-1501159279-1187819920=:20445--