4

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.

4

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               ║
╚══════════╧══════════╧═════════════╧═════════════════╧═════════════════╝
  • TIL.. cool answer! – Evan Carroll Jul 13 '17 at 14:21
  • thanks a lot for your answer! It seems the best source to get the current source of setting with no doubt. Rewriting comment helped me understand there cant be any monkey way to get all "scheduled" SET - if there would be, I would not have to reconnect and recheck all manually – Vao Tsun Jul 13 '17 at 14:46
  • @VaoTsun Answer updated. – Abelisto Jul 13 '17 at 17:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.