Subject: Change to 'timing on' globally

From: Balkrishna Sharma <[email-masked]>
Date: Mon, 5 Jul 2010 13:00:44 -0400

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 by a specificied user ?
Thanks,-Bala 		 	   		  
_________________________________________________________________
The New Busy is not the too busy. Combine all your e-mail accounts with Hotmail.
http://www.windowslive.com/campaign/thenewbusy?tile=multiaccount&ocid=PID28326::T:WLMTAGL:ON:WL:en-US:WM_HMP:042010_4

---

From: Alvaro Herrera <[email-masked]>
Date: Mon, 05 Jul 2010 13:10:30 -0400

> Is there a way to set the default to 'timing on' globally across the database or atleast across all psql statements by a specificied user ?

.psqlrc ?

---

From: Balkrishna Sharma <[email-masked]>
Date: Mon, 5 Jul 2010 15:40:47 -0400

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.

> CC: [email-masked]
> Subject: Re: [ADMIN] Change to 'timing on' globally
> From: [email-masked]
> To: [email-masked]
> Date: Mon, 5 Jul 2010 13:10:30 -0400
> 
> -- 
> Sent via pgsql-admin mailing list ([email-masked])
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
 		 	   		  
_________________________________________________________________
Hotmail has tools for the New Busy. Search, chat and e-mail from your inbox.
http://www.windowslive.com/campaign/thenewbusy?ocid=PID28326::T:WLMTAGL:ON:WL:en-US:WM_HMP:042010_1

---

From: Tim Landscheidt <[email-masked]>
Date: Mon, 05 Jul 2010 20:16:15 +0000

Balkrishna Sharma <[email-masked]> wrote on -admin:

> [...]

This got me wondering: With the 8.4 RPMs, in psql's man
page, there is an unexplained
"*PREFIX*/share/psqlrc.sample"; the sample psqlrc installed
in /usr/share/pgsql/psqlrc.sample (sic!) says:

| --  Copy this to your sysconf directory (typically /usr/local/pgsql/etc) and
| --  rename it psqlrc.

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.

Tim

---

From: Tom Lane <[email-masked]>
Date: Mon, 05 Jul 2010 20:08:39 -0400

> Con: Adds another level of complexity to the build system.

The bigger "con" is that anyone reading the docs on-line would be
given information that might be inappropriate for their platform.

			regards, tom lane

---

From: Tim Landscheidt <[email-masked]>
Date: Tue, 06 Jul 2010 12:38:39 +0000

> 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.

Tim
diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index df06517..4f3ef5c 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -79,7 +79,9 @@ PostgreSQL documentation
       <para>
       Specifies that <application>psql</application> is to execute one
       command string, <replaceable class="parameter">command</replaceable>,
-      and then exit. This is useful in shell scripts.
+      and then exit. This is useful in shell scripts. Start-up files
+      (<filename>psqlrc</filename> and <filename>~/.psqlrc</filename>) are
+      ignored with this option.
       </para>
       <para>
       <replaceable class="parameter">command</replaceable> must be either
@@ -3090,10 +3092,12 @@ $endif
   <itemizedlist>
    <listitem>
     <para>
-     Before starting up, <application>psql</application> attempts to
+     Unless it is passed an <option>-X</option>
+     or <option>-c</option> option,
+     <application>psql</application> attempts to
      read and execute commands from the system-wide
      <filename>psqlrc</filename> file and the user's
-     <filename>~/.psqlrc</filename> file.
+     <filename>~/.psqlrc</filename> file before starting up.
      (On Windows, the user's startup file is named
      <filename>%APPDATA%\postgresql\psqlrc.conf</filename>.)
      See <filename><replaceable>PREFIX</>/share/psqlrc.sample</>

---

From: Tim Landscheidt <[email-masked]>
Date: Tue, 06 Jul 2010 12:53:45 +0000

> given information that might be inappropriate for their platform.

Why? That would only be the case if the on-line docs were
copied verbatim from some developer's working directory (or
a buildfarm box). I don't know the current workflow, but it
shouldn't be a problem to "./configure --host=DOC-DUMMY &&
make www-install".

  The biggest con I see is with the generation. If I under-
stood the discussion on the diagrams correctly, the tar
balls for the packagers have the documentation already done,
so the generation of the docs would either have to be pushed
to a later stage, or the users of packages wouldn't have any
benefit at all.

  So, looking at the pro: Shelved it.

Tim

---

From: Tom Lane <[email-masked]>
Date: Tue, 06 Jul 2010 10:25:17 -0400

> a buildfarm box).

They have to be built *somewhere* ...

			regards, tom lane

---

From: Bruce Momjian <[email-masked]>
Date: Tue, 6 Jul 2010 10:48:48 -0400 (EDT)

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)?

> 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  <[email-masked]>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

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

---

From: Tim Landscheidt <[email-masked]>
Date: Tue, 06 Jul 2010 15:00:49 +0000

> They have to be built *somewhere* ...

That's why the paragraph read:

| Why? That would only be the case if the on-line docs were
| copied verbatim from some developer's working directory (or
| a buildfarm box). I don't know the current workflow, but it
| shouldn't be a problem to "./configure --host=DOC-DUMMY &&
| make www-install".

Tim

---

From: Balkrishna Sharma <[email-masked]>
Date: Tue, 6 Jul 2010 11:20:52 -0400

> 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.

> From: [email-masked]
> Date: Tue, 6 Jul 2010 10:48:48 -0400
> CC: [email-masked]; [email-masked]
> 
> Balkrishna Sharma wrote:
> > 
 		 	   		  
_________________________________________________________________
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

---

From: Bruce Momjian <[email-masked]>
Date: Tue, 6 Jul 2010 11:29:12 -0400 (EDT)

> 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: Peter Eisentraut <[email-masked]>
Date: Tue, 06 Jul 2010 18:36:10 +0300

> 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: Robert Haas <[email-masked]>
Date: Fri, 9 Jul 2010 20:50:59 -0400

> Patch attached to clarify the latter.

Committed.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company