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

On Tue, Jan 14, 2025 at 9:46 PM Ashutosh Bapat
<ashutosh(dot)bapat(dot)oss(at)gmail(dot)com> wrote:
>
> On Tue, Jan 14, 2025 at 3:15 AM Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com> wrote:
> >
> > On Mon, Jan 13, 2025 at 1:31 AM Ashutosh Bapat
> > <ashutosh(dot)bapat(dot)oss(at)gmail(dot)com> wrote:
> > >
> > > On Mon, Jan 13, 2025 at 2:52 PM vignesh C <vignesh21(at)gmail(dot)com> wrote:
> > > >
> > > > I felt that the only disadvantage with this approach is that we
> > > > currently wait for all in-progress transactions to complete before
> > > > enabling logical decoding. If a long-running transaction exists and
> > > > the session enabling logical decoding fails—due to factors like
> > > > statement_timeout, transaction_timeout,
> > > > idle_in_transaction_session_timeout, idle_session_timeout, or any
> > > > other failure. This would require restarting the wait. During this
> > > > time, there's a risk that a new long-running transaction could start,
> > > > further delaying the process. Probably this could be solved if the
> > > > waiting is done from any of the background processes through
> > > > PGC_SIGHUP. While this type of failure is likely rare, I’m unsure
> > > > whether we should consider this scenario.
> > >
> > > A related question: While the operation is waiting for already running
> > > transactions to end, the backends whose transactions have finished may
> > > start new transactions. When we switch WAL from 'replica' to
> > > 'logical', there may be some transactions running. Will this lead to
> > > WAL stream with mixes WAL records - some with logical information and
> > > some without?
> >
> > Yes. There could be mixed WAL records until all running transactions complete.
> >
> > > Do we need to adjust logical decoding to tackle this
> > > situation? Is there a chance that some WAL from same transaction have
> > > logical information and some do not?
> >
> > In the current approach, we first enable logical info WAL-logging to
> > let newly started transactions do logical info WAL-logging, then allow
> > the logical decoding only after all running transactions complete.
> > Therefore, at the time when we allow the logical decoding, all WAL
> > records are written with logical information.
>
> Thanks for the clarification. Let's consider that T1, T2, T3 were
> running when the request to enable logical decoding came in. Let's say
> T1 finished first, then T2 and then T3. But in-the mean time T4, T5
> and T6 were started, which logged WAL with logical information. While
> T2, T3, T4, T5, T6 all are running simultaneously, there will be mixed
> WAL logs (some with logical information and some without). When T3
> ends, logical decoding will be allowed. If a logical decoding process
> is started at this point, it will start reading WAL from the earliest
> of T4, T5 or T6 and hence it will encounter WAL written by T2 or T3
> which do not have logical information.

IIUC we should prevent a logical slot from reserving the WAL until T1,
T2, and T3 finishes. That is, we wait for T1, T2, and T3 to finish
before calling ReplicationSlotReserveWal() where we decide the start
point for reading WAL. That way, the logical decoding will start
reading WAL from the point after the latest commit record of T1, T2,
and T3 (i.e. T3 in this case), meaning that there is no mixed WAL
after this point and T4, T5, and T6 would not be included in the
logical decoding.

> Will the mixed logs create a problem for an ongoing physical stream replication?

I don't see any problem for physical stream replication in terms of
mixed logs. We write a WAL record to enable the logical decoding after
the point where we can ensure there are no mixed WAL records. So the
logical decoding on standbys also can start reading WAL records from
that point.

Regards,

--
Masahiko Sawada
Amazon Web Services: https://aws.amazon.com