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

On Mon, Jul 21, 2025 at 10:48 AM shveta malik <shveta(dot)malik(at)gmail(dot)com> wrote:
>
> On Fri, Jul 18, 2025 at 3:03 PM Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
> >
> > On Fri, Jul 18, 2025 at 2:27 PM shveta malik <shveta(dot)malik(at)gmail(dot)com> wrote:
> > >
> > > On Thu, Jul 17, 2025 at 3:06 PM Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
> > > >
> > > > On Wed, Jun 18, 2025 at 3:23 PM shveta malik <shveta(dot)malik(at)gmail(dot)com> wrote:
> > > > >
> > > > > On Wed, Jun 18, 2025 at 2:39 PM Bertrand Drouvot
> > > > > <bertranddrouvot(dot)pg(at)gmail(dot)com> wrote:
> > > > > >
> > > > > > Hi,
> > > > > >
> > > > > > On Tue, Jun 10, 2025 at 02:00:55PM -0700, Masahiko Sawada wrote:
> > > > > > > > > > > > 0001 patch allows us to create a logical slot without WAL reservation.
> > > > > >
> > > > > > Thanks for the patch and sorry to be late in this conversation.
> > > > > >
> > > > > > The thing that worry me a bit with this is that that could be easy to attempt
> > > > > > to use the slot "by mistake" and then (as a consequence) trigger WAL reservation
> > > > > > by mistake on the primary. I think that this mistake is more likely to happen
> > > > > > with a logical slot as compared to a physical slot.
> > > > > >
> > > > >
> > > > > Yes, agreed. Another concern is the possibility of someone
> > > > > intentionally using it and associating it with a subscription. If the
> > > > > subscription is later dropped, it could also cause the slot to be
> > > > > removed.
> > > > >
> > > >
> > > > IIUC, your main concern here is that the last slot on
> > > > primary/publisher could be dropped unintentionally by the user leading
> > > > to invalidating the logical slots on any physical-standby connected
> > > > with publisher, right?
> > > >
> > >
> > > Right.
> > >
> > > > What if we make DROP SUBSCRIPTION fail if it can lead to removal of
> > > > the last slot on publisher and allow DROP to succeed when the
> > > > subscription's drop_slot_force (a new subscription option) is set?
> > > > Now, users can still be allowed to Drop the subscription, if it
> > > > disassociates the subscription from the slot by using method explained
> > > > in docs [1] (See Notes section). Similarly when a user is trying to
> > > > drop the last logical slot via pg_drop_replication_slot, we will allow
> > > > it only with the force option. This should ensure that the user is
> > > > aware of the consequences of dropping the last slot.
> > > >
> > >
> > > One concern I have is regarding the default setting of
> > > 'force_slot_drop' . I assume the default value of this new DROP-SUB
> > > argument will be 'false' to prevent customers from inadvertently
> > > dropping the last slot on the publisher. But, would this be
> > > acceptable, considering that users may have DROP-SUBSCRIPTION commands
> > > in their scripts which would suddenly stop dropping slot now?
> > >
> >
> > That would only happen when users use this new idea of enabling
> > wal_level to 'logical' on the fly. I think the users having existing
> > setups with pub-sub would have kept the default wal_level to 'logical'
> > on publisher.
> >
>
> Okay, but then we will have to avoid doing the enhancement of getting
> rid of wal_level='logical' as suggested in [1].
>
> Even if we do so, I am not very much convinced for this argument and its value.
> --The value of ''force_slot_drop" will hold its meaning only in a
> conditional scenario. Assuming default is false, then it will still
> drop the slots until it is last slot and wal_level < logical on
> primary. This behavior can seem a bit unintuitive or confusing from
> the user's perspective.
> --If the user is trying to actually retain the slot by giving
> force_slot_drop=false , then how are we going to track that i.e.
> distinguish from its default.
>
> Bertrand has proposed a similar design in [2]. We can revisit that as well once.
>
> I'm continuing to think it through and will share any further thoughts
> if something comes to mind.
>

How about a parameter named 'on_last_logical_slot' with possible
values: 'error', 'warn', 'drop', 'retain'?
Alternatively, we could use 'last_logical_slot_drop_policy' with
values: 'error', 'warn', 'allow'.

These parameters could be supported by both DROP SUBSCRIPTION and
pg_drop_replication_slot(), or alternatively implemented as a GUC on
the primary server. The default value should be either 'warn' or,
preferably, 'error' for safer behavior.

It seems more logical to me for this to be a GUC on the primary since
it falls within the primary’s scope.

With this, when the user attempts to drop a subscription associated
with the last slot on the primary, and the GUC is set to 'error', then
the DROP SUBSCRIPTION command should fail with a message like:

ERROR: cannot drop last logical slot; logical decoding would be
disabled on primary.
HINT: Disassociate subscription from the slot by executing ALTER
SUBSCRIPTION ... SET (slot_name = NONE)

And if the user tries to do pg_drop_replication_slot() on primary and
if it is the last logical slot, then error should be:

ERROR: cannot drop last logical slot; logical decoding would be disabled
HINT: Set last_logical_slot_drop_policy= 'allow' to override.

Thoughts?

thanks
Shveta