Thread: Parameter value from (mb/gb) to bytes

Raul Kaubi schrieb am 14.10.2020 um 12:22:
> Is there a simple way to dynamically get for example parameter
> „shared buffers“ value (megabytes or gigabytes) to bytes, for
> monitoring perspective..?>
>  
>
> At the moment, this gives me value in GB.
>
> # psql -U postgres -Atc "show shared_buffers;"
> 1GB
>
> This value may as well be in MB. So I am looking a way to dynamically get the value in bytes.

Instead of using "show" you can use a SELECT with pg_size_bytes():

  select pg_size_bytes(setting)
  from pg_settings
  where name = 'shared_buffers';

Thomas Kellerer schrieb am 14.10.2020 um 15:55:
> Raul Kaubi schrieb am 14.10.2020 um 12:22:
>> Is there a simple way to dynamically get for example parameter
>> „shared buffers“ value (megabytes or gigabytes) to bytes, for
>> monitoring perspective..?>
>>  
>>
>> At the moment, this gives me value in GB.
>>
>> # psql -U postgres -Atc "show shared_buffers;"
>> 1GB
>>
>> This value may as well be in MB. So I am looking a way to dynamically get the value in bytes.
>
> Instead of using "show" you can use a SELECT with pg_size_bytes():
>
>   select pg_size_bytes(setting)
>   from pg_settings
>   where name = 'shared_buffers';

Ah, forgot that shared_buffers is in 8K pages.

So you actually need:

   select pg_size_bytes(setting) * 8192
   from pg_settings
   where name = 'shared_buffers';

Magnus Hagander <magnus@hagander.net> writes:
> On Wed, Oct 14, 2020 at 3:57 PM Thomas Kellerer <shammat@gmx.net> wrote:
>> select pg_size_bytes(setting) * 8192
>> from pg_settings
>> where name = 'shared_buffers';

> Actually, it doesn't have to be in 8k pages, that depends on the build
> options. So if you want to be perfectly correct, you should probably
> multiply with current_setting('block_size') instead of a hardcoded 8192 :)

It's fairly annoying that this doesn't work:

regression=# select pg_size_bytes(setting||' '||unit) from pg_settings where name = 'shared_buffers';
ERROR:  invalid size: "16384 8kB"
DETAIL:  Invalid size unit: "8kB".
HINT:  Valid units are "bytes", "kB", "MB", "GB", and "TB".

Maybe we should teach pg_size_bytes to cope with that.

            regards, tom lane

## Magnus Hagander (magnus@hagander.net):

> Actually, it doesn't have to be in 8k pages, that depends on the build
> options. So if you want to be perfectly correct, you should probably
> multiply with current_setting('block_size') instead of a hardcoded 8192 :)

More self-contained:
  select pg_size_bytes(setting) * pg_size_bytes(unit)
  from pg_settings
  where name = 'shared_buffers';

I guess that's what that unit column is there for.

Regards,
Christoph

-- 
Spare Space

Magnus Hagander <magnus@hagander.net> writes:
> On Wed, Oct 14, 2020 at 5:10 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> It's fairly annoying that this doesn't work:
>> regression=# select pg_size_bytes(setting||' '||unit) from pg_settings
>> where name = 'shared_buffers';

> Actually thinking though, surely *this* particular case can be spelled as:
> SELECT  pg_size_bytes(current_setting('shared_buffers'))

Yeah, that might be the most recommendable way.

> Or if doing it off pg_settings:
> SELECT setting::bigint * pg_size_bytes(unit) from pg_settings where
> name='shared_buffers'

No, because that will fail for any unit other than '8kB', eg

regression=# select pg_size_bytes('MB');
ERROR:  invalid size: "MB"

> I'm not sure having pg_size_bytes() parse "16384 8kB" is reasonable, I have
> a feeling that could lead to a lot of accidental entries giving the wrong
> results.

Yeah, that's definitely a risk.  Given that current_setting() already does
what's needed, that's probably a better answer.

            regards, tom lane