PostgreSQL Configuration – Finding the Source of a Specific Setting

configurationpostgresql

If I get it right, settings are hierarchical: 

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
---------------------------------------------------+------------+-------+----------------+---------+---------+-------
 /etc/postgresql/9.6/main/a                        |          1 |     1 | enable_seqscan | off     | f       |
 /var/lib/postgresql/9.6/main/postgresql.auto.conf |          3 |    26 | enable_seqscan | on      | t       |
(2 rows)

So If I don't check every source my changes can be not respected.

Best Answer

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               ║
╚══════════╧══════════╧═════════════╧═════════════════╧═════════════════╝
Related Question