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_-- From bruce@momjian.us Tue Jun 2 01:38:59 2026 Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id 128D1632B87 for ; Tue, 6 Jul 2010 12:29:22 -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 17299-05 for ; Tue, 6 Jul 2010 15:29:14 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from momjian.us (momjian.us [70.90.9.53]) by mail.postgresql.org (Postfix) with ESMTP id 4806D62F51F for ; Tue, 6 Jul 2010 12:29:14 -0300 (ADT) Received: (from bruce@localhost) by momjian.us (8.11.6/8.11.6) id o66FTCX15444; Tue, 6 Jul 2010 11:29:12 -0400 (EDT) From: Bruce Momjian Message-Id: <201007061529.o66FTCX15444@momjian.us> Subject: Re: Change to 'timing on' globally In-Reply-To: To: Balkrishna Sharma Date: Tue, 6 Jul 2010 11:29:12 -0400 (EDT) CC: alvherre@commandprompt.com, pgsql-admin@postgresql.org X-Mailer: ELM [version 2.4ME+ PL124 (25)] MIME-Version: 1.0 Content-Transfer-Encoding: 7bit Content-Type: text/plain; charset="US-ASCII" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.91 tagged_above=-10 required=5 tests=BAYES_00=-1.9, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201007/16 X-Sequence-Number: 35035 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 CentOS as 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)ON SERVER 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 the psql 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 more informative 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 the server 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 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 http://momjian.us EnterpriseDB http://enterprisedb.com + None of us is going to be here forever. + From peter_e@gmx.net Tue Jun 2 01:38:59 2026 Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id A3CC06332AE for ; Tue, 6 Jul 2010 12:36:20 -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 41355-02 for ; Tue, 6 Jul 2010 15:36:13 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail.gmx.net (mail.gmx.net [213.165.64.20]) by mail.postgresql.org (Postfix) with SMTP id DA17463327D for ; Tue, 6 Jul 2010 12:36:12 -0300 (ADT) Received: (qmail invoked by alias); 06 Jul 2010 15:36:11 -0000 Received: from a88-115-218-165.elisa-laajakaista.fi (EHLO [10.0.0.101]) [88.115.218.165] by mail.gmx.net (mp069) with SMTP; 06 Jul 2010 17:36:11 +0200 X-Authenticated: #495269 X-Provags-ID: V01U2FsdGVkX19jvQRzhnhjgbJ+nK8fgi/zJfDSzhp8ddP0glbr+m MP7cZ4DCljVKry Subject: Re: "Localizing" paths? From: Peter Eisentraut To: Tim Landscheidt Cc: pgsql-docs@postgresql.org In-Reply-To: References: <1278349811-sup-6775@alvh.no-ip.org>

Content-Type: text/plain; charset="UTF-8" Date: Tue, 06 Jul 2010 18:36:10 +0300 Message-ID: <1278430570.24375.0.camel@vanquo.pezone.net> Mime-Version: 1.0 X-Mailer: Evolution 2.30.1.2 Content-Transfer-Encoding: 8bit X-Y-GMX-Trusted: 0 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.79 tagged_above=-5 required=5 tests=BAYES_50=0.8, RCVD_IN_DNSWL_NONE=-0.0001, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201007/60 X-Sequence-Number: 5716 On mån, 2010-07-05 at 20:16 +0000, Tim Landscheidt wrote: > Would it be feasible and desirable to "localize" the paths > in the configure stage? Pro: Fewer guesses by new users. > Con: Adds another level of complexity to the build system. The documentation is shipped pre-built in the source tarball. So other than with a sed, we can't really change it to match local situations. From robertmhaas@gmail.com Tue Jun 2 01:38:59 2026 Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id 8DAAB63242D for ; Fri, 9 Jul 2010 21:51:08 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024) with ESMTP id 10459-03 for ; Sat, 10 Jul 2010 00:51:01 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-iw0-f174.google.com (mail-iw0-f174.google.com [209.85.214.174]) by mail.postgresql.org (Postfix) with ESMTP id 50D76633384 for ; Fri, 9 Jul 2010 21:51:01 -0300 (ADT) Received: by iwn7 with SMTP id 7so2791079iwn.19 for ; Fri, 09 Jul 2010 17:50:59 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:received:received:in-reply-to :references:date:message-id:subject:from:to:content-type; bh=DhR0aJMLdJP5hER0SXL6wZvnsPJ544uL6hTCKfrdtqI=; b=XNVYHj5OvLmazJMn6qLcbj7LWRLL3s24C/kyyWOwHebWWHJab3kt68oRsjr1KqgZ0C CvmadXWIAg+XpkRGG6Ono3bNDQaN3DH5S80ndCkZdEMnyTiRtpMLTa5ZtUNRpVDntqO6 onk77nx1GsFiJZ+MGiYl3YTpfNQ/UotSyqha4= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :content-type; b=nlEjcJR+Qmytu564OysxKkG54QoPW0QxZwm2useiuq7Dawa7YzYnpjm4NMeiDQ3HsA BVslM/mGphJlXWeW+urzR3fEY6ZSfV36V5QXM4tldVT8kGfTqZv/p4dqR764iPmM3EdC l0NuVevTPkWyJq1e2QGugPDYlPNjKA6ibS0lw= MIME-Version: 1.0 Received: by 10.231.30.129 with SMTP id u1mr11280121ibc.63.1278723059446; Fri, 09 Jul 2010 17:50:59 -0700 (PDT) Received: by 10.231.30.131 with HTTP; Fri, 9 Jul 2010 17:50:59 -0700 (PDT) In-Reply-To: References: <1278349811-sup-6775@alvh.no-ip.org>

Date: Fri, 9 Jul 2010 20:50:59 -0400 Message-ID: Subject: Re: [PATCH] Clarify that "psql -c" ignores psqlrc files From: Robert Haas To: pgsql-docs@postgresql.org Content-Type: text/plain; charset=ISO-8859-1 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.9 tagged_above=-5 required=5 tests=BAYES_00=-1.9 X-Spam-Level: X-Archive-Number: 201007/88 X-Sequence-Number: 5744 On Tue, Jul 6, 2010 at 8:38 AM, Tim Landscheidt wrote: > Balkrishna Sharma wrote on -admin: > >> 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. > >> 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. >> [...] > > Patch attached to clarify the latter. Committed. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company