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 by a specificied user ?

.psqlrc ?

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: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Change to 'timing on' globally
From: alvherre@commandprompt.com
To: b_ki@hotmail.com
Date: Mon, 5 Jul 2010 13:10:30 -0400

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 by a specificied user ?

.psqlrc ?

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
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

Balkrishna Sharma <b_ki@hotmail.com> 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.
[...]

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

Tim Landscheidt <tim@tim-landscheidt.de> writes:

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 bigger "con" is that anyone reading the docs on-line would be
given information that might be inappropriate for their platform.

regards, tom lane

Balkrishna Sharma <b_ki@hotmail.com> 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.

Tim

Tom Lane <tgl@sss.pgh.pa.us> 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 bigger "con" is that anyone reading the docs on-line would be
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

Tim Landscheidt <tim@tim-landscheidt.de> writes:

Tom Lane <tgl@sss.pgh.pa.us> wrote:

The bigger "con" is that anyone reading the docs on-line would be
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).

They have to be built *somewhere* ...

regards, tom lane

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

Tom Lane <tgl@sss.pgh.pa.us> wrote:

Tim Landscheidt <tim@tim-landscheidt.de> writes:

Tom Lane <tgl@sss.pgh.pa.us> wrote:

The bigger "con" is that anyone reading the docs on-line would be
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).

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

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

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

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.

On Tue, Jul 6, 2010 at 8:38 AM, Tim Landscheidt <tim@tim-landscheidt.de> wrote:

Balkrishna Sharma <b_ki@hotmail.com> 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