Re: SQL command to edit postgresql.conf, with comments

Dimitri Fontaine <dimitri(at)2ndQuadrant(dot)fr> writes:
> cat postgresql.conf.d/work_mem
> 16MB
> This database needs at least such a value.
> Note it's overridden in some ROLEs setup.
>
>
> With such a format (name is filename, value is first line content's,
> rest is comments), it's easy to preserve comments and have them machine
> editable. What do I miss?

Allow me to insist on this some more, because there's something
important going on here. The other proposal (.auto) have a major failure
mode that I don't think is acceptable.

SET PERMANENT work_mem TO '8 MB';
select pg_reload_conf();

There's simply no way after this sequence to guess the current active
value of work_mem, because it could be overridden in the non-automatic
file. How do you work with such a system?

So, back to my funny proposal. It does not have the problem above
because it's either postgresql.conf or postgresql.conf.d, not both. A
single source of authority.

I'm being told that we're talking about something over 200 files and
that's too many. That's a problem size we already know how to handle, I
would say:

dim ~/dev/PostgreSQL/postgresql-extension ls -C1 doc/src/sgml/ref/ |wc -l
166

Ok, now, some people want to still be able to edit the files by hand
because it's the way they do it now. Well, first, that's still possible
and easy to script if so you care, then again, open an editor a prepare
a SQL script:

set permanent foo to bar with comment 'blah';
set …

Then you psql -f the script and you're done. So you use the "remote
edit" feature to be able to edit a single file rather than a bunch of
them if so you want.

Another option in the same spirit would be to prepare a file in the CSV
format (name;value) and have a script that COPY it into some table, then
SELECT set_config(name, value, 'permanent')
FROM import_setup_table;

With a CSV foreign data wrapper at the door, you don't even have to use
an intermediate table where to direct your COPY.

Executive Summary: you can still edit some file the old way, you just
need a tool to then issue the SQL for you rather than reload.

Now, there are some comments that are not tied to any setting in
particular. We could extend my idea to include a .comments file in the
directory, and a command to edit it.

We could make it so that COMMENT ON SETTING would just amend the given
GUC's file, and that COMMENT ON CONFIG allow you to edit the main
.comment file.

Then, there's another feature here, which is having a single comment
entry for more than one parameter. Again, we could support it rather
easily by having directories to group files, and the common comment
would be the groupname/.comment file entry. Add some new commands to
manage that, and refuse to consider the setup at all as soon as there's
file name duplication in there.

CREATE SETTING GROUP name WITH foo, bar, ... AND COMMENT 'foobar';
CREATE SETTING GROUP other WITH baz, foo;
NOTICE: foo was in the group name, it's been moved to other.

Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support