Thread: Change to 'timing on' globally

Excerpts from Balkrishna Sharma's message of lun jul 05 13:00:44 -0400 2010:
>
> I know that we can toggle the timing at session level by using \timing in psql.
> Is there a way to set the default to 'timing on' globally across the database or atleast across all psql statements
bya specificied user ? 

.psqlrc ?

Balkrishna Sharma wrote:
>
> Thanks. If I want to do at system-wide level, where do I store the
> psqlrc file (assuming I want to change the timing behavior system-wide)?

> (CentOS 5, 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.

    postgres=#

> On a side-note, I observered that timing value in ~/.psqlrc was
> ignored by psql -c "..." command but not by echo "...."|psqlThought
> it was strange.

Yeah, that is odd.

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + None of us is going to be here forever. +

Balkrishna Sharma wrote:
>
> > I just tested it here on Ubuntu and it worked:I followed your steps and it worked in the way you indicated, on
CentOSas well. But it still does not:a. work with psql -c "query" syntax. (Works  in echo mode or in interactive
mode.)b.it does not still seem to work if you fire the queries from a client box (in any mode - interactive or
otherwise)ONSERVER I get:Timing is on.             now------------------------------ 2010-07-06 11:06:13.16734-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
thepsql are hanging for some other reasons. I also need to capture the timing of each query. So I need timing to be on. 
> Doing the following captures the timing but I don't know which psql statement 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 pts/1    S    Jul05   0:00 psql4672  0.0  0.0
155636 1672 pts/1    S    Jul05   0:00 psql4713  0.0  0.0 155636  1672 pts/1    S    Jul05   0:00 psql4737  0.0  0.0
155636 1672 pts/1    S    Jul05   0:00 psql4798  0.0  0.0 155636  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 = 1000 and field_id =1988;'  does make the ps aux
moreinformative but it does not capture the query timing. From what I understand you cannot mix  ('timing + query') in
"-c"mode. 
> So trying to set 'timing on' outside the individual queries (and preferably outside the client machines) somewhere on
theserver so that psql -c on client would capture the timing automatically. 

I think you need to look at postgresql.conf variables like
log_min_duration_statement, and you are right that psqlrc is only going
to be read for clients on the server machine, and only via psql.

---------------------------------------------------------------------------


>
>
>
> > From: bruce@momjian.us
> > Subject: Re: [ADMIN] Change to 'timing on' globally
> > To: b_ki@hotmail.com
> > Date: Tue, 6 Jul 2010 10:48:48 -0400
> > CC: alvherre@commandprompt.com; pgsql-admin@postgresql.org
> >
> > Balkrishna Sharma wrote:
> > >
> > > Thanks. If I want to do at system-wide level, where do I store the
> > > psqlrc file (assuming I want to change the timing behavior system-wide)?
> >
> > > (CentOS 5, 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.
> >
> >     postgres=#
> >
> > > On a side-note, I observered that timing value in ~/.psqlrc was
> > > ignored by psql -c "..." command but not by echo "...."|psqlThought
> > > it was strange.
> >
> > Yeah, that is odd.
> >
> > --
> >   Bruce Momjian  <bruce@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=PID28326::T:WLMTAGL:ON:WL:en-US:WM_HMP:042010_2
--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + None of us is going to be here forever. +