HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

Postgres settings reported differently in pgsql

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
differentlysettingspostgresreportedpgsql

Problem

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?

Solution

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

Code Snippets

show shared_buffers;
 shared_buffers 
────────────────
 768MB


select name, setting from pg_settings where name = 'shared_buffers';
      name      │ setting 
────────────────┼─────────
 shared_buffers │ 98304
SELECT 98304 * 8192 / (1024 * 1024);
 ?column? 
──────────
      768
SELECT current_setting('shared_buffer');
 current_setting
-----------------
 768MB

Context

StackExchange Database Administrators Q#59635, answer score: 5

Revisions (0)

No revisions yet.