Postgres settings reported differently in pgsql

Question

We are running PostgreSQL 9.2 on CentOS 6 and I am seeing the settings are reported differently when I run the show effective_cache_size; command vs select * from pg_settings where name = 'effective_cache_size'; query. As far as I can understand these commands should be identical. For example

show effective_cache_size;
effective_cache_size
----------------------
 2816MB
(1 row)

vs

select name,setting from pg_settings where name = 'effective_cache_size';
name | effective_cache_size
setting | 360448

I am trying to figure out the value used by PostgreSQL. I get the same results if I run this as a superuser. Which one do I trust?

• user1822: In your question you talk about shared buffers, yet your example shows effective_cache_size.
• Colin 't Hart: Isn't "effective_cache_size" a parameter that can be set on cluster and on session level?
• sumit: @a_horse_with_no_name edited the question to match the example output, thanks.
• sumit: @colin-t-hart no setting has been done to this param besides the config file

Answer 1

I see the same difference on my test database:

show shared_buffers;
 shared_buffers 
────────────────
 768MB
select name, setting from pg_settings where name = 'shared_buffers';
 name │ setting 
────────────────┼─────────
 shared_buffers │ 98304

Then watching the numbers for a short while, I set up the following query:

SELECT 98304 * 8192 / (1024 * 1024);
 ?column? 
──────────
 768

So, to me it looks like that pg_settings reports these sizes in 8 kb pages (8192 refers to this fact).

Note that there is a third way to check settings, namely the current_setting() function:

SELECT current_setting('shared_buffer');
 current_setting
-----------------
 768MB

• sumit: thanks @dezso for the explanation, same calculation applies for my sample as well