Re: POC: enable logical decoding when wal_level = 'replica' without a server restart

On Tue, Jun 24, 2025 at 2:12 PM Bertrand Drouvot
<bertranddrouvot(dot)pg(at)gmail(dot)com> wrote:
>
> Hi,
>
> On Tue, Jun 24, 2025 at 12:13:32AM +0900, Masahiko Sawada wrote:
> > On Mon, Jun 23, 2025 at 7:01 PM Bertrand Drouvot
> > <bertranddrouvot(dot)pg(at)gmail(dot)com> wrote:
> > >
> > > Hi,
> > >
> > > On Mon, Jun 23, 2025 at 05:10:37PM +0900, Masahiko Sawada wrote:
> > > > On Thu, Jun 19, 2025 at 6:00 PM Bertrand Drouvot
> > > > <bertranddrouvot(dot)pg(at)gmail(dot)com> wrote:
> > > > >
> > > > > - pg_activate_logical_decoding() is needed only if there is not already a logical
> > > > > slot on the primary
> > > > > - the drop requires the user to think twice if this is the last logical slot
> > > > > - we don't ask the user to create a logical slot if he does not want to use it
> > > > > on the primary
> > > > >
> > > > > Thoughts?
> > > >
> > > > If there is no logical slot on the primary, how can the user disable
> > > > logical decoding that has been enabled via
> > > > pg_activate_logical_decoding()?
> > >
> > > I was thinking to keep the pg_deactivate_logical_decoding() API proposed
> > > in this thread.
> >
> > Okay. One approach that combines your idea and Shveta's idea is:
> >
> > - a special (empty) logical slot with the reserved slot name can be
> > created and deleted only by SQL functions,
> > pg_activate_logical_decoding() and pg_deactivate_logical_decoding().
> > - this special slot cannot be used by logical decoding.
> > - effective_wal_level is increased and decreased when creating and
> > dropping a slot (i.e., either a normal logical slots or the special
> > logical slot).
>
> Yeah, I think that sounds reasonable and that would avoid users to use
> the slot created with immediately_reserve set to false by mistake.
>

+1.
I think we do need to provide 'immediately_reserve' as a new argument
now for logical slots creation. If the slot is a special one with a
reserved name, it can internally be created with WALs not reserved for
our purpose.

> > > You mean a GUC to both automaticly set effective_wal_level to logical at slot creation
> > > and also decrease effective_wal_level to replica if last replication slot is dropped?
> >
> > What I imagined was to control only the decreasing behavior that could
> > be more problematic than the increase case. But it might be rather
> > confusing (e.g., what if we turn off that behavior and restart the
> > server?).
>
> Right...So not sure we need such a GUC. What about always behave with the
> automatic behavior?
>

Does it make sense to provide a GUC which will have the default set to
automatic but if the user is not interested or having some issues with
new behaviour, he can switch off the GUC, making the new functions
no-op as well?
In absence of such a GUC, users will have absolutely no way to switch
back to old behaviour. Will that be okay?

thanks
Shveta