Show <parameter> in psql does any calculations?

Started by Raghavendraover 14 years ago6 messagesgeneral

raghavendra.rao@enterprisedb.com

over 14 years ago

Respected,

Am in PG 9.1. See below ouputs.

*By query:*
postgres=# SELECT name, setting, unit,context FROM pg_settings WHERE
category like '%Resource Usage / Memory%' ORDER BY name;
name | setting | unit | context
---------------------------+---------+------+------------
maintenance_work_mem | 16384 | kB | user
max_prepared_transactions | 0 | | postmaster
max_stack_depth | 2048 | kB | superuser
shared_buffers | 4096 | 8kB | postmaster
temp_buffers | 1024 | 8kB | user
track_activity_query_size | 1024 | | postmaster
work_mem | 2048 | kB | user
(7 rows)

*By session command:*
postgres=# show work_mem ;
work_mem
----------
2MB
(1 row)

postgres=# show shared_buffers ;
shared_buffers
----------------
32MB
(1 row)

By the query, its clear that "setting" value is multiplied with unit to get
the sesssion level command value which is displayed here. However, I am
trying to get the query of it which is executed at session-level with SHOW
command. I tried "psql -E" option tooo..

-bash-3.2$ psql -E
psql.bin (9.1.0)
Type "help" for help.

postgres=# show work_mem ;
work_mem
----------
2MB
(1 row)

Why the query is not displayed with -E option ?
Am I missing something here, please help me in clarifying it.

---
Regards,
Raghavendra
EnterpriseDB Corporation
Blog: http://raghavt.blogspot.com/

Guillaume Lelarge

guillaume@lelarge.info

over 14 years ago

In reply to: Raghavendra (#1)

Re: Show <parameter> in psql does any calculations?

On Tue, 2011-12-27 at 14:56 +0530, Raghavendra wrote:

Respected,

Am in PG 9.1. See below ouputs.

*By query:*
postgres=# SELECT name, setting, unit,context FROM pg_settings WHERE
category like '%Resource Usage / Memory%' ORDER BY name;
name | setting | unit | context
---------------------------+---------+------+------------
maintenance_work_mem | 16384 | kB | user
max_prepared_transactions | 0 | | postmaster
max_stack_depth | 2048 | kB | superuser
shared_buffers | 4096 | 8kB | postmaster
temp_buffers | 1024 | 8kB | user
track_activity_query_size | 1024 | | postmaster
work_mem | 2048 | kB | user
(7 rows)

*By session command:*
postgres=# show work_mem ;
work_mem
----------
2MB
(1 row)

postgres=# show shared_buffers ;
shared_buffers
----------------
32MB
(1 row)

By the query, its clear that "setting" value is multiplied with unit to get
the sesssion level command value which is displayed here. However, I am
trying to get the query of it which is executed at session-level with SHOW
command. I tried "psql -E" option tooo..

-bash-3.2$ psql -E
psql.bin (9.1.0)
Type "help" for help.

postgres=# show work_mem ;
work_mem
----------
2MB
(1 row)

Why the query is not displayed with -E option ?
Am I missing something here, please help me in clarifying it.

-E works only for meta-commands, ie commands which start with \

Moreover, SHOW is an actual SQL command. So I'm not sure what you're
trying to find with the -E command line switch.

--
Guillaume
http://blog.guillaume.lelarge.info
http://www.dalibo.com
PostgreSQL Sessions #3: http://www.postgresql-sessions.org

raghavendra.rao@enterprisedb.com

over 14 years ago

In reply to: Guillaume Lelarge (#2)

Re: Show <parameter> in psql does any calculations?

On Tue, Dec 27, 2011 at 3:16 PM, Guillaume Lelarge
<guillaume@lelarge.info>wrote:

On Tue, 2011-12-27 at 14:56 +0530, Raghavendra wrote:

Respected,

Am in PG 9.1. See below ouputs.

*By query:*
postgres=# SELECT name, setting, unit,context FROM pg_settings WHERE
category like '%Resource Usage / Memory%' ORDER BY name;
name | setting | unit | context
---------------------------+---------+------+------------
maintenance_work_mem | 16384 | kB | user
max_prepared_transactions | 0 | | postmaster
max_stack_depth | 2048 | kB | superuser
shared_buffers | 4096 | 8kB | postmaster
temp_buffers | 1024 | 8kB | user
track_activity_query_size | 1024 | | postmaster
work_mem | 2048 | kB | user
(7 rows)

*By session command:*
postgres=# show work_mem ;
work_mem
----------
2MB
(1 row)

postgres=# show shared_buffers ;
shared_buffers
----------------
32MB
(1 row)

By the query, its clear that "setting" value is multiplied with unit to

get

the sesssion level command value which is displayed here. However, I am
trying to get the query of it which is executed at session-level with

SHOW

command. I tried "psql -E" option tooo..

-bash-3.2$ psql -E
psql.bin (9.1.0)
Type "help" for help.

postgres=# show work_mem ;
work_mem
----------
2MB
(1 row)

Why the query is not displayed with -E option ?
Am I missing something here, please help me in clarifying it.

-E works only for meta-commands, ie commands which start with \

Moreover, SHOW is an actual SQL command. So I'm not sure what you're
trying to find with the -E command line switch.

Hey, Many thanks for your quick response.

I am looking how SHOW command value is converted into MB's or GB's ?

---
Regards,
Raghavendra
EnterpriseDB Corporation
Blog: http://raghavt.blogspot.com/

Guillaume Lelarge

guillaume@lelarge.info

over 14 years ago

In reply to: Raghavendra (#3)

Re: Show <parameter> in psql does any calculations?

On Tue, 2011-12-27 at 15:21 +0530, Raghavendra wrote:

On Tue, Dec 27, 2011 at 3:16 PM, Guillaume Lelarge
<guillaume@lelarge.info> wrote:
On Tue, 2011-12-27 at 14:56 +0530, Raghavendra wrote:

Respected,

Am in PG 9.1. See below ouputs.

*By query:*
postgres=# SELECT name, setting, unit,context FROM

pg_settings WHERE

category like '%Resource Usage / Memory%' ORDER BY name;
name | setting | unit | context
---------------------------+---------+------+------------
maintenance_work_mem | 16384 | kB | user
max_prepared_transactions | 0 | |

postmaster

max_stack_depth | 2048 | kB |

superuser

shared_buffers | 4096 | 8kB |

postmaster

temp_buffers | 1024 | 8kB | user
track_activity_query_size | 1024 | | postmaster
work_mem | 2048 | kB | user
(7 rows)

*By session command:*
postgres=# show work_mem ;
work_mem
----------
2MB
(1 row)

postgres=# show shared_buffers ;
shared_buffers
----------------
32MB
(1 row)

By the query, its clear that "setting" value is multiplied

with unit to get

the sesssion level command value which is displayed here.

However, I am

trying to get the query of it which is executed at

session-level with SHOW

command. I tried "psql -E" option tooo..

-bash-3.2$ psql -E
psql.bin (9.1.0)
Type "help" for help.

postgres=# show work_mem ;
work_mem
----------
2MB
(1 row)

Why the query is not displayed with -E option ?
Am I missing something here, please help me in clarifying

it.

-E works only for meta-commands, ie commands which start with
\

Moreover, SHOW is an actual SQL command. So I'm not sure what
you're
trying to find with the -E command line switch.

Hey, Many thanks for your quick response.

I am looking how SHOW command value is converted into MB's or GB's ?

AFAIUI, SHOW gives you the value used internaly, so it doesn't look at
pg_settings at all.

--
Guillaume
http://blog.guillaume.lelarge.info
http://www.dalibo.com
PostgreSQL Sessions #3: http://www.postgresql-sessions.org

Filip Rembiałkowski

plk.zuber@gmail.com

over 14 years ago

In reply to: Raghavendra (#3)

Re: Show <parameter> in psql does any calculations?

so as long as actual logic is buried in the guts of psql, best you can
do in SQL to get human-readable value is

SELECT name, setting, unit, case when unit='kB' then
pg_size_pretty(setting::int*1024) when unit='8kB' then
pg_size_pretty(setting::int*1024*8) else coalesce(setting||'
'||unit,setting) end AS setting_human_readable,
context FROM pg_settings WHERE category like '%Resource Usage /
Memory%' ORDER BY name;

Filip

2011/12/27 Raghavendra <raghavendra.rao@enterprisedb.com>:

Show quoted text

On Tue, Dec 27, 2011 at 3:16 PM, Guillaume Lelarge <guillaume@lelarge.info>
wrote:

On Tue, 2011-12-27 at 14:56 +0530, Raghavendra wrote:

Respected,

Am in PG 9.1. See below ouputs.

*By query:*
postgres=# SELECT name, setting, unit,context FROM pg_settings WHERE
category like '%Resource Usage / Memory%' ORDER BY name;
name | setting | unit | context
---------------------------+---------+------+------------
maintenance_work_mem | 16384 | kB | user
max_prepared_transactions | 0 | | postmaster
max_stack_depth | 2048 | kB | superuser
shared_buffers | 4096 | 8kB | postmaster
temp_buffers | 1024 | 8kB | user
track_activity_query_size | 1024 | | postmaster
work_mem | 2048 | kB | user
(7 rows)

*By session command:*
postgres=# show work_mem ;
work_mem
----------
2MB
(1 row)

postgres=# show shared_buffers ;
shared_buffers
----------------
32MB
(1 row)

By the query, its clear that "setting" value is multiplied with unit to
get
the sesssion level command value which is displayed here. However, I am
trying to get the query of it which is executed at session-level with
SHOW
command. I tried "psql -E" option tooo..

-bash-3.2$ psql -E
psql.bin (9.1.0)
Type "help" for help.

postgres=# show work_mem ;
work_mem
----------
2MB
(1 row)

Why the query is not displayed with -E option ?
Am I missing something here, please help me in clarifying it.

-E works only for meta-commands, ie commands which start with \

Moreover, SHOW is an actual SQL command. So I'm not sure what you're
trying to find with the -E command line switch.

Hey, Many thanks for your quick response.

I am looking how SHOW command value is converted into MB's or GB's ?

---
Regards,
Raghavendra
EnterpriseDB Corporation
Blog: http://raghavt.blogspot.com/

raghavendra.rao@enterprisedb.com

over 14 years ago

In reply to: Filip Rembiałkowski (#5)

Re: Show <parameter> in psql does any calculations?

Thanks all for clarifying me... Good to know this...

---
Regards,
Raghavendra
EnterpriseDB Corporation
Blog: http://raghavt.blogspot.com/

2011/12/27 Filip Rembiałkowski <plk.zuber@gmail.com>

Show quoted text

so as long as actual logic is buried in the guts of psql, best you can
do in SQL to get human-readable value is

SELECT name, setting, unit, case when unit='kB' then
pg_size_pretty(setting::int*1024) when unit='8kB' then
pg_size_pretty(setting::int*1024*8) else coalesce(setting||'
'||unit,setting) end AS setting_human_readable,
context FROM pg_settings WHERE category like '%Resource Usage /
Memory%' ORDER BY name;

Filip

2011/12/27 Raghavendra <raghavendra.rao@enterprisedb.com>:

On Tue, Dec 27, 2011 at 3:16 PM, Guillaume Lelarge <

guillaume@lelarge.info>

wrote:

On Tue, 2011-12-27 at 14:56 +0530, Raghavendra wrote:

Respected,

Am in PG 9.1. See below ouputs.

*By query:*
postgres=# SELECT name, setting, unit,context FROM pg_settings WHERE
category like '%Resource Usage / Memory%' ORDER BY name;
name | setting | unit | context
---------------------------+---------+------+------------
maintenance_work_mem | 16384 | kB | user
max_prepared_transactions | 0 | | postmaster
max_stack_depth | 2048 | kB | superuser
shared_buffers | 4096 | 8kB | postmaster
temp_buffers | 1024 | 8kB | user
track_activity_query_size | 1024 | | postmaster
work_mem | 2048 | kB | user
(7 rows)

*By session command:*
postgres=# show work_mem ;
work_mem
----------
2MB
(1 row)

postgres=# show shared_buffers ;
shared_buffers
----------------
32MB
(1 row)

By the query, its clear that "setting" value is multiplied with unit

to

get
the sesssion level command value which is displayed here. However, I

am

trying to get the query of it which is executed at session-level with
SHOW
command. I tried "psql -E" option tooo..

-bash-3.2$ psql -E
psql.bin (9.1.0)
Type "help" for help.

postgres=# show work_mem ;
work_mem
----------
2MB
(1 row)

Why the query is not displayed with -E option ?
Am I missing something here, please help me in clarifying it.

-E works only for meta-commands, ie commands which start with \

Moreover, SHOW is an actual SQL command. So I'm not sure what you're
trying to find with the -E command line switch.

Hey, Many thanks for your quick response.

I am looking how SHOW command value is converted into MB's or GB's ?

---
Regards,
Raghavendra
EnterpriseDB Corporation
Blog: http://raghavt.blogspot.com/