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

On Fri, Aug 29, 2025 at 9:59 AM Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com> wrote:
>
> On Thu, Aug 28, 2025 at 4:29 AM shveta malik <shveta(dot)malik(at)gmail(dot)com> wrote:
> >
> > On Wed, Aug 27, 2025 at 12:12 PM Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com> wrote:
> > >
> > > On Tue, Aug 26, 2025 at 2:32 AM shveta malik <shveta(dot)malik(at)gmail(dot)com> wrote:
> > > >
> > > > On Tue, Aug 26, 2025 at 12:54 AM Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com> wrote:
> > > > >
> > > > > I've attached the updated patch that incorporated the comments and is
> > > > > rebased to the current HEAD.
> > > > >
> > > >
> > > > Thanks for the patch, please find a few comments concerning LOG messages:
> > >
> > > Thank you for reviewing the patch!
> > >
> > > >
> > > > 1)
> > > > slotsync worker gives LOG:
> > > > LOG: replication slot synchronization requires logical decoding to be enabled
> > > >
> > > > By this LOG message, the user might not know how to enable logical
> > > > decoding. Shall we add HINT/DETAIL similar to other places:
> > > > To enable logical decoding on standby, set "wal_level" >= "logical" or
> > > > create at least one logical slot on the primary server.
> > >
> > > Sounds good.
> > >
> > > >
> > > > 2)
> > > > When we try to create a logical slot on standby, it takes some time
> > > > until runnign-txns are logged on primary. During that wait-time, if we
> > > > drop logical slot on primary disabling logical_deocding on standby,
> > > > then slot-creation fails with:
> > > >
> > > > postgres=# SELECT pg_create_logical_replication_slot('st_slot2',
> > > > 'pgoutput', false, false, false);
> > > > ERROR: canceling statement due to conflict with recovery
> > > > DETAIL: User was using a logical replication slot that must be invalidated.
> > > >
> > > > Do we need to tweak the message a little bit as this new case is is
> > > > not the case of invalidation?
> > >
> > > I think this is the case of invalidation but why do you think it's not?
> > >
> >
> > Sorry, I did not get. Which slot got invalidated? Primary's slot was
> > dropped and standby's slot did not even finish creation. So, I am
> > confused with the detail-message.
>
> Okay, so we would probably need to distinguish between "creating a
> slot" and "using a slot"? Given that this scenario can happen even
> today, It might be worth considering improving the error detail
> message but I think we should do that in a separate patch.
>

Okay, I initially thought that this message is the problem of current
patch. But I tested it without this patch. If create-slot is waiting
on standby, meanwhile we switch wal_level on primary to < logical and
restart the primary server; standby's slot creation eventually fails
with same error:

postgres=# SELECT pg_create_logical_replication_slot('sub22',
'pgoutput', false, false, false);
ERROR: canceling statement due to conflict with recovery
DETAIL: User was using a logical replication slot that must be invalidated.

So, yes we can consider fixing the message separately.

thanks
Shveta