Re: Parsing config files in a directory

Per discussion at the developer meeting back in Ottawa, attached is an
initial patch that implements reading a directory of configuration
files instead of just one. The idea being that something like a tuning
tool, or pgadmin, for example can drop and modify files in this
directory instead of modifying the main config file (which can be very
hard to machine-parse). The idea is the same as other software like
apache that parses multiple files.

Files are parsed in alphabetical order so it's predictable, and you
can make sure some files override others etc.

Comments, before I go do the final polishing? :-)

--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/

On 24 Oct 2009, at 14:41, Magnus Hagander wrote:

> Per discussion at the developer meeting back in Ottawa, attached is an
> initial patch that implements reading a directory of configuration
> files instead of just one. The idea being that something like a tuning
> tool, or pgadmin, for example can drop and modify files in this
> directory instead of modifying the main config file (which can be very
> hard to machine-parse). The idea is the same as other software like
> apache that parses multiple files.
>
> Files are parsed in alphabetical order so it's predictable, and you
> can make sure some files override others etc.
>
> Comments, before I go do the final polishing? :-)
I don't know what the discussion topics were, since I was not there.
But primary question is, cannot that be achieved with simple includes
in postgresql.conf ?

On Sat, 2009-10-24 at 15:41 +0200, Magnus Hagander wrote:

> Per discussion at the developer meeting back in Ottawa, attached is an
> initial patch that implements reading a directory of configuration
> files instead of just one. The idea being that something like a tuning
> tool, or pgadmin, for example can drop and modify files in this
> directory instead of modifying the main config file (which can be very
> hard to machine-parse). The idea is the same as other software like
> apache that parses multiple files.
>
> Files are parsed in alphabetical order so it's predictable, and you
> can make sure some files override others etc.
>
> Comments, before I go do the final polishing? :-)

I really don't like this at all. It seems like too much change. The
whole world knows about postgresql.conf, lets not change that.

I'm happy with the new feature, however, so is there a way to do this?

Could we have a new directive in postgresql.conf that allows you to
specify an includedirectory? Like an include directive but for a whole
directory rather than just a file. Users would then also be able to
specify more than one directory, if required. This way we would allow
people to have the multi-conf file feature but without changing existing
ways of working. By default, we would have one entry at the bottom of
postgresql.conf which would point to pg_conf, a new directory that
starts off empty. So by default, nothing has changed, yet the new
feature is allowed.

--
Simon Riggs www.2ndQuadrant.com

2009/10/24 Grzegorz Jaskiewicz <gj(at)pointblue(dot)com(dot)pl>:
>
> On 24 Oct 2009, at 14:41, Magnus Hagander wrote:
>
>> Per discussion at the developer meeting back in Ottawa, attached is an
>> initial patch that implements reading a directory of configuration
>> files instead of just one. The idea being that something like a tuning
>> tool, or pgadmin, for example can drop and modify files in this
>> directory instead of modifying the main config file (which can be very
>> hard to machine-parse). The idea is the same as other software like
>> apache that parses multiple files.
>>
>> Files are parsed in alphabetical order so it's predictable, and you
>> can make sure some files override others etc.
>>
>> Comments, before I go do the final polishing? :-)
>
> I don't know what the discussion topics were, since I was not there. But primary question is, cannot that be achieved with simple includes in postgresql.conf ?

You could, but that would take away the main point - which is that the
utilities would once again have to edit and parse postgresql.conf,
which is *very* hard to do reliably given that it's a free-format
file.

--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/

2009/10/24 Simon Riggs <simon(at)2ndquadrant(dot)com>:
> On Sat, 2009-10-24 at 15:41 +0200, Magnus Hagander wrote:
>
>> Per discussion at the developer meeting back in Ottawa, attached is an
>> initial patch that implements reading a directory of configuration
>> files instead of just one. The idea being that something like a tuning
>> tool, or pgadmin, for example can drop and modify files in this
>> directory instead of modifying the main config file (which can be very
>> hard to machine-parse). The idea is the same as other software like
>> apache that parses multiple files.
>>
>> Files are parsed in alphabetical order so it's predictable, and you
>> can make sure some files override others etc.
>>
>> Comments, before I go do the final polishing? :-)
>
> I really don't like this at all. It seems like too much change. The
> whole world knows about postgresql.conf, lets not change that.

We're not. It will still be there, just like before. We're just adding
one more way to do it.

> I'm happy with the new feature, however, so is there a way to do this?
>
> Could we have a new directive in postgresql.conf that allows you to
> specify an includedirectory? Like an include directive but for a whole
> directory rather than just a file.

We could do it that way, but that would make the change bigger, not smaller :-P

> Users would then also be able to
> specify more than one directory, if required. This way we would allow
> people to have the multi-conf file feature but without changing existing
> ways of working. By default, we would have one entry at the bottom of
> postgresql.conf which would point to pg_conf, a new directory that
> starts off empty. So by default, nothing has changed, yet the new
> feature is allowed.

Did you look at the patch? That's basically what it does now, except
it doesn't add a parameter in postgresql.conf. If you lkeave the
pg_config directory empty, it will just parse the postgresql.conf file
just like before, and that's it. only if you put something in the
pg_config directory will it load it, and only *after* it has loaded
the main configuration file.

--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/

On Sat, 2009-10-24 at 18:34 +0200, Magnus Hagander wrote:

> Did you look at the patch?

I did, yes. But no docs with it. It would be good to see the proposal,
not just the patch (or a reference to the written proposal from
earlier).

> That's basically what it does now, except
> it doesn't add a parameter in postgresql.conf. If you lkeave the
> pg_config directory empty, it will just parse the postgresql.conf file
> just like before, and that's it. only if you put something in the
> pg_config directory will it load it, and only *after* it has loaded
> the main configuration file.

OK, I didn't pick up on that. So now I like it, apart from one thing: I
would prefer to have explicit control over that (via the directive I
mentioned or otherwise), rather than just doing that implicitly. Doing
things implicitly will make it even harder for most people to trace
which parameters will get picked up and stupid mistakes will be made on
production servers.

--
Simon Riggs www.2ndQuadrant.com

Magnus Hagander wrote:
>> I'm happy with the new feature, however, so is there a way to do this?
>>
>> Could we have a new directive in postgresql.conf that allows you to
>> specify an includedirectory? Like an include directive but for a whole
>> directory rather than just a file.
>>
>
> We could do it that way, but that would make the change bigger, not smaller :-P
>

If we're going to do this at all, ISTM the location should be
configurable, just like other file locations are.

>
>
>> Users would then also be able to
>> specify more than one directory, if required. This way we would allow
>> people to have the multi-conf file feature but without changing existing
>> ways of working. By default, we would have one entry at the bottom of
>> postgresql.conf which would point to pg_conf, a new directory that
>> starts off empty. So by default, nothing has changed, yet the new
>> feature is allowed.
>>
>
> Did you look at the patch? That's basically what it does now, except
> it doesn't add a parameter in postgresql.conf. If you lkeave the
> pg_config directory empty, it will just parse the postgresql.conf file
> just like before, and that's it. only if you put something in the
> pg_config directory will it load it, and only *after* it has loaded
> the main configuration file.
>
>

What bothers me some is that it sounds like a bit of a footgun. A
postgres cluster is a shared resource, and we surely don't want
applications meddling with the shared config. This seems quite different
from, say, an application dropping a file in /etc/cron.d.

I don't have strong feelings on it, but I do have some niggling worries.

cheers

andrew

2009/10/24 Simon Riggs <simon(at)2ndquadrant(dot)com>:
> On Sat, 2009-10-24 at 18:34 +0200, Magnus Hagander wrote:
>
>> Did you look at the patch?
>
> I did, yes. But no docs with it. It would be good to see the proposal,
> not just the patch (or a reference to the written proposal from
> earlier).

We discussed it at the developer meeting - I believe you were there by phone.

http://wiki.postgresql.org/wiki/PgCon_2009_Developer_Meeting

See the section on auto tuning, including an action list.

>> That's basically what it does now, except
>> it doesn't add a parameter in postgresql.conf. If you lkeave the
>> pg_config directory empty, it will just parse the postgresql.conf file
>> just like before, and that's it. only if you put something in the
>> pg_config directory will it load it, and only *after* it has loaded
>> the main configuration file.
>
> OK, I didn't pick up on that. So now I like it, apart from one thing: I
> would prefer to have explicit control over that (via the directive I
> mentioned or otherwise), rather than just doing that implicitly. Doing
> things implicitly will make it even harder for most people to trace
> which parameters will get picked up and stupid mistakes will be made on
> production servers.

So you're suggesting an includedir parameter, right? And one such
parameter at the bottom of the default postgresql.conf file, with the
ability to remove it and/or add others, correct?

--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/

On Sat, 2009-10-24 at 12:52 -0400, Andrew Dunstan wrote:

> What bothers me some is that it sounds like a bit of a footgun. A
> postgres cluster is a shared resource, and we surely don't want
> applications meddling with the shared config. This seems quite different
> from, say, an application dropping a file in /etc/cron.d.
>
> I don't have strong feelings on it, but I do have some niggling worries.

Yes, that describes my mild unease about this a little better.

--
Simon Riggs www.2ndQuadrant.com

On Sat, 2009-10-24 at 18:57 +0200, Magnus Hagander wrote:
>> That's basically what it does now, except
> >> it doesn't add a parameter in postgresql.conf. If you lkeave the
> >> pg_config directory empty, it will just parse the postgresql.conf file
> >> just like before, and that's it. only if you put something in the
> >> pg_config directory will it load it, and only *after* it has loaded
> >> the main configuration file.
> >
> > OK, I didn't pick up on that. So now I like it, apart from one thing: I
> > would prefer to have explicit control over that (via the directive I
> > mentioned or otherwise), rather than just doing that implicitly. Doing
> > things implicitly will make it even harder for most people to trace
> > which parameters will get picked up and stupid mistakes will be made on
> > production servers.
>
> So you're suggesting an includedir parameter, right? And one such
> parameter at the bottom of the default postgresql.conf file, with the
> ability to remove it and/or add others, correct?

Yes, please.

> 2009/10/24 Simon Riggs <simon(at)2ndquadrant(dot)com>:
> > On Sat, 2009-10-24 at 18:34 +0200, Magnus Hagander wrote:
> >
> >> Did you look at the patch?
> >
> > I did, yes. But no docs with it. It would be good to see the proposal,
> > not just the patch (or a reference to the written proposal from
> > earlier).
>
> We discussed it at the developer meeting - I believe you were there by phone.
>
> http://wiki.postgresql.org/wiki/PgCon_2009_Developer_Meeting
>
> See the section on auto tuning, including an action list.

I wasn't there for that part of the meeting, and the notes from the
meeting aren't a design proposal, just a note to do a feature. The notes
do put an action on someone to "work out details on hackers". Not doing
design before coding is a general bugbear of mine - no need to pick on
you in particular with this and I'm sorry I brought that up.

--
Simon Riggs www.2ndQuadrant.com

On Sat, 24 Oct 2009, Andrew Dunstan wrote:

> If we're going to do this at all, ISTM the location should be configurable,
> just like other file locations are.
> ...
> What bothers me some is that it sounds like a bit of a footgun. A postgres
> cluster is a shared resource, and we surely don't want applications meddling
> with the shared config. This seems quite different from, say, an application
> dropping a file in /etc/cron.d.

It's hard to satisfy both these at once. If you want to make it secured
against random application changes, the logical default place to put the
files is in the database directory itself. If apps are allowed to write
there, they can surely cause more mayhem them just overwriting the config
files. If instead you allow the files to go anywhere, that's more
flexible, but then requires you to make sure that alternate location is
similarly secured.

Regardless, the UI I was hoping for was to make the default
postgresql.conf file end with a line like this:

directory 'conf'

Which makes the inclusion explicit for people used to navigating the
current style: look for a "conf" directory under PGDATA, those have the
modifications you're looking for. That's also easy for distributors to
patch, so you might even have the RHEL build ship with something like:

directory '/etc/sysconfig/postgresql/config'

I don't think it's asking too much of tool authors that given a PGDATA,
they would need to parse the postgresql.conf file in there just well
enough to figure out where the directory(s) of additional config files is
at. That level of config file manipulation there's already code for in
initdb, I was planning to refactor that into a library I can include for
the built-in pgtune I've been planning.

--
* Greg Smith gsmith(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD

On Sat, Oct 24, 2009 at 9:41 AM, Magnus Hagander <magnus(at)hagander(dot)net> wrote:
> Per discussion at the developer meeting back in Ottawa, attached is an
> initial patch that implements reading a directory of configuration
> files instead of just one. The idea being that something like a tuning
> tool, or pgadmin, for example can drop and modify files in this
> directory instead of modifying the main config file (which can be very
> hard to machine-parse).

The solution to the problem mentioned parenthetically here is

> $PGDATA/postgresql.conf

And no matter how much anyone cares to protest, that is the ONLY real
solution to the problem of postgresql.conf being hard to parse until
we have software that can pass the Turing test. At the aforementioned
developer meeting, or anyway sometime at PGcon, there was some
discussion of the following variant, which would also work:

echo "# 'man postgresql.conf' for information about the contents of
this file" > $PGDATA/postgresql.conf

Supporting an include-directory seems harmless to me, and even
potentially useful. But the only way to solve the problem of
machine-parsing the config file is to remove the instructions (which
can only EVER be parsed by human beings) and put them somewhere else.

To reiterate, I have no problem with the proposal (I have not examined
the code), but I respectfully submit that it's not solving the problem
you think it's solving.

...Robert

phone answering, please forgive style...

--
dim

Le 24 oct. 2009 à 20:10, Robert Haas <robertmhaas(at)gmail(dot)com> a écrit :
>> $PGDATA/postgresql.conf

I think the multiple files should go in $PGDATA/postgresql.conf.d

> But the only way to solve the problem of
> machine-parsing the config file is to remove the instructions (which
> can only EVER be parsed by human beings) and put them somewhere else.

Not true. The problem we're trying to solve, methinks, is to be able
to provide a kind of SET PERMANENT feature.

The easiest alternative for providing this given current conf file
content style is to offer one file per GUC, where the first non
comment line is supposed to contain the option value.

Now if you first load postresql.conf then files in postresql.conf.d,
you did not change current behavior for $EDITOR people and made it
easy to have SET PERSISTENT + free form comment.

Regards,

On Oct 24, 2009, at 4:01 PM, Dimitri Fontaine <dfontaine(at)hi-media(dot)com>
wrote:
> Le 24 oct. 2009 à 20:10, Robert Haas <robertmhaas(at)gmail(dot)com> a écrit
> :
>>> $PGDATA/postgresql.conf
>
> I think the multiple files should go in $PGDATA/postgresql.conf.d
>
>> But the only way to solve the problem of
>> machine-parsing the config file is to remove the instructions (which
>> can only EVER be parsed by human beings) and put them somewhere else.
>
> Not true. The problem we're trying to solve, methinks, is to be able
> to provide a kind of SET PERMANENT feature.
>
> The easiest alternative for providing this given current conf file
> content style is to offer one file per GUC, where the first non
> comment line is supposed to contain the option value.
>
> Now if you first load postresql.conf then files in postresql.conf.d,
> you did not change current behavior for $EDITOR people and made it
> easy to have SET PERSISTENT + free form comment.

I guess that would solve the problem of knowing which comment is
associated with which setting, but it won't prevent SET PERSISTENT
from falsifying a comment like "the previous value of this setting was
4MB, but I changed it on YYYY-MM-DD". Maybe we don't care about that,
though.

...Robert

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> Supporting an include-directory seems harmless to me, and even
> potentially useful. But the only way to solve the problem of
> machine-parsing the config file is to remove the instructions (which
> can only EVER be parsed by human beings) and put them somewhere else.

Uh, that is complete nonsense. The instructions are not a problem.
The ability to have comments at all might be thought to be a problem,
but removing it isn't going to be an acceptable solution.

regards, tom lane

On Sat, 24 Oct 2009, Robert Haas wrote:

> But the only way to solve the problem of machine-parsing the config file
> is to remove the instructions (which can only EVER be parsed by human
> beings) and put them somewhere else.

Ah, back to this again already. Your suggestion presumes there is someone
who can successfully force a decision to deprecate the existing format.
There is no such person, and thinking you have to win that battle is one
of the many traps one can fall into here and never escape from.

The roadmap here looks something like this:

1) Support a standard for include directories

2) Update tools to use them rather than ever modifying the primary
postgresql.conf

3) Once one of those matures, bundle a standard tool with the database
that does most of what people want for initial configuration. That only
has to worry about writing to the new include directory format rather than
trying to parse existing postgresql.conf files and write them back out
again at all.

4) Once the tool has proven itself, and people have become more
comfortable with using the newer config format, allow the option of
generating a shorter example file rather than the current large one.

5) Completely deprecate the giant config file *only* if the new approach
becomes so wildly successful that fans of editing the giant file admit
defeat at the hands of the new approach. This is completely optional and
possibly not ever possible.

If we get bogged down presuming (5) is the first useful step here, which
seems to be what you're suggesting, no progress will ever get made here.

> To reiterate, I have no problem with the proposal (I have not examined
> the code), but I respectfully submit that it's not solving the problem
> you think it's solving.

As the message introducing it says, the goal this aims at is making life
easier for tool builders. I think you're extrapolating beyond its
intended scope in your evaluation of what problem it's aiming to solve.

--
* Greg Smith gsmith(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD

On Sat, Oct 24, 2009 at 7:38 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> Supporting an include-directory seems harmless to me, and even
>> potentially useful.  But the only way to solve the problem of
>> machine-parsing the config file is to remove the instructions (which
>> can only EVER be parsed by human beings) and put them somewhere else.
>
> Uh, that is complete nonsense.  The instructions are not a problem.
> The ability to have comments at all might be thought to be a problem,
> but removing it isn't going to be an acceptable solution.

If you're saying that the instructions are no more of a problem than
any other comment, then I guess I agree. But the comments in the
default file which we ship are mostly instructions, hence my original
statement.

I don't believe that the *ability* to have comments is the problem.
It wouldn't hurt anything to ship a file with a general comment block
at the top, with whatever content someone wants to put there. What
makes it impossible to machine-edit this file is that there is a
comment for every single setting, and that the "right place" to insert
a value for any particular setting is (at least in the default
configuration) marked by a comment which can be interpreted by humans
and not by a computer.

Shipping a mostly-empty file with a pointer to a man page that
included all of the instructions now contained in the file would make
things immensely easier for people who want to write programs to tune
the configuration, because it would transform the task of writing a
program that rewrites the configuration file from Turing-complete to
very easy. For the same reason, it would also allow us to support SET
PERSISTENT. On the flip side, as long as we leave it the way it is,
we can't do those things. We can argue about whether that's a good
trade-off, but that IS the trade-off.

...Robert

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> I don't believe that the *ability* to have comments is the problem.
> It wouldn't hurt anything to ship a file with a general comment block
> at the top, with whatever content someone wants to put there. What
> makes it impossible to machine-edit this file is that there is a
> comment for every single setting, and that the "right place" to insert
> a value for any particular setting is (at least in the default
> configuration) marked by a comment which can be interpreted by humans
> and not by a computer.

Right, but your mistake is in supposing that that statement has
something to do with the instructions. What it has to do with is
a style of usage that the instructions happen to exemplify --- but
getting rid of the instructions wouldn't make people change their
usage habits.

I concur with Greg Smith's nearby comments that the way to go at this
is a stepwise process. It is only *after* there is a workable tool
that is a clear improvement on manual editing that you will have any
chance of getting people to move away from manual editing, or even
getting them to entertain any change proposals that make manual editing
less pleasant.

regards, tom lane

Magnus Hagander <magnus(at)hagander(dot)net> writes:
> 2009/10/24 Simon Riggs <simon(at)2ndquadrant(dot)com>:
>> Could we have a new directive in postgresql.conf that allows you to
>> specify an includedirectory? Like an include directive but for a whole
>> directory rather than just a file.

> We could do it that way, but that would make the change bigger, not smaller :-P

I think we should have an explicit include-directory directive, and the
reason I think so is that it makes it fairly easy for the user to
control the relative precedence of the manual settings (presumed to
still be kept in postgresql.conf) and the automatic settings (presumed
to be in files in the directory). Manual settings before the include
are overridable, those after are not.

> Did you look at the patch? That's basically what it does now, except
> it doesn't add a parameter in postgresql.conf. If you lkeave the
> pg_config directory empty, it will just parse the postgresql.conf file
> just like before, and that's it. only if you put something in the
> pg_config directory will it load it, and only *after* it has loaded
> the main configuration file.

That last is a deal-breaker for me; I do not want a hard wired
presumption that manual settings should be overridden.

regards, tom lane

On Sat, Oct 24, 2009 at 9:50 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> I don't believe that the *ability* to have comments is the problem.
>> It wouldn't hurt anything to ship a file with a general comment block
>> at the top, with whatever content someone wants to put there.  What
>> makes it impossible to machine-edit this file is that there is a
>> comment for every single setting, and that the "right place" to insert
>> a value for any particular setting is (at least in the default
>> configuration) marked by a comment which can be interpreted by humans
>> and not by a computer.
>
> Right, but your mistake is in supposing that that statement has
> something to do with the instructions.  What it has to do with is
> a style of usage that the instructions happen to exemplify --- but
> getting rid of the instructions wouldn't make people change their
> usage habits.

I don't really understand this. What usage habits do we need to
change? The problem is that people expect that the setting for some
GUC is going to be near the comment block that describes that GUC. If
the comment blocks describing those GUCs were gone, then that
expectation would be removed.

It's true that someone might take an empty default file and do
something like this:

# ok, so now i'm going to set work_mem to a ridiculous value
work_mem = '1GB'

And they might be surprised if some automated-config-file rewriter
ended up shuffling the setting to some location in the file that was
no longer close to the comment. But most people probably won't add
things like that if they're not already present, and even if they do
there probably won't be that many of them, and they'll get it sorted
out.

> I concur with Greg Smith's nearby comments that the way to go at this
> is a stepwise process.  It is only *after* there is a workable tool
> that is a clear improvement on manual editing that you will have any
> chance of getting people to move away from manual editing, or even
> getting them to entertain any change proposals that make manual editing
> less pleasant.

Well, there are certainly config-tuning tools already. Just since I
started reading this list, there is pgtune; and I'm sure there are
others I don't know about. Coming up with the settings is the easy
part; getting them into the file is the hard part. There have also
been several requests for a SQL command that updates postgresql.conf,
which would not be very hard to write if we made it so that such a
command needn't care about the comments beyond preserving them, but
which is completely unworkable as things stand. I think it's
completely backwards to suppose that nobody has written the tools but
when they do we'll consider adjusting the file; rather, people have
done the best they can already given that the file is very difficult
to work with, and when/if it stops being so difficult, they'll likely
do more.

I realize that the current file format is an old and familiar friend;
it is for me, too. But I think it's standing in the way of progress.
Being able to type a SQL command to update postgresql.conf would be
more substantially convenient than logging in as root, using su to
become postgres, changing to the correct directory, starting up vi,
finding the right setting, editing it, quitting out, and requesting a
reload. And I deal with 1 PostgreSQL instance at a time, not tens or
hundreds or thousands.

...Robert

On lör, 2009-10-24 at 13:32 -0400, Greg Smith wrote:
> Regardless, the UI I was hoping for was to make the default
> postgresql.conf file end with a line like this:
>
> directory 'conf'

I think something like is this is definitely more understandable for
users and less overkill in the implementation.

As a file point, I would prefer something like

include 'whatever/*.conf'

that is, listing the files as a glob pattern instead of naming a
directory. Because packaging tools, editors, etc. will leave backup and
temporary files lying around that you don't want to include, and we
don't want to get involved into knowing all the naming patterns that
people might want to use for those.

Actually,

include 'postgresql.conf.d/*.conf'

sounds nice as a default.

On Sun, Oct 25, 2009 at 10:08 AM, Peter Eisentraut <peter_e(at)gmx(dot)net> wrote:
> As a file point, I would prefer something like
>
> include 'whatever/*.conf'

+1 for that. That's what Apache does and it works well for the users
and the packagers.

--
Guillaume

Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
> As a file point, I would prefer something like

> include 'whatever/*.conf'

> that is, listing the files as a glob pattern instead of naming a
> directory.

+1, but *only* if it does not lead to us having to write our own
version of glob(). It's not worth that much work, compared to just
allowing a directory name and hard-wiring the "*.conf" rule.

Some poking around suggests that glob(3) is reasonably portable
across Unixen, but is it provided on Windows?

regards, tom lane

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> I don't really understand this. What usage habits do we need to
> change?

The assumption that it's okay to document what you've done with
something like

# work_mem = '1GB'
# changed to give saner behavior 10/25/08
work_mem = '10MB'

> The problem is that people expect that the setting for some
> GUC is going to be near the comment block that describes that GUC.

Or more generally, that the comments they've put next to a setting
are going to stay next to it. Once again: this isn't about the
instructions.

regards, tom lane

On Sun, Oct 25, 2009 at 6:06 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> I don't really understand this.  What usage habits do we need to
>> change?
>
> The assumption that it's okay to document what you've done with
> something like
>
>        # work_mem = '1GB'
>        # changed to give saner behavior 10/25/08
>        work_mem = '10MB'
>
>> The problem is that people expect that the setting for some
>> GUC is going to be near the comment block that describes that GUC.
>
> Or more generally, that the comments they've put next to a setting
> are going to stay next to it.  Once again: this isn't about the
> instructions.

I just don't buy it. With the instructions in the file, a program
that rewrites the file will fail miserably on every installation out
there (or will be full of logic that tries, futilely, to parse the
comments). With the instructions out of the file, a program that
rewrites the file will work just fine on all installations except
those that have chosen to do the thing you describe above. And we can
tell people "don't do that any more, because it'll mess up the
automated tools".

...Robert

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> I just don't buy it. With the instructions in the file, a program
> that rewrites the file will fail miserably on every installation out
> there (or will be full of logic that tries, futilely, to parse the
> comments). With the instructions out of the file, a program that
> rewrites the file will work just fine on all installations except
> those that have chosen to do the thing you describe above. And we can
> tell people "don't do that any more, because it'll mess up the
> automated tools".

No, you *think* you might be able to tell people that. It is not
going to fly. It is especially not going to fly when the automated
tools are still just pie-in-the-sky rather than real live commonly
used tools.

Please re-read what Greg had to say.

regards, tom lane

On Sun, Oct 25, 2009 at 6:21 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> I just don't buy it.  With the instructions in the file, a program
>> that rewrites the file will fail miserably on every installation out
>> there (or will be full of logic that tries, futilely, to parse the
>> comments).  With the instructions out of the file, a program that
>> rewrites the file will work just fine on all installations except
>> those that have chosen to do the thing you describe above.  And we can
>> tell people "don't do that any more, because it'll mess up the
>> automated tools".
>
> No, you *think* you might be able to tell people that.  It is not
> going to fly.  It is especially not going to fly when the automated
> tools are still just pie-in-the-sky rather than real live commonly
> used tools.
>
> Please re-read what Greg had to say.

Well, there is obviously a difference of opinion on this topic, and
that is fine. I have reread what Greg said, and it seems to me that
we are arguing two sides of the same coin. Greg believes that it
isn't politically feasible to change the default postgresql.conf, now
or perhaps ever. I notice that he didn't say that he thinks it's a
bad idea. So he has come up with an alternate plan which he believes
is the best one possible considering that limitation.

I don't particularly disagree with his conclusion that his plan is the
best one possible without changing the current format; but I am not
sure that I agree that his plan will actually work. I especially
don't believe that it will ever support SET PERSISTENT, which I
believe to be a feature a lot of people want. Perhaps you have an
idea how that will happen?

...Robert

On Sun, 25 Oct 2009, Robert Haas wrote:

> I especially don't believe that it will ever support SET PERSISTENT,
> which I believe to be a feature a lot of people want.

It actually makes it completely trivial to implement. SET PERSISTENT can
now write all the changes out to a new file in the include directory.
Just ship the database with a persistent.conf in there that looks like
this:

# Changes made with SET PERSISTENT will appear here.

We might want to put a warning about the risks of editing it by hand in
there too once those are defined. Then just dump anything the user asks
to be changed with that command at the end. So if I did:

SET PERSISTENT checkpoint_segments='3';

persistent.conf would now look like this:

# Changes made with SET PERSISTENT will appear here.
# user gsmith 2009-10-26 10:12:02 checkpoint_segments=16 (was 3)
checkpoint_segments=16

And since that will get parsed after the primary postgresql.conf in the
default configuration, this implemenation will completely bypass the issue
of how to parse and make changes to the master file. We only need to
support the subset we expect in persistent.conf, and you even have the
option of rejecting SET PERSISTENT if that file isn't in the standard
format when you execute that statement.

It also makes undoing ill-advised changes made with the command trivial;
just remove everything that was added to that file. Given the "glob
*.conf" implementation, you might just make a backup copy with a different
extension. Worst case you just note how many lines the file had when the
server last started successfully and comment out the ones after that.

That also makes it possible to do risky changes to the configuration (like
increases to shared_buffers) in a sane way, because of that easy
reversibility. If the server won't start after a SET PERSISTENT change,
there's only one file with a well defined format to back out changes to.
One of the problems with all previous suggestions here was not having a
good answer to the "what if you make a change that breaks the server from
starting?" issue.

As for your concerns about parsing comments, that's the first step on the
path to madness here. In my mind, one major accomplishment of Magnus's
patch is providing a way to push in config files changes of all sorts
without ever having to pay attention to the current postgresql.conf format
and its associated mess. The best we could do before this patch was
manually adding new, clean include files to the end of the existing
postgresql.conf, which is pretty obviously not a good solution either.
If the default file shipped includes a directory to glob from, the most
new tools (which includes SET PERSISTENT) should have to interact with the
primary postgresql.conf is just to confirm that include directive exists
before they create/update a simpler config file in the directory.

--
* Greg Smith gsmith(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD

On Sun, 25 Oct 2009, Tom Lane wrote:

> Some poking around suggests that glob(3) is reasonably portable
> across Unixen, but is it provided on Windows?

You can probably use FindFirstFile for that:
http://msdn.microsoft.com/en-us/library/aa364418%28VS.85%29.aspx

Standard UNIX-ish glob implementations aren't ideal for strings like
"C:\Program Files\stuff" anyway. More notes on this subject:

http://stackoverflow.com/questions/1269480/globbing-in-c-c-on-windows
http://coding.derkeiler.com/Archive/Perl/comp.lang.perl.misc/2008-03/msg01419.html

If you look at the Perl code providing a Windows-oriented glob:
http://cpansearch.perl.org/src/TATE/File-Glob-Windows-0.1.3/lib/File/Glob/Windows.pm
you can see it even worries about things like correctly handling the fact
that there's a current directory on each drive in Windows land.

--
* Greg Smith gsmith(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD

On 10/25/09 5:33 PM, Robert Haas wrote:
> Greg believes that it
> isn't politically feasible to change the default postgresql.conf, now
> or perhaps ever. I notice that he didn't say that he thinks it's a
> bad idea. So he has come up with an alternate plan which he believes
> is the best one possible considering that limitation.

I agree with Greg. I would love to dump the current stupid long
postgresql.conf, but I've lost that argument every time I've had it.

We have to work around it.

--Josh Berkus

On Mon, Oct 26, 2009 at 12:46 AM, Josh Berkus <josh(at)agliodbs(dot)com> wrote:
> On 10/25/09 5:33 PM, Robert Haas wrote:
>>  Greg believes that it
>> isn't politically feasible to change the default postgresql.conf, now
>> or perhaps ever.  I notice that he didn't say that he thinks it's a
>> bad idea.  So he has come up with an alternate plan which he believes
>> is the best one possible considering that limitation.
>
> I agree with Greg.  I would love to dump the current stupid long
> postgresql.conf, but I've lost that argument every time I've had it.
>
> We have to work around it.

Do you have a pointer to the archives?

...Robert

On Mon, Oct 26, 2009 at 12:18 AM, Greg Smith <gsmith(at)gregsmith(dot)com> wrote:
> On Sun, 25 Oct 2009, Robert Haas wrote:
>
>> I especially don't believe that it will ever support SET PERSISTENT, which
>> I believe to be a feature a lot of people want.
>
> It actually makes it completely trivial to implement.  SET PERSISTENT can
> now write all the changes out to a new file in the include directory. Just
> ship the database with a persistent.conf in there that looks like this:

This only sorta works. If the changes are written out to a file that
is processed after postgresql.conf (or some other file that contains
values for those variables), then someone who edits postgresql.conf
(or some other file) by hand will think they have changed a setting
when they really haven't. On the flip side, there could also be still
other files that are processed afterwards, in which case SET
PERSISTENT would appear to work but not actually do anything.

...Robert

On Mon, 2009-10-26 at 09:04 -0400, Robert Haas wrote:
> On Mon, Oct 26, 2009 at 12:46 AM, Josh Berkus <josh(at)agliodbs(dot)com> wrote:
> > On 10/25/09 5:33 PM, Robert Haas wrote:
> >> Greg believes that it
> >> isn't politically feasible to change the default postgresql.conf, now
> >> or perhaps ever. I notice that he didn't say that he thinks it's a
> >> bad idea. So he has come up with an alternate plan which he believes
> >> is the best one possible considering that limitation.
> >
> > I agree with Greg. I would love to dump the current stupid long
> > postgresql.conf, but I've lost that argument every time I've had it.
> >
> > We have to work around it.
>
> Do you have a pointer to the archives?

http://archives.postgresql.org/pgsql-hackers/2008-08/msg00812.php

Robert Haas escribió:
> On Mon, Oct 26, 2009 at 12:18 AM, Greg Smith <gsmith(at)gregsmith(dot)com> wrote:

> > It actually makes it completely trivial to implement.  SET PERSISTENT can
> > now write all the changes out to a new file in the include directory. Just
> > ship the database with a persistent.conf in there that looks like this:
>
> This only sorta works. If the changes are written out to a file that
> is processed after postgresql.conf (or some other file that contains
> values for those variables), then someone who edits postgresql.conf
> (or some other file) by hand will think they have changed a setting
> when they really haven't.

Maybe SET PERSISTENT needs to go back to postgresql.conf, add an
automatic comment "# overridden in persistent.conf" and put a comment
marker in front of the original line. That way the user is led to the
actual authoritative source.

> On the flip side, there could also be still
> other files that are processed afterwards, in which case SET
> PERSISTENT would appear to work but not actually do anything.

Fortunately we now have an easy way to find out which file is each
setting's value coming from.

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Alvaro Herrera <alvherre(at)commandprompt(dot)com> writes:
> Maybe SET PERSISTENT needs to go back to postgresql.conf, add an
> automatic comment "# overridden in persistent.conf" and put a comment
> marker in front of the original line. That way the user is led to the
> actual authoritative source.

Doesn't that require the same AI-complete parsing ability we have said
we don't want to implement?

Personally I think this is just a matter of usage. If you want to use
SET PERSISTENT, don't set values manually in postgresql.conf. How is
that different from the existing rule that if you want to set values in
postgresql.conf, you'd better not set them on the postmaster command
line?

> Fortunately we now have an easy way to find out which file is each
> setting's value coming from.

Yeah --- that feature should make it easy enough to debug any conflicts.

I think we shouldn't overthink this. The separate file with a clear
warning to not edit it manually seems like a fine approach from here.

regards, tom lane

Tom Lane escribió:
> Alvaro Herrera <alvherre(at)commandprompt(dot)com> writes:
> > Maybe SET PERSISTENT needs to go back to postgresql.conf, add an
> > automatic comment "# overridden in persistent.conf" and put a comment
> > marker in front of the original line. That way the user is led to the
> > actual authoritative source.
>
> Doesn't that require the same AI-complete parsing ability we have said
> we don't want to implement?

Huh, no, it's not necessary to parse the comment previous to the value.
Just comment it off.

> Personally I think this is just a matter of usage. If you want to use
> SET PERSISTENT, don't set values manually in postgresql.conf. How is
> that different from the existing rule that if you want to set values in
> postgresql.conf, you'd better not set them on the postmaster command
> line?

I agree, except that some things are defined in postgresql.conf by
initdb and you probably want to be able to change them by SET PERSISTENT
anyway (e.g. lc_messages, listen_addresses, shared_buffers)

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

On Mon, Oct 26, 2009 at 9:51 AM, Peter Eisentraut <peter_e(at)gmx(dot)net> wrote:
> On Mon, 2009-10-26 at 09:04 -0400, Robert Haas wrote:
>> On Mon, Oct 26, 2009 at 12:46 AM, Josh Berkus <josh(at)agliodbs(dot)com> wrote:
>> > On 10/25/09 5:33 PM, Robert Haas wrote:
>> >>  Greg believes that it
>> >> isn't politically feasible to change the default postgresql.conf, now
>> >> or perhaps ever.  I notice that he didn't say that he thinks it's a
>> >> bad idea.  So he has come up with an alternate plan which he believes
>> >> is the best one possible considering that limitation.
>> >
>> > I agree with Greg.  I would love to dump the current stupid long
>> > postgresql.conf, but I've lost that argument every time I've had it.
>> >
>> > We have to work around it.
>>
>> Do you have a pointer to the archives?
>
> http://archives.postgresql.org/pgsql-hackers/2008-08/msg00812.php

Thanks. This thread seems to contain overwhelming SUPPORT for
shortening the file. Greg Sabino Mullane didn't like it, and there
were a smattering of ideas like "we should have a postgresql.conf man
page", "we should make sure to document which parameters people are
most likely to need to adjust", and "we should have a config
generator" (all of which are good ideas), but nearly everyone seemed
to agree with the general idea that the current file contained way too
much unnecessary cruft. What am I missing here?

...Robert

Alvaro Herrera <alvherre(at)commandprompt(dot)com> writes:
> Tom Lane escribi:
>> Personally I think this is just a matter of usage. If you want to use
>> SET PERSISTENT, don't set values manually in postgresql.conf.

> I agree, except that some things are defined in postgresql.conf by
> initdb and you probably want to be able to change them by SET PERSISTENT
> anyway (e.g. lc_messages, listen_addresses, shared_buffers)

Well, initdb would also find it a lot easier to dump its settings into a
machine-generated file. I don't think we have to continue doing things
exactly the same way there.

regards, tom lane

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> What am I missing here?

You're still attacking the wrong straw man. Whether the file contains a
lot of commentary by default is NOT the problem, and removing the
commentary is NOT the solution.

regards, tom lane

Tom Lane escribió:
> Alvaro Herrera <alvherre(at)commandprompt(dot)com> writes:
> > Tom Lane escribi:
> >> Personally I think this is just a matter of usage. If you want to use
> >> SET PERSISTENT, don't set values manually in postgresql.conf.
>
> > I agree, except that some things are defined in postgresql.conf by
> > initdb and you probably want to be able to change them by SET PERSISTENT
> > anyway (e.g. lc_messages, listen_addresses, shared_buffers)
>
> Well, initdb would also find it a lot easier to dump its settings into a
> machine-generated file. I don't think we have to continue doing things
> exactly the same way there.

Hmm, so it would create a "00initdb.conf" file instead of the current
mess with search & replace on the template? That sounds good.

(But to me this also says that SET PERSISTENT has to go over
00initdb.conf and add a comment mark to the setting.)

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Alvaro Herrera <alvherre(at)commandprompt(dot)com> writes:
> (But to me this also says that SET PERSISTENT has to go over
> 00initdb.conf and add a comment mark to the setting.)

Why? As you yourself pointed out, pg_settings will show exactly where
the active value came from. Moreover, should we then conclude that any
edit to any file in the config directory has to run around and edit
every other file? I'll bet a lot of money that no Apache config editor
does that.

regards, tom lane

On Mon, 26 Oct 2009, Alvaro Herrera wrote:

> some things are defined in postgresql.conf by initdb and you probably
> want to be able to change them by SET PERSISTENT anyway (e.g.
> lc_messages, listen_addresses, shared_buffers)

An obvious next step once the directory parsing is committed is to change
initdb to put all of its changes into a separate file. Ideally, 8.5 would
ship with a postgresql.conf having zero active settings, and the conf/
directory would have two entries:

initdb.conf : shared_buffers, lc_messages, listen_addresses, etc.
persistent.conf : Blank except for comment text

People who want to continue managing just the giant postgresql.conf are
free to collapse the initdb.conf back into the larger file instead. If we
wanted to make that transition easier, an option to initdb saying "do
things the old way" might make sense. I think the best we can do here is
make a path where new users who don't ask for anything special get a setup
that's easy for tools to work on, while not completely deprecating the old
approach for those who want it--but you have to ask for it.

--
* Greg Smith gsmith(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD

Robert Haas <robertmhaas(at)gmail(dot)com> wrote:

> I realize that the current file format is an old and familiar
> friend; it is for me, too. But I think it's standing in the way of
> progress. Being able to type a SQL command to update postgresql.conf
> would be more substantially convenient than logging in as root,
> using su to become postgres, changing to the correct directory,
> starting up vi, finding the right setting, editing it, quitting out,
> and requesting a reload. And I deal with 1 PostgreSQL instance at a
> time, not tens or hundreds or thousands.

Speaking as someone who has to help keep 200 geographically dispersed
PostgreSQL clusters running, I can say that "convenient" ways to
change configuration settings on individual servers has little appeal,
particularly if it makes it harder to enforce configuration policies
or to audit current settings. Generally, before applying any update
or configuration change to production servers we must first apply it
to a development environment and prove that it improves things without
breaking anything, then it can be rolled to a test environment where
those results must be confirmed, and then to a staging environment to
confirm both our install procedures and the behavior of the change
with a large number of testers going through standard scripts for
exercising the application software.

Copying scripts into place and reloading or restarting PostgreSQL is
not an imposition; anything which reduces my confidence in knowing
what configuration is in use is an imposition. Piping a list of
server names through xargs to a deploy script just isn't a big deal,
once we have an acceptable configuration.

We do find the include capabilities useful. For example, for our 72
production servers for county Circuit Court systems, we copy an
identical postgresql.conf file to each county, with the last line
being an include to an overrides conf file in /etc/. For most
counties that file is empty. For counties where we've installed extra
RAM or where data is not fully cached, we override settings like
effective_cache_size or the page costs. I can't see where any of the
options under discussion would do much to help an environment like
ours -- they seem more likely to help shops with fewer servers or more
relaxed deployment procedures.

-Kevin

Greg Smith <gsmith(at)gregsmith(dot)com> writes:
> People who want to continue managing just the giant postgresql.conf are
> free to collapse the initdb.conf back into the larger file instead. If we
> wanted to make that transition easier, an option to initdb saying "do
> things the old way" might make sense. I think the best we can do here is
> make a path where new users who don't ask for anything special get a setup
> that's easy for tools to work on, while not completely deprecating the old
> approach for those who want it--but you have to ask for it.

I don't think we need an explicit option for that. What we need is an
'includedir' directive at the bottom of postgresql.conf. Someone who
prefers to let manual settings override anything else might choose to
move it to the top, or even comment it out (at the cost of breaking
SET PERSISTENT). Everybody is happy, trogdolyte or otherwise.

I would personally suggest having initdb dump its settings right into
persistent.conf, rather than having a separate file for them, but it's
not a big deal either way.

(BTW, why do we actually need an includedir mechanism for this?
A simple include of a persistent.conf file seems like it would be
enough.)

regards, tom lane

On Mon, 26 Oct 2009, Alvaro Herrera wrote:

> But to me this also says that SET PERSISTENT has to go over
> 00initdb.conf and add a comment mark to the setting.

Now you're back to being screwed if the server won't start because of your
change, because you've lost the original working setting.

I think the whole idea of making tools find duplicates and comment them
out as part of making their changes is fundamentally broken, and it's just
going to get worse when switching to use more config files. The fact that
user edits can introduce the same problem, where something is set in more
than one file but only one of them works, means that you can run into this
even if tool editing hygiene is perfect.

A whole new approach is needed if you're going to get rid of this problem
both for tools and for manual edits. What I've been thinking of is making
it possible to run a configuration file check that scans the config
structure exactly the same way as the server, but when it finds a
duplicate setting it produces a warning showing where the one being
ignored is. The patch added near to the end of 8.4 development that
remembers the source file and line number of lines already parsed made
that more straightforward I think. Not having that data is what made this
hard to write when I last considered it a while ago.

If you had that utility, it's a simple jump to then make it run in a
"--fix" mode that just comments out every such ignored duplicate. Now
you've got a solution to this problem that handles any sort of way users
can mess with the configuration. One might even make a case that this
tool should get run just after every time the server starts successfully.

--
* Greg Smith gsmith(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD

Greg Smith <gsmith(at)gregsmith(dot)com> writes:
> I think the whole idea of making tools find duplicates and comment them
> out as part of making their changes is fundamentally broken, and it's just
> going to get worse when switching to use more config files.

Quite. There seems to me to be a whole lot of solving of hypothetical
problems going on in this thread. I think we should just do the
simplest thing and see how it works. When and if there is some evidence
of people actually getting confused, we could consider trying to
auto-comment-out duplicate settings. But I've never heard of any other
tool doing that, and fail to see why we should think Postgres needs to.

regards, tom lane

On Mon, 26 Oct 2009, Tom Lane wrote:

> BTW, why do we actually need an includedir mechanism for this?
> A simple include of a persistent.conf file seems like it would be
> enough.

Sure, you could do it that way. This patch is more about elegance rather
than being strictly required. The general consensus here seemed to be
that if you're going to start shipping the database with more than one
config file, rather than just hacking those in one at a time it would be
preferrable to grab a directory of them. That seems to be how similar
programs handle things once the number of shipped config files goes from 1
to >1.

One thing this discussion has made me reconsider is whether one of those
files needs to be enforced as always the last one to be parsed, similar to
how postgresql.conf is always the first one. I am slightly concerned that
a future SET PERSISTENT mechanism might update a setting that's later
overriden by a file that just happens to be found later than the mythical
persistent.conf. I'd rather worry about that in the future rather than
burden current design with that detail though. Alvaro already introduced
the init-script way of handling this by suggesting the configuration file
name 00initdb ; using that and 99persistent would seem to be a reasonable
solution that's quite familiar to much of the target audience here. Note
that I don't think that standard requires anything beyond what the
proposed patch already does, processing files in alphabetical order.

--
* Greg Smith gsmith(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD

On Mon, 26 Oct 2009, Tom Lane wrote:

> When and if there is some evidence of people actually getting confused,
> we could consider trying to auto-comment-out duplicate settings. But
> I've never heard of any other tool doing that, and fail to see why we
> should think Postgres needs to.

It's what people tend to do when editing the postgresql.conf file(s) by
hand, which is why I think there's some expectation that tools will
continue that behavior. What everyone should understand is that we don't
have more tools exactly because their design always gets burdened with
details like that. This is easy to handle by hand, but hard to get a
program to do in a way that satisfies what everyone is looking for.
Raising the bar for tool-assisted changes (and I'm including SET
PERSISTENT in that category) like that is one reason so few such tools
have been written.

--
* Greg Smith gsmith(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD

On Mon, Oct 26, 2009 at 11:07 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Greg Smith <gsmith(at)gregsmith(dot)com> writes:
>> People who want to continue managing just the giant postgresql.conf are
>> free to collapse the initdb.conf back into the larger file instead.  If we
>> wanted to make that transition easier, an option to initdb saying "do
>> things the old way" might make sense.  I think the best we can do here is
>> make a path where new users who don't ask for anything special get a setup
>> that's easy for tools to work on, while not completely deprecating the old
>> approach for those who want it--but you have to ask for it.
>
> I don't think we need an explicit option for that.  What we need is an
> 'includedir' directive at the bottom of postgresql.conf.  Someone who
> prefers to let manual settings override anything else might choose to
> move it to the top, or even comment it out (at the cost of breaking
> SET PERSISTENT).  Everybody is happy, trogdolyte or otherwise.
>
> I would personally suggest having initdb dump its settings right into
> persistent.conf, rather than having a separate file for them, but it's
> not a big deal either way.

That make sense to me.

> (BTW, why do we actually need an includedir mechanism for this?
> A simple include of a persistent.conf file seems like it would be
> enough.)

I was starting to wonder that, too.

...Robert

On Mon, Oct 26, 2009 at 10:47 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>>  What am I missing here?
>
> You're still attacking the wrong straw man.  Whether the file contains a
> lot of commentary by default is NOT the problem, and removing the
> commentary is NOT the solution.

Wow, not only am I attacking a straw man, but I'm attacking the wrong one. :-)

I'm not sure whether you're saying that I'm bringing this issue up in
the wrong thread, or whether you disagree with the basic suggestion.
If it's the former, I'm prepared to concede the point and will start a
new thread. If the latter, you took the opposite position here.

http://archives.postgresql.org/pgsql-hackers/2008-08/msg00835.php

I think the questions of what goes into the default postgresql.conf
files, the include-dir mechanism, automatic tuning tools, and SET
PERSISTENT are all closely related, and if you think otherwise, I
don't understand why, but would appreciate an explanation. Elsewhere
on this thread, you suggested dumping the initdb functions into a
mostly-empty persistent.conf file that would be read after
postgresql.conf. If we did that, then we would presumably advise
people not to set settings in postgresql.conf because of the
possibility that they would be overriden in persistent.conf, which
begs the question of why we need two files at all.

...Robert

On Sat, Oct 24, 2009 at 6:55 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> I think we should have an explicit include-directory directive, and the
> reason I think so is that it makes it fairly easy for the user to
> control the relative precedence of the manual settings (presumed to
> still be kept in postgresql.conf) and the automatic settings (presumed
> to be in files in the directory).  Manual settings before the include
> are overridable, those after are not.

I think we can actually aim higher now. We don't need nearly as many
degrees of freedom as people seem to be suggesting. And in this space
degrees of freedom just mean the ability to have confusing
configurations that surprise users.

I would suggest the following:

The system always scans postgresql.conf and postgresql.conf.d in the
same location. We can support include and includedir directives though
I think they would be mostly unnecessary. But they would be purely for
the purpose of organizing your files and adding additional locations,
not replacing the standard locations. They might be useful for, for
example, having a site-wide set of defaults which are loaded before
the cluster-specific files.

postgresql.conf settings override postgresql.conf.d settings.
postgresql.conf should no longer be a place for tools to automatically
edit, and ideally it should be shipped empty so anything there is an
explicit manual instruction from a sysadmin and should override
anything installed by a package or tool.

When scanning postgresql.conf.d we should follow the Apache/Debian
standard of scanning only files which match a single simple hard-coded
template. I think the convention is basically the regexp
^[0-9a-zA-Z-]*.conf$. It's important that it exclude typical backup
file conventions like foo~ or foo.bak and lock file conventions like
.#foo. There's no need for this to be configurable and I think that
would be actively harmful.

--
greg

On Mon, Oct 26, 2009 at 7:06 AM, Alvaro Herrera
<alvherre(at)commandprompt(dot)com> wrote:
> Maybe SET PERSISTENT needs to go back to postgresql.conf, add an
> automatic comment "# overridden in persistent.conf" and put a comment
> marker in front of the original line.  That way the user is led to the
> actual authoritative source.

We cannot have automatic processes editing user configuration files.
What we could do is give a warning if you do set persistent and the
source of the current value is a postgresql.conf.

--
greg

On Mon, Oct 26, 2009 at 7:25 AM, Alvaro Herrera
<alvherre(at)commandprompt(dot)com> wrote:
> I agree, except that some things are defined in postgresql.conf by
> initdb and you probably want to be able to change them by SET PERSISTENT
> anyway (e.g. lc_messages, listen_addresses, shared_buffers)

These things should go into a postgresql.d/00initdb-defaults.conf file
instead. Otherwise tools wouldn't be able to tune them at all without
user intervention.

--
greg

On Mon, 2009-10-26 at 10:19 -0400, Tom Lane wrote:
> Alvaro Herrera <alvherre(at)commandprompt(dot)com> writes:
> > Maybe SET PERSISTENT needs to go back to postgresql.conf, add an
> > automatic comment "# overridden in persistent.conf" and put a comment
> > marker in front of the original line. That way the user is led to the
> > actual authoritative source.
>
> Doesn't that require the same AI-complete parsing ability we have said
> we don't want to implement?
>
> Personally I think this is just a matter of usage. If you want to use
> SET PERSISTENT, don't set values manually in postgresql.conf. How is
> that different from the existing rule that if you want to set values in
> postgresql.conf, you'd better not set them on the postmaster command
> line?
>
> > Fortunately we now have an easy way to find out which file is each
> > setting's value coming from.
>
> Yeah --- that feature should make it easy enough to debug any conflicts.
>
> I think we shouldn't overthink this. The separate file with a clear
> warning to not edit it manually seems like a fine approach from here.

+1

This is a very usual thing to do. You just have a warning that says,

"THIS FILE IS AUTOGENERATED FROM .... SEE THE PERSISTANCE DOCS"

Joshua D. Drake

>
> regards, tom lane
>

--
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564
Consulting, Training, Support, Custom Development, Engineering
If the world pushes look it in the eye and GRR. Then push back harder. - Salamander

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> I'm not sure whether you're saying that I'm bringing this issue up in
> the wrong thread, or whether you disagree with the basic suggestion.

The former --- whether we want to trim down the commentary in
postgresql.conf seems to me to have nothing to do with what's
being discussed in this thread. As Greg Smith already stated
in a couple of ways, the issue here is about being able to support
**simple** tools that make modifications to system-wide parameter
settings. Removing default commentary from postgresql.conf does
not help that at all. Removing the ability to use comments there
at all might help, but if you haven't figured out yet that no such
proposal will fly, I'm not sure how much clearer I can say it.

The desire to not have a ridiculously high bar for config adjustment
tools also seems to me to be plenty of reason to reject the various
odd ideas we have seen like making tools go around and edit files
other than the one they are chartered to put settings in.

> on this thread, you suggested dumping the initdb functions into a
> mostly-empty persistent.conf file that would be read after
> postgresql.conf. If we did that, then we would presumably advise
> people not to set settings in postgresql.conf because of the
> possibility that they would be overriden in persistent.conf, which
> begs the question of why we need two files at all.

You are confusing who is in charge here. It's not the tool, it's
the DBA, and anybody who thinks differently is going to keep losing
arguments. I would actually suggest that it'd be better to put
the include of persistent.conf first, with a comment (!) pointing
out that any subsequent manual settings will override that. Some
people will choose to use persistent.conf, some won't care to; and
the main problem I'm seeing in this debate is the apparent desire
to force the latter group to do what somebody else thinks is good
for them. If you design a setup that can be used in multiple styles,
including the old one, you'll have a lot better chance of getting it
through.

regards, tom lane

On 10/26/09 9:01 AM, Robert Haas wrote:
>> (BTW, why do we actually need an includedir mechanism for this?
>> > A simple include of a persistent.conf file seems like it would be
>> > enough.)
>
> I was starting to wonder that, too.

Different issue, really, which is that some people (including me) would
like to break up PostgreSQL configuration into 7 or 8 files based on
functional area (e.g. memory.conf, logging.conf, custom_options.conf
...). I do this with my Apache configs, and find it vastly more
manageable than one big file, especially under SCM. If I write a
config management tool, my tool will also do this.

That's the reason for the dir, not persistent.conf, which I agree could
be a single file.

--Josh Berkus

On Mon, Oct 26, 2009 at 8:07 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> (BTW, why do we actually need an includedir mechanism for this?
> A simple include of a persistent.conf file seems like it would be
> enough.)

Actually I think the include directory came from another use case
which we've also discussed. Namely modules which need some
configuration themselves. So for example when you install PostGIS it
could drop a postgis.conf in the directory which you could then either
edit yourself or override with SET PERSISTENT.
--
greg

On Mon, Oct 26, 2009 at 4:33 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> I'm not sure whether you're saying that I'm bringing this issue up in
>> the wrong thread, or whether you disagree with the basic suggestion.
>
> The former --- whether we want to trim down the commentary in
> postgresql.conf seems to me to have nothing to do with what's
> being discussed in this thread.  As Greg Smith already stated
> in a couple of ways, the issue here is about being able to support
> **simple** tools that make modifications to system-wide parameter
> settings.  Removing default commentary from postgresql.conf does
> not help that at all.

[ shrug ]

I don't particularly agree; but perhaps we can agree to disagree on
this point. For what it's worth, the thing that originally got me
involved in this thread was the following sentence in Magnus' OP.

"The idea being that something like a tuning tool, or pgadmin, for
example can drop and modify files in this directory instead of
modifying the main config file (which can be very hard to
machine-parse)."

I presume this sentence must refer to the aforementioned commentary,
because what else is there in that file that could be hard to parse?
Or that would be any different in an include-dir file?

> Removing the ability to use comments there
> at all might help, but if you haven't figured out yet that no such
> proposal will fly, I'm not sure how much clearer I can say it.

I agree that that would be a bad design, which is why I did not suggest it.

> The desire to not have a ridiculously high bar for config adjustment
> tools also seems to me to be plenty of reason to reject the various
> odd ideas we have seen like making tools go around and edit files
> other than the one they are chartered to put settings in.

I agree completely.

>> on this thread, you suggested dumping the initdb functions into a
>> mostly-empty persistent.conf file that would be read after
>> postgresql.conf.  If we did that, then we would presumably advise
>> people not to set settings in postgresql.conf because of the
>> possibility that they would be overriden in persistent.conf, which
>> begs the question of why we need two files at all.
>
> You are confusing who is in charge here.  It's not the tool, it's
> the DBA, and anybody who thinks differently is going to keep losing
> arguments.  I would actually suggest that it'd be better to put
> the include of persistent.conf first, with a comment (!) pointing
> out that any subsequent manual settings will override that.  Some
> people will choose to use persistent.conf, some won't care to; and
> the main problem I'm seeing in this debate is the apparent desire
> to force the latter group to do what somebody else thinks is good
> for them.  If you design a setup that can be used in multiple styles,
> including the old one, you'll have a lot better chance of getting it
> through.

What you're proposing here will work with both styles, but it might
sometimes exhibit the rather surprising behavior that SET PERSISTENT
appears to work but doesn't actually do anything, with no clear
warning to the user of what has gone wrong. It seems like it would be
better to have some kind of a hard switch - e.g. if
postgresql.conf.auto exists, then we read settings that file, and SET
PERSISTENT updates it. If not, then we read the regular
postgresql.conf file, and any attempt to SET PERSISTENT fails with a
suitably informative error message.

...Robert

On Mon, Oct 26, 2009 at 1:40 PM, Josh Berkus <josh(at)agliodbs(dot)com> wrote:
>
> Different issue, really, which is that some people (including me) would
> like to break up PostgreSQL configuration into 7 or 8 files based on
> functional area (e.g. memory.conf, logging.conf, custom_options.conf
> ...).  I do this with my Apache configs, and find it vastly more
> manageable than one big file, especially under SCM.    If I write a
> config management tool, my tool will also do this.

This actually seems like a bad idea to me. It's fine for something
like apache virtual hosts where there's no ambiguity and in any case
it's you organizing it and you reading it back out. But for a tool to
do this is only going to lead to confusion when my thinking of where
to find the variables differs from yours.

>
> That's the reason for the dir, not persistent.conf, which I agree could
> be a single file.

Well you're assuming there's only one tool generating this config? We
have at least two and possibly more. initdb generates an initial set
of defaults, the user may well run some kind of autotuning program,
and then they also have variables set by SET PERSISTENT. That's three
pieces of configuration being edited by different pieces of software.
The only way that will stay sane will be if each piece of software has
its own file to dump its own configuration into. If they start editing
each others configuration it'll all be one big pile of
non-deterministic spaghetti.

--
greg

+1

Would you make it +2?

--
dim

Le 26 oct. 2009 à 19:15, Greg Stark <gsstark(at)mit(dot)edu> a écrit :

> On Sat, Oct 24, 2009 at 6:55 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>
>> I think we should have an explicit include-directory directive, and
>> the
>> reason I think so is that it makes it fairly easy for the user to
>> control the relative precedence of the manual settings (presumed to
>> still be kept in postgresql.conf) and the automatic settings
>> (presumed
>> to be in files in the directory). Manual settings before the include
>> are overridable, those after are not.
>
> I think we can actually aim higher now. We don't need nearly as many
> degrees of freedom as people seem to be suggesting. And in this space
> degrees of freedom just mean the ability to have confusing
> configurations that surprise users.
>
> I would suggest the following:
>
> The system always scans postgresql.conf and postgresql.conf.d in the
> same location. We can support include and includedir directives though
> I think they would be mostly unnecessary. But they would be purely for
> the purpose of organizing your files and adding additional locations,
> not replacing the standard locations. They might be useful for, for
> example, having a site-wide set of defaults which are loaded before
> the cluster-specific files.
>
> postgresql.conf settings override postgresql.conf.d settings.
> postgresql.conf should no longer be a place for tools to automatically
> edit, and ideally it should be shipped empty so anything there is an
> explicit manual instruction from a sysadmin and should override
> anything installed by a package or tool.
>
> When scanning postgresql.conf.d we should follow the Apache/Debian
> standard of scanning only files which match a single simple hard-coded
> template. I think the convention is basically the regexp
> ^[0-9a-zA-Z-]*.conf$. It's important that it exclude typical backup
> file conventions like foo~ or foo.bak and lock file conventions like
> .#foo. There's no need for this to be configurable and I think that
> would be actively harmful.
>
> --
> greg
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers

On Mon, 26 Oct 2009, Greg Stark wrote:

> Actually I think the include directory came from another use case
> which we've also discussed. Namely modules which need some
> configuration themselves. So for example when you install PostGIS it
> could drop a postgis.conf in the directory which you could then either
> edit yourself or override with SET PERSISTENT.

For modules that want to touch custom_variable_classes, they would still
need to touch the global config. While there was some discussion about
the ability to append/prepend/filter search_path, is there something
simpler (because order doesn't matter) we can do for
custom_variable_classes?

Kris Jurka

Greg,

> This actually seems like a bad idea to me.

You write your tool your way, I'll write my tool mine. We'll see which
one works the best in the field.

> Well you're assuming there's only one tool generating this config? We
> have at least two and possibly more. initdb generates an initial set
> of defaults, the user may well run some kind of autotuning program,
> and then they also have variables set by SET PERSISTENT. That's three
> pieces of configuration being edited by different pieces of software.

Well, that's what I'd call a bad idea. Mixing external autotuner which
writes to files with SET PERSISTENT?

--Josh Berkus

On Mon, Oct 26, 2009 at 3:30 PM, Josh Berkus <josh(at)agliodbs(dot)com> wrote:
> Greg,
>
>> This actually seems like a bad idea to me.
>
> You write your tool your way, I'll write my tool mine.  We'll see which
> one works the best in the field.

Yeah actually I meant to but YMMV on that comment and forgot.

>
>> Well you're assuming there's only one tool generating this config? We
>> have at least two and possibly more. initdb generates an initial set
>> of defaults, the user may well run some kind of autotuning program,
>> and then they also have variables set by SET PERSISTENT. That's three
>> pieces of configuration being edited by different pieces of software.
>
> Well, that's what I'd call a bad idea.  Mixing external autotuner which
> writes to files with SET PERSISTENT?

Well you'll need a story for that. You can't stop users from doing SET
PERSISTENT and you'll probably want to adjust some of the variables
that initdb sets up too.

I'm thinking a typical postgresql.d directory would contain
00initdb.conf
50autotuner.conf
99persistent.conf

And also of course read postgresql.conf for any manual settings.

When you run autotuner you could either check if any variables have a
source which comes after 50autotuner.conf and take them into account
or just dump your settings into 50autotuner.conf and then give a
warning if any of them are overridden.

Likewise I would expect SET PERSISTENT to check if any variables have
a source which comes later than 99persistent.conf (namely
postgresql.conf normally) and give a warning. (but still dump the
variable into the 99persistent.conf file)

--
greg

On Mon, 26 Oct 2009, Kevin Grittner wrote:

> We do find the include capabilities useful. For example, for our 72
> production servers for county Circuit Court systems, we copy an
> identical postgresql.conf file to each county, with the last line
> being an include to an overrides conf file in /etc/. For most
> counties that file is empty. For counties where we've installed extra
> RAM or where data is not fully cached, we override settings like
> effective_cache_size or the page costs. I can't see where any of the
> options under discussion would do much to help an environment like
> ours -- they seem more likely to help shops with fewer servers or more
> relaxed deployment procedures.

That's exactly a use case the "parsing config files in a directory"
feature aims to make easier to manage. You can just mix and match files
that adjust a subset of the postgresql.conf without having to explicitly
include them. For this sort of situation, you could create a base set of
configuration changes, then a set that customizes for less common server
configurations, and possibly even server-specific ones. Copy in the
subset from that master list of possible configuration sets that apply to
this server and you're done.

Since variations on this feedback keep coming up, let's be be clear here:
there is nothing this patch aims to add you can't already do with include
files. It's just a way to make more aggressive use of include files
easier to manage, and therefore make doing so in the default configuration
less objectionable.

--
* Greg Smith gsmith(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD

On Mon, 26 Oct 2009, Greg Stark wrote:

> When scanning postgresql.conf.d we should follow the Apache/Debian
> standard of scanning only files which match a single simple hard-coded
> template. I think the convention is basically the regexp
> ^[0-9a-zA-Z-]*.conf$. It's important that it exclude typical backup file
> conventions like foo~ or foo.bak and lock file conventions like .#foo.
> There's no need for this to be configurable and I think that would be
> actively harmful.

If the default glob pattern is *.conf, won't all those already be screened
out? I can see your point that letting it be adustable will inevitably
result in some fool one day writing a bad matching pattern that does grab
backup/lock files. But is that concern so important that we should limit
what people who know what they're doing are allowed to do?

That also seems to be the theme of the rest of your comments about how to
reorganize the postgresql.conf file. Your comments about what should and
shouldn't be configurable presumes it's OK for your priorities and what
you like to be enforced as policy on everyone. Whether or not I agree
with you, I object to the idea of dictating in this area because it just
encourages argument. The goal here is to add flexibility and ways people
can choose to work with the configuration, not to replace what's being
done now outright with an approach everyone must adopt.

--
* Greg Smith gsmith(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD

On Mon, Oct 26, 2009 at 6:55 PM, Greg Smith <gsmith(at)gregsmith(dot)com> wrote:
> If the default glob pattern is *.conf, won't all those already be screened
> out?  I can see your point that letting it be adustable will inevitably
> result in some fool one day writing a bad matching pattern that does grab
> backup/lock files.  But is that concern so important that we should limit
> what people who know what they're doing are allowed to do?

Well the file name is a kind of API so if it's not fixed it's less
useful. Modules won't know how to name the files they drop in so
they'll be read. Autotuning packages won't know what characters are
allowed in their filename or how to disable their file or backup files
if they want. Etc.

There's not really any flexibility to be gained in using a different
pattern anyways since the user and other programmers are free to
choose filenames which match the pattern.

This isn't novel territory. Other software like Apache have had config
directories for aeons. And Debian makes extensive use of them for
packages which have other packages providing behaviour changing
addons. I believe the conventions I suggested are the common
conventions used by most -- afaik all -- of these other projects.

--
greg

Greg Smith <gsmith(at)gregsmith(dot)com> writes:
> On Mon, 26 Oct 2009, Greg Stark wrote:
>> When scanning postgresql.conf.d we should follow the Apache/Debian
>> standard of scanning only files which match a single simple hard-coded
>> template.

> If the default glob pattern is *.conf, won't all those already be screened
> out? I can see your point that letting it be adustable will inevitably
> result in some fool one day writing a bad matching pattern that does grab
> backup/lock files. But is that concern so important that we should limit
> what people who know what they're doing are allowed to do?

I'm with Greg Stark on this: let's just specify a directory name and
hard-wire the filename pattern as *.conf. The use-case for anything
else is at best tissue-thin and at worst counterproductive. More,
AFAICS it is going to take us a substantial amount of code to do
something more general. (We already touched on the question of whether
glob(3) is portable, and even if it is, we have to consider the code
that will need to be added to ensure it's cleaned up on error.
A ReadDir loop around a strncmp call is no problem from that point of
view, because we already have that infrastructure; but ensuring
globfree() happens is another question.)

> I object to the idea of dictating in this area because it just
> encourages argument.

I quite agree with that as a general principle, but I don't think that
dictating the filename pattern is really limiting people in any
discernible fashion.

regards, tom lane

It sounds like there's a consensus brewing here on what should get done
with this particular patch now. Let me try to summarize:

-The new feature should be activated by allowing you to specify a
directory to include in the postgresql.conf like this:

includedir 'conf'

With the same basic semantics for how that directory name is interpreted
as the existing "include" directive. Tom has some concerns on how this
will be implemented, with "glob" portability to Windows and error cleanup
as two of the issues to consider.

-Within that directory, only file names of the form "*.conf" will be
processed. More flexibility is hard to implement and of questionable
value.

-The order they are processed in will be alphabetical. This allows (but
doesn't explictly require) using the common convention of names like
"99name" to get a really obvious ordering.

-The default postgresql.conf should be updated to end with the sample
includedir statement shown above. This will make anything that goes into
there be processed after the main file, and therefore override anything in
it.

-An intended purpose here is making tools easier to construct. It's
impractical to expect every tool that touches files in the config
directory to do an exhaustive sweep to find every other place there might
be a conflict and comment them all out. The fact that pg_settings shows
users the exact file and line they setting that is the active one is a
good enough tool to allow DBAs to work through most of the problem cases.

And as far as how it impacts planning:

-A future patch to initdb could move the changes it makes from the primary
file to one in the config directory. It might make sense to use a name
like 00initdb.conf to encourage a known good naming practice for files in
the config directory; that doesn't need to get nailed down now though.

-This patch makes it easier to envision implementing a smaller default
postgresql.conf, but it doesn't require such a change to be useful.

-SET PERSISTENT is still a bit away. This patch assists in providing a
cleaner preferred way to implement that, and certainly doesn't make it
harder to build. The issue of how to handle backing out changes that
result in a non-functional server configuration is still there. And
there's some support for the idea that the SQL interface should do more
sanity checks to make sure its setting changes aren't being overridden by
config files parsed later than we might expect from external tuning tools.

Magnus, was there anything else you wanted feedback on here?

--
* Greg Smith gsmith(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD

On Mon, 2009-10-26 at 14:13 -0700, Greg Stark wrote:
> On Mon, Oct 26, 2009 at 1:40 PM, Josh Berkus <josh(at)agliodbs(dot)com> wrote:
> >
> > Different issue, really, which is that some people (including me) would
> > like to break up PostgreSQL configuration into 7 or 8 files based on
> > functional area (e.g. memory.conf, logging.conf, custom_options.conf
> > ...). I do this with my Apache configs, and find it vastly more
> > manageable than one big file, especially under SCM. If I write a
> > config management tool, my tool will also do this.
>
> This actually seems like a bad idea to me. It's fine for something
> like apache virtual hosts where there's no ambiguity and in any case
> it's you organizing it and you reading it back out. But for a tool to
> do this is only going to lead to confusion when my thinking of where
> to find the variables differs from yours.

Right, but you'll notice that Josh already got his way into how the
current postgresql.conf is laid out and how the documentation is
structured. I can't find anything in the documentation anymore. Just
as a side note ... when we start giving people new ways to access the
configuration settings, they might also like a documentation layout that
matches their thinking.

Hi,

Greg Smith <gsmith(at)gregsmith(dot)com> writes:
> It sounds like there's a consensus brewing here on what should get done with
> this particular patch now. Let me try to summarize:
>
> -The new feature should be activated by allowing you to specify a directory
> to include in the postgresql.conf like this:
>
> includedir 'conf'

I parse the current status as always reading files in the
postgresql.conf.d directory located in the same place as the current
postgresql.conf file.

Tom had a reserve about allowing the user the control the overloading
behavior, but it appears that what we're trying to provide is a way for
tools not to fight against DBA but help him/her. So Greg Stark's idea do
sounds better: .d/ files are read first in alphabetical order,
then postgresql.conf is read. If the DBA want to manually edit the
configuration and be sure his edit will have effect, he just edits
postgresql.conf. No wondering.

> -Within that directory, only file names of the form "*.conf" will be
> processed. More flexibility is hard to implement and of questionable
> value.

The regexp is still to be agreed upon, [0-9a-zA-Z-_.]+.conf or sth.

> -The order they are processed in will be alphabetical. This allows (but
> doesn't explictly require) using the common convention of names like
> "99name" to get a really obvious ordering.

Yes.

> -The default postgresql.conf should be updated to end with the sample
> includedir statement shown above. This will make anything that goes into
> there be processed after the main file, and therefore override anything in
> it.

No, hardwired knowledge makes life easier without trading
capabilities. Loading is deterministic:
1. files in postgresql.conf.d in alphabetical order
2. postgresql.conf

This way the directory is for tools or common setup and the file for
local editing by the admin.

> -An intended purpose here is making tools easier to construct. It's
> impractical to expect every tool that touches files in the config directory
> to do an exhaustive sweep to find every other place there might be a
> conflict and comment them all out. The fact that pg_settings shows users
> the exact file and line they setting that is the active one is a good enough
> tool to allow DBAs to work through most of the problem cases.

If we want to insist on having both user comments and settings in the
files in postgresql.conf.d, I still think the best is to have there GUC
named file. First line contains current value. Rest of the file is
comments. Now you can even have SET PERSISTENT ... WITH COMMENT ... and
COMMENT ON GUC ...;

Then the pg_settings view could also embed the comments.

> And as far as how it impacts planning:
>
> -A future patch to initdb could move the changes it makes from the primary
> file to one in the config directory. It might make sense to use a name like
> 00initdb.conf to encourage a known good naming practice for files in the
> config directory; that doesn't need to get nailed down now though.

00-initdb.conf if you want some bikesheding to happen :)

> -This patch makes it easier to envision implementing a smaller default
> postgresql.conf, but it doesn't require such a change to be useful.

The postgresql.conf file could remain the same or not, and still is
intended for manual editing only.

> -SET PERSISTENT is still a bit away. This patch assists in providing a
> cleaner preferred way to implement that, and certainly doesn't make it
> harder to build.

If we stick to « dba is not supposed to manually edit any file in the
directory or things will get broken », then have either a
99-persistent.conf file or the one-file-per-GUC approach. The former
sounds easy to implement if we drop comments out of tool scope, the
latter is more flexible but looks ugly to most... (but you're forbidden
to have a look there).

> The issue of how to handle backing out changes that result
> in a non-functional server configuration is still there. And there's some
> support for the idea that the SQL interface should do more sanity checks to
> make sure its setting changes aren't being overridden by config files parsed
> later than we might expect from external tuning tools.

In the one-file-per-GUC / do-not-edit-any-postgresql.conf.d-file idea,
it's possible to have a convention for tools to manage history of settings.

Regards,
--
dim

On Tue, 2009-10-27 at 00:38 -0400, Greg Smith wrote:
> It sounds like there's a consensus brewing here on what should get done
> with this particular patch now. Let me try to summarize:

+1

--
Simon Riggs www.2ndQuadrant.com

Greg Smith <gsmith(at)gregsmith(dot)com> wrote:
> On Mon, 26 Oct 2009, Kevin Grittner wrote:
>
>> for our 72 production servers for county Circuit Court systems, we
>> copy an identical postgresql.conf file to each county, with the
>> last line being an include to an overrides conf file in /etc/.
>> For most counties that file is empty.

> That's exactly a use case the "parsing config files in a directory"
> feature aims to make easier to manage. You can just mix and match
> files

Mixing and matching files in a subdirectory would not make the control
and auditing of a small number of cluster-specific overrides to a
single standard configuration file easier.

I wasn't arguing against adding the feature, since it appears to be
useful for some environments; I was responding to Robert's musing
about it helping his single-cluster environment, but not knowing about
the impact on those with larger numbers of clusters. I have 200
clusters. I understand the proposal. I see no benefit to me.

-Kevin, the troglodyte ;-)

On Tue, 27 Oct 2009, Dimitri Fontaine wrote:

> I parse the current status as always reading files in the
> postgresql.conf.d directory located in the same place as the current
> postgresql.conf file.

Way upthread I pointed out that what some packagers have really wanted for
a while now is to put the local postgresql.conf changes into /etc rather
than have them live where the database does. Allowing the directory to be
customized makes that possible. The idea is to improve flexiblity and
options for DBAs and packagers as long as it's not difficult to implement
the idea, and allowing for a relocatable config directory isn't that hard.

> Tom had a reserve about allowing the user the control the overloading
> behavior, but it appears that what we're trying to provide is a way for
> tools not to fight against DBA but help him/her. So Greg Stark's idea do
> sounds better: .d/ files are read first in alphabetical order,
> then postgresql.conf is read. If the DBA want to manually edit the
> configuration and be sure his edit will have effect, he just edits
> postgresql.conf. No wondering.

We're trying to make allowances and a smooth upgrade path for old-school
users who don't want to use this approach. At the same time, let's be
clear: people who do that are going to find themselves increasingly
cut-off from recommended pracice moving forward. I want to make it
possible for them to continue operating as they have been, while making it
obvious that approach is on its way out.

If you want a future where it's easier for tools to operate, the config
directory goes last and overrides anything put in the primary
postgresql.conf in the default config. Having it inserted as an explicit
includedir line lets the DBA move it to the front themselves if they want
to. One thing we cannot do is make the includedir line implicit. It must
be the case that someone who opens a new postgresql.conf file and browses
it sees exactly what's being done, so they can disable it or move the
order it happens in around.

> The regexp is still to be agreed upon, [0-9a-zA-Z-_.]+.conf or sth.

This is being left to the author of the code to decide. There's reason to
believe that *.conf is going to be hard enough to implement, and that's
acceptable. If it turns out that it's easier than expected to make a full
regex syntax possible here, maybe this should get revisited on next
review.

> Then the pg_settings view could also embed the comments.

That whole bit you outlined is an interesting idea, but it doesn't impact
this patch so I'd rather not see it drag discussion out further right now.

> 00-initdb.conf if you want some bikesheding to happen

That's a future patch anyway, we can bikeshed more after it's been
submitted. One file per GUC is certainly never going to fly though, it's
been hard enough getting people to accept going from one file to more than
one.

--
* Greg Smith gsmith(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD

On Tue, 27 Oct 2009, Kevin Grittner wrote:

> I have 200 clusters. I understand the proposal. I see no benefit to
> me.
>
> -Kevin, the troglodyte ;-)

It looks like we'll have to settle this the only way your kind understands
then: a battle to the death using clubs. See you at the next conference!

--
* Greg Smith gsmith(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD

Peter,

> Right, but you'll notice that Josh already got his way into how the
> current postgresql.conf is laid out and how the documentation is
> structured. I can't find anything in the documentation anymore. Just
> as a side note ... when we start giving people new ways to access the
> configuration settings, they might also like a documentation layout that
> matches their thinking.

Yeah, and I'd like to reorganize it again, since "Client connection
defaults" has become the trash-heap of GUCs.

How, exactly, would you "match their thinking?" Whose thinking are you
matching exactly?

The categorization of the GUCs matched *my* thinking as of 8.0. It's
kind of out of date now, but a *lot* of people found it helpful,
especially compared to "historical ordering" which was what we had
before. I've continued to categorize GUCs by functional area in my
tutorials, and literally hundreds of people have found it helpful.

I agree that the Docs need an alpha index of settings as well as the
current categorical organization, but as previously discussed as long as
there are no editors I can use which like our project dialect of SGML,
constructing such an index is going to be up to you.

Or you could order them alphabetically and provide a categorical index
-- that might be better, actually, because it would make it easier to
re-categorize or multiple-categorize. As long as people can look them
up both ways, it doesn't really matter. You're the Doc master, go for it.

I'll continue to release *my* documentation on the GUCs in database
format, which is really the only thing which allows as much flexibility
as needed.

--Josh Berkus

Greg Smith <gsmith(at)gregsmith(dot)com> writes:
> ... One file per GUC is certainly never going to fly though, it's
> been hard enough getting people to accept going from one file to more than
> one.

One thing that concerns me a bit about the lack of consensus on that
is what will happen if different config-adjustment tools adopt different
philosophies. If Dimitri writes a tool that drops settings into per-GUC
files, and you write one that puts them all in persistent.conf, and
somebody tries to use both those tools, no good will come of it.

If we forgot about the config-dir idea and just had one file that was
meant to be hacked by automated tools, the problem would go away.
However I suspect that that proposal won't fly, so we ought to think
about providing some guidance to tools writers about what to do.
Is there any consensus on how multiple config files actually get used
over in the Apache/etc world?

regards, tom lane

On Tue, Oct 27, 2009 at 1:21 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Greg Smith <gsmith(at)gregsmith(dot)com> writes:
>> ... One file per GUC is certainly never going to fly though, it's
>> been hard enough getting people to accept going from one file to more than
>> one.
>
> One thing that concerns me a bit about the lack of consensus on that
> is what will happen if different config-adjustment tools adopt different
> philosophies.  If Dimitri writes a tool that drops settings into per-GUC
> files, and you write one that puts them all in persistent.conf, and
> somebody tries to use both those tools, no good will come of it.
>
> If we forgot about the config-dir idea and just had one file that was
> meant to be hacked by automated tools, the problem would go away.
> However I suspect that that proposal won't fly, so we ought to think
> about providing some guidance to tools writers about what to do.
> Is there any consensus on how multiple config files actually get used
> over in the Apache/etc world?

IME, the use case for multiple Apache configuration files is that
there are bits of configuration that support particular modules which
packagers want installed only in conjunction with the corresponding
modules - it has nothing to do with being able to automate config-file
updates, or at least I am not aware that it does.

I think that might be somewhat less of an issue for us, but I'm not
sure. I think we have fewer settings that are absolutely required to
get the system up than Apache. An unscientific survey of one server I
use shows 16 lines of uncommented configuration in postgresql.conf,
vs. 224 in httpd.conf and 95 more in httpd.conf.d/*.conf

...Robert

On Tue, 2009-10-27 at 13:21 -0400, Tom Lane wrote:
> Greg Smith <gsmith(at)gregsmith(dot)com> writes:
> > ... One file per GUC is certainly never going to fly though, it's
> > been hard enough getting people to accept going from one file to more than
> > one.
>
> One thing that concerns me a bit about the lack of consensus on that
> is what will happen if different config-adjustment tools adopt different
> philosophies. If Dimitri writes a tool that drops settings into per-GUC
> files, and you write one that puts them all in persistent.conf, and
> somebody tries to use both those tools, no good will come of it.
>
> If we forgot about the config-dir idea and just had one file that was
> meant to be hacked by automated tools, the problem would go away.
> However I suspect that that proposal won't fly, so we ought to think
> about providing some guidance to tools writers about what to do.
> Is there any consensus on how multiple config files actually get used
> over in the Apache/etc world?

Apache has an include functionality that supports wildcards etc... so I
can do:

include "conf/*.conf"

And it just parses them.

Joshua D. Drake

>
> regards, tom lane
>

--

On Tue, Oct 27, 2009 at 11:06 AM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> IME, the use case for multiple Apache configuration files is that
> there are bits of configuration that support particular modules which
> packagers want installed only in conjunction with the corresponding
> modules - it has nothing to do with being able to automate config-file
> updates, or at least I am not aware that it does.

That sounds like automated config file updates to me. Individual
modules are being installed and uninstalled and automatically updating
the configuration to handle the modules.

It's also not just modules, it's things like virtual sites. So for
example in Debian if you install a package which includes a web
interface it installs a configuration file for that web interface
under the appropriate directory.

I don't see the problem Tom describes. Clearly there's a conflict and
the settings from one of the files will have higher priority than the
other, but at least the two sets of settings will be kept separate.
Neither module will have to deal with rereading its output to see if
it has been mysteriously changed by another program. A good tool might
still want to check the settings in the running database to see if its
file is the source of the current value to give the user feedback.

If they all had to edit the same file then they have to deal with
writing out values and also reading them back. Everyone would need a
config file parser and have to make deductions about what other tools
were trying to do and how to interact with them.

I didn't realize Apache supported wildcards, and looking at Debian's
run-parts it looks like it's more liberal than I realized (though
still more conservative than the *.conf people keep insisting on). I
still think a simple hard coded rule is more useful and than allowing
sysadmins to specify any regexp or glob and then having modules or
tools not know what's allowed or not.

--
greg

On Tue, 2009-10-27 at 00:38 -0400, Greg Smith wrote:
> new feature

One additional point that would be useful is a way to match up the usage
of custom_variable_classes with this new style of .conf file processing.

At the moment if you wish to add a custom variable class everybody needs
to edit the *same* parameter. Finding which one to edit could be a
little difficult with a whole directory to search in.

I propose a new form of processing for that variable: each new parameter
instance is added to last one, rather than replacing it.
e.g.
custom_variable_class = 'x'
custom_variable_class = 'y'
custom_variable_class = 'z'
is equivalent to
custom_variable_classes = 'x,y,z'

That allows NewFeatureX to drop in a file called "newfeaturex.conf",
which looks like this

custom_variable_class = 'newfeaturex'
newfeaturex.param1 = x
newfeaturex.param2 = y
newfeaturex.param3 = z

This requires no editing of any other files, just a straight drop in.
That will make it much easier to produce real installers/deinstallers
for add-in modules.

--
Simon Riggs www.2ndQuadrant.com

Greg Stark <gsstark(at)mit(dot)edu> writes:
> I still think a simple hard coded rule is more useful and than allowing
> sysadmins to specify any regexp or glob and then having modules or
> tools not know what's allowed or not.

Yeah. Considering that the entire argument for this feature is to
simplify matters for automated config tools, I can't understand why
we'd be adding details that complicate life for them without buying
any significant benefit.

regards, tom lane

On Tue, 27 Oct 2009, Greg Stark wrote:

> If they all had to edit the same file then they have to deal with
> writing out values and also reading them back. Everyone would need a
> config file parser and have to make deductions about what other tools
> were trying to do and how to interact with them.

Exactly, that's the situation we're trying to escape from now in a
nutshell.

To answer Tom's question about providing better guidelines for tool
authors, I was hoping to provide the first such tool and submit a patch
for refactoring initdb using the same approach before 8.5 is done. I'd
rather see that nailed down with a concrete proof of concept attached that
implements a candidate approach by example rather than to just talk about
it in general. I don't think that needs to hold up work on this patch
though, particularly given that I'm dependent on this one being committed
for my plan to work.

--
* Greg Smith gsmith(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD

Hi,

Phone quoting again...

--
dim

Le 27 oct. 2009 à 18:06, Greg Smith <gsmith(at)gregsmith(dot)com> a écrit :

> On Tue, 27 Oct 2009, Dimitri Fontaine wrote:
>
>> I parse the current status as always reading files in the
>> postgresql.conf.d directory located in the same place as the current
>> postgresql.conf file.
>
> Way upthread I pointed out that what some packagers have really
> wanted for a while now is to put the local postgresql.conf changes
> into /etc rather than have them live where the database does.
> Allowing the directory to be customized makes that possible. The
> idea is to improve flexiblity and options for DBAs and packagers as
> long as it's not difficult to implement the idea, and allowing for a
> relocatable config directory isn't that hard.

Well choising where to store postgresql.conf is already possible and
what debian is doing. My proposal is to build on this: add .d and you
find the directory.

>
>> Tom had a reserve about allowing the user the control the overloading
>> behavior, but it appears that what we're trying to provide is a way
>> for
>> tools not to fight against DBA but help him/her. So Greg Stark's
>> idea do
>> sounds better: .d/ files are read first in alphabetical order,
>> then postgresql.conf is read. If the DBA want to manually edit the
>> configuration and be sure his edit will have effect, he just edits
>> postgresql.conf. No wondering.
>
> We're trying to make allowances and a smooth upgrade path for old-
> school users who don't want to use this approach. At the same time,
> let's be clear: people who do that are going to find themselves
> increasingly cut-off from recommended pracice moving forward. I
> want to make it possible for them to continue operating as they have
> been, while making it obvious that approach is on its way out.

Historic file loaded last fullfills the need in my mind.

>
> If you want a future where it's easier for tools to operate, the
> config directory goes last and overrides anything put in the primary
> postgresql.conf in the default config. Having it inserted as an
> explicit includedir line lets the DBA move it to the front
> themselves if they want to. One thing we cannot do is make the
> includedir line implicit. It must be the case that someone who
> opens a new postgresql.conf file and browses it sees exactly what's
> being done, so they can disable it or move the order it happens in
> around.
>

include directive or hardwired documented rule: in either case you
know what happens when. In one case you can choose, at the expense of
having to discover local setup rather than knowing your docs.

What I have in mind is for SET PERSISTENT to warn users when settings
source is postgresql.conf.

>> The regexp is still to be agreed upon, [0-9a-zA-Z-_.]+.conf or sth.
>
> This is being left to the author of the code to decide. There's
> reason to believe that *.conf is going to be hard enough to
> implement, and that's acceptable. If it turns out that it's easier
> than expected to make a full regex syntax possible here, maybe this
> should get revisited on next review.

Yes. But full regexp makes it harder for tools than hardwired rules.

>
>> Then the pg_settings view could also embed the comments.
>
> That whole bit you outlined is an interesting idea, but it doesn't
> impact this patch so I'd rather not see it drag discussion out
> further right now.

Ok if the goal is include dir.

If tools and modules are concerned, it Will be easier to SET
persistent variable classes then create files like
preprepare.at_init.conf e.g.

This problem should be seen as an API problem for only automated
tools, I think, like Greg Stark said.

>
>> 00-initdb.conf if you want some bikesheding to happen
>
> That's a future patch anyway, we can bikeshed more after it's been
> submitted. One file per GUC is certainly never going to fly though,
> it's been hard enough getting people to accept going from one file
> to more than one.
>
> --
> * Greg Smith gsmith(at)gregsmith(dot)com http://www.gregsmith.com
> Baltimore, MD

--
dim

Le 27 oct. 2009 à 18:21, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> a écrit :

> Greg Smith <gsmith(at)gregsmith(dot)com> writes:
>> ... One file per GUC is certainly never going to fly though, it's
>> been hard enough getting people to accept going from one file to
>> more than
>> one.
>
> One thing that concerns me a bit about the lack of consensus on that
> is what will happen if different config-adjustment tools adopt
> different
> philosophies. If Dimitri writes a tool that drops settings into per-
> GUC
> files, and you write one that puts them all in persistent.conf, and
> somebody tries to use both those tools, no good will come of it.

Right, that's why Greg Stark convinced me that we're defining an API
here, not just a facility.

>
> If we forgot about the config-dir idea and just had one file that was
> meant to be hacked by automated tools, the problem would go away.
> However I suspect that that proposal won't fly, so we ought to think
> about providing some guidance to tools writers about what to do.
> Is there any consensus on how multiple config files actually get used
> over in the Apache/etc world?

What they have is different contexts where to apply the same settings.
You basically write one file per context.

As you are saying that does not translate well to our case where we
want one context and N tools.

I don't see that opening the possibility to edit the same GUC in more
than exactly 2 places is giving us anything. First the tool location,
then the local DBA hand maintained file. Which still could include
other files, as Kevin does, for park managment purpose.

The DBA friendly option is the existing include directive. includedir
buys nothing in my mind. We want a tool API and the first tool to
expose it in the form of SET PERSISTENT. I think :)
>

On Tue, Oct 27, 2009 at 2:59 PM, Greg Stark <gsstark(at)mit(dot)edu> wrote:
> On Tue, Oct 27, 2009 at 11:06 AM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>> IME, the use case for multiple Apache configuration files is that
>> there are bits of configuration that support particular modules which
>> packagers want installed only in conjunction with the corresponding
>> modules - it has nothing to do with being able to automate config-file
>> updates, or at least I am not aware that it does.
>
> That sounds like automated config file updates to me. Individual
> modules are being installed and uninstalled and automatically updating
> the configuration to handle the modules.

Well, OK, fair enough. I guess my point is that there are two things
that you might want:

- multiple config files separated by domain (e.g. this is the config
file for autoexplain, and this one is for vacuum)
- multiple config files separated by how they are updated (e.g. this
config file is only for people with text editors, and this one is for
people using SET PERSISTENT)

The Apache model is definitely the first of these, AFAICS. The
proposals on this thread mostly seem to be an amalgam of both, which
doesn't strike me as a terribly good idea, but evidently I'm in the
minority.

...Robert

Robert,

> The Apache model is definitely the first of these, AFAICS. The
> proposals on this thread mostly seem to be an amalgam of both, which
> doesn't strike me as a terribly good idea, but evidently I'm in the
> minority.

Well, an individual DBA would not want to do it both ways. But we
should *allow* both ways rather than trying to mandate how the files get
created or what their names are.

--Josh Berkus

On Tue, Oct 27, 2009 at 9:05 PM, Josh Berkus <josh(at)agliodbs(dot)com> wrote:
>> The Apache model is definitely the first of these, AFAICS.  The
>> proposals on this thread mostly seem to be an amalgam of both, which
>> doesn't strike me as a terribly good idea, but evidently I'm in the
>> minority.
>
> Well, an individual DBA would not want to do it both ways.  But we
> should *allow* both ways rather than trying to mandate how the files get
> created or what their names are.

I guess all I'm saying is that if we took the approach of making SET
PERSISTENT rewrite postgresql.conf, we actually could let people do it
either way they pleased without the complexity of having multiple
files.

The only reason we can't do that today is because postgresql.conf
contains unparseable comments. The only way to fix that problem
completely is, as Tom says, to remove the ability to have comments.
But that seems like overkill. If we simply removed most of the
comments that are there by default, then we could say: "You can edit
this file with a text editor. Or you can edit it using SET
PERSISTENT. Or you can do both. But if you do both, your comments
may end up getting moved relative to your settings. So if you care
about that, then don't use SET PERSISTENT. In fact, if you want,
there's a GUC called enable_set_persistent that you can set to false."

That seems like a win for everyone. People who want to use a text
editor can do so. People who want to use SET PERSISTENT can do so.
People who want to do both can do so, too, and without the confusion
of having two different places for settings one of which will override
the other. I think the only people who will be unhappy are (1) people
who like the current really long postgresql.conf [but the previous
discussion of this topic suggested there weren't too many of those]
and (2) people who want to edit postgresql.conf by hand AND want to
edit it with SET PERSISTENT AND can't stand having their comments
shuffled around relative to their settings [but these people will
never be happy no matter what we do].

But evidently this is not such a good idea as I think it is, or else
I've been explaining it really, really badly.

...Robert

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> I guess all I'm saying is that if we took the approach of making SET
> PERSISTENT rewrite postgresql.conf, we actually could let people do it
> either way they pleased without the complexity of having multiple
> files.

You keep saying that, but what you don't seem to get is that that
amounts to telling the people who want to go slow that they should
go jump in a lake. The moment they even experiment with SET PERSISTENT,
everything they've ever done with postgresql.conf goes up in smoke.
This is not going to be acceptable.

Furthermore, that approach is not especially simple from the tools'
standpoint either, because then the minimum bar for doing anything at
all is the ability to parse postgresql.conf, remove conflicting old
settings, and add your own. Even without any comment support, that is
an order of magnitude harder than just dropping a prebuilt file into a
directory, which is feasible for at least some use-cases with the
directory approach.

regards, tom lane

On Tue, Oct 27, 2009 at 10:53 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> I guess all I'm saying is that if we took the approach of making SET
>> PERSISTENT rewrite postgresql.conf, we actually could let people do it
>> either way they pleased without the complexity of having multiple
>> files.
>
> You keep saying that, but what you don't seem to get is that that
> amounts to telling the people who want to go slow that they should
> go jump in a lake.  The moment they even experiment with SET PERSISTENT,
> everything they've ever done with postgresql.conf goes up in smoke.
> This is not going to be acceptable.

I guess I didn't consider the possibility that someone might reuse an
8.4 postgresql.conf on an 8.5 server. That could be awkward. But
even if they do, it's not *nearly* as bad as "everything they've ever
done with postgresql.conf goes up in smoke". I was thinking that the
algorithm would be something like: "Read the old postgresql.conf and
write it back out to a new file line by line. If, in the process of
doing this, you find a setting for the variable you're trying to
change, then write out the new line in place of the original line. If
you subsequently find anymore output lines that set that same
variable, then skip writing them to the output file altogether. If
you get to the end of the file without finding the setting you're
trying to change, then append a new line setting that variable to the
specified value."

If someone uses this on a setting that already exists - uncommented -
in their legacy postgresql.conf file, then at most they will lose any
comment on the same line as the setting they changed. The new setting
will end up on the same line in the new file as the old one, with all
the same comments around it.

If someone uses this on a setting that does not exist in their legacy
postgresql.conf file, the new setting will just be appended to the
end, while the rest of the file will remain unchanged. At worst,
they'll be surprised that the setting ended up not where they were
expecting it to be - but if they were expecting SET PERSISTENT to
notice where the commented-out version of the setting was and put the
new value there, they're going to be disappointed under any
implementation we're likely to settle on (having the new setting in a
different file altogether doesn't seem better, at least not to me).

> Furthermore, that approach is not especially simple from the tools'
> standpoint either, because then the minimum bar for doing anything at
> all is the ability to parse postgresql.conf, remove conflicting old
> settings, and add your own.  Even without any comment support, that is
> an order of magnitude harder than just dropping a prebuilt file into a
> directory, which is feasible for at least some use-cases with the
> directory approach.

True, but actually having a good SET PERSISTENT command would solve
most of this problem, because the tools could just use that. The
ability to just drop in a file is superficially attractive, but I
think it's a red herring. Whatever settings the tool sets will still
potentially be set elsewhere in other files (maybe even files created
by other tools). So tool A comes and drops in a file that sets
work_mem, checkpoint_segments, and wal_buffers, and then tool B comes
along and sets max_connections, a couple of autovacuum settings, and
checkpoint_segments again. Then tool C comes along and does something
else again, and now the DBA wants to change a setting with SET
PERSISTENT, while the other DBA edits postgresql.conf using $EDITOR.

We may have tools to tell you which file is providing the value for
any particular parameter, but I don't think they're going to make this
kind of situation un-confusing. Normally, you're going to want the
value for any particular parameter to be the one the value to which it
was most recently changed, and that just won't be the case with this
setup, at least not without some pretty substantial gymnastics.

...Robert

On 10/27/09 8:24 PM, Robert Haas wrote:
> read the old postgresql.conf and
> write it back out to a new file line by line. If, in the process of
> doing this, you find a setting for the variable you're trying to
> change, then write out the new line in place of the original line.

You've hit the problem on the head right there. The requirement to do
something like that is *exactly* the problem which makes writing
config-management tools hard/impossible.

If you require that a tool (or SET PERISTENT) parse through a file in
order to change one setting, then you've just doubled or tripled the
code size of the tool, as well as added a host of failure conditions
which wouldn't have existed otherwise.

You're hearing from the people who are working on tools: requiring that
any tool parse a hand-written config file is a non-starter.

--Josh Berkus

On Tue, 27 Oct 2009, Robert Haas wrote:

> I guess I didn't consider the possibility that someone might reuse an
> 8.4 postgresql.conf on an 8.5 server. That could be awkward.

Happens all the time, and it ends up causing problems like people still
having settings for GUCs that doesn't even exist anymore. You know how we
could make this problem less likely to bite people? By putting everything
the user wants to customize that isn't done by initdb into another file.
Then they can just move that file into the new version. That's the
direction we're trying to move here, except much slower than you're
suggesting because we've already through about some of these gotchas.
Obviously you could do the same thing by completely gutting the whole
postgresql.conf, but I was hoping for a step in the right direction that
doesn't require something that drastic yet.

The length of this thread has already proven why it's not worth even
trying to completely trim the file down. Had you never brought that up
this discussion would be done already. If you have a strong feeling about
this, write a patch and submit it; I'm not going to talk about this
anymore.

> I was thinking that the algorithm would be something like: "Read the old
> postgresql.conf and write it back out to a new file line by line....

This sounds familiar...oh, that's right, this is almost the same algorithm
pgtune uses. And it sucks, and it's a pain to covert the tool into C
because of it, and the fact that you have to write this sort of boring
code before you can do a single line of productive work is one reason why
we don't have more tools available; way too much painful grunt work to
write.

> True, but actually having a good SET PERSISTENT command would solve
> most of this problem, because the tools could just use that.

The system running the tool and the one where the changes are being made
are not the same. The database isn't necessarily even up when the tool is
being run yet. The main overlap here is that one of the output formats
available to future tools could be a series of SET PERSISTENT commands one
could then run elsewhere, which is already on my pgtune roadmap when it's
possible to implement.

You're doing a good job of reminding me why I didn't have a good vision of
where this all needed to go until after I wrote a working tuning tool, to
get a feel for the painful parts. I wish I could share all of the
postgresql.conf files I've seen so you could better appreciate how people
torture the poor file in the field.

--
* Greg Smith gsmith(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD

On Tue, Oct 27, 2009 at 8:40 PM, Josh Berkus <josh(at)agliodbs(dot)com> wrote:
> You're hearing from the people who are working on tools: requiring that
> any tool parse a hand-written config file is a non-starter.

It can be done, pgadmin actually does it currently. But I totally
agree it's a bad idea.

But the difficulty of parsing the handwritten stuff is not the only
reason it's a bad idea. Any time you have multiple pieces of software,
to say nothing of humans, editing the same file you're going to have
headaches. They need to agree on everything and be able to handle
anything any other program generates. Such a file would be a kind of
API itself.

It's much simpler and more reliable to have each program generate a
separate file. Each program can just do its calculations and dump out
a file with those variables. It doesn't have to worry about the
ordering, indentation, or precise formatting. It can put whatever
comments it wants to explain how they're calculated or to warn that
it's an auto-generated file and any changes will be replaced the next
time it's generated. It doesn't have to worry about anything else
parsing or making sense of the file except the database server itself.

--
greg

Greg Stark <gsstark(at)mit(dot)edu> writes:
> On Tue, Oct 27, 2009 at 8:40 PM, Josh Berkus <josh(at)agliodbs(dot)com> wrote:
>> You're hearing from the people who are working on tools: requiring that
>> any tool parse a hand-written config file is a non-starter.
>
> It can be done, pgadmin actually does it currently. But I totally
> agree it's a bad idea.
>
> But the difficulty of parsing the handwritten stuff is not the only
> reason it's a bad idea. Any time you have multiple pieces of software,
> to say nothing of humans, editing the same file you're going to have
> headaches. They need to agree on everything and be able to handle
> anything any other program generates. Such a file would be a kind of
> API itself.

That's why I'm proposing the following API at file level:
- 1 file per GUC
- file name is {class.}guc_name.conf
- first line only contains value of setting
- rest of the file contains comments

Now any tool can see current value for itself, and change it, keeping
the old one as comment is easy too:
$ myguc=`cat postgresql.conf.d/my_guc.conf`
$ (echo newvalue; echo $myguc) > postgresql.conf.d/my_guc.conf

Furthermore, extensions are required to use a custom class, so they will
need to edit custom_variable_classes then their own files. Any tool
could support editing those files too, it's rather easy until you want
to provide specific wizard kind knowledge to the user.

A dedicated facility to add a new class to custom_variable_classes GUC
could be devised later, but doesn't feel like it's in this patch
playground.

> It's much simpler and more reliable to have each program generate a
> separate file.

On the viewpoint of the program itself only. For the DBA, that soon
becomes a nightmare because the same GUC could come from any number of
tools and the precedence rules, even explicit and as easy as
alphanumeric orderding (which locale already?), make it error prone.

I really want to insist on having only ONE location for settings from
tools (all of them) and one location for manual/local editing.

> time it's generated. It doesn't have to worry about anything else
> parsing or making sense of the file except the database server itself.

But it'll never know if the settings it just generated are superseded by
some other tool's configuration file. With my proposal the SET
PERSISTENT command can easily warn user: as soon as current source for
the GUC is NOT postgresql.conf.d you know you're not affecting anything,
it's been the DBA choice to manually set something else.

It if happens you are the DBA, you can go edit postgresql.conf to
comment out the GUC and enjoy your new tool suite.

Regards,
--
dim

On Tue, Oct 27, 2009 at 11:40 PM, Josh Berkus <josh(at)agliodbs(dot)com> wrote:
> On 10/27/09 8:24 PM, Robert Haas wrote:
>> read the old postgresql.conf and
>> write it back out to a new file line by line.  If, in the process of
>> doing this, you find a setting for the variable you're trying to
>> change, then write out the new line in place of the original line.
>
> You've hit the problem on the head right there.  The requirement to do
> something like that is *exactly* the problem which makes writing
> config-management tools hard/impossible.
>
> If you require that a tool (or SET PERISTENT) parse through a file in
> order to change one setting, then you've just doubled or tripled the
> code size of the tool, as well as added a host of failure conditions
> which wouldn't have existed otherwise.

I think you're just trading one set of failure conditions for another.
Now instead of having one unparseable configuration file you're going
to have a whole pile of them with possibly-conflicting settings.

> You're hearing from the people who are working on tools: requiring that
> any tool parse a hand-written config file is a non-starter.

Yep: and I'm baffled by that, because I understand neither why it's
hard nor what the reasonable alternatives are. The algorithm I just
proposed can be implemented by a very short Perl script. But my
bafflement doesn't (and isn't intended to) prevent others from
implementing what they like. As Tom is fond of saying (and it's 10x
more true of me), I'm not the only vote here.

...Robert

Forgive me for jumping in again on discussion of a feature I might
never use, but as an "outside observer" something doesn't make sense
to me.

Josh Berkus <josh(at)agliodbs(dot)com> wrote:

> If you require that a tool (or SET PERISTENT) parse through a file
> in order to change one setting, then you've just doubled or tripled
> the code size of the tool, as well as added a host of failure
> conditions which wouldn't have existed otherwise.

Not if there is one implementation of which is distributed with
PostgreSQL. Give it a clean API and a command-line application (for
scripting in non-C languages) and this is a non-issue. This really
seems like a red herring.

I know it would be more lines in C than a bash script; but really,
think about how little work this would be for any script which has
grep and sed available -- at least if you assume it shouldn't follow
include statements. But I think that's where the rub is -- when you
have more than one source for information, what's the precedence?
That question doesn't go away with the proposed feature. It seems
that in reading this thread I've seen a lot of conflicting notions on
how it *should* work, with a handwavy assertion that it doesn't matter
because the DBA can sort it all out. But then will the tools always
do what people expect?

It seems like there's a significant base of users who want their
database product to self-configure; and there's clearly a significant
base of professional DBAs who want to be able to hand-tune for a
variety of reasons. I assume that addressing these disparate needs is
one of the goals here? As well as an easy way to drop in
configuration for additional features? The directory seems to make
sense for the latter, but seems horrible to me for the former. It
turns the risk of a spaghetti configuration file into a sorcerer's
apprentice collection of competing, conflicting files which are a
worse mess that the spaghetti.

Perhaps there should be two separate features for the two separate use
cases?

-Kevin

Greg Smith escribió:

> >I was thinking that the algorithm would be something like: "Read
> >the old postgresql.conf and write it back out to a new file line
> >by line....
>
> This sounds familiar...oh, that's right, this is almost the same
> algorithm pgtune uses. And it sucks, and it's a pain to covert the
> tool into C because of it, and the fact that you have to write this
> sort of boring code before you can do a single line of productive
> work is one reason why we don't have more tools available; way too
> much painful grunt work to write.

Huh, isn't this code in initdb.c already? Since it's BSD-licensed (or
is it MIT?) you could just have lifted it. Surely this isn't the reason
the tool isn't written in C.

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Kevin Grittner wrote:

> But I think that's where the rub is -- when you
> have more than one source for information, what's the precedence?

This is not a problem nowadays because that info is in pg_settings.
File name and line number.

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Alvaro Herrera wrote:
> Greg Smith escribió:
>
>
>>> I was thinking that the algorithm would be something like: "Read
>>> the old postgresql.conf and write it back out to a new file line
>>> by line....
>>>
>> This sounds familiar...oh, that's right, this is almost the same
>> algorithm pgtune uses. And it sucks, and it's a pain to covert the
>> tool into C because of it, and the fact that you have to write this
>> sort of boring code before you can do a single line of productive
>> work is one reason why we don't have more tools available; way too
>> much painful grunt work to write.
>>
>
> Huh, isn't this code in initdb.c already? Since it's BSD-licensed (or
> is it MIT?) you could just have lifted it. Surely this isn't the reason
> the tool isn't written in C.
>
>

In any case, initdb has to be in C for portability reasons (I'm more
aware of this than most ;-) ), but other tools don't unless the server
has to rely on them.

cheers

andrew

Alvaro Herrera <alvherre(at)commandprompt(dot)com> wrote:
> Kevin Grittner wrote:
>
>> But I think that's where the rub is -- when you
>> have more than one source for information, what's the precedence?
>
> This is not a problem nowadays because that info is in pg_settings.
> File name and line number.

I'm talking about how the decision should be made as to which takes
precedence. It's fine to document which one *was* chosen, but that
doesn't eliminate the problem of conflicting settings making a mess.
Someone else (Robert maybe?) gave an explicit example of how three
files could have overlapping settings. Of course, *my* tool will name
its configuration file " !.conf".

-Kevin

On Wed, 28 Oct 2009, Alvaro Herrera wrote:

> Huh, isn't this code in initdb.c already?

The sketched out design I have for a contrib/pgtune in C presumes that I'd
start by refactoring the relevant bits from initdb into a library for both
programs to use. But the initdb code doesn't care about preserving
existing values when making changes to them; it just throws in its new
settings and moves along. So what's there already only handles about half
the annoying parts most people would expect a tuning tool that reads the
existing file and operates on it to do.

Also, I wouldn't be surprised to find that it chokes on some real-world
postgresql.conf files. The postgresql.conf.sample it's being fed is
fairly pristine. A tuning tool that intends to read any postgresql.conf
it's fed can't always assume it's in exactly standard form. I've recently
started collecting complicated postgresql.conf lines that crashed my
Python code as people submit bug reports with those. You might be
surprised at all of the places people put whitespace at.

--
* Greg Smith gsmith(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD

Greg Smith <gsmith(at)gregsmith(dot)com> writes:
> The sketched out design I have for a contrib/pgtune in C presumes that I'd
> start by refactoring the relevant bits from initdb into a library for both
> programs to use. But the initdb code doesn't care about preserving
> existing values when making changes to them; it just throws in its new
> settings and moves along. So what's there already only handles about half
> the annoying parts most people would expect a tuning tool that reads the
> existing file and operates on it to do.

> Also, I wouldn't be surprised to find that it chokes on some real-world
> postgresql.conf files. The postgresql.conf.sample it's being fed is
> fairly pristine.

Indeed. Why in the world are you looking at initdb? The standard
reference for postgresql.conf-reading code, by definition, is
guc-file.l. I think the odds of building something that works right,
without borrowing that same flex logic, are about nil.

regards, tom lane

On Wed, Oct 28, 2009 at 7:33 AM, Alvaro Herrera
<alvherre(at)commandprompt(dot)com> wrote:
> Greg Smith escribió:
>
>> This sounds familiar...oh, that's right, this is almost the same
>> algorithm pgtune uses.  And it sucks,

It's also a blatant violation of packaging rules for Debian if not
every distribution. If you edit the user's configuration file then
there's no way to install a modified default configuration file. You
can't tell the automatic modifications apart from the user's
modifications. So the user will get a prompt asking if he wants the
new config file or to keep his modifications which he never remembered
making.

--
greg

On Wed, Oct 28, 2009 at 2:37 AM, Dimitri Fontaine
<dfontaine(at)hi-media(dot)com> wrote:
> That's why I'm proposing the following API at file level:

That's exactly the same as putting them all in the same file, only a
different syntax. It still requires that any program understand what
every other program was trying to do.

>> It's much simpler and more reliable to have each program generate a
>> separate file.
>
> On the viewpoint of the program itself only. For the DBA, that soon
> becomes a nightmare because the same GUC could come from any number of
> tools and the precedence rules, even explicit and as easy as
> alphanumeric orderding (which locale already?), make it error prone.

But the DBA *wants* to control those precedence rules. The automatic
software certainly can't unless they know what other automatic
software exists in the world -- or will exist in the future.

> I really want to insist on having only ONE location for settings from
> tools (all of them) and one location for manual/local editing.
>
>> time it's generated. It doesn't have to worry about anything else
>> parsing or making sense of the file except the database server itself.
>
> But it'll never know if the settings it just generated are superseded by
> some other tool's configuration file.

That's precisely what makes things simpler. The less each module has
to know about each other module the simpler and more reliable it will
be. I actually would suggest that they check the current "source" by
checking with postgres, just to give a warning.

--
greg

Kevin,

> I'm talking about how the decision should be made as to which takes
> precedence. It's fine to document which one *was* chosen, but that
> doesn't eliminate the problem of conflicting settings making a mess.
> Someone else (Robert maybe?) gave an explicit example of how three
> files could have overlapping settings. Of course, *my* tool will name
> its configuration file " !.conf".

Hey, if a DBA wants to do that, then it's fine with me. They can check
pg_settings afterwards to find out which was chosen.

The precedence issues you (and Robert) are citing are no different from
what we have currently in a single file. I absolutely can't tell you
the number of hacked-up postgresql.conf files I've seen with the same
setting appearing in more than 3 places. And with the conf file being
over 1000 lines long, it's easy to miss that someone or some tool added
another instance of the variable at the bottom.

Plus we already support includes of single files. Why is an include of
a directory controversial? If someone doesn't want to use it, they
don't have to.

If someone here thinks writing a tool which reliably parses and
re-writes a hand-written PostgresQL.conf and runs on all the OSes we
support is *easy*, then please write it for me! I'll happly use such a
tool. But after wasting a couple dozen hours on the problem, I won't
write one.

Otherwise, please let us have our directory so that we can experiment
with easy-to-write-and-revise autoconfig tools.

--Josh Berkus

On Wed, 28 Oct 2009, Tom Lane wrote:

> Why in the world are you looking at initdb? The standard reference for
> postgresql.conf-reading code, by definition, is guc-file.l. I think the
> odds of building something that works right, without borrowing that same
> flex logic, are about nil.

initdb was the only sample around that actually makes changes to the
postgresql.conf. It's also a nice simple standalone program that's easy
to borrow pieces from, which guc-file.l is not. That's the reason it
looks tempting at first.

If as you say the only right way to do this is to use the flex logic, that
just reinforced how high the bar is for someone who wants to write a tool
that modifies the file. Periodically we get people who show up saying
"hey, I'd like to write a little [web|cli|gui] tool to help people update
their postgresql.conf file", and when the answer they get incudes "first
you need to implement this grammar..." that's scares off almost all of
them. It didn't work on me because I used to write compilers for fun
before flex existed. But even I just skimmed it and pragmatically wrote a
simpler postgresql.conf parser implementation that worked well enough to
get a working prototype out the door, rather than properly the whole
grammar.

--
* Greg Smith gsmith(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD

On Wed, 28 Oct 2009, Greg Stark wrote:

> It's also a blatant violation of packaging rules for Debian if not
> every distribution. If you edit the user's configuration file then
> there's no way to install a modified default configuration file. You
> can't tell the automatic modifications apart from the user's
> modifications. So the user will get a prompt asking if he wants the
> new config file or to keep his modifications which he never remembered
> making.

The postgresql.conf file being modified is generated by initdb, and it's
already being customized per install by the initdb-time rules like
detection for maximum supported shared_buffers. It isn't one of the files
installed by the package manager where the logic you're describing kicks
in. The conflict case would show up, to use a RHEL example, if I edited a
/etc/sysconfig/postgresql file and then a changed version of that file
appeared upstream. Stuff in PGDATA is all yours and not tracked as a
config file.

--
* Greg Smith gsmith(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD

Josh Berkus <josh(at)agliodbs(dot)com> wrote:

> The precedence issues you (and Robert) are citing are no different
> from what we have currently in a single file.

I think that's *why* we're mentioning it. This would seem to be the
juncture to look for ways to improve that, not just settle for "no
worse" -- but perhaps that's not possible.

> If someone here thinks writing a tool which reliably parses and
> re-writes a hand-written PostgresQL.conf and runs on all the OSes we
> support is *easy*, then please write it for me! I'll happly use
> such a tool. But after wasting a couple dozen hours on the problem,
> I won't write one.

Perhaps the ease of writing something like that with sed or perl has
caused me to underestimate the effort required in C. I am curious
whether you actually mean that, or said it for rhetorical effect.

> Otherwise, please let us have our directory so that we can
> experiment with easy-to-write-and-revise autoconfig tools.

Well, I wouldn't vote against it since it seems to do me no harm; I
was just confused at the repeated assertion that update-in-place was
such a hard problem. Some of the people saying that seem to regularly
eat problems which seem much harder than that (to me, anyway) for
lunch. That seemed to suggest there could be other reasons for
wanting the directory approach which weren't getting proper focus. If
we solve the wrong problem, the solution is likely to be suboptimal
for the real issues.

-Kevin

Greg Smith <gsmith(at)gregsmith(dot)com> writes:
> If as you say the only right way to do this is to use the flex logic, that
> just reinforced how high the bar is for someone who wants to write a tool
> that modifies the file.

Yup, exactly. Personally I think that trying to auto-modify
postgresql.conf is insane. The whole and entire reason behind this
discussion is that we want the tools modifying OTHER files, for which
we will establish different and much simpler rules for what is allowed.

regards, tom lane

On Wed, Oct 28, 2009 at 10:28 AM, Greg Smith <gsmith(at)gregsmith(dot)com> wrote:
> The postgresql.conf file being modified is generated by initdb, and it's
> already being customized per install by the initdb-time rules like detection
> for maximum supported shared_buffers. It isn't one of the files installed by
> the package manager where the logic you're describing kicks in.  The
> conflict case would show up, to use a RHEL example, if I edited a
> /etc/sysconfig/postgresql file and then a changed version of that file
> appeared upstream.  Stuff in PGDATA is all yours and not tracked as a config
> file.

Well putting configuration files in PGDATA is itself a packaging
violation. I'm talking about /etc/postgresql.conf. Yes it's possible
for packages to simply opt out of the configuration file management
which at least means they're not actively causing problems -- but it's
a cheat, it means it's giving up on providing the user with useful
upgrades of configuration files.

--
greg

Kevin,

> Perhaps the ease of writing something like that with sed or perl has
> caused me to underestimate the effort required in C. I am curious
> whether you actually mean that, or said it for rhetorical effect.

I actually mean that. It *looks* easy in perl, and in fact *is* easy
for *your* postgresql.conf which you control. But writing a parser for
every postgresql.conf which exists in the world, no matter how someone
has hacked it up creatively? No matter how they've handled upgrades?
For every version of PostgreSQL? That requires writing a full parser
with grammar and near-turing capabilities.

> Well, I wouldn't vote against it since it seems to do me no harm; I
> was just confused at the repeated assertion that update-in-place was
> such a hard problem.

It's the basic and unsolvable issue of how do you have a file which is
both perfectly human-readable-and-editable *and* perfectly
machine-readable-and-editable at the same time.

--Josh Berkus

On Wed, Oct 28, 2009 at 12:08 PM, Josh Berkus <josh(at)agliodbs(dot)com> wrote:
>> Perhaps the ease of writing something like that with sed or perl has
>> caused me to underestimate the effort required in C.  I am curious
>> whether you actually mean that, or said it for rhetorical effect.
>
> I actually mean that.  It *looks* easy in perl, and in fact *is* easy
> for *your* postgresql.conf which you control.  But writing a parser for
> every postgresql.conf which exists in the world, no matter how someone
> has hacked it up creatively?  No matter how they've handled upgrades?
> For every version of PostgreSQL?  That requires writing a full parser
> with grammar and near-turing capabilities.

I think we're getting distracted by the basic parsing questions. These
are actually solvable -- pgadmin solves them today even.

I think the bigger problem is the semantic questions. If I've lowered
random_page_cost and your tool decides it should raise
sequential_page_cost should it raise random_page_cost proportionally
from my setting or to the absolute value it calculates? When it does
will I be annoyed to see my settings overwritten? What if I set some
of the cpu_* parameters based on my random_page_cost setting and now
that you've overwritten my random_page_cost setting they're all out of
whack?

And not all programs editing these files will be equally intelligent.
Say I post a snippet of configuration and say to drop it in wholesale
into your postgresql.conf.d. Then the user runs autotune which drops a
configuration in after it which overrides those settings. Then later I
post an updated snippet and the user replaces the original snippet. If
they're in separate files and he put the snippet in before the
autotune configuration then he doesn't have to worry if the new
snippet contains things which autotune overrode. They'll still
override the new settings.

If you keep them separate then the actual settings may not be in sync
but at least I can see each group of settings and understand what they
were trying to do. The precedence remains the same. It also means the
database could give warnings if files are overriding earlier files if
we want that.

--
greg

Josh Berkus <josh(at)agliodbs(dot)com> writes:
> Kevin,
>> Perhaps the ease of writing something like that with sed or perl has
>> caused me to underestimate the effort required in C. I am curious
>> whether you actually mean that, or said it for rhetorical effect.

> I actually mean that. It *looks* easy in perl, and in fact *is* easy
> for *your* postgresql.conf which you control. But writing a parser for
> every postgresql.conf which exists in the world, no matter how someone
> has hacked it up creatively? No matter how they've handled upgrades?

The issue isn't even with writing a parser. The conf file is certainly
machine-parsable; guc-file.l is an existence proof, and the relatively
slow rate of change of that file indicates that it's been a reasonably
stable format over time. The issue is that if you want to modify the
file while preserving comments, commenting out superseded entries,
putting new entries where the user would expect to find them, etc etc,
you have a hard AI problem in front of you. This is why Robert keeps
harping on the default commentary being a problem --- if you removed all
comments (and didn't care about ordering etc), it would be easier.
But short of telling people who prefer $EDITOR to get lost, that's not
going to work.

I think the point of the discussions here is that we want to establish
an alternate config file (or set of config files) in which the
expectations are different: no promise to preserve any comments, no
intention to be human-friendly for editing, etc. In one sense this
would be the same machine-readable format, since the backend is still
going to parse it with guc-file.l; but changing the human expectations
can make the editing problem much simpler.

regards, tom lane

On Wed, 28 Oct 2009, Josh Berkus wrote:

> It's the basic and unsolvable issue of how do you have a file which is
> both perfectly human-readable-and-editable *and* perfectly
> machine-readable-and-editable at the same time.

Let's see...if I remember correctly from the last two rounds of this
discussion, this is the point where someone pops up and says that
switching to XML for the postgresql.conf will solve this problem.
Whoever does that this time goes into the ring with Kevin and I, but they
don't get a club. (All fight proceeds to benefit SPI of course).

--
* Greg Smith gsmith(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD

On Wed, Oct 28, 2009 at 3:28 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Josh Berkus <josh(at)agliodbs(dot)com> writes:
>> Kevin,
>>> Perhaps the ease of writing something like that with sed or perl has
>>> caused me to underestimate the effort required in C.  I am curious
>>> whether you actually mean that, or said it for rhetorical effect.
>
>> I actually mean that.  It *looks* easy in perl, and in fact *is* easy
>> for *your* postgresql.conf which you control.  But writing a parser for
>> every postgresql.conf which exists in the world, no matter how someone
>> has hacked it up creatively?  No matter how they've handled upgrades?
>
> The issue isn't even with writing a parser.  The conf file is certainly
> machine-parsable; guc-file.l is an existence proof, and the relatively
> slow rate of change of that file indicates that it's been a reasonably
> stable format over time.

Right.

> The issue is that if you want to modify the
> file while preserving comments, commenting out superseded entries,
> putting new entries where the user would expect to find them, etc etc,
> you have a hard AI problem in front of you.

Right. In other words, it's not possible. You can drive yourself
crazy trying to approximate it, but it is a hopeless waste of time.

> This is why Robert keeps
> harping on the default commentary being a problem --- if you removed all
> comments (and didn't care about ordering etc), it would be easier.

Yes - and we even had some consensus that this was a good idea, at one
point. Maybe not complete, precise consensus on every detail, but
certainly enough to have a conversation about it.

> But short of telling people who prefer $EDITOR to get lost, that's not
> going to work.

This is where I get off the train. Preferring $EDITOR is not the same
thing as feeling that we need 500 lines of comments in the default
file. There may be some people who hold both opinions, of course.

> I think the point of the discussions here is that we want to establish
> an alternate config file (or set of config files) in which the
> expectations are different: no promise to preserve any comments, no
> intention to be human-friendly for editing, etc.  In one sense this
> would be the same machine-readable format, since the backend is still
> going to parse it with guc-file.l; but changing the human expectations
> can make the editing problem much simpler.

I don't think this idea is without merit, but I don't think it's a
silver bullet, either. If you can change the human expectations for
some file that gets processed along with postgresql.conf, you can
change the expectations for postgresql.conf itself. In fact, you'll
have to: adding more files is BY DEFINITION going to change the
interpretation of postgresql.conf. It will either be the magic bullet
file that overrides the other file, or visca versa - rather than, as
is the case in a default install today, being THE configuration file.

One of the issues that we need to face is: how many new files? There
seems to be some sentiment that we can just "drop in" new files and
expect things to work. I think that's likely to lead to chaos.
Having TWO files - one for $EDITOR and one for tools - may be
manageable. There will be some user confusion as to which one is in
charge, but there are only two choices, so maybe it's not too bad.
But having a whole directory full of files emitted by different tools
sounds like a disaster, and therefore it seems to me that there is no
getting around the need to have a tool which can merge new settings
into an existing configuration file.

It would be completely logical to break up the configuration file into
subfiles by TOPIC. That would complicate things for tool-writers
because they would need to get each setting into the proper file, and
we currently don't have any infrastructure for that. But that's not
why people want this feature anyway. What they want is to be able to
deposit new settings and have them take effect without parsing a
config file. But they can do that today. Just open postgresql.conf
for append, write a newline in case the file didn't already end with
one, write your settings, and close the file. Your settings win
because they are last. The problem is - now you've left a mess for
someone else to clean up. Overtime duplicates will accumulate. The
order of settings won't be preserved. Comments won't be properly
updated. But writing to another file doesn't actually fix any of
that. Merging settings (either in postgresql.conf or in a separate
persistent.conf) does, at least in part.

...Robert

> Let's see...if I remember correctly from the last two rounds of this
> discussion, this is the point where someone pops up and says that
> switching to XML for the postgresql.conf will solve this problem.
> Whoever does that this time goes into the ring with Kevin and I, but
> they don't get a club. (All fight proceeds to benefit SPI of course).

XML is soooo last-week. JSON!

Oh, wait, we're PostgreSQL, we're not that mainstream. YAML!

--Josh

On Wed, Oct 28, 2009 at 4:24 PM, Josh Berkus <josh(at)agliodbs(dot)com> wrote:
>
>> Let's see...if I remember correctly from the last two rounds of this
>> discussion, this is the point where someone pops up and says that
>> switching to XML for the postgresql.conf will solve this problem.
>> Whoever does that this time goes into the ring with Kevin and I, but
>> they don't get a club.  (All fight proceeds to benefit SPI of course).
>
> XML is soooo last-week.  JSON!
>
> Oh, wait, we're PostgreSQL, we're not that mainstream.  YAML!

Definitely ASN.1

...Robert

On Wed, 28 Oct 2009, Robert Haas wrote:

> It would be completely logical to break up the configuration file into
> subfiles by TOPIC. That would complicate things for tool-writers
> because they would need to get each setting into the proper file, and
> we currently don't have any infrastructure for that.

Already done:

# select name,category from pg_settings limit 1;
name | category
------------------+-------------------------------------------------------------------
add_missing_from | Version and Platform Compatibility / Previous
PostgreSQL Versions

You could make one per category, and pgtune for example already knows all
this info. The somewhat arbitrary category assignments Josh put things
into are what Peter was complaining about upthread. Questions like "is
'effective_cache_size' a memory parameters or an optimizer one?" show why
this is not trivial to do well.

--
* Greg Smith gsmith(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD

On Wed, Oct 28, 2009 at 4:52 PM, Greg Smith <gsmith(at)gregsmith(dot)com> wrote:
> On Wed, 28 Oct 2009, Robert Haas wrote:
>
>> It would be completely logical to break up the configuration file into
>> subfiles by TOPIC.  That would complicate things for tool-writers
>> because they would need to get each setting into the proper file, and
>> we currently don't have any infrastructure for that.
>
> Already done:
>
> # select name,category from pg_settings limit 1;
>       name       |                             category
> ------------------+-------------------------------------------------------------------
>  add_missing_from | Version and Platform Compatibility / Previous PostgreSQL
> Versions
>
> You could make one per category, and pgtune for example already knows all
> this info.  The somewhat arbitrary category assignments Josh put things into
> are what Peter was complaining about upthread.  Questions like "is
> 'effective_cache_size' a memory parameters or an optimizer one?" show why
> this is not trivial to do well.

I stand corrected. I think the basic thrust of the paragraph stands -
this is not why people are asking for the feature, or if it is that
hasn't been articulated or discussed.

...Robert

Greg Smith wrote:
> On Wed, 28 Oct 2009, Josh Berkus wrote:
>
>> It's the basic and unsolvable issue of how do you have a file which is
>> both perfectly human-readable-and-editable *and* perfectly
>> machine-readable-and-editable at the same time.
>
> Let's see...if I remember correctly from the last two rounds of this
> discussion, this is the point where someone pops up and says that
> switching to XML for the postgresql.conf will solve this problem.
> Whoever does that this time goes into the ring with Kevin and I, but
> they don't get a club. (All fight proceeds to benefit SPI of course).
>
>

That's precisely why I didn't get into this discussion (you guys are scary).

It really does seem like deja vu all over again. I'm usually good for a
given argument once.

cheers

andrew

2009/10/27 Simon Riggs <simon(at)2ndquadrant(dot)com>:
> On Tue, 2009-10-27 at 00:38 -0400, Greg Smith wrote:
>> new feature
>
> One additional point that would be useful is a way to match up the usage
> of custom_variable_classes with this new style of .conf file processing.
>
> At the moment if you wish to add a custom variable class everybody needs
> to edit the *same* parameter. Finding which one to edit could be a
> little difficult with a whole directory to search in.
>
> I propose a new form of processing for that variable: each new parameter
> instance is added to last one, rather than replacing it.
> e.g.
> custom_variable_class = 'x'
> custom_variable_class = 'y'
> custom_variable_class = 'z'
> is equivalent to
> custom_variable_classes = 'x,y,z'
>
> That allows NewFeatureX to drop in a file called "newfeaturex.conf",
> which looks like this
>
> custom_variable_class = 'newfeaturex'
> newfeaturex.param1 = x
> newfeaturex.param2 = y
> newfeaturex.param3 = z
>
> This requires no editing of any other files, just a straight drop in.
> That will make it much easier to produce real installers/deinstallers
> for add-in modules.

I understand, but this behave is confusing. Maybe better is using some
trivial keywords like append, delete, reset

so
append custom_variable_class = 'x'
append custom_variable_class = 'y'
append custom_variable_class = 'z'

is custom_variable_classes = 'x,y,z'

Regards
Pavel

>
> --
>  Simon Riggs           www.2ndQuadrant.com
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

On Tue, 2009-10-27 at 20:40 -0700, Josh Berkus wrote:
> If you require that a tool (or SET PERISTENT) parse through a file in
> order to change one setting, then you've just doubled or tripled the
> code size of the tool, as well as added a host of failure conditions
> which wouldn't have existed otherwise.

Hehe, this tool already exists. It's called postconf. And it actually
works. With PostgreSQL! Just rename your postgresql.conf to main.cf
for the time being. ;-)

On Wed, 2009-10-28 at 09:39 -0700, Greg Stark wrote:
> On Wed, Oct 28, 2009 at 7:33 AM, Alvaro Herrera
> <alvherre(at)commandprompt(dot)com> wrote:
> > Greg Smith escribió:
> >
> >> This sounds familiar...oh, that's right, this is almost the same
> >> algorithm pgtune uses. And it sucks,
>
> It's also a blatant violation of packaging rules for Debian if not
> every distribution. If you edit the user's configuration file then
> there's no way to install a modified default configuration file. You
> can't tell the automatic modifications apart from the user's
> modifications. So the user will get a prompt asking if he wants the
> new config file or to keep his modifications which he never remembered
> making.

This is not quite accurate. What the Debian policy says is that local
changes to configuration files must be preserved during package
upgrades. You are free to implement this in a variety of ways. One way
to do it is to mark the file a "conffile" with dpkg, and then dpkg will
handle the upgrades. If you mark a configuration file a "conffile",
then packages' maintainer scripts are not allowed to touch the file
(because dpkg handles it).

But this is irrelevant for the postgresql package, because
postgresql.conf is not a conffile, primarily because it is created by
the postgresql package's maintainer script in the first place (via
initdb).

Moreover, it is not illegal for a program or a routine that is
explicitly invoked by a user to modify a configuration file (or a
"conffile" even). The only policy is again packages' maintainer scripts
(preinst, postint, prerm, postrm, for those reading along) modifying
"conffiles" *automatically* during package installation or removal.

So from a Debian packaging policy point of view, none of the schemes
described so far appear to be disallowed outright.

Le dimanche 25 octobre 2009 10:08:33, Peter Eisentraut a écrit :
> On lör, 2009-10-24 at 13:32 -0400, Greg Smith wrote:
> > Regardless, the UI I was hoping for was to make the default
> > postgresql.conf file end with a line like this:
> >
> > directory 'conf'
>
> I think something like is this is definitely more understandable for
> users and less overkill in the implementation.
>
> As a file point, I would prefer something like
>
> include 'whatever/*.conf'
>
> that is, listing the files as a glob pattern instead of naming a
> directory. Because packaging tools, editors, etc. will leave backup and
> temporary files lying around that you don't want to include, and we
> don't want to get involved into knowing all the naming patterns that
> people might want to use for those.
>
> Actually,
>
> include 'postgresql.conf.d/*.conf'

+1

>
> sounds nice as a default.
>

--
Cédric Villemain
Administrateur de Base de Données
Cel: +33 (0)6 74 15 56 53
http://dalibo.com - http://dalibo.org

Pavel Stehule wrote:
> 2009/10/27 Simon Riggs <simon(at)2ndquadrant(dot)com>:
>
>> On Tue, 2009-10-27 at 00:38 -0400, Greg Smith wrote:
>>
>>> new feature
>>>
>> One additional point that would be useful is a way to match up the usage
>> of custom_variable_classes with this new style of .conf file processing.
>>
>> At the moment if you wish to add a custom variable class everybody needs
>> to edit the *same* parameter. Finding which one to edit could be a
>> little difficult with a whole directory to search in.
>>
>> I propose a new form of processing for that variable: each new parameter
>> instance is added to last one, rather than replacing it.
>> e.g.
>> custom_variable_class = 'x'
>> custom_variable_class = 'y'
>> custom_variable_class = 'z'
>> is equivalent to
>> custom_variable_classes = 'x,y,z'
>>
>> That allows NewFeatureX to drop in a file called "newfeaturex.conf",
>> which looks like this
>>
>> custom_variable_class = 'newfeaturex'
>> newfeaturex.param1 = x
>> newfeaturex.param2 = y
>> newfeaturex.param3 = z
>>
>> This requires no editing of any other files, just a straight drop in.
>> That will make it much easier to produce real installers/deinstallers
>> for add-in modules.
>>
>
> I understand, but this behave is confusing. Maybe better is using some
> trivial keywords like append, delete, reset
>
> so
> append custom_variable_class = 'x'
> append custom_variable_class = 'y'
> append custom_variable_class = 'z'
>
> is custom_variable_classes = 'x,y,z'
>
>

Why not allow something like += or .= instead of the = to denote
appending to a list?

custom_variable_classes += 'x'

seems a whole lot nicer to me.

cheers

andrew

2009/10/29 Andrew Dunstan <andrew(at)dunslane(dot)net>:
>
>
> Pavel Stehule wrote:
>>
>> 2009/10/27 Simon Riggs <simon(at)2ndquadrant(dot)com>:
>>
>>>
>>> On Tue, 2009-10-27 at 00:38 -0400, Greg Smith wrote:
>>>
>>>>
>>>> new feature
>>>>
>>>
>>> One additional point that would be useful is a way to match up the usage
>>> of custom_variable_classes with this new style of .conf file processing.
>>>
>>> At the moment if you wish to add a custom variable class everybody needs
>>> to edit the *same* parameter. Finding which one to edit could be a
>>> little difficult with a whole directory to search in.
>>>
>>> I propose a new form of processing for that variable: each new parameter
>>> instance is added to last one, rather than replacing it.
>>> e.g.
>>> custom_variable_class = 'x'
>>> custom_variable_class = 'y'
>>> custom_variable_class = 'z'
>>> is equivalent to
>>> custom_variable_classes = 'x,y,z'
>>>
>>> That allows NewFeatureX to drop in a file called "newfeaturex.conf",
>>> which looks like this
>>>
>>> custom_variable_class = 'newfeaturex'
>>> newfeaturex.param1 = x
>>> newfeaturex.param2 = y
>>> newfeaturex.param3 = z
>>>
>>> This requires no editing of any other files, just a straight drop in.
>>> That will make it much easier to produce real installers/deinstallers
>>> for add-in modules.
>>>
>>
>> I understand, but this behave is confusing. Maybe better is using some
>> trivial keywords like append, delete, reset
>>
>> so
>> append custom_variable_class = 'x'
>> append custom_variable_class = 'y'
>> append custom_variable_class = 'z'
>>
>> is custom_variable_classes = 'x,y,z'
>>
>>
>
> Why not allow something like += or .= instead of the = to denote appending
> to a list?
>
>   custom_variable_classes += 'x'
>
> seems a whole lot nicer to me.
>
-1