Re: Bump default wal_level to logical

On Tue, Jun 9, 2020 at 1:20 PM Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:

> On Tue, Jun 9, 2020 at 2:31 PM Magnus Hagander <magnus(at)hagander(dot)net>
> wrote:
> >
> > On Tue, Jun 9, 2020 at 10:53 AM Kyotaro Horiguchi <
> horikyota(dot)ntt(at)gmail(dot)com> wrote:
> >>
> >> At Tue, 9 Jun 2020 08:52:24 +0200, Peter Eisentraut <
> peter(dot)eisentraut(at)2ndquadrant(dot)com> wrote in
> >> > On 2020-06-08 23:32, Andres Freund wrote:
> >> > > On 2020-06-08 13:27:50 -0400, Tom Lane wrote:
> >> > >> If we can allow wal_level to be changed on the fly, I agree that
> would
> >> > >> help reduce the pressure to make the default setting more
> expensive.
> >> > >> I don't recall why it's PGC_POSTMASTER right now, but I suppose
> there
> >> > >> was a reason for that ...
> >> > > There's reasons, but IIRC they're all solvable with reasonable
> >> > > effort. I
> >> > > think most of it boils down to only being able to rely on the new
> >> > > wal_level after a while. For minimal->recovery we basically need a
> >> > > checkpoint started after the change in configuration, and for
> >> > > recovery->logical we need to wait until all sessions have a) read
> the
> >> > > new config setting b) finished the transaction that used the old
> >> > > setting.
> >> >
> >> > The best behavior from a user's perspective would be if the WAL level
> >> > automatically switched to logical if logical replication slots are
> >> > present. You might not even need 'logical' as an actual value of
> >> > wal_level anymore, you just need to keep a flag in shared memory that
> >> > records whether at least one logical slot exists.
> >>
> >> Currently logical slots cannot be created while wal_level <
> >> logical. Thus a database that has a logical slot must have been once
> >> executed with wal_level >= logical before the creation of the slot.
> >>
>
> I think the creation of slot would take a lot more time in that case
> as it needs to wait for existing transactions to finish which I feel
> could be confusing to users. Sure, the cost would have to be incurred
> the first time but still the user might tempt to cancel such an
> operation if he is not aware of the internals.
>

Yeah, I am unsure if this is doable, but I think that's what Peter was
trying to explain, because that's what would be most user-friendly. But it
may definitely not be worth the complexity, I'm guessing.

Being able to change wal_level on reload instead of restart would be less
user friendly than that, but more user friendly than what we have now.

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