Обсуждение: Re: POC: enable logical decoding when wal_level = 'replica' without a server restart

On Tue, 31 Dec 2024 at 10:15, Masahiko Sawada <sawada.mshk@gmail.com> wrote:
>
> Hi all,
>
> Logical decoding (and logical replication) are available only when
> wal_level = logical. As the documentation says[1], Using the 'logical'
> level increases the WAL volume which could negatively affect the
> performance. For that reason, users might want to start with using
> 'replica', but when they want to use logical decoding they need a
> server restart to increase wal_level to 'logical'. My goal is to allow
> users who are using 'replica' level to use logical decoding without a
> server restart. There are other GUC parameters related to logical
> decoding and logical replication such as max_wal_senders,
> max_logical_replication_workers, and max_replication_slots, but even
> if users set these parameters >0, there would not be a noticeable
> performance impact. And their default values are already >0. So I'd
> like to focus on making only the wal_level dynamic GUC parameter.
> There are several earlier discussions[2][3] but no one has submitted
> patches unless I'm missing something.
>
> The first idea I came up with is to make the wal_level a PGC_SIGHUP
> parameter. However, it affects not only setting 'replica' to 'logical'
> but also setting 'minimal' to 'replica' or higher. I'm not sure the
> latter case is common and it might require a checkpoint. I don't want
> to make the patch complex for uncommon cases.
>
> The second idea is to somehow allow both WAL-logging logical info and
> logical decoding even when wal_level is 'replica'. I've attached a PoC
> patch for that. The patch introduces new SQL functions such as
> pg_activate_logical_decoding() and pg_deactivate_logical_decoding().
> These functions are available only when wal_level is 'repilca'(or
> higher). In pg_activate_logical_decoding(), we set the status of
> logical decoding stored on the shared memory from 'disabled' to
> 'xlog-logical-info', allowing all processes to write logical
> information to WAL records for logical decoding. But the logical
> decoding is still not allowed. Once we confirm all in-progress
> transactions completed, we switch the status to
> 'logical-decoding-ready', meaning that users can create logical
> replication slots and use logical decoding.

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.

Thoughts?

Regards,
Vignesh

On Mon, Jan 13, 2025 at 2:52 PM vignesh C <vignesh21@gmail.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? 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?

--
Best Wishes,
Ashutosh Bapat

On Mon, Jan 13, 2025 at 1:22 AM vignesh C <vignesh21@gmail.com> wrote:
>
> On Tue, 31 Dec 2024 at 10:15, Masahiko Sawada <sawada.mshk@gmail.com> wrote:
> >
> > Hi all,
> >
> > Logical decoding (and logical replication) are available only when
> > wal_level = logical. As the documentation says[1], Using the 'logical'
> > level increases the WAL volume which could negatively affect the
> > performance. For that reason, users might want to start with using
> > 'replica', but when they want to use logical decoding they need a
> > server restart to increase wal_level to 'logical'. My goal is to allow
> > users who are using 'replica' level to use logical decoding without a
> > server restart. There are other GUC parameters related to logical
> > decoding and logical replication such as max_wal_senders,
> > max_logical_replication_workers, and max_replication_slots, but even
> > if users set these parameters >0, there would not be a noticeable
> > performance impact. And their default values are already >0. So I'd
> > like to focus on making only the wal_level dynamic GUC parameter.
> > There are several earlier discussions[2][3] but no one has submitted
> > patches unless I'm missing something.
> >
> > The first idea I came up with is to make the wal_level a PGC_SIGHUP
> > parameter. However, it affects not only setting 'replica' to 'logical'
> > but also setting 'minimal' to 'replica' or higher. I'm not sure the
> > latter case is common and it might require a checkpoint. I don't want
> > to make the patch complex for uncommon cases.
> >
> > The second idea is to somehow allow both WAL-logging logical info and
> > logical decoding even when wal_level is 'replica'. I've attached a PoC
> > patch for that. The patch introduces new SQL functions such as
> > pg_activate_logical_decoding() and pg_deactivate_logical_decoding().
> > These functions are available only when wal_level is 'repilca'(or
> > higher). In pg_activate_logical_decoding(), we set the status of
> > logical decoding stored on the shared memory from 'disabled' to
> > 'xlog-logical-info', allowing all processes to write logical
> > information to WAL records for logical decoding. But the logical
> > decoding is still not allowed. Once we confirm all in-progress
> > transactions completed, we switch the status to
> > 'logical-decoding-ready', meaning that users can create logical
> > replication slots and use logical decoding.
>
> 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.
>
> Thoughts?

Yeah, delegating the activation to the background process such as the
checkpointer would also be one solution. This would work with the
approach that we enable the logical decoding via
pg_activate_logical_decoding(). On the other hand, if we support
automatically enabling the logical decoding (and logical info logging)
when the first logical slot is created, we might want to have the
process who is creating the logical slot activate the logical decoding
too.

Regards,

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

On Mon, Jan 13, 2025 at 1:31 AM Ashutosh Bapat
<ashutosh.bapat.oss@gmail.com> wrote:
>
> On Mon, Jan 13, 2025 at 2:52 PM vignesh C <vignesh21@gmail.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.

Regards,

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

On Tue, 14 Jan 2025 at 03:03, Masahiko Sawada <sawada.mshk@gmail.com> wrote:
>
> Yeah, delegating the activation to the background process such as the
> checkpointer would also be one solution. This would work with the
> approach that we enable the logical decoding via
> pg_activate_logical_decoding(). On the other hand, if we support
> automatically enabling the logical decoding (and logical info logging)
> when the first logical slot is created, we might want to have the
> process who is creating the logical slot activate the logical decoding
> too.

One possible approach is to delegate the task of updating the wal
level to include logical decoding information during the creation of
the first logical replication slot also to a background process (e.g.,
the checkpoint process) and wait until the wal level has been updated
and reaches ready state. Additionally, we could introduce a new
optional parameter during slot creation to specify whether we should
roll back changes related to updating the WAL level in case of
failure. If a rollback is needed, we could invoke the
logical_decoding_activation_abort_callback. By default, the rollback
could be set to false, since in most failure scenarios, the user will
likely attempt to create the slot again immediately. By then, the WAL
level may have already been updated, eliminating the need to wait for
any new long-running transactions.
An alternative approach could be to skip the rollback of logical info
logging during logical replication slot creation in case of failure,
without introducing a rollback parameter. This is because there may be
concurrent transactions (e.g., parallel slot creation or
pg_activate_logical_decoding from different sessions) that would also
fail. In such cases, the user could be allowed to call
pg_deactivate_logical_decoding if needed.

Regards,
Vignesh

On Tue, Jan 14, 2025 at 3:15 AM Masahiko Sawada <sawada.mshk@gmail.com> wrote:
>
> On Mon, Jan 13, 2025 at 1:31 AM Ashutosh Bapat
> <ashutosh.bapat.oss@gmail.com> wrote:
> >
> > On Mon, Jan 13, 2025 at 2:52 PM vignesh C <vignesh21@gmail.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. That would create a problem.
Thus in order for the logical process to start The transactions T4, T5
and T6 need to be finished as well. Am I correct? If that is so, my
earlier proposal to combine the step to wait for running transactions
to complete for both logical decoding as well as replication slot
creation would not be possible.

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

--
Best Wishes,
Ashutosh Bapat

On Tue, Jan 14, 2025 at 9:46 PM Ashutosh Bapat
<ashutosh.bapat.oss@gmail.com> wrote:
>
> On Tue, Jan 14, 2025 at 3:15 AM Masahiko Sawada <sawada.mshk@gmail.com> wrote:
> >
> > On Mon, Jan 13, 2025 at 1:31 AM Ashutosh Bapat
> > <ashutosh.bapat.oss@gmail.com> wrote:
> > >
> > > On Mon, Jan 13, 2025 at 2:52 PM vignesh C <vignesh21@gmail.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