Re: Report checkpoint progress with pg_stat_progress_checkpoint (was: Report checkpoint progress in server logs)

> > > > Thank you for sharing the information. 'triggering backend PID' (int)
> > > > - can be stored without any problem.
> > >
> > > There can be multiple processes triggering a checkpoint, or at least wanting it
> > > to happen or happen faster.
> >
> > Yes. There can be multiple processes but there will be one checkpoint
> > operation at a time. So the backend PID corresponds to the current
> > checkpoint operation. Let me know if I am missing something.
>
> If there's a checkpoint timed triggered and then someone calls
> pg_start_backup() which then wait for the end of the current checkpoint
> (possibly after changing the flags), I think the view should reflect that in
> some way. Maybe storing an array of (pid, flags) is too much, but at least a
> counter with the number of processes actively waiting for the end of the
> checkpoint.

Okay. I feel this can be added as additional field but it will not
replace backend_pid field as this represents the pid of the backend
which triggered the current checkpoint. Probably a new field named
'processes_wiating' or 'events_waiting' can be added for this purpose.
Thoughts?

> > > > 'checkpoint or restartpoint?'
> > >
> > > Do you actually need to store that? Can't it be inferred from
> > > pg_is_in_recovery()?
> >
> > AFAIK we cannot use pg_is_in_recovery() to predict whether it is a
> > checkpoint or restartpoint because if the system exits from recovery
> > mode during restartpoint then any query to pg_stat_progress_checkpoint
> > view will return it as a checkpoint which is ideally not correct. Please
> > correct me if I am wrong.
>
> Recovery ends with an end-of-recovery checkpoint that has to finish before the
> promotion can happen, so I don't think that a restart can still be in progress
> if pg_is_in_recovery() returns false.

Probably writing of buffers or syncing files may complete before
pg_is_in_recovery() returns false. But there are some cleanup
operations happen as part of the checkpoint. During this scenario, we
may get false value for pg_is_in_recovery(). Please refer following
piece of code which is present in CreateRestartpoint().

if (!RecoveryInProgress())
replayTLI = XLogCtl->InsertTimeLineID;

Thanks & Regards,
Nitin Jadhav

On Thu, Feb 17, 2022 at 10:57 PM Julien Rouhaud <rjuju123(at)gmail(dot)com> wrote:
>
> Hi,
>
> On Thu, Feb 17, 2022 at 10:39:02PM +0530, Nitin Jadhav wrote:
> > > > Thank you for sharing the information. 'triggering backend PID' (int)
> > > > - can be stored without any problem.
> > >
> > > There can be multiple processes triggering a checkpoint, or at least wanting it
> > > to happen or happen faster.
> >
> > Yes. There can be multiple processes but there will be one checkpoint
> > operation at a time. So the backend PID corresponds to the current
> > checkpoint operation. Let me know if I am missing something.
>
> If there's a checkpoint timed triggered and then someone calls
> pg_start_backup() which then wait for the end of the current checkpoint
> (possibly after changing the flags), I think the view should reflect that in
> some way. Maybe storing an array of (pid, flags) is too much, but at least a
> counter with the number of processes actively waiting for the end of the
> checkpoint.
>
> > > > 'checkpoint or restartpoint?'
> > >
> > > Do you actually need to store that? Can't it be inferred from
> > > pg_is_in_recovery()?
> >
> > AFAIK we cannot use pg_is_in_recovery() to predict whether it is a
> > checkpoint or restartpoint because if the system exits from recovery
> > mode during restartpoint then any query to pg_stat_progress_checkpoint
> > view will return it as a checkpoint which is ideally not correct. Please
> > correct me if I am wrong.
>
> Recovery ends with an end-of-recovery checkpoint that has to finish before the
> promotion can happen, so I don't think that a restart can still be in progress
> if pg_is_in_recovery() returns false.