Re: pgsql: Track last_inactive_time in pg_replication_slots.

On Mon, Mar 25, 2024 at 7:51 PM Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>
> On Mon, Mar 25, 2024 at 10:02 AM Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
> > We considered the other two names as last_inactive_at and
> > last_active_time. For the first (last_inactive_at), there was an
> > argument that most other fields that display time ends with _time. For
> > the second (last_active_time), there was an argument that it could be
> > misleading as one could think that it should be updated each time WAL
> > record decoding is happening [1]. The other possibility is to name it
> > last_used_time but I think it won't be much different from
> > last_active_time.
>
> I don't understand the bit about updating it each time WAL record
> decoding is happening. If it's the last active time, and the slot is
> currently active, then the answer is either "right now" or "currently
> undefined." I'd expect to see NULL in the system view in such a case.
> And if that's so, then there's nothing to update each time a record is
> decoded, because it's just still going to show NULL.
>

IIUC, Bertrand's point was that users can interpret last_active_time
as a value that gets updated each time they decode a change which is
not what we are doing. So, this can confuse users. Your expectation of
answer (NULL) when the slot is active is correct and that is what will
happen.

> Why does this field get set to the current time when the slot is
> restored from disk?
>

It is because we don't want to include the time the server is down in
the last_inactive_time. Say, if we are shutting down the server at
time X and the server remains down for another two hours, we don't
want to include those two hours as the slot inactive time. The related
theory is that this field will be used to invalidate inactive slots
based on a threshold (say inactive_timeout). Say, before the shutdown,
we release the slot and set the current_time for last_inactive_time
for each slot and persist that information as well. Now, if the server
is down for a long time, we may invalidate the slots as soon as the
server comes up. So, instead, we just set this field at the time we
read slots for disk and then reset it to 0/NULL as soon as the slot
became active.

--
With Regards,
Amit Kapila.