Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id 6F2DE6332AE for ; Tue, 6 Jul 2010 12:21:02 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia, port 10024) with ESMTP id 42583-01 for ; Tue, 6 Jul 2010 15:20:54 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from bay0-omc4-s25.bay0.hotmail.com (bay0-omc4-s25.bay0.hotmail.com [65.54.190.227]) by mail.postgresql.org (Postfix) with ESMTP id BA3DF63327D for ; Tue, 6 Jul 2010 12:20:53 -0300 (ADT) Received: from BAY149-W56 ([65.54.190.199]) by bay0-omc4-s25.bay0.hotmail.com with Microsoft SMTPSVC(6.0.3790.4675); Tue, 6 Jul 2010 08:20:51 -0700 Message-ID: Content-Type: multipart/alternative; boundary="_b8345835-e341-4b04-83a3-b28dc25b1ffe_" X-Originating-IP: [129.44.63.215] From: Balkrishna Sharma To: CC: , Subject: Re: Change to 'timing on' globally Date: Tue, 6 Jul 2010 11:20:52 -0400 Importance: Normal In-Reply-To: <201007061448.o66EmmF10095@momjian.us> References: , <201007061448.o66EmmF10095@momjian.us> MIME-Version: 1.0 X-OriginalArrivalTime: 06 Jul 2010 15:20:51.0969 (UTC) FILETIME=[D200E710:01CB1D1E] X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.909 tagged_above=-10 required=5 tests=BAYES_00=-1.9, HTML_MESSAGE=0.001, RCVD_IN_DNSWL_NONE=-0.0001, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201007/15 X-Sequence-Number: 35034 --_b8345835-e341-4b04-83a3-b28dc25b1ffe_ Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable > I just tested it here on Ubuntu and it worked:I followed your steps and i= t worked in the way you indicated=2C on CentOS as well. But it still does n= ot:a. work with psql -c "query" syntax. (Works in echo mode or in interact= ive mode.)b. it does not still seem to work if you fire the queries from a = client box (in any mode - interactive or otherwise)ON SERVER I get:Timing i= s on. now------------------------------ 2010-07-06 11:06:13.167= 34-04(1 row)Time: 0.574 ms ON CLIENT I just get: now------------------------------- 2010-= 07-06 11:06:28.455395-04(1 row) ~~~~~~~~~~~~~~~~~~~~Basically I am firing a lot of psql through unix script= on several client machines and a lot of the psql are hanging for some othe= r reasons. I also need to capture the timing of each query. So I need timin= g to be on. Doing the following captures the timing but I don't know which psql stateme= nt is hanging when I do ps aux|grep psqlecho '\timing \\select * from ....= ....' | psqlOn ps aux|grep psql I just see:> ps aux|grep psql2255 0.0 0.0= 155636 1668 pts/1 S Jul05 0:00 psql3883 0.0 0.0 155636 1676 pt= s/1 S Jul05 0:00 psql4672 0.0 0.0 155636 1672 pts/1 S Jul0= 5 0:00 psql4713 0.0 0.0 155636 1672 pts/1 S Jul05 0:00 psql473= 7 0.0 0.0 155636 1672 pts/1 S Jul05 0:00 psql4798 0.0 0.0 1556= 36 1668 pts/1 S Jul05 0:00 psql5050 0.0 0.0 155636 1676 pts/1 = S Jul05 0:00 psql5086 0.0 0.0 155636 1668 pts/1 S Jul05 0= :00 psql5405 0.0 0.0 155636 1668 pts/1 S Jul05 0:00 psql7255 0.= 0 0.0 155644 1796 pts/1 S Jul05 0:00 psql psql -c 'select * from "DAPP".student_common_data where student_id =3D 100= 0 and field_id =3D1988=3B' does make the ps aux more informative but it do= es not capture the query timing. From what I understand you cannot mix ('t= iming + query') in "-c" mode. So trying to set 'timing on' outside the individual queries (and preferably= outside the client machines) somewhere on the server so that psql -c on cl= ient would capture the timing automatically. > From: bruce@momjian.us > Subject: Re: [ADMIN] Change to 'timing on' globally > To: b_ki@hotmail.com > Date: Tue=2C 6 Jul 2010 10:48:48 -0400 > CC: alvherre@commandprompt.com=3B pgsql-admin@postgresql.org >=20 > Balkrishna Sharma wrote: > >=20 > > Thanks. If I want to do at system-wide level=2C where do I store the > > psqlrc file (assuming I want to change the timing behavior system-wide)= ? >=20 > > (CentOS 5=2C Postgres 8.4) > > $ ./pg_config --sysconfdir/opt/PostgreSQL/8.4/etc/postgresql >=20 > > But I don't have /opt/PostgreSQL/8.4/etc/postgresql directory. Just > > creating the directory and putting a psqlrc file over there does not > > seem to work. >=20 > I just tested it here on Ubuntu and it worked: >=20 > $ sudo mkdir etc > $ sudo mkdir etc/postgresql > $ cd etc/postgresql/ > $ sudo vi psqlrc > # add \echo test > $ pwd > /opt/PostgreSQL/8.4/etc/postgresql > $ ../../bin/psql -U postgres postgres > --> test > psql (8.4.2) > Type "help" for help. > =09 > postgres=3D#=20 >=20 > > On a side-note=2C I observered that timing value in ~/.psqlrc was > > ignored by psql -c "..." command but not by echo "...."|psqlThought > > it was strange. >=20 > Yeah=2C that is odd. >=20 > --=20 > Bruce Momjian http://momjian.us > EnterpriseDB http://enterprisedb.com >=20 > + None of us is going to be here forever. + =20 _________________________________________________________________ Hotmail is redefining busy with tools for the New Busy. Get more from your = inbox. http://www.windowslive.com/campaign/thenewbusy?ocid=3DPID28326::T:WLMTAGL:O= N:WL:en-US:WM_HMP:042010_2= --_b8345835-e341-4b04-83a3-b28dc25b1ffe_ Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable >=3B I just tested it here on Ubuntu and it worked:
I followed your s= teps and it worked in the way you indicated=2C on CentOS as well. But it st= ill does not:
a. work with psql -c "query" syntax. (Works  = =3Bin echo mode or in interactive mode.)
b. it does not still see= m to work if you fire the queries from a client box (in any mode - interact= ive or otherwise)
ON SERVER I get:
Timing is on.
 =3B =3B  =3B  =3B  =3B  =3B  =3B now<= /div>
------------------------------
 =3B2010-07-06 11:06= :13.16734-04
(1 row)
Time: 0.574 ms

ON CLIENT I just get:
 =3B&nb= sp=3B  =3B  =3B  =3B  =3B  =3B  =3Bnow
--= -----------------------------
 =3B2010-07-06 11:06:28.455395-= 04
(1 row)


~~~~~~~~= ~~~~~~~~~~~~
Basically I am firing a lot of psql through unix scr= ipt on several client machines and a lot of the psql are hanging for some o= ther reasons. I also need to capture the timing of each query. So I need ti= ming to be on.

Doing the following captures the ti= ming but I don't know which psql statement is hanging when I do ps aux|grep= psql
echo '\timing \\select * from  =3B........' | psql
On ps aux|grep psql I just see:
>=3B ps aux|grep psq= l
2255  =3B0.0  =3B0.0 155636  =3B1668 pts/1  =3B=  =3BS  =3B  =3BJul05  =3B 0:00 psql
3883  = =3B0.0  =3B0.0 155636  =3B1676 pts/1  =3B  =3BS  =3B &n= bsp=3BJul05  =3B 0:00 psql
4672  =3B0.0  =3B0.0 15563= 6  =3B1672 pts/1  =3B  =3BS  =3B  =3BJul05  =3B 0:0= 0 psql
4713  =3B0.0  =3B0.0 155636  =3B1672 pts/1 &nb= sp=3B  =3BS  =3B  =3BJul05  =3B 0:00 psql
4737 &n= bsp=3B0.0  =3B0.0 155636  =3B1672 pts/1  =3B  =3BS  =3B=  =3BJul05  =3B 0:00 psql
4798  =3B0.0  =3B0.0 15= 5636  =3B1668 pts/1  =3B  =3BS  =3B  =3BJul05  =3B = 0:00 psql
5050  =3B0.0  =3B0.0 155636  =3B1676 pts/1 =  =3B  =3BS  =3B  =3BJul05  =3B 0:00 psql
5086=  =3B0.0  =3B0.0 155636  =3B1668 pts/1  =3B  =3BS  = =3B  =3BJul05  =3B 0:00 psql
5405  =3B0.0  =3B0.0= 155636  =3B1668 pts/1  =3B  =3BS  =3B  =3BJul05  = =3B 0:00 psql
7255  =3B0.0  =3B0.0 155644  =3B1796 pt= s/1  =3B  =3BS  =3B  =3BJul05  =3B 0:00 psql


psql -c 'select * from  =3B"DAPP".s= tudent_common_data where student_id =3D 1000 and field_id =3D1988=3B'  = =3Bdoes make the ps aux more informative but it does not capture the query = timing. From what I understand you cannot mix  =3B('timing + query') in= "-c" mode.

So trying to set 'timing on' outside t= he individual queries (and preferably outside the client machines) somewher= e on the server so that psql -c on client would capture the timing automati= cally.




>=3B From: bruce@m= omjian.us
>=3B Subject: Re: [ADMIN] Change to 'timing on' globally
= >=3B To: b_ki@hotmail.com
>=3B Date: Tue=2C 6 Jul 2010 10:48:48 -040= 0
>=3B CC: alvherre@commandprompt.com=3B pgsql-admin@postgresql.org>=3B
>=3B Balkrishna Sharma wrote:
>=3B >=3B
>=3B >= =3B Thanks. If I want to do at system-wide level=2C where do I store the>=3B >=3B psqlrc file (assuming I want to change the timing behavior s= ystem-wide)?
>=3B
>=3B >=3B (CentOS 5=2C Postgres 8.4)
>= =3B >=3B $ ./pg_config --sysconfdir/opt/PostgreSQL/8.4/etc/postgresql
= >=3B
>=3B >=3B But I don't have /opt/PostgreSQL/8.4/etc/postgresq= l directory. Just
>=3B >=3B creating the directory and putting a psq= lrc file over there does not
>=3B >=3B seem to work.
>=3B
&= gt=3B I just tested it here on Ubuntu and it worked:
>=3B
>=3B = $ sudo mkdir etc
>=3B $ sudo mkdir etc/postgresql
>=3B $ cd etc= /postgresql/
>=3B $ sudo vi psqlrc
>=3B # add \echo test
>= =3B $ pwd
>=3B /opt/PostgreSQL/8.4/etc/postgresql
>=3B $ ../..= /bin/psql -U postgres postgres
>=3B -->=3B test
>=3B psql (8.4= .2)
>=3B Type "help" for help.
>=3B
>=3B postgres=3D# >=3B
>=3B >=3B On a side-note=2C I observered that timing value= in ~/.psqlrc was
>=3B >=3B ignored by psql -c "..." command but not= by echo "...."|psqlThought
>=3B >=3B it was strange.
>=3B
= >=3B Yeah=2C that is odd.
>=3B
>=3B --
>=3B Bruce Momj= ian <=3Bbruce@momjian.us>=3B http://momjian.us
>=3B Ente= rpriseDB http://enterprisedb.com
>=3B
= >=3B + None of us is going to be here forever. +
<= br />
Hotmail is redefining busy with tools for the New Busy. Get more= from your inbox. Se= e how. = --_b8345835-e341-4b04-83a3-b28dc25b1ffe_--