Re: Proposal for Allow postgresql.conf values to be changed via SQL

On Thursday, November 08, 2012 1:45 AM Robert Haas wrote:
> On Wed, Nov 7, 2012 at 2:50 PM, Josh Berkus <josh(at)agliodbs(dot)com> wrote:
> >> Well, Magnus' proposed implementation supposed that the existing
> values
> >> *have* been loaded into the current session. I agree that with some
> >> locking and yet more code you could implement it without that. But
> this
> >> still doesn't seem to offer any detectable benefit over value-per-
> file.
> >
> > Well, value-per-file is ugly (imagine you've set 40 different
> variables
> > that way) but dodges a lot of complicated issues. And I suppose
> "ugly"
> > doesn't matter, because the whole idea of the auto-generated files is
> > that users aren't supposed to look at them anyway.
>
> That's pretty much how I feel about it, too. I think value-per-file
> is an ugly wimp-out that shouldn't really be necessary to solve this
> problem. It can't be that hard to rewrite a file where every like is
> of the form:
>
> key = 'value'

I also believe that it should be possible to rewrite a file without loading
values into the current session.
One of the solution if we assume that file is of fixed format and each
record (key = 'value') of fixed length can be:

1. While writing .auto file, it will always assume that .auto file contain
all config parameters.
Now as this .auto file is of fixed format and fixed record size, it can
directly write a given record to its particular position.
2. To handle locking issues, we can follow an approach similar to what "GIT"
is doing for editing conf files (using .lock file):
a. copy the latest content of .auto to .auto.lock
b. make all the changes to auto.lock file.
c. at the end of command rename the auto.lock file to .auto file
d. otherwise if SQL COMMAND/function failed in-between we can delete the
".auto.lock" file
3. Two backends trying to write to .auto file
we can use ".auto.lock" as the the lock by trying to create it in
exclusive mode as the first step
of the command. If it already exists then backend needs to wait.

> However, as Josh said upthread, +1 for the implementation that will
> get committed.

With Regards,
Amit Kapila.