postgresql.auto.conf and reload

On Wed, Jun 25, 2014 at 6:11 PM, Christoph Berg <cb@df7cb.de> wrote:

I've just run into this:

$ psql -p 5433 (that port is configured in postgresql.conf)
# alter system set port = 5494;

... restart the server

$ psql -p 5494
# select pg_reload_conf();

2014-06-25 14:22:07 CEST [11297-4] LOG: received SIGHUP, reloading

configuration files

2014-06-25 14:22:07 CEST [11297-5] LOG: parameter "port" cannot be

changed without restarting the server

2014-06-25 14:22:07 CEST [11297-6] LOG: configuration file

"/etc/postgresql/9.4/main/postgresql.conf" contains errors; unaffected
changes were applied

This will happen without Alter System as well, if you change
the value of port in postgresql.conf and try to load conf file with SIGHUP.
You cannot reload PGC_POSTMASTER parameters without server restart.

With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com

Hi,

On Wed, 2014-06-25 at 18:42 +0530, Amit Kapila wrote:

This will happen without Alter System as well, if you change
the value of port in postgresql.conf and try to load conf file with
SIGHUP. You cannot reload PGC_POSTMASTER parameters without server
restart.

Ok, but Christoph already started the server (successfully) with port
5494, so the pg_reload_conf() should ignore it in the next attempt.

Regards,
--
Devrim GÜNDÜZ
Principal Systems Engineer @ EnterpriseDB: http://www.enterprisedb.com
PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer
Twitter: @DevrimGunduz , @DevrimGunduzTR

Re: Amit Kapila 2014-06-25 <CAA4eK1Log98jvFOV9wzTqpCdEWJa+5JR54TTpkiZ3XBnGJydLA@mail.gmail.com>

On Wed, Jun 25, 2014 at 6:11 PM, Christoph Berg <cb@df7cb.de> wrote:

I've just run into this:

$ psql -p 5433 (that port is configured in postgresql.conf)
# alter system set port = 5494;

... restart the server

$ psql -p 5494
# select pg_reload_conf();

2014-06-25 14:22:07 CEST [11297-4] LOG: received SIGHUP, reloading

configuration files

2014-06-25 14:22:07 CEST [11297-5] LOG: parameter "port" cannot be

changed without restarting the server

2014-06-25 14:22:07 CEST [11297-6] LOG: configuration file

"/etc/postgresql/9.4/main/postgresql.conf" contains errors; unaffected
changes were applied

This will happen without Alter System as well, if you change
the value of port in postgresql.conf and try to load conf file with SIGHUP.
You cannot reload PGC_POSTMASTER parameters without server restart.

That's not the issue. I did restart the server, which didn't log any
problems, yet reload keeps complaining indefinitely. This makes ALTER
SYSTEM not-so-nice-to-use to override parameters already set in
postgresql.conf.

Christoph
--
cb@df7cb.de | http://www.df7cb.de/

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Wed, Jun 25, 2014 at 6:47 PM, Christoph Berg <cb@df7cb.de> wrote:

Re: Amit Kapila 2014-06-25 <

CAA4eK1Log98jvFOV9wzTqpCdEWJa+5JR54TTpkiZ3XBnGJydLA@mail.gmail.com>

This will happen without Alter System as well, if you change
the value of port in postgresql.conf and try to load conf file with

SIGHUP.

You cannot reload PGC_POSTMASTER parameters without server restart.

That's not the issue. I did restart the server, which didn't log any
problems, yet reload keeps complaining indefinitely. This makes ALTER
SYSTEM not-so-nice-to-use to override parameters already set in
postgresql.conf.

The main reason behind such such a behaviour after restart is
that there are duplicate entries, one in postgresql.conf and
another in postgresql.conf. It always first read postgresql.conf
and then .auto file and applies the changed parameters one by one,
so when it reads a different value than current setting, it can lead
to such messages. During processing of config files it doesn't try
to eliminate duplicate entries.

You can observe same behaviour incase you have another conf
file (special.conf, containing conflicting settings) and include that
in postgresql.conf.

One way could be that while processing if we could eliminate
duplicate entries, then it will not lead to such messages, but I think
that is existing mechanism and not introduced by Alter System,
so changing just for Alter System might impact some existing users.

I think maintaining values both in postgresql.conf and by Alter System
is not advisable.

I am not sure if this addresses your concern completely, but I thinking
changing some existing mechanism (maintaining duplicate entries during
processing of config files) at this point might be risky.

With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com

On Wed, Jun 25, 2014 at 7:45 PM, Amit Kapila <amit.kapila16@gmail.com>
wrote:

On Wed, Jun 25, 2014 at 6:47 PM, Christoph Berg <cb@df7cb.de> wrote:

Re: Amit Kapila 2014-06-25 <

CAA4eK1Log98jvFOV9wzTqpCdEWJa+5JR54TTpkiZ3XBnGJydLA@mail.gmail.com>

This will happen without Alter System as well, if you change
the value of port in postgresql.conf and try to load conf file with

SIGHUP.

You cannot reload PGC_POSTMASTER parameters without server restart.

That's not the issue. I did restart the server, which didn't log any
problems, yet reload keeps complaining indefinitely. This makes ALTER
SYSTEM not-so-nice-to-use to override parameters already set in
postgresql.conf.

The main reason behind such such a behaviour after restart is
that there are duplicate entries, one in postgresql.conf and
another in postgresql.conf.

Sorry, I mean to say one in postgresql.conf and another in
postgresql.auto.conf

With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com

Re: Amit Kapila 2014-06-25 <CAA4eK1+F9ZtoGvVw-WYj2+vT0K8_JXTziqHp8iVB7wdo1w1Rkw@mail.gmail.com>

The main reason behind such such a behaviour after restart is
that there are duplicate entries, one in postgresql.conf and
another in postgresql.conf. It always first read postgresql.conf
and then .auto file and applies the changed parameters one by one,
so when it reads a different value than current setting, it can lead
to such messages. During processing of config files it doesn't try
to eliminate duplicate entries.

You can observe same behaviour incase you have another conf
file (special.conf, containing conflicting settings) and include that
in postgresql.conf.

Or even two statements for the same guc in postgresql.conf itself.

One way could be that while processing if we could eliminate
duplicate entries, then it will not lead to such messages, but I think
that is existing mechanism and not introduced by Alter System,
so changing just for Alter System might impact some existing users.

Yes, it's the same mechanism as before, but now we have a tool that
was specifically meant to be used to set and override postgresql.conf
parameters, so duplicate entries in postgresql.conf and
postgresql.auto.conf are no longer user error (or weirdness), but
expected. The system should deal with it.

I think maintaining values both in postgresql.conf and by Alter System
is not advisable.

Possibly, but then the system should be warning about all options, not
just the restart-only ones. And it should warn at startup, not at
reload time.

I am not sure if this addresses your concern completely, but I thinking
changing some existing mechanism (maintaining duplicate entries during
processing of config files) at this point might be risky.

Not sure how intrusive a fix would be - collect all settings during
config parse, and only warn once everything has been seen, instead of
emitting warnings line-by-line.

Christoph
--
cb@df7cb.de | http://www.df7cb.de/

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Wed, Jun 25, 2014 at 7:52 PM, Christoph Berg <cb@df7cb.de> wrote:

Re: Amit Kapila 2014-06-25 <

CAA4eK1+F9ZtoGvVw-WYj2+vT0K8_JXTziqHp8iVB7wdo1w1Rkw@mail.gmail.com>

I think maintaining values both in postgresql.conf and by Alter System
is not advisable.

Possibly, but then the system should be warning about all options, not
just the restart-only ones. And it should warn at startup, not at
reload time.

How about adding a note in Alter System so that users are aware of
such behaviour and can ensure that they don't have duplicate entries?

Clearly such warnings indicate that there are conflicting settings, so
user can take appropriate action to avoid it.

I am not sure if this addresses your concern completely, but I thinking
changing some existing mechanism (maintaining duplicate entries during
processing of config files) at this point might be risky.

Not sure how intrusive a fix would be - collect all settings during
config parse, and only warn once everything has been seen, instead of
emitting warnings line-by-line.

As per my understanding, it is more appropriate to eliminate duplicate
entries.

With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com

Re: Amit Kapila 2014-06-26 <CAA4eK1+mUTjc=GXJK3bYtSwV2BmBni=PHEvBqLQkhdUV9cWd1A@mail.gmail.com>

On Wed, Jun 25, 2014 at 7:52 PM, Christoph Berg <cb@df7cb.de> wrote:

Re: Amit Kapila 2014-06-25 <

CAA4eK1+F9ZtoGvVw-WYj2+vT0K8_JXTziqHp8iVB7wdo1w1Rkw@mail.gmail.com>

I think maintaining values both in postgresql.conf and by Alter System
is not advisable.

Possibly, but then the system should be warning about all options, not
just the restart-only ones. And it should warn at startup, not at
reload time.

How about adding a note in Alter System so that users are aware of
such behaviour and can ensure that they don't have duplicate entries?

If the behavior isn't going to change, that issue need to be
documented, sure.

Clearly such warnings indicate that there are conflicting settings, so
user can take appropriate action to avoid it.

I don't think conflicting settings in postgresql.auto.conf are a user
error, or misconfiguration. They are just normal use of ALTER SYSTEM.
Of course the user might want to eventually consolidate their config,
but we shouldn't treat the situation as bogus.

Frankly what bugs me most about this is that the warnings occur only
at reload time, not at startup. If the server thinks something is
wrong with my config, it should tell me rightaway.

Christoph
--
cb@df7cb.de | http://www.df7cb.de/

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Thu, Jun 26, 2014 at 1:49 PM, Christoph Berg <cb@df7cb.de> wrote:

Re: Amit Kapila 2014-06-26 <CAA4eK1+mUTjc=GXJK3bYtSwV2BmBni=

PHEvBqLQkhdUV9cWd1A@mail.gmail.com>

On Wed, Jun 25, 2014 at 7:52 PM, Christoph Berg <cb@df7cb.de> wrote:

Re: Amit Kapila 2014-06-25 <

CAA4eK1+F9ZtoGvVw-WYj2+vT0K8_JXTziqHp8iVB7wdo1w1Rkw@mail.gmail.com>

I think maintaining values both in postgresql.conf and by Alter

System

is not advisable.

Possibly, but then the system should be warning about all options, not
just the restart-only ones. And it should warn at startup, not at
reload time.

How about adding a note in Alter System so that users are aware of
such behaviour and can ensure that they don't have duplicate entries?

If the behavior isn't going to change, that issue need to be
documented, sure.

I will send a patch to address this unless someone comes with a better
way to address this and if no one objects to adding a note in Alter System
documentation.

Clearly such warnings indicate that there are conflicting settings, so
user can take appropriate action to avoid it.

I don't think conflicting settings in postgresql.auto.conf are a user
error, or misconfiguration. They are just normal use of ALTER SYSTEM.
Of course the user might want to eventually consolidate their config,
but we shouldn't treat the situation as bogus.

Frankly what bugs me most about this is that the warnings occur only
at reload time, not at startup. If the server thinks something is
wrong with my config, it should tell me rightaway.

As per current design/code, server don't treat duplicate entries
via config file's as a problem, rather the last one is given preference.
So in the case you are mentioning, it gives warning at reload time as
it encounter's a different value than current value for PGC_POSTMASTER
parameter.

With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com

On Fri, Jun 27, 2014 at 9:11 AM, Amit Kapila <amit.kapila16@gmail.com>
wrote:

On Thu, Jun 26, 2014 at 1:49 PM, Christoph Berg <cb@df7cb.de> wrote:

Re: Amit Kapila 2014-06-26 <CAA4eK1+mUTjc=GXJK3bYtSwV2BmBni=

PHEvBqLQkhdUV9cWd1A@mail.gmail.com>

How about adding a note in Alter System so that users are aware of
such behaviour and can ensure that they don't have duplicate entries?

If the behavior isn't going to change, that issue need to be
documented, sure.

I will send a patch to address this unless someone comes with a better
way to address this and if no one objects to adding a note in Alter System
documentation.

Please find the patch attached to address the above concern. I have
updated docs, so that users can be aware of such behaviour.

I will add this patch for next CF to avoid getting lost, however I believe
it should be done for 9.4.

With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com

Amit Kapila <amit.kapila16@gmail.com> writes:

Please find the patch attached to address the above concern. I have
updated docs, so that users can be aware of such behaviour.

I'm in the camp that says that we need to do something about this, not
just claim that it's operating as designed. AFAICS, the *entire* argument
for having ALTER SYSTEM at all is ease of use; but this behavior is not
what I would call facilitating ease of use. In particular, if you are
conveniently able to edit postgresql.conf, what the heck do you need
ALTER SYSTEM for?

One possible answer is to modify guc-file.l so that only the last value
supplied for any one GUC gets processed. The naive way of doing that
would be O(N^2) in the number of uncommented settings, which might or
might not be a problem; if it is, we could no doubt devise a smarter
data structure.

regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Sat, Jul 5, 2014 at 8:22 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Amit Kapila <amit.kapila16@gmail.com> writes:

Please find the patch attached to address the above concern. I have
updated docs, so that users can be aware of such behaviour.

I'm in the camp that says that we need to do something about this, not
just claim that it's operating as designed. AFAICS, the *entire* argument
for having ALTER SYSTEM at all is ease of use; but this behavior is not
what I would call facilitating ease of use. In particular, if you are
conveniently able to edit postgresql.conf, what the heck do you need
ALTER SYSTEM for?

One possible answer is to modify guc-file.l so that only the last value
supplied for any one GUC gets processed.

Another could be that during initdb all the uncommented settings be
written to postgresql.auto.conf file rather than to postgresql.conf.
I think we can do this by changing code in initdb.c->setup_config().
This will ensure that unless user is changing settings in a mixed way
(some by Alter System and some manually by editing postgresql.conf),
there will no such problem.

The naive way of doing that
would be O(N^2) in the number of uncommented settings, which might or
might not be a problem; if it is, we could no doubt devise a smarter
data structure.

Okay. To implement it, we can make sure during parsing Configfile
that only unique element can be present in list. We can modify
function ParseConfigFp() to achieve this functionality. Another
way could be that after the list is formed, we can eliminate
duplicate entries from it, we might need to do this at multiple places.
Do you have anything else in mind?

With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com

Re: Amit Kapila 2014-07-06 <CAA4eK1K=2WCD5ur8c-34NOw+XKg57Q4k0SajwSQXcwciD-=+0w@mail.gmail.com>

Another could be that during initdb all the uncommented settings be
written to postgresql.auto.conf file rather than to postgresql.conf.
I think we can do this by changing code in initdb.c->setup_config().
This will ensure that unless user is changing settings in a mixed way
(some by Alter System and some manually by editing postgresql.conf),
there will no such problem.

That will make editing postgresql.conf totally useless, because users
will always need to check if their new value isn't overridden by
something in auto.conf. This will affect *all* users, as everyone
needs to bump shared_buffers, which is set by initdb. We'd get 10^X
complaints from admins who got confused where their config actually
is.

Christoph
--
cb@df7cb.de | http://www.df7cb.de/

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Sun, Jul 6, 2014 at 1:57 PM, Christoph Berg <cb@df7cb.de> wrote:

Re: Amit Kapila 2014-07-06

<CAA4eK1K=2WCD5ur8c-34NOw+XKg57Q4k0SajwSQXcwciD-=+0w@mail.gmail.com>

Another could be that during initdb all the uncommented settings be
written to postgresql.auto.conf file rather than to postgresql.conf.
I think we can do this by changing code in initdb.c->setup_config().
This will ensure that unless user is changing settings in a mixed way
(some by Alter System and some manually by editing postgresql.conf),
there will no such problem.

That will make editing postgresql.conf totally useless, because users
will always need to check if their new value isn't overridden by
something in auto.conf. This will affect *all* users, as everyone
needs to bump shared_buffers, which is set by initdb. We'd get 10^X
complaints from admins who got confused where their config actually
is.

Users/admins can always confirm that from pg_settings (sourcefile can
be used to find that out).

Another thing is that if user is using mixed way (Alter System and manually
editing postgresql.conf) to change the configuration parameters, then he
needs to be bit careful about setting the same parameter in both ways.
Example:
1. Default value of autovacuum_vacuum_cost_delay = 20
2. Edit postgresql.conf and change it to 30
3. Perform Reload
4. Check the value by Show command, it will be 30
5. Alter System set autovacuum_vacuum_cost_delay =40
6. Perform Reload
7. Check the value by Show command, it will be 40
8. Alter System set autovacuum_vacuum_cost_delay = Default;
9 Perform Reload
10 Check the value by Show command, it will be 30

In Step-10, ideally he can expect default value for parameter which
is 20, however the actual value will be 30 because of editing done in
Step-2.

With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com

On Sat, Jul 5, 2014 at 10:52 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Amit Kapila <amit.kapila16@gmail.com> writes:

Please find the patch attached to address the above concern. I have
updated docs, so that users can be aware of such behaviour.

I'm in the camp that says that we need to do something about this, not
just claim that it's operating as designed. AFAICS, the *entire* argument
for having ALTER SYSTEM at all is ease of use; but this behavior is not
what I would call facilitating ease of use. In particular, if you are
conveniently able to edit postgresql.conf, what the heck do you need
ALTER SYSTEM for?

One possible answer is to modify guc-file.l so that only the last value
supplied for any one GUC gets processed. The naive way of doing that
would be O(N^2) in the number of uncommented settings, which might or
might not be a problem; if it is, we could no doubt devise a smarter
data structure.

I've been really annoyed by the current behavior even on older
releases because I have a script, which I use frequently, that does
this:

initdb
cat >> $PGDATA/postgresql.conf <<EOM
shared_buffers=8GB
another setting
another setting
another setting
EOM

Now, sometimes while experimenting, I will change a setting in
postgresql.conf and reload the config. At which point it complains
that it can't change shared_buffers at runtime. It does this because
the line from initdb is in the file, and so is the one I added. But
I'm *not* trying to change shared_buffers. Sure, the first value in
the config file doesn't match the current value, but the *last* line
in the config file is the one that's supposed to control, so why is it
complaining about the earlier line?

I haven't looked at the code in this area too carefully, but it seems
to me like the flow ought to be:

1. Read all of the config files and determine what the final value
present in each config file is.
2. *Then*, in a second pass, enforce requirements like "can't be
changed except at server start".

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 07/08/2014 10:07 AM, Robert Haas wrote:

I haven't looked at the code in this area too carefully, but it seems
to me like the flow ought to be:

1. Read all of the config files and determine what the final value
present in each config file is.
2. *Then*, in a second pass, enforce requirements like "can't be
changed except at server start".

+1

This would also make conf.d much more useful; I wouldn't have to worry
as much about overlapping config settings.

Sounds like a 9.5 feature, though.

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 07/06/2014 01:27 AM, Christoph Berg wrote:

Another could be that during initdb all the uncommented settings be

written to postgresql.auto.conf file rather than to postgresql.conf.
I think we can do this by changing code in initdb.c->setup_config().
This will ensure that unless user is changing settings in a mixed way
(some by Alter System and some manually by editing postgresql.conf),
there will no such problem.

There is no reasonable way for us to prevent issues for users who are
manually changing both pg.conf and pg.auto.conf. Users should stick to
one or the other method of management (or, thirdly, using conf.d); if
they mix methods, we can't prevent confusion at restart time and we
shouldn't try.

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Josh Berkus <josh@agliodbs.com> writes:

On 07/08/2014 10:07 AM, Robert Haas wrote:

I haven't looked at the code in this area too carefully, but it seems
to me like the flow ought to be:

1. Read all of the config files and determine what the final value
present in each config file is.

AFAICS we only care about the final value, not the final-value-in-each-
config-file.

2. *Then*, in a second pass, enforce requirements like "can't be
changed except at server start".

+1

This would also make conf.d much more useful; I wouldn't have to worry
as much about overlapping config settings.

Sounds like a 9.5 feature, though.

No, ALTER SYSTEM is there now and it needs to work right in its first
release. I will go fix this if nobody else does.

regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 09/07/14 05:13, Josh Berkus wrote:

On 07/06/2014 01:27 AM, Christoph Berg wrote:

Another could be that during initdb all the uncommented settings be

written to postgresql.auto.conf file rather than to postgresql.conf.
I think we can do this by changing code in initdb.c->setup_config().
This will ensure that unless user is changing settings in a mixed way
(some by Alter System and some manually by editing postgresql.conf),
there will no such problem.

There is no reasonable way for us to prevent issues for users who are
manually changing both pg.conf and pg.auto.conf. Users should stick to
one or the other method of management (or, thirdly, using conf.d); if
they mix methods, we can't prevent confusion at restart time and we
shouldn't try.

Yes, but even well behaved users will see this type of error, because
initdb uncomments certain values (ones that are dead certs for being
changed via ALTER SYSTEM subsequently like shared_buffers), and then -
bang! your next reload gets that "your postgresql.conf contains errors"
message.

Regards

Mark

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers