Re: Merging postgresql.conf and postgresql.auto.conf
| От | David Johnston |
|---|---|
| Тема | Re: Merging postgresql.conf and postgresql.auto.conf |
| Дата | |
| Msg-id | CAKFQuwY+DiTGZpxf=EOH50JgJFBMmbvUGWmSNYU-FxYaND7WkQ@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: Merging postgresql.conf and postgresql.auto.conf (Jim Nasby <Jim.Nasby@BlueTreble.com>) |
| Ответы |
Re: Merging postgresql.conf and postgresql.auto.conf
(Robert Haas <robertmhaas@gmail.com>)
|
| Список | pgsql-hackers |
On 1/16/15 10:32 PM, David G Johnston wrote:
Two changes solve this problem in what seems to be a clean way.
1) Upon each parsing of postgresql.conf we store all assigned variables
somewhere
Parsing is relatively cheap, and it's not like we need high performance from this. So, -1 on permanent storage.
OK
2) We display these assignments in a new pg_settings column named
"system_reset_val"
I would also extend this to include:
a) upon each parsing of postgresql.auto.conf we store all assigned variables
somewhere (maybe the same place as postgresql.conf and simply label the file
source)
You can not assume there are only postgresql.conf and postgresql.auto.conf. Complex environments will have multiple included files.
#include files still appear to come from postgresql.conf; I'm not proposing we try and memorize every single instance of a variable declaration and provide a global "overlaps" query - though that piece already seems to be in place...
b) add an "alter_system_val" field to show that value (or null)
c) add a "db_role_val" to show the current value for the session via
pg_db_role_setting
You're forgetting that there are also per-role settings. And I'm with Robert; what's wrong with sourcefile and sourceline? Perhaps we just need to teach those about ALTER ROLE SET and ALTER DATABASE SET (if they don't already know about them).
Actually, there are not separate PER ROLE and PER DATABASE settings even though there are different SQL commands. The catalog is simply "pg_db_role_setting" with the use of "all" tags (i.e., 0) as necessary. But I see where you are going and do not disagree that precedence information could be useful.
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.
c.1) add a "db_role_id" to show the named user that is being used for the
db_role_val lookup
The thinking for c.1 is that in situations with role hierarchies and SET
ROLE usage it would be nice to be able to query what the connection role -
the one used during variable lookup - is.
I'm losing track of exactly what we're trying to solve here, but...
If the goal is to figure out what settings would be in place for a specific user connecting to a specific database, then we should create a SRF that does just that (accepting a database name and role name). You could then do...
SELECT * FROM pg_show_all_settings( 'database', 'role' ) a;
This is fine - but I'm thinking about situations where a user immediately SET ROLE on their session and typically operate as said user; if they try doing an ALTER ROLE SET for this SET ROLE user it will not work because their login user is what matters. This is probably a solution looking for a problem but it is a dynamic that one needs to be aware of.
I'm probably going overkill on this but there are not a lot of difference
sources nor do they change frequently so extending the pg_settings view to
be more of a one-stop-shopping for this information seems to make sense.
Speaking of overkill... one thing that you currently can't do is find out what #includes have been processed. Perhaps it's worth having a SRF that would return that info...As it relates back to this thread the desired "merging" ends up being done
inside this view and at least gives the DBA a central location (well, along
with pg_db_role_setting) to go and look at the configuration landscape for
the system.
I think the goal is good, but the interface needs to be rethought.
--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com
My main concern with the UI would be too-much-information; but otherwise if we accept the premise that we should include as much as possible and then let the user (or us) provide more useful subsets based upon common use-cases the main issue is making sure we've identified all of the relevant information that needs to be captured - even if it is something as minor as the whole logon vs. current role dynamic. I'm ignoring the cost/benefit aspect of implementation for the moment largely because I do known enough about the backend to make reasonable comments. But much of the data is already present in various views and catalogs - I just think having one-stop-shop would be useful and go a long way toward addressing concerns like: "where is that stupid setting coming from" or "what will happen if I make this change?"
David J.
В списке pgsql-hackers по дате отправления: