Is Recovery actually paused?

> We have an interface to pause the WAL replay (pg_wal_replay_pause) and
> to know whether the WAL replay pause is requested
> (pg_is_wal_replay_paused).  But there is no way to know whether the
> recovery is actually paused or not.  Actually, the recovery process
> might process an extra WAL before pausing the recovery.  So does it
> make sense to provide a new interface to tell whether the recovery is
> actually paused or not?
>
> One solution could be that we convert the XLogCtlData->recoveryPause
> from bool to tri-state variable (0-> recovery not paused 1-> pause
> requested 2-> actually paused).
>
> Any opinion on this?

>
> On Mon, 19 Oct 2020 at 15:11, Dilip Kumar <[hidden email]> wrote:
>
> > We have an interface to pause the WAL replay (pg_wal_replay_pause) and
> > to know whether the WAL replay pause is requested
> > (pg_is_wal_replay_paused).  But there is no way to know whether the
> > recovery is actually paused or not.  Actually, the recovery process
> > might process an extra WAL before pausing the recovery.  So does it
> > make sense to provide a new interface to tell whether the recovery is
> > actually paused or not?
> >
> > One solution could be that we convert the XLogCtlData->recoveryPause
> > from bool to tri-state variable (0-> recovery not paused 1-> pause
> > requested 2-> actually paused).
> >
> > Any opinion on this?
>
> Why would we want this? What problem are you trying to solve?

> > > Why would we want this? What problem are you trying to solve?
> >
> > The requirement is to know the last replayed WAL on the standby so
> > unless we can guarantee that the recovery is actually paused we can
> > never get the safe last_replay_lsn value.
> >
> > > If we do care, why not fix pg_is_wal_replay_paused() so it responds as you wish?
> >
> > Maybe we can also do that but pg_is_wal_replay_paused is an existing
> > API and the behavior is to know whether the recovery paused is
> > requested or not,  So I am not sure is it a good idea to change the
> > behavior of the existing API?
> >
>
> Attached is the POC patch to show what I have in mind.

>
> On Tue, 20 Oct 2020 at 09:50, Dilip Kumar <[hidden email]> wrote:
>
> > > > Why would we want this? What problem are you trying to solve?
> > >
> > > The requirement is to know the last replayed WAL on the standby so
> > > unless we can guarantee that the recovery is actually paused we can
> > > never get the safe last_replay_lsn value.
> > >
> > > > If we do care, why not fix pg_is_wal_replay_paused() so it responds as you wish?
> > >
> > > Maybe we can also do that but pg_is_wal_replay_paused is an existing
> > > API and the behavior is to know whether the recovery paused is
> > > requested or not,  So I am not sure is it a good idea to change the
> > > behavior of the existing API?
> > >
> >
> > Attached is the POC patch to show what I have in mind.
>
> If you don't like it, I doubt anyone else cares for the exact current
> behavior either. Thanks for pointing those issues out.
>
> It would make sense to alter pg_wal_replay_pause() so that it blocks
> until paused.
>
> I suggest you add the 3-value state as you suggest, but make
> pg_is_wal_replay_paused() respond:
> if paused, true
> if requested, wait until paused, then return true
> else false
>
> That then solves your issues with a smoother interface.
>

>
> On Tue, Oct 20, 2020 at 3:00 PM Simon Riggs <[hidden email]> wrote:
> >
> > On Tue, 20 Oct 2020 at 09:50, Dilip Kumar <[hidden email]> wrote:
> >
> > > > > Why would we want this? What problem are you trying to solve?
> > > >
> > > > The requirement is to know the last replayed WAL on the standby so
> > > > unless we can guarantee that the recovery is actually paused we can
> > > > never get the safe last_replay_lsn value.
> > > >
> > > > > If we do care, why not fix pg_is_wal_replay_paused() so it responds as you wish?
> > > >
> > > > Maybe we can also do that but pg_is_wal_replay_paused is an existing
> > > > API and the behavior is to know whether the recovery paused is
> > > > requested or not,  So I am not sure is it a good idea to change the
> > > > behavior of the existing API?
> > > >
> > >
> > > Attached is the POC patch to show what I have in mind.
> >
> > If you don't like it, I doubt anyone else cares for the exact current
> > behavior either. Thanks for pointing those issues out.
> >
> > It would make sense to alter pg_wal_replay_pause() so that it blocks
> > until paused.
> >
> > I suggest you add the 3-value state as you suggest, but make
> > pg_is_wal_replay_paused() respond:
> > if paused, true
> > if requested, wait until paused, then return true
> > else false
> >
> > That then solves your issues with a smoother interface.
> >
>
> Make sense to me, I will change as per the suggestion.

> I have noticed one more issue, the problem is that if the recovery
> process is currently not processing any WAL and just waiting for the
> WAL to become available then the pg_is_wal_replay_paused will be stuck
> forever.  Having said that there is the same problem even if we design
> the new interface which checks whether the recovery is actually paused
> or not because until the recovery process gets the next wal it will
> not check whether the recovery pause is requested or not so the actual
> recovery paused flag will never be set.
>
> One idea could be, if the recovery process is waiting for WAL and a
> recovery pause is requested then we can assume that the recovery is
> paused because before processing the next wal it will always check
> whether the recovery pause is requested or not.

>
> On Tue, Oct 20, 2020 at 5:59 PM Dilip Kumar <[hidden email]> wrote:
> >
> > On Tue, Oct 20, 2020 at 3:00 PM Simon Riggs <[hidden email]> wrote:
> > >
> > > On Tue, 20 Oct 2020 at 09:50, Dilip Kumar <[hidden email]> wrote:
> > >
> > > > > > Why would we want this? What problem are you trying to solve?
> > > > >
> > > > > The requirement is to know the last replayed WAL on the standby so
> > > > > unless we can guarantee that the recovery is actually paused we can
> > > > > never get the safe last_replay_lsn value.
> > > > >
> > > > > > If we do care, why not fix pg_is_wal_replay_paused() so it responds as you wish?
> > > > >
> > > > > Maybe we can also do that but pg_is_wal_replay_paused is an existing
> > > > > API and the behavior is to know whether the recovery paused is
> > > > > requested or not,  So I am not sure is it a good idea to change the
> > > > > behavior of the existing API?
> > > > >
> > > >
> > > > Attached is the POC patch to show what I have in mind.
> > >
> > > If you don't like it, I doubt anyone else cares for the exact current
> > > behavior either. Thanks for pointing those issues out.
> > >
> > > It would make sense to alter pg_wal_replay_pause() so that it blocks
> > > until paused.
> > >
> > > I suggest you add the 3-value state as you suggest, but make
> > > pg_is_wal_replay_paused() respond:
> > > if paused, true
> > > if requested, wait until paused, then return true
> > > else false
> > >
> > > That then solves your issues with a smoother interface.
> > >
> >
> > Make sense to me, I will change as per the suggestion.
>
> I have noticed one more issue, the problem is that if the recovery
> process is currently not processing any WAL and just waiting for the
> WAL to become available then the pg_is_wal_replay_paused will be stuck
> forever.  Having said that there is the same problem even if we design
> the new interface which checks whether the recovery is actually paused
> or not because until the recovery process gets the next wal it will
> not check whether the recovery pause is requested or not so the actual
> recovery paused flag will never be set.
>
> One idea could be, if the recovery process is waiting for WAL and a
> recovery pause is requested then we can assume that the recovery is
> paused because before processing the next wal it will always check
> whether the recovery pause is requested or not.

>
> At Wed, 21 Oct 2020 11:14:24 -0400, Robert Haas <[hidden email]> wrote in
> > On Wed, Oct 21, 2020 at 7:16 AM Dilip Kumar <[hidden email]> wrote:
> > > One idea could be, if the recovery process is waiting for WAL and a
> > > recovery pause is requested then we can assume that the recovery is
> > > paused because before processing the next wal it will always check
> > > whether the recovery pause is requested or not.
> ..
> > However, it might be better to implement this by having the system
> > absorb the pause immediately when it's in this state, rather than
> > trying to detect this state and treat it specially.
>
> The paused state is shown in pg_stat_activity.wait_event and it is
> strange that pg_is_wal_replay_paused() is inconsistent with the
> column.

> On Thu, Oct 22, 2020 at 7:50 PM Dilip Kumar <[hidden email]> wrote:
> >
> > On Thu, Oct 22, 2020 at 6:59 AM Kyotaro Horiguchi
> > <[hidden email]> wrote:
> > >
> > > At Wed, 21 Oct 2020 11:14:24 -0400, Robert Haas <[hidden email]> wrote in
> > > > On Wed, Oct 21, 2020 at 7:16 AM Dilip Kumar <[hidden email]> wrote:
> > > > > One idea could be, if the recovery process is waiting for WAL and a
> > > > > recovery pause is requested then we can assume that the recovery is
> > > > > paused because before processing the next wal it will always check
> > > > > whether the recovery pause is requested or not.
> > > ..
> > > > However, it might be better to implement this by having the system
> > > > absorb the pause immediately when it's in this state, rather than
> > > > trying to detect this state and treat it specially.
> > >
> > > The paused state is shown in pg_stat_activity.wait_event and it is
> > > strange that pg_is_wal_replay_paused() is inconsistent with the
> > > column.
> >
> > Right
> >
> > To make them consistent, we need to call recoveryPausesHere()
> > > at the end of WaitForWALToBecomeAvailable() and let
> > > pg_wal_replay_pause() call WakeupRecovery().
> > >
> > > I think we don't need a separate function to find the state.
> >
> > The idea makes sense to me.  I will try to change the patch as per the
> > suggestion.
>
> Here is the patch based on this idea.

> On Thu, 22 Oct 2020 20:36:48 +0530
> Dilip Kumar <[hidden email]> wrote:
>
> > On Thu, Oct 22, 2020 at 7:50 PM Dilip Kumar <[hidden email]> wrote:
> > >
> > > On Thu, Oct 22, 2020 at 6:59 AM Kyotaro Horiguchi
> > > <[hidden email]> wrote:
> > > >
> > > > At Wed, 21 Oct 2020 11:14:24 -0400, Robert Haas <[hidden email]> wrote in
> > > > > On Wed, Oct 21, 2020 at 7:16 AM Dilip Kumar <[hidden email]> wrote:
> > > > > > One idea could be, if the recovery process is waiting for WAL and a
> > > > > > recovery pause is requested then we can assume that the recovery is
> > > > > > paused because before processing the next wal it will always check
> > > > > > whether the recovery pause is requested or not.
> > > > ..
> > > > > However, it might be better to implement this by having the system
> > > > > absorb the pause immediately when it's in this state, rather than
> > > > > trying to detect this state and treat it specially.
> > > >
> > > > The paused state is shown in pg_stat_activity.wait_event and it is
> > > > strange that pg_is_wal_replay_paused() is inconsistent with the
> > > > column.
> > >
> > > Right
> > >
> > > To make them consistent, we need to call recoveryPausesHere()
> > > > at the end of WaitForWALToBecomeAvailable() and let
> > > > pg_wal_replay_pause() call WakeupRecovery().
> > > >
> > > > I think we don't need a separate function to find the state.
> > >
> > > The idea makes sense to me.  I will try to change the patch as per the
> > > suggestion.
> >
> > Here is the patch based on this idea.
>
> I reviewd this patch.
>
> First, I made a recovery conflict situation using a table lock.
>
> Standby:
> #= begin;
> #= select * from t;
>
> Primary:
> #= begin;
> #= lock t in ;
>
> After this, WAL of the table lock cannot be replayed due to a lock acquired
> in the standby.
>
> Second, during the delay, I executed pg_wal_replay_pause() and
> pg_is_wal_replay_paused(). Then, pg_is_wal_replay_paused was blocked until
> max_standby_streaming_delay was expired, and eventually returned true.
>
> I can also see the same behaviour by setting recovery_min_apply_delay.
>
> So, pg_is_wal_replay_paused waits for recovery to get paused and this works
> successfully as expected.
>
> However, I wonder users don't expect pg_is_wal_replay_paused to wait.
> Especially, if max_standby_streaming_delay is -1, this will be blocked forever,
> although this setting may not be usual. In addition, some users may set
> recovery_min_apply_delay for a large.  If such users call pg_is_wal_replay_paused,
> it could wait for a long time.
>
> At least, I think we need some descriptions on document to explain
> pg_is_wal_replay_paused could wait while a time.