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

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

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