In the json output created by:
pgmetrics --no-password --format=json
All of the setting values are stored as strings in the JSON output:
"maintenance_work_mem": {
"setting": "65536"
},
"max_connections": {
"setting": "100"
},
"max_files_per_process": {
"setting": "1000"
},
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)