On Tue, Jan 20, 2015 at 4:07 PM, David Johnston <david.g.johnston@gmail.com> wrote: > sourceline and sourcefile pertain only to the current value while the point > of adding these other pieces is to provide a snapshot of all the different > mappings that the system knows about; instead of having to tell a user to go > look in two different files (and associated includes) and a database catalog > to find out what possible values are in place. That doesn't solve the need > to scan the catalog to see other possible values - though you could at least > put a counter in pg_settings that indicates how many pg_db_role_setting > entries reference the given variable so that if non-zero the user is clued > into the fact that they need to check out said catalog table.
This last proposal seems pointless to me. If the user knows about pg_db_role_setting, they will know to check it; if they don't, a counter won't fix that. I can see that there might be some utility to a query that would tell you, for a given setting, all sources of that setting the system knew about, whether in configuration files, pg_db_role_setting, or the current session. But I don't see that putting information that's already available via one system catalog query into a different system catalog query helps anything - we should presume DBAs know how to write SQL.
While this is not exactly a high-traffic catalog/view why have them write a likely 4-way join query (pg_db_role_setting is not the friendly in its current form), or even two separate queries, when we can give them a solid and comprehensive view standard. I guess part of the mixup is that I am basically talking about a view of multiple catalogs as a DBA UI as opposed to really even caring what specific catalogs (existing or otherwise) or functions are needed
to make the whole thing work. Maybe it does all fit directly on pg_settings but tacking on some read-only columns to this updateable view/table doesn't come across as something that should be forbidden in general.
Maybe I am imagining a use-case that just isn't there but if there are two separate queries needed, and we call one "consolidated", then having that query indicate whether the other query has useful information, and it is quite likely that it will not, avoids the user expending the effort to run the wasteful secondary query.