--_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 [at] momjian.us
> Subject: Re: [ADMIN] Change to 'timing on' globally
> To: b_ki [at] hotmail.com
> Date: Tue=2C 6 Jul 2010 10:48:48 -0400
> CC: alvherre [at] commandprompt.com=3B pgsql-admin [at] postgresql.org
>
> Balkrishna Sharma wrote:
> >
> > 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)=
?
>
> > (CentOS 5=2C Postgres 8.4)
> > $ ./pg_config --sysconfdir/opt/PostgreSQL/8.4/etc/postgresql
>
> > 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.
>
> I just tested it here on Ubuntu and it worked:
>
> $ 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#
>
> > 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.
>
> Yeah=2C that is odd.
>
> --
> Bruce Momjian <bruce [at] momjian.us> http://momjian.us
> EnterpriseDB http://enterprisedb.com
>
> + None of us is going to be here forever. +
____________________________________________________________ _____
Hotmail is redefining busy with tools for the New Busy. Get more from your =
inbox.
http://www.windowslive.com/campaign/thenewbusy?ocid=3DPID283 26::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
<html>
<head>
<style><!--
..hmmessage P
{
margin:0px=3B
padding:0px
}
body.hmmessage
{
font-size: 10pt=3B
font-family:Verdana
}
--></style>
</head>
<body class=3D'hmmessage'>
>=3B I just tested it here on Ubuntu and it worked:<div>I followed your s=
teps and it worked in the way you indicated=2C on CentOS as well. But it st=
ill does not:</div><div>a. work with psql -c "query" syntax. (Works =
=3Bin echo mode or in interactive mode.)</div><div>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)</div><div>ON SERVER I get:</div><div><div>Timing is on.</=
div><div> =3B =3B =3B =3B =3B =3B =3B now<=
/div><div>------------------------------</div><div> =3B2010-07-06 11:06=
:13.16734-04</div><div>(1 row)</div><div>Time: 0.574 ms</div></div><div><br=
></div><div><br></div><div>ON CLIENT I just get:</div><div><div> =3B&nb=
sp=3B =3B =3B =3B =3B =3B =3Bnow</div><div>--=
-----------------------------</div><div> =3B2010-07-06 11:06:28.455395-=
04</div><div>(1 row)</div></div><div><br></div><div><br></div><div>~~~~~~~~=
~~~~~~~~~~~~</div><div>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.</div><div><br></div><div>Doing the following captures the ti=
ming but I don't know which psql statement is hanging when I do ps aux|grep=
psql</div><div>echo '\timing \\select * from =3B........' | psql</div=
><div>On ps aux|grep psql I just see:</div><div><div>>=3B ps aux|grep psq=
l</div><div>2255 =3B0.0 =3B0.0 155636 =3B1668 pts/1 =3B=
=3BS =3B =3BJul05 =3B 0:00 psql</div><div>3883 =
=3B0.0 =3B0.0 155636 =3B1676 pts/1 =3B =3BS =3B &n=
bsp=3BJul05 =3B 0:00 psql</div><div>4672 =3B0.0 =3B0.0 15563=
6 =3B1672 pts/1 =3B =3BS =3B =3BJul05 =3B 0:0=
0 psql</div><div>4713 =3B0.0 =3B0.0 155636 =3B1672 pts/1 &nb=
sp=3B =3BS =3B =3BJul05 =3B 0:00 psql</div><div>4737 &n=
bsp=3B0.0 =3B0.0 155636 =3B1672 pts/1 =3B =3BS =3B=
=3BJul05 =3B 0:00 psql</div><div>4798 =3B0.0 =3B0.0 15=
5636 =3B1668 pts/1 =3B =3BS =3B =3BJul05 =3B =
0:00 psql</div><div>5050 =3B0.0 =3B0.0 155636 =3B1676 pts/1 =
=3B =3BS =3B =3BJul05 =3B 0:00 psql</div><div>5086=
=3B0.0 =3B0.0 155636 =3B1668 pts/1 =3B =3BS =
=3B =3BJul05 =3B 0:00 psql</div><div>5405 =3B0.0 =3B0.0=
155636 =3B1668 pts/1 =3B =3BS =3B =3BJul05 =
=3B 0:00 psql</div><div>7255 =3B0.0 =3B0.0 155644 =3B1796 pt=
s/1 =3B =3BS =3B =3BJul05 =3B 0:00 psql</div></div=
><div><br></div><div><br></div><div>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.</div><div><br></div><div>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.</div><div><br></div><div><br></div><div><br><br>>=3B From: bruce [at] m=
omjian.us<br>>=3B Subject: Re: [ADMIN] Change to 'timing on' globally<br>=
>=3B To: b_ki [at] hotmail.com<br>>=3B Date: Tue=2C 6 Jul 2010 10:48:48 -040=
0<br>>=3B CC: alvherre [at] commandprompt.com=3B pgsql-admin [at] postgresql.org<br=
>>=3B <br>>=3B Balkrishna Sharma wrote:<br>>=3B >=3B <br>>=3B >=
=3B Thanks. If I want to do at system-wide level=2C where do I store the<br=
>>=3B >=3B psqlrc file (assuming I want to change the timing behavior s=
ystem-wide)?<br>>=3B <br>>=3B >=3B (CentOS 5=2C Postgres 8.4)<br>>=
=3B >=3B $ ./pg_config --sysconfdir/opt/PostgreSQL/8.4/etc/postgresql<br>=
>=3B <br>>=3B >=3B But I don't have /opt/PostgreSQL/8.4/etc/postgresq=
l directory. Just<br>>=3B >=3B creating the directory and putting a psq=
lrc file over there does not<br>>=3B >=3B seem to work.<br>>=3B <br>&=
gt=3B I just tested it here on Ubuntu and it worked:<br>>=3B <br>>=3B =
$ sudo mkdir etc<br>>=3B $ sudo mkdir etc/postgresql<br>>=3B $ cd etc=
/postgresql/<br>>=3B $ sudo vi psqlrc<br>>=3B # add \echo test<br>>=
=3B $ pwd<br>>=3B /opt/PostgreSQL/8.4/etc/postgresql<br>>=3B $ ../..=
/bin/psql -U postgres postgres<br>>=3B -->=3B test<br>>=3B psql (8.4=
..2)<br>>=3B Type "help" for help.<br>>=3B <br>>=3B postgres=3D# <b=
r>>=3B <br>>=3B >=3B On a side-note=2C I observered that timing value=
in ~/.psqlrc was<br>>=3B >=3B ignored by psql -c "..." command but not=
by echo "...."|psqlThought<br>>=3B >=3B it was strange.<br>>=3B <br>=
>=3B Yeah=2C that is odd.<br>>=3B <br>>=3B -- <br>>=3B Bruce Momj=
ian <=3Bbruce [at] momjian.us>=3B http://momjian.us<br>>=3B Ente=
rpriseDB http://enterprisedb.com<br>>=3B <br>=
>=3B + None of us is going to be here forever. +<br></div> <=
br /><hr />Hotmail is redefining busy with tools for the New Busy. Get more=
from your inbox. <a href=3D'http://www.windowslive.com/campaign/thenewbusy=
?ocid=3DPID28326::T:WLMTAGL:ON:WL:en-US:WM_HMP:042010_2' target=3D'_new'>Se=
e how.</a></body>
</html>=
--_b8345835-e341-4b04-83a3-b28dc25b1ffe_--
