You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
Would it be possible (or sensible) to output parameters that we "know" will be numbers as actual numbers in JSON? That just makes it easier later on to do mathematics on the result.
We can automatically get the three main groupings of string/integer/real with:
string
SELECT name, setting, vartype, COALESCE(boot_val,'') AS boot_val, source,
COALESCE(sourcefile,'') AS sourcefile, COALESCE(sourceline,0) AS sourceline
FROM pg_settings
WHERE vartype NOT IN ('integer','real')
ORDER BY name ASC;
integer
SELECT name, setting::integer, vartype, COALESCE(boot_val,'')::integer AS boot_val, source,
COALESCE(sourcefile,'') AS sourcefile, COALESCE(sourceline,0) AS sourceline
FROM pg_settings
WHERE vartype = 'integer'
ORDER BY name ASC;
real
SELECT name, setting::real, vartype, COALESCE(boot_val,'')::real AS boot_val, source,
COALESCE(sourcefile,'') AS sourcefile, COALESCE(sourceline,0) AS sourceline
FROM pg_settings
WHERE vartype = 'real'
ORDER BY name ASC;
I guess we don't really need vartype in there, but I thought it may be helpful (or needed) when the JSON is created.
Also, I've added column aliases "AS boot_val", etc. just so that the SQL output is human readable, rather than columns being called "coalesce".
I did a basic test of the theory here:
integer
postgres=# select array_to_json(array_agg(row_to_json(t)))
postgres-# from (
postgres(# SELECT name, setting::integer
postgres(# FROM pg_settings
postgres(# WHERE vartype = 'integer'
postgres(# AND name IN ('maintenance_work_mem','max_connections','max_files_per_process')
postgres(# ORDER BY name ASC
postgres(# )t
postgres-# ;
array_to_json
--------------------------------------------------------------------------------------------------------------------------------------------
[{"name":"maintenance_work_mem","setting":65536},{"name":"max_connections","setting":100},{"name":"max_files_per_process","setting":1000}]
(1 row)
real
postgres=# select array_to_json(array_agg(row_to_json(t)))
postgres-# from (
postgres(# SELECT name, setting::real
postgres(# FROM pg_settings
postgres(# WHERE vartype = 'real'
postgres(# ORDER BY name ASC
postgres(# LIMIT 3
postgres(# )t
postgres-# ;
array_to_json
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
[{"name":"autovacuum_analyze_scale_factor","setting":0.1},{"name":"autovacuum_vacuum_scale_factor","setting":0.2},{"name":"bgwriter_lru_multiplier","setting":2}]
(1 row)
The text was updated successfully, but these errors were encountered:
Right now, the situation is that you need to refer to pg_settings inorder to interpret the "settings" field of the JSON output. Additionally, the pg_settings has to be of the same major version of Postgres as the server you ran pgmetrics on. This is because (1) values have unintuitive units (2) these units differ across versions for the same setting. To illustrate, if the settings says "max_wal_size" is "1", the actual value is either 16777216 bytes (if pg 9.5, 9.6) or 1048576 bytes (if pg 10).
For the settings value to be actually useful -- as in be able to be directly used for processing by a script without needing to consult pg_settings -- would require that the units also be considered while representing the value as a number. Byte-dimension values with non-byte units ("8kB", "MB", "kB" in pg10) should be normalized to bytes and time-dimension values ("min", "ms" in pg10) to seconds.
So, if we have a query or two (and/or Go code) that can do this, then we can add a new field (because we don't want to break the existing JSON schema) of float type (because JSON has only a "number" type, which is 64-bit float) at the same level as "setting", like this:
In the json output created by:
pgmetrics --no-password --format=json
All of the setting values are stored as strings in the JSON output:
Would it be possible (or sensible) to output parameters that we "know" will be numbers as actual numbers in JSON? That just makes it easier later on to do mathematics on the result.
We can automatically get the three main groupings of
string
/integer
/real
with:string
integer
real
I guess we don't really need
vartype
in there, but I thought it may be helpful (or needed) when the JSON is created.Also, I've added column aliases "
AS boot_val
", etc. just so that the SQL output is human readable, rather than columns being called "coalesce".I did a basic test of the theory here:
integer
real
The text was updated successfully, but these errors were encountered: