patternsqlMinor
Finding what the source of a specific PostgreSQL configuration setting
Viewed 0 times
postgresqlthewhatsourcefindingsettingspecificconfiguration
Problem
If I get it right, settings are hierarchical:
let's say I have
Because it is so for user vao, or for database vao, or deeper...
What is a short way to find the primary source of current value?.. Or preferably matrix of all sources for the setting. Is there any interface or monkey hack?
update to reflect brilliant Abelisto answer:
```
vao=# select setting, source, sourcefile from pg_settings where name = 'enable_seqscan';
setting | source | sourcefile
---------+----------+------------
off | database |
(1 row)
vao=# select * from pg_file_settings where name = 'enable_seqscan';
sourcefile | sourceline | seqno | name | setting | applied | error
---------------------------------------------------+------------+-------+----------------+---------+--------
postgres.conf->
postgres.auto.conf (ALTER SYSTEM)->
ALTER DATABASE(where applicable)->
ALTER USER(where applicable)->
SET SESSION(where applicable)->
SET LOCAL(where applicable)let's say I have
enable_seqscan set to off at some point. SHOW or current_setting( or pg_settings will show me the current value only. But to check why I have it off, I have to check whole chain. Eg I suspect somebody set it per user or per database, or in auto.conf - to find how wide the setting is I have to check em all. Otherwise resetting value can fail, eg:vao=# show enable_seqscan;
enable_seqscan
----------------
off
(1 row)
vao=# set enable_seqscan to default;
SET
vao=# show enable_seqscan;
enable_seqscan
----------------
off
(1 row)Because it is so for user vao, or for database vao, or deeper...
What is a short way to find the primary source of current value?.. Or preferably matrix of all sources for the setting. Is there any interface or monkey hack?
update to reflect brilliant Abelisto answer:
source, sourcefile from pg_settings is a wonderful source of information, still I'm asking for a monkey way to check for those reasons: to see the database or user in source I have to relogin - obviously. So before they actually are applied to the session, I can't check them. local and session set shows both SESSION (which kind of reasonable too) and lastly I still have to reset to default step by step all hierarchy. Eg:```
vao=# select setting, source, sourcefile from pg_settings where name = 'enable_seqscan';
setting | source | sourcefile
---------+----------+------------
off | database |
(1 row)
vao=# select * from pg_file_settings where name = 'enable_seqscan';
sourcefile | sourceline | seqno | name | setting | applied | error
---------------------------------------------------+------------+-------+----------------+---------+--------
Solution
pg_settings - look for source*For example, after
alter system set array_nulls to off;(and reloading configuration)
select * from pg_settings where name = 'array_nulls';╔═[ RECORD 1 ]════╤════════════════════════════════════════════════════════════════════════════════════════════════════════════╗
║ name │ array_nulls ║
║ setting │ off ║
║ unit │ ░░░░ ║
║ category │ Version and Platform Compatibility / Previous PostgreSQL Versions ║
║ short_desc │ Enable input of NULL elements in arrays. ║
║ extra_desc │ When turned on, unquoted NULL in an array input value means a null value; otherwise it is taken literally. ║
║ context │ user ║
║ vartype │ bool ║
║ source │ configuration file ║
║ min_val │ ░░░░ ║
║ max_val │ ░░░░ ║
║ enumvals │ ░░░░ ║
║ boot_val │ on ║
║ reset_val │ off ║
║ sourcefile │ /var/lib/postgresql/9.5/main/postgresql.auto.conf ║
║ sourceline │ 3 ║
║ pending_restart │ f ║
╚═════════════════╧════════════════════════════════════════════════════════════════════════════════════════════════════════════╝
Update
And there is the query to get db/role settings, without relogin required:
with
opt_db as (
select s.*, d.datname, opt_name, opt_value from
pg_db_role_setting s join
pg_database d on (s.setdatabase = d.oid) cross join lateral (
select
split_part(opt, '=',1) as opt_name,
split_part(opt, '=', 2) as opt_value
from
unnest(setconfig) as opt) as opt
),
opt_role as (
select s.*, r.rolname, opt_name, opt_value from
pg_db_role_setting s join
pg_roles r on (s.setrole = r.oid) cross join lateral (
select
split_part(opt, '=', 1) as opt_name,
split_part(opt, '=', 2) as opt_value
from
unnest(setconfig) as opt) as opt
)
select
datname,
rolname,
opt_name,
opt_db.opt_value as db_value,
opt_role.opt_value as role_value
from opt_db full join opt_role using(opt_name);Possible result:
╔══════════╤══════════╤═════════════╤═════════════════╤═════════════════╗
║ datname │ rolname │ opt_name │ db_value │ role_value ║
╠══════════╪══════════╪═════════════╪═════════════════╪═════════════════╣
║ postgres │ nd │ search_path │ "$user", public │ "$user", public ║
║ postgres │ postgres │ array_nulls │ off │ on ║
║ ░░░░ │ nd │ foo.bar │ ░░░░ │ 3 ║
╚══════════╧══════════╧═════════════╧═════════════════╧═════════════════╝
Code Snippets
alter system set array_nulls to off;select * from pg_settings where name = 'array_nulls';with
opt_db as (
select s.*, d.datname, opt_name, opt_value from
pg_db_role_setting s join
pg_database d on (s.setdatabase = d.oid) cross join lateral (
select
split_part(opt, '=',1) as opt_name,
split_part(opt, '=', 2) as opt_value
from
unnest(setconfig) as opt) as opt
),
opt_role as (
select s.*, r.rolname, opt_name, opt_value from
pg_db_role_setting s join
pg_roles r on (s.setrole = r.oid) cross join lateral (
select
split_part(opt, '=', 1) as opt_name,
split_part(opt, '=', 2) as opt_value
from
unnest(setconfig) as opt) as opt
)
select
datname,
rolname,
opt_name,
opt_db.opt_value as db_value,
opt_role.opt_value as role_value
from opt_db full join opt_role using(opt_name);Context
StackExchange Database Administrators Q#179759, answer score: 5
Revisions (0)
No revisions yet.