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

On Fri, Jul 18, 2025 at 12:26 AM Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com> wrote:
>
> On Thu, Jul 17, 2025 at 2:36 AM Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
> >
> > 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.
>
> I think even if we prevent the last logical replication that was used
> for logical replication from being removed, the primary would continue
> to accumulate WALs for that slot. I think it actually doesn't need to
> hold WALs and dead catalog tuples in order to keep the effective WAL
> level 'logical'. So probably it's better to 'reset' the slot, meaning
> to clear the last slot's restart_lsn and catalog_xmin? Using such
> option would work in interactive case but I'm not sure how works in
> tools like shell scripts.
>

We can reset the slot's properties like catalog_xmin or restart_lsn at
the time of DROP SUBSCRIPTION even if it is the last logical slot on
publisher. We can probably use ALTER_REPLICATION_SLOT to do it. If we
want to do this, the steps during DROP SUBSCRIPTION would be to check
if the slot to be dropped is the last logical slot, if so, then we
will simply reset some of its elements, otherwise drop it. If we can't
drop it, then we can give a WARNING/NOTICE to the user. If a user has
set a new option like force_drop_slot then we will drop the slot
irrespective of whether it is a last slot or not. We can additionally
check wal_level on publisher as well before deciding whether to drop
the slot or not. If the wal_level is logical then we can probably drop
the slot.

--
With Regards,
Amit Kapila.