Thread: Append to a GUC parameter ?
Using the 'include' keyword in postgresql.conf let's us do groovy things like paste together pieces of general purpose configs into the working file. -- But since all we can do is set/reset the parameters the possibility of concatenating fragments that use some of the listparameters won't work. To wit; suppose we had a fragment for enabling auto_explain and another for enabling pg_stat_statements. Both of these require us to put something in shared_preload_libraries and both make use of custom_variable_classes. Just FWIW... Something like... shared_preload_libraries += auto_explain Would do the trick. I've never heard this mentioned before so presume not many have contemplated this. Comments? Jerry Sievers Postgres DBA/Development Consulting e: postgres.consulting@comcast.net p: 312.241.7800
On 08/05/2014 11:12 AM, Jerry Sievers wrote: > shared_preload_libraries += auto_explain > > Would do the trick. > > I've never heard this mentioned before so presume not many have > contemplated this. It's been discussed. However, other than shared_preload_libraries, I can't think of a GUC which would benefit from this. Can you? -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com
On Tue, Aug 5, 2014 at 12:52:51PM -0700, Josh Berkus wrote: > On 08/05/2014 11:12 AM, Jerry Sievers wrote: > > shared_preload_libraries += auto_explain > > > > Would do the trick. > > > > I've never heard this mentioned before so presume not many have > > contemplated this. > > It's been discussed. > > However, other than shared_preload_libraries, I can't think of a GUC > which would benefit from this. Can you? search_path? -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. +
Bruce Momjian wrote: > On Tue, Aug 5, 2014 at 12:52:51PM -0700, Josh Berkus wrote: > > On 08/05/2014 11:12 AM, Jerry Sievers wrote: > > > shared_preload_libraries += auto_explain > > > > > > Would do the trick. > > > > > > I've never heard this mentioned before so presume not many have > > > contemplated this. > > > > It's been discussed. > > > > However, other than shared_preload_libraries, I can't think of a GUC > > which would benefit from this. Can you? > > search_path? Almost anything that's marked GUC_LIST_INPUT. A quick grep shows some variables for which it doesn't make sense, such as DateStyle, and others for which it would, such as temp_tablespaces, synchronous_standby_names, listen_addresses, and the various preload_libraries params. Not sure about log_destination. I think this merits a new GUC flag, say GUC_LIST_ADDITIVE. -- Álvaro Herrera http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
Alvaro Herrera-9 wrote > Bruce Momjian wrote: >> On Tue, Aug 5, 2014 at 12:52:51PM -0700, Josh Berkus wrote: >> > On 08/05/2014 11:12 AM, Jerry Sievers wrote: >> > > shared_preload_libraries += auto_explain >> > > >> > > Would do the trick. >> > > >> > > I've never heard this mentioned before so presume not many have >> > > contemplated this. >> > >> > It's been discussed. >> > >> > However, other than shared_preload_libraries, I can't think of a GUC >> > which would benefit from this. Can you? >> >> search_path? > > Almost anything that's marked GUC_LIST_INPUT. A quick grep shows some > variables for which it doesn't make sense, such as DateStyle, and others > for which it would, such as temp_tablespaces, synchronous_standby_names, > listen_addresses, and the various preload_libraries params. Not sure > about log_destination. > > I think this merits a new GUC flag, say GUC_LIST_ADDITIVE. Would that allow, without any special syntax, multiple declarations of, say, shared_preload_libraries, to have their values appended instead of the most (or least, I forget which) recent one overwrite (or be ignored by) the existing value? The idea of requiring "+=" instead of just "=" is not particularly appealing... David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Append-to-a-GUC-parameter-tp5813811p5813842.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.
Bruce Momjian <bruce@momjian.us> writes: > On Tue, Aug 5, 2014 at 12:52:51PM -0700, Josh Berkus wrote: >> However, other than shared_preload_libraries, I can't think of a GUC >> which would benefit from this. Can you? > search_path? Given the security implications of search_path, assembling its value from independent parts sounds like a darn bad idea from here. regards, tom lane
David G Johnston wrote: > Alvaro Herrera-9 wrote > > I think this merits a new GUC flag, say GUC_LIST_ADDITIVE. > > Would that allow, without any special syntax, multiple declarations of, say, > shared_preload_libraries, to have their values appended instead of the most > (or least, I forget which) recent one overwrite (or be ignored by) the > existing value? > > The idea of requiring "+=" instead of just "=" is not particularly > appealing... I'm not sold on += as the syntax to use. It just needs to be something different from =. But I am thinking that whatever it is, it would be required. Otherwise, for example if you have search_path=foo in postgresql.conf and search_path=bar in auto.conf via ALTER SYSTEM, you would end up with search_path=foo,bar instead of just bar which I think would be the expected outcome. Of course, we would offer a new ALTER SYSTEM option to indicate that you want to have auto.conf use += instead of =. The idea behing GUC_LIST_ADDITIVE is that += is only supported for variables that have that flag set. If you tried to use += with other variables, an error would be raised. -- Álvaro Herrera http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
On 08/05/2014 06:25 PM, Alvaro Herrera wrote: > I'm not sold on += as the syntax to use. It just needs to be something > different from =. Alternative is to use "+" in the value string: shared_preload_libraries = '+auto_explain' ... not sure that's better. The alternative is to add an "append" keyword to everything, which strikes me as yucky. > But I am thinking that whatever it is, it would be > required. Yes, absolutely. > The idea behing GUC_LIST_ADDITIVE is that += is only supported for > variables that have that flag set. If you tried to use += with other > variables, an error would be raised. Yes. BTW, while there's unlikely to be a good reason to put search_path in pg.conf with appends, there are a LOT of reasons to want to be able to append to it during a session. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com
Josh Berkus <josh@agliodbs.com> writes: > BTW, while there's unlikely to be a good reason to put search_path in > pg.conf with appends, there are a LOT of reasons to want to be able to > append to it during a session. [shrug...] You can do that today with current_setting()/set_config(). regards, tom lane
On Tue, Aug 5, 2014 at 06:36:01PM -0700, Josh Berkus wrote: > On 08/05/2014 06:25 PM, Alvaro Herrera wrote: > > > I'm not sold on += as the syntax to use. It just needs to be something > > different from =. > > Alternative is to use "+" in the value string: > > shared_preload_libraries = '+auto_explain' > > ... not sure that's better. > > The alternative is to add an "append" keyword to everything, which > strikes me as yucky. I think you need _prefix_ and _suffix_ operators. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. +
On Tue, Aug 5, 2014 at 07:22:13PM -0600, Tom Lane wrote: > Bruce Momjian <bruce@momjian.us> writes: > > On Tue, Aug 5, 2014 at 12:52:51PM -0700, Josh Berkus wrote: > >> However, other than shared_preload_libraries, I can't think of a GUC > >> which would benefit from this. Can you? > > > search_path? > > Given the security implications of search_path, assembling its value > from independent parts sounds like a darn bad idea from here. I can imagine someone wanting to prefix/suffix to search_path for a user via ALTER USER. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. +
On Tue, Aug 5, 2014 at 10:55 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
+1
With a very simple statement you can do that:
fabrizio=# SELECT current_setting('search_path');
current_setting
-----------------
"$user",public
(1 row)
-- Josh Berkus <josh@agliodbs.com> writes:[shrug...] You can do that today with current_setting()/set_config().
> BTW, while there's unlikely to be a good reason to put search_path in
> pg.conf with appends, there are a LOT of reasons to want to be able to
> append to it during a session.
+1
With a very simple statement you can do that:
fabrizio=# SELECT current_setting('search_path');
current_setting
-----------------
"$user",public
(1 row)
fabrizio=# SELECT set_config('search_path', current_setting('search_path')||', another_schema', false);
set_config
--------------------------------
"$user",public, another_schema
(1 row)
set_config
--------------------------------
"$user",public, another_schema
(1 row)
Or you can create a simple sql function to concat your configs:
fabrizio=# CREATE FUNCTION concat_config(name TEXT, value TEXT, is_local BOOLEAN)
fabrizio-# RETURNS text
fabrizio-# LANGUAGE sql
fabrizio-# AS $$ SELECT set_config(name, current_setting(name)||','||value, is_local); $$;
CREATE FUNCTION
fabrizio=# SELECT concat_config('search_path', 'another_schema', false);
concat_config
-------------------------------
"$user",public,another_schema
(1 row)
fabrizio=# SELECT concat_config('search_path', 'schema2', false);
concat_config
---------------------------------------
"$user",public,another_schema,schema2
(1 row)
fabrizio-# RETURNS text
fabrizio-# LANGUAGE sql
fabrizio-# AS $$ SELECT set_config(name, current_setting(name)||','||value, is_local); $$;
CREATE FUNCTION
fabrizio=# SELECT concat_config('search_path', 'another_schema', false);
concat_config
-------------------------------
"$user",public,another_schema
(1 row)
fabrizio=# SELECT concat_config('search_path', 'schema2', false);
concat_config
---------------------------------------
"$user",public,another_schema,schema2
(1 row)
Regards,
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL
Consultoria/Coaching PostgreSQL
>> Timbira: http://www.timbira.com.br
>> Blog sobre TI: http://fabriziomello.blogspot.com
>> Perfil Linkedin: http://br.linkedin.com/in/fabriziomello
>> Twitter: http://twitter.com/fabriziomello
>> Blog sobre TI: http://fabriziomello.blogspot.com
>> Perfil Linkedin: http://br.linkedin.com/in/fabriziomello
>> Twitter: http://twitter.com/fabriziomello
On Wed, Aug 6, 2014 at 12:12:29AM -0300, Fabrízio de Royes Mello wrote: > > On Tue, Aug 5, 2014 at 10:55 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > > Josh Berkus <josh@agliodbs.com> writes: > > BTW, while there's unlikely to be a good reason to put search_path in > > pg.conf with appends, there are a LOT of reasons to want to be able to > > append to it during a session. > > [shrug...] You can do that today with current_setting()/set_config(). > > > > +1 > > With a very simple statement you can do that: > > fabrizio=# SELECT current_setting('search_path'); > current_setting > ----------------- > "$user",public > (1 row) > > fabrizio=# SELECT set_config('search_path', current_setting('search_path')||', > another_schema', false); > set_config > -------------------------------- > "$user",public, another_schema > (1 row) Yes, that is very nice, but it only works for session-level changes, i.e. you can't use that in postgresql.conf or via ALTER USER/DATABASE. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. +
Fabrízio de Royes Mello wrote: > On Tue, Aug 5, 2014 at 10:55 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > > > Josh Berkus <josh@agliodbs.com> writes: > > > BTW, while there's unlikely to be a good reason to put search_path in > > > pg.conf with appends, there are a LOT of reasons to want to be able to > > > append to it during a session. > > > > [shrug...] You can do that today with current_setting()/set_config(). > > With a very simple statement you can do that: Of course, this doesn't solve the requirement that started this thread, which is about having "includeable" pg.conf fragments to enable extensions. -- Álvaro Herrera http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
Alvaro Herrera <alvherre@2ndquadrant.com> writes: > Fabrízio de Royes Mello wrote: > >> On Tue, Aug 5, 2014 at 10:55 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: >> >> > Josh Berkus <josh@agliodbs.com> writes: >> > > BTW, while there's unlikely to be a good reason to put search_path in >> > > pg.conf with appends, there are a LOT of reasons to want to be able to >> > > append to it during a session. >> > >> > [shrug...] You can do that today with current_setting()/set_config(). >> >> With a very simple statement you can do that: > > Of course, this doesn't solve the requirement that started this thread, > which is about having "includeable" pg.conf fragments to enable > extensions. o ISTM the idea of a token in the value string that would expand to an existing setting s/b general purpose enough to allow for prepend/append and not require adding a new opperator as += or whatever. > I say this without knowing just exactly what the implementation effort is but just to reiterate the original intent. I think someone already suggest this upthread. shared_preload_libraries = '%,more_libs' shared_preload_libraries = 'more_libs,%' At conclusion of file processing, stripping off an unnecessary delimiter at beginning or end of string would be a nice asthetic feature and/or might be required depending on whether an empty list value is legal. Thanks > -- > Álvaro Herrera http://www.2ndQuadrant.com/ > PostgreSQL Development, 24x7 Support, Training & Services -- Jerry Sievers Postgres DBA/Development Consulting e: postgres.consulting@comcast.net p: 312.241.7800
On Thu, Aug 7, 2014 at 10:04:47AM -0400, Alvaro Herrera wrote: > Fabrízio de Royes Mello wrote: > > On Tue, Aug 5, 2014 at 10:55 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > > > > > Josh Berkus <josh@agliodbs.com> writes: > > > > BTW, while there's unlikely to be a good reason to put search_path in > > > > pg.conf with appends, there are a LOT of reasons to want to be able to > > > > append to it during a session. > > > > > > [shrug...] You can do that today with current_setting()/set_config(). > > > > With a very simple statement you can do that: > > Of course, this doesn't solve the requirement that started this thread, > which is about having "includeable" pg.conf fragments to enable > extensions. Should this be a TODO item? -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. +