Re: Add last commit LSN to pg_last_committed_xact()

On Tue, Jan 18, 2022 at 4:32 PM Andres Freund <andres(at)anarazel(dot)de> wrote:
>
> Hi,
>
> On 2022-01-17 18:34:16 -0300, Alvaro Herrera wrote:
> > Maybe it would work to have a single LSN in shared memory, as an atomic
> > variable, which uses monotonic advance[1] to be updated.
>
> That could be a reasonable approach.
>
>
> > Whether this is updated or not would depend on a new GUC, maybe
> > track_latest_commit_lsn. Causing performance pain during transaction commit
> > is not great, but at least this way it shouldn't be *too* a large hit.
>
> What kind of consistency are we expecting from this new bit of information?
> Does it have to be perfectly aligned with visibility? If so, it'd need to
> happen in ProcArrayEndTransaction(), with ProcArrayLock held - which I'd
> consider a complete no-go, that's way too contended.

My use case wouldn't require perfect alignment with visibility (I'm
not sure about the use case Alvaro mentioned in pglogical).

> If it's "just" another piece of work happening "sometime around" transaction
> commit, it'd be a bit less concerning.

That raises the interesting question of where the existing commit_ts
infrastructure and last commit caching falls into that range.

> I wonder if a very different approach could make sense here. Presumably this
> wouldn't need to be queried at a very high frequency, right? If so, what about
> storing the latest commit LSN for each backend in PGPROC? That could be
> maintained without a lock/atomics, and should be just about free.
> pg_last_committed_xact() then would have to iterate over all PGPROCs to
> complete the LSN, but that's not too bad for an operation like that. We'd also
> need to maintain a value for all disconnected backends, but that's also not a hot
> path.

I expect most monitoring setups default to around something like
checking anywhere from every single digit seconds to minutes.

If I read between the lines I imagine you'd see even e.g. every 2s as
not that big of a deal here, right?

Thanks,
James Coleman