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';

On Wed, Oct 14, 2020 at 3:57 PM Thomas Kellerer <shammat@gmx.net> wrote:

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';

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 :)

--
Magnus Hagander
Me: https://www.hagander.net/ <http://www.hagander.net/&gt;
Work: https://www.redpill-linpro.com/ <http://www.redpill-linpro.com/&gt;

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

st 14. 10. 2020 v 17:10 odesílatel Tom Lane <tgl@sss.pgh.pa.us> napsal:

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.

"8kB" is strange unit - maybe we can introduce new - "page"

On Wed, Oct 14, 2020 at 5:10 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:

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.

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

Or if doing it off pg_settings:

SELECT setting::bigint * pg_size_bytes(unit) from pg_settings where
name='shared_buffers'

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.

--
Magnus Hagander
Me: https://www.hagander.net/ <http://www.hagander.net/&gt;
Work: https://www.redpill-linpro.com/ <http://www.redpill-linpro.com/&gt;

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

Hi

Thanks for all the replies.

So at first, I did this:
select (pg_size_bytes(setting) * (select setting from pg_settings where
name = 'block_size')::int) as shared_buffers from pg_settings where name =
'shared_buffers';

But as I understood, that the preferred way would be this, correct..? (at
least, it seems a lot simpler, I agree)
SELECT pg_size_bytes(current_setting('shared_buffers'))

Regards
Raul

Kontakt Tom Lane (<tgl@sss.pgh.pa.us>) kirjutas kuupäeval K, 14. oktoober
2020 kell 18:23:

On Wed, Oct 14, 2020 at 5:23 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:

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"

Right, but it would certainly work for *this* case using pg_asettings, is
what I meant.

That said, I think it'd then actually be better to teach pg_size_bytes to
know that "MB" is the same as "1MB" and parse that. That might be something
that would actually be useful in other cases as well -- basically as a way
to get conversion units in general. Basically if the string is "unit only"
then consider that as "1 unit".

--
Magnus Hagander
Me: https://www.hagander.net/ <http://www.hagander.net/&gt;
Work: https://www.redpill-linpro.com/ <http://www.redpill-linpro.com/&gt;

Hi

Thanks, you solution works for most of the databases. But now I noticed
that we have single 9.5 version also.
And seems like this function *pg_size_bytes* came from 9.6

# psql -U postgres -Atc "select

pg_size_bytes(current_setting('shared_buffers'));"
ERROR: function pg_size_bytes(text) does not exist
LINE 1: select pg_size_bytes(current_setting('shared_buffers'));
^
HINT: No function matches the given name and argument types. You might
need to add explicit type casts.

Do you perhaps happen to know a way how to get this to work in 9.5 also..?

Thanks again.

Regards
Raul

Kontakt Magnus Hagander (<magnus@hagander.net>) kirjutas kuupäeval K, 14.
oktoober 2020 kell 18:38: